The transaction in MySQL is the smallest indivisible unit of work, and the transaction can ensure the integrity of a business
There are multiple SQL statements in a business, and these SQL statements must be successful at the same time or At the same time, it fails. At this time, a transaction is required to ensure this requirement.
For example: a transfer 100 from your bank card to b
update user set money=money-100 where name=’a’;
update user set moeny=money+100 where name=’b’;
These two sql statements must succeed or fail at the same time, which requires a transaction
MySQL is automatically opened for transaction And automatically submit data, so that every SQL statement executed will submit the data to the database, so that a transaction is completed
The record submitted to the database cannot be undone, so if the following SQL statement is executed No, the previous sql cannot be withdrawn.
We can turn off the automatic opening of transactions and automatic submission of data, so that we can set up multiple SQL statements in a transaction, when they are all executed, submit data to the database, so that we can solve the problem of multiple SQL statements Successful operations of the two statements at the same time
Revocation of uncommitted SQL operations as rollback
Turn off automatic open transaction and automatic data submission set autocommit =0;
Open transaction ：Begin/start transaction;
Manually submit data ：commit
Example:
Set the transaction manually and turn off the automatic data submission function. The multiple sql operations we performed on cmd will not change Submit the data to the database to manually start the transaction begin;
We view the database data in another cmd and are not affected by it
We manually submit the data commit
At this time, the operation records in cmd are transferred to the database. Before submitting, a rollback can cancel all sql statements in this transaction
After submitting, the database will modify the data in the database.
Four characteristics of transactions
1: Atomicity Transaction is the smallest unit and cannot be split
2: Consistency SQL statements in a transaction must succeed or fail at the same time
< h4>3: Isolation Different transactions will not affect each other