MySQL transaction processing

MyISAM storage engine in MySQL does not support transactions, InnoDB and BDB support transactions

A transaction is a contiguous set of database operations, which is indivisible, meaning that the set of operations is either fully successful or fails to execute all (actually).

Transactions have the following four standard attributes of the abbreviated acid, commonly referred to as:

Atomicity: Ensure that all operations within the unit of work are completed successfully, otherwise the transaction will be aborted at the point of failure, and the previous operation will be rolled back to the previous state.

Consistency: Ensure that the database has successfully committed a transaction after it has changed state correctly.

Isolation: Makes transactional operations independent and transparent to each other.

Persistence: Ensures that the result of the committed transaction or the effect of the system is still present in the event of a failure.

 There are two main ways to handle MySQL transactions.
1, with Begin,rollback,commit to achieve
Begin a transaction
rollback Transaction rollback
Commit TRANSACTION Acknowledgement
2, directly with set to change the MySQL automatic submission mode
MySQL is automatically submitted by default, that is, you submit a query, it is executed directly! We can pass
Set autocommit=0 prohibit auto-commit
Set autocommit=1 turn on auto-commit
To implement transaction processing.
But note that when you use set autocommit=0, all of your later SQL will be transacted until you end with commit confirmation or rollback, and notice that when you end the transaction, you start a new transaction! Press the first method to only present the current as a transaction!

Go again:

Create a new InnoDB table first

Create Table  int(9)) ENGINE=INNODB;

Operation:

begin ; Insert  into Test value (1); Insert  into Test value (2); commit;

Test it.

begin ; Insert  into Test value (3); Select *  from test; (There are 3 of the results in this case); rollback ; Select *  from test; (No 3 in the result);

MySQL transaction processing