The principle of MySQL transaction

The principle of atomicity, stability and persistence

Atomicity, stability, and persistence are implemented through the redo and undo log files, and both redo and undo files have a cache that we call Redo_buf and undo_buf. Similarly, the database file will have a cache called Data_buf.

4.1 Undo Log File

Undo records the value of the data before the transaction starts, and can recover the data by the value of the undo record when the transaction fails or rollback. For example, the initial values for AA and BB are 3, 5, respectively.

A 事务开始B 记录AA=3到undo_bufC 修改AA=1D 记录BB=5到undo_bufE 修改BB=7F 将undo_buf写到undo(磁盘)G 将data_buf写到datafile(磁盘)H 事务提交

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8

The use of undo guarantees atomicity, stability, and durability
　　if the transaction crashes before F because the data has not been written to disk, the data is not destroyed.
　　If a transaction crashes before G or is rolled back, it can revert to its original state based on undo.
Data is written to disk before the task is committed to ensure durability.
However, simply using undo to ensure atomicity and persistence requires that the data be written to disk before the transaction commits, wasting a lot of I/O.

4.2 Redo/undo log file

By introducing the modified value of the Redo logging data, you can avoid the need for data to be written to disk before the transaction commits, reducing I/O.

A 事务开始B 记录AA=3到undo_bufC 修改AA=1 记录redo_bufD 记录BB=5到undo_bufE 修改BB=7 记录redo_bufF 将redo_buf写到redo（磁盘）G 事务提交

• 1
• 2
• 3
• 4
• 5
• 6
• 7

　　by using Undo to ensure the atomicity of the transaction, redo guarantees durability.
　　F before crashing because all the data is in memory, after recovering the disk before re-flushing the data, the data is not corrupted.
　　The crash between FG can be recovered using redo.
　　The rollback before G can be done using Undo.

5 Transaction Operation Commands

If you need to use transactions, you must choose a database engine that supports transactions, such as InnoDB and Falcon,myisam, and does not support transactions.
By default, MySQL turns on the autocommit mode, which implies that each statement is treated as a transaction, and each SQL is automatically committed. When we use begin or start transcation, the autocommit is suspended until the call commit is displayed. There are two ways to use transactions:

begin;//Start transaction, suspend autocommit insert into T_cart_shopcart (user_id, sku_id, amount, shop_id, status ) values (10001, 10001, 1, 10001, 0); insert into T_cart_shopcart (user_id, sku_id, amount, shop_id, Status) values (10001, 10002, 1, 10001, 0); commit;//COMMIT TRANSACTION, resume auto-commit           

• 1
• 2
• 3
• 4

Set autocommit =0; Pending Auto-Commitinsert into T_cart_shopcart (user_id, sku_id, amount, shop_id, Status) values (10001, 10001, 1, 10001, 0); insert into T_cart_shopcart (user_id, sku_id, amount, shop_id, Status) values (10001, 10002, 1, 10001, 0); commit;//COMMIT TRANSACTION set autocommit = Span class= "Hljs-number" >1; Resume Auto-commit                

• 1
• 2
• 3
• 4
• 5

These two methods have the same effect

The principle of MySQL transaction