A transaction in MySQL

I. The role of affairs

A transaction is a sequence of successive operations that will occur.

Transaction security is to ensure that the results of a series of operations are kept in sync to ensure data integrity.

When a transaction is turned on, the results of the operation are saved to the transaction log, and the data in the table is not modified until the operation has completed committing the transaction.

　Note: Transaction security is supported only by the InnoDB storage engine and the BDB storage engine.

Ii. use of the transaction

transaction;    -- Open Transaction -- SQL statement 1 ... savepoint SP1;         -- set the rollback point -- SQL Statement 2 ... -- If the operation fails, it can be returned to the rollback point with rollback  SP1; -- SQL statement 3 ... -- when the operation is complete, commit the transaction with commit commit;

Third, automatic transaction submission

Whether the transaction is automatically committed is controlled by the system variable autocommit, 1 is on, and 0 is off. The default is enabled.

-- set up automatic transaction submission　　set autocommit:=0; --MySQL = generally used for comparison,  : = is the assignment

Iv. characteristics of the transaction--acid
A:atomic, Atomicity, the operation of the whole transaction is a whole, indivisible, either all succeeds, or all fails.

C:consistency, consistency, and before and after transaction operations, the data in the data table has not changed.

I:isolation, isolation, different transactional operations are isolated from each other and unaffected.

D:durability, persistence, data once submitted, it will not be changed.

Lock mechanism: InnoDB is a row lock by default, but when the transaction operation is not used in the index, it is automatically promoted to a table lock, and the full table retrieves the data.

A transaction in MySQL