How does Mysql implement MVCC? There are countless people asking this question, but there is no answer in google. This article attempts to find the answer from the Mysql source code.
In Mysql, MVCC is supported in the Innodb Storage engine. Innodb implements three hidden fields for each row of records:
- 6-byte transaction ID (
DB_TRX_ID )
- 7-byte rollback pointer (DB_ROLL_PTR)
- Hidden ID
A 6-byte transaction ID is used to identify the transactions described in this row. A 7-byte rollback pointer needs to understand the transaction model of Innodb.
1. Innodb Transaction-related concepts in order to support transactions, Innbodb introduces the following concepts:
- Redo log
Redo log is used to save the executed SQL statement to a specified Log file. when Mysql executes the recovery, it can re-execute the SQL operation of the redo log record. When the client executes each SQL statement (update statement), The redo log is first written into the log buffer. When the client executes the COMMIT command, the content in the log buffer is refreshed to the disk as needed. Redo log exists as an independent file on the disk, that is, the Innodb log file.
- Undo log
Opposite to redo log, undo log is used for rollback. The specific content is to copy the database content (ROW) before the transaction to the undo buffer, refresh the content in the undo buffer to the disk at the appropriate time. Like the redo buffer, the undo buffer is also a ring buffer, but when the buffer is full, the content in the undo buffer will be refreshed to the disk. Unlike the redo log, there is no separate undo log file on the disk. All undo logs are stored in the primary ibd data file (tablespace), even if each table is set to a data file on the client.
- Rollback segment
The concept of rollback segments comes from the Oracle transaction model. In Innodb, the undo log is divided into multiple segments, and the undo log of a Specific Row is stored in a specific segment, it is called a rollback segment. It can be considered that the undo log and rollback segment share the same meaning.
- Lock
Innodb provides row-based locks. If the number of rows is very large, the number of locks under high concurrency may be relatively large. According to Innodb documentation, innodb effectively optimizes the lock space. Even if the concurrency is high, the memory will not be exhausted.
There are two types of row locks: exclusive locks and shared locks. The exclusive lock and the exclusive lock are completely equivalent to the read/write lock. If a transaction is updating a row (exclusive lock), other transactions, whether read or write, must wait. If a transaction reads a row (shared lock ), other read tasks do not have to wait, but write tasks do. The shared lock ensures no waiting among multiple reads, but the lock application depends on the transaction isolation level of Mysql.
- Isolation level
The isolation level is used to limit the degree of direct interaction of transactions. Currently there are several industrial standards:
-READ_UNCOMMITTED: Dirty read
-READ_COMMITTED: Read and submit
-REPEATABLE_READ: Repeated read
-SERIALIZABLE: SERIALIZABLE
Innodb supports all four types of data. There are not many dirty read and serialized application scenarios, and it is widely used for read submission and repeated read. The implementation method will be introduced later.
2. the row update process below demonstrates the transaction update process for a row record: 1. Initial Data row
F1 ~ F6 is the name of a row or column, 1 ~ 6 is the corresponding data. The following three hidden fields correspond to the transaction number and rollback pointer of the row respectively. If the data is just inserted, the ID is 1 and the other two fields are blank. 2. Transaction 1 changes the value of each field in the row
When transaction 1 changes the value of this row, the following operations are performed:
- Lock this row with exclusive locks
- Redo log
- Copy the value before the row modification to the undo log, that is, the lower row
- Modify the value of the current row and enter the transaction number to point the rollback pointer to the row before the modification in the undo log.
3. Transaction 2 modify the value of this row
Same as transaction 1. At this time, the undo log contains two rows of records and is linked together through the rollback pointer. Therefore, if the undo log is not deleted, the initial content of the row will be traced back through the rollback pointer of the current record. Fortunately, the purge thread exists in Innodb, it queries undo logs earlier than the oldest active transactions and deletes them to ensure that the undo log file does not grow infinitely. 4. transaction COMMIT when the transaction is committed normally, Innbod only needs to change the transaction state to COMMIT without additional work, while Rollback is slightly more complex, you need to find the transaction version before modification from the undo log based on the current rollback pointer and restore it. If there are many rows affected by the transaction, rollback may be less efficient. Based on experience, there are no rows in the transaction: 1000 ~ Between 10000, Innodb efficiency is still very high. Obviously, Innodb is a storage engine with a higher COMMIT efficiency than Rollback. It is said that the implementation of ipvss is exactly the opposite. 5. the above process of Insert Undo log describes the transaction process of UPDATE. In fact, the undo log is divided into insert and update undo log because the original data does not exist during insert, therefore, the insert undo log can be discarded during rollback, while the update undo log must follow the above process.
