Transactions feature Atomicity, Consistency, Isolation, persistence, etc. Their implementation is as follows:
- Atomicity of transactions is achieved through undo log
- Transaction persistence is implemented through redo logs
- Transaction isolation is achieved through (read/write lock +MVCC)
- The consistency of transactions is achieved through atomicity, persistence and isolation
1. Implementation of atomicity
What is atomicity:
A transaction must be treated as an indivisible minimum unit of work. All operations in a transaction must either be committed successfully or rolled back on failure. It is impossible for a transaction to perform only some of the operations, such is the atomicity of the transaction.
The above quote is taken from the book High Performance MySQL, which defines atomicity. Atomicity can be summarized as implementing all fail or all succeed.
I believe everyone understands the above concepts, so how is the database implemented? By rolling back.
A rollback operation is one that requires data to be rolled back to its original state when an error occurs or an explicit rollback statement is executed. Therefore, undo log is used to rollback the data
1.1 Undo Log Generation
Assume that there are two tables bank and Finance. The original data in the table is as shown in the figure below. The undo log generated when inserting, deleting and updating is as shown in the figure below:
As you can see from the figure above, data changes are accompanied by a rollback log:
(1) Rollback logs of the data before modification (Zhangsan,1000) are generated
(2) A rollback log of data (Zhangsan,0) before modification is generated
According to the above process, the following conclusions can be drawn:
- Each data change (INSERT /update/ DELETE) operation is accompanied by the generation of an undo log, and the rollback log must be persisted to disk before the data
- Rollback refers to reverse operations based on rollback logs. For example, delete is reversed to INSERT, insert is reversed to DELETE, and update is reversed to update.
1.2 Rollback Based on the Undo log
To achieve both success and failure, you need to rollback the system based on the undo log when an error occurs or the rollback operation is performed
The rollback operation is to restore the original state. Undo log records the information before the data modification, as well as the information about new and deleted data. According to undo log, rollback statements are generated, such as:
- If a new data record is added to the rollback log, a statement is generated to delete it
- If there is a delete record in the rollback log, a statement is generated to generate that record
- If there is a record of changes in the rollback log, a statement is generated to modify the original data
2. Implementation of persistence
Once a transaction is committed, its changes are permanently stored in the database, so that the modified data will not be lost even if the system crashes.
MySQL tables are stored on disk, which requires disk I/O to be accessed. However, even SSD disk I/O is very performance consuming.
To improve performance InnoDB provides a Buffer Pool that contains a map of disk data pages and can be used as a cache:
- Read data: reads data from the buffer pool first. If not, reads data from disk before putting it into the buffer pool.
- Write data: The system writes data to the buffer pool first. Data in the buffer pool is periodically synchronized to disks.
While this buffer pool approach has brought a quantum leap in performance, it has also introduced new problems, such as the loss of data when the MySQL system is down and the power is off!!
Because our data has already been committed but is in the buffer pool and has not yet been persisted on disk, we urgently need a mechanism to store the committed transaction data for recovery.
That’s where the redo log comes in. Let’s take a look at when redo logs were created.
Redo logs are created after a transaction has started. Redo logs are not written to the redo log file as the transaction commits.
If redo logs are stored and disk IO is involved, why use redo logs?
-
Redo logs are stored sequentially, while cache synchronization is a random operation.
-
Cache synchronization is done on a data page basis, and each transfer is larger than the redo log.
2.1 redo log
Redo logs are used for transaction persistence. The log file consists of two parts: the redo log buffer, which is in memory, and the redo log, which is on disk.
All changes are stored in the log after the transaction is committed. Let’s say I have a table called Tb1 (ID,username) and now I want to insert data (3, ceshi)
start transaction; select balance from bank where name="zhangsan"; // Generate redo log balance=600 update bank set balance= balance-400; Update finance set amount= amount + 400; commit;Copy the code
2.2 What does redo Log do?
To improve performance, mysql does not synchronize every change to disk in real time. Instead, it stores it in a Boffer Pool and uses it as a cache. Background threads are then used to synchronize between the buffer pool and the disk.
So the question is, what if the synchronization fails or the power fails? There is no time to perform the operation shown in red in the figure above. This will cause the modification information of some committed transactions to be lost!
Therefore, the redo log is introduced to record the changes of successfully committed transactions, and the redo log is persisted to disk. After the system restarts, the redo log is read to recover the latest data.
2.3 Do I Write redo Log Files First
A DML might involve data modification and redo log logging. What is the order of execution? There are articles on the Web that say you should log the redo log first and log the redo log second. What is the real situation?
The redo log buffer writes to the redo log file at the time of a transaction, and the redo log buffer writes to the redo log file at the time of a transaction, and the redo log is flushed ata later time.
Transfer: segmentfault.com/a/119000003…
zhuanlan.zhihu.com/p/408075372