Although MySQL is rarely used, but the database as a basic skill still can not forget, recently in the study of database isolation level, write a personal understanding to prepare for review.
We all know the four characteristics of acid (atomicity, consistency, isolation, and durability) of database transactions, and also know that there are three concurrency problems (dirty read, non-repeatable read, Phantom Read) in the database, as well as a targeted four isolation levels (READ UNCOMMITTED, Read committed, Repeatable read, serialization).
| Resolved or not |
Dirty Read |
Non-REPEATABLE READ |
Phantom reading |
| Read not submitted |
Yes |
Yes |
Yes |
| Read Committed |
No |
Yes |
Yes |
| REPEATABLE READ |
No |
No |
Yes |
| Serialization |
No |
No |
No |
As a special reminder, the isolation level functions at the connection (or session) level. Each time the client connects to the database, it is appropriate to set its own transaction isolation level based on the degree of consistency required.
So the question is, what technology does the bottom of MySQL use to achieve these four isolation levels?
Read not submitted
The MySQL full table data is stored in the B + Tree index with the primary key as the sorted value, and the leaf node stores the entire row record of the corresponding primary key. It should be noted that the data of the leaf node is the most recent data, either a consistent state after the commit of the transaction, or an intermediate value in the execution of the transaction (which may be rolled back).
When the isolation level is set to RU:
- All read without lock, read the most recent value on the leaf node, the best performance.
- All write (update, insert, delete) plus row level exclusion lock, there is no dirty write problem, after writing to release the lock.
Read Committed
When the isolation level is RC and RR, talk about the famous MVCC (multi-versioning) technology. By adding a number of hidden fields to each line, it achieves a good performance without locking the read operation.
- First of all, the RC level of write operations, MySQL is still added row-level rejection lock. The current valid record value is written to the undo log at the start of the transaction, and the hidden column data_roll_ptr of the B + Tree leaf node stores a pointer to the undo record. The data_roll_ptr pointer along the line forms a linked list that records the valid history of the row's data.
- In addition to the unlocked read operation, if the leaf node is locked by another transaction, then MySQL will follow the leaf node's data_roll_ptr pointer to find a valid history.
REPEATABLE READ
At the beginning of the transaction, in addition to the data that is written back to the undo log normally, a readview is created that records the ID of the other transaction that is currently active, with a minimum value of tmin and a maximum value of Tmax.
When you perform a select operation, MySQL looks for a historical version that matches the condition with the data_roll_ptr pointer of the row record. Here is another hidden column data_trx_id, which stores the transaction ID that updates the record (the transaction ID is globally incremented and unique). If the delete_bit is 1, a transaction with ID data_trx_id is deleted for the current row.
The filter conditions for a linked list of scanned history versions are:
- If the data_trx_id of the current record is less than tmin (previously existing data), then the delete_bit determines whether it is visible; otherwise, go to 2.
- If the current record's data_trx_id is less than Tmax and is not in the active transaction ID collection, the delete_bit determines whether the visibility is considered visible; otherwise, go 3.
- Otherwise considered invisible, follow data_roll_ptr into the previous version of History, or end up backtracking because of the head.
Because the inserted data version number is either within the active Transaction ID collection or greater than the current transaction ID, the MVCC mechanism solves the Phantom read problem at the same time.
It should be noted that the read in the above three isolation levels is a normal select. If you are using SELECT ... LOCK in SHARE mode or SELCT ... For UPDATE, all of which belong to the current read. That is, write lock, read the latest leaf node value, if the earlier transaction changed the row value, there will still be a non-repeatable read situation.
At the RR level, if there is a unique index on the condition column of the where, then MySQL only adds row-level locks, and if it is a normal index, a gap lock is added to prevent Phantom reads, and if there is no index, a table lock is used, which greatly reduces the ability to write concurrently.
Serialization
Reading and writing are added to the table-level read-write lock, directly read the primary key index B + Tree leaf node of the latest data. At this level, data consistency is strong, but the ability to write concurrently is very poor.
Implementation of the MySQL isolation level