I. DEFINITION of MVCC

Concurrency Control Mutil Version Concurrency Control (MVCC) is a method of Concurrency Control that allows concurrent access to a database.

The above explanation is rather abstract. Let’s analyze it bit by bit.

1. Possible problems caused by concurrent transactions

  • When a transaction accesses data in a database, there is no concurrency problem, whether it is read or written.

  • When two transactions simultaneously access the same data in a database, there are several possible situations:

    • Read: Both transactions query data. When two transactions are all reads on the same data, there are no concurrency issues.
    • Read + write: One transaction queries data and one transaction modifies data. When two transactions for the same data reading with writing, may lead to dirty reads, non-repeatable, phantom read question (but dirty reads, non-repeatable, phantom read just doesn’t have to say there is a problem, specific still should see the scene, some business scenarios occur not repeatable degrees is not allowed, but some of the business scenario may be dirty read also do not have what matter).
    • Write: Two transactions modify data. When two transactions write to the same data, data loss (rollback loss, overwrite loss) may occur.

    Multiple transactions simultaneously access the same data in the database, which is also the same. There may be three cases: [read], [read + write], [write].

So how do you solve the above problem?

  • Concurrent transactions do not create concurrent problems when reading data, so do not solve them.
  • MySQL InnoDB implements MVCC to better deal with read/write conflicts. Even if there is concurrent read/write, it can also be done without locking, to achieve “non-blocking concurrent read”.
  • Concurrent transaction writes to data can only be resolved by locking (optimistic/pessimistic locking).

Here, we need to have such an impression in your mind: MySQL MVCC, mainly used in the case of concurrent read and write, to ensure “read” the data without lock can also be read to the data of a certain version of a snapshot, benefits can be avoided lock, reduce costs, to solve the conflict, speaking, reading and writing, increasing the concurrency performance of database.

2. Current read and snapshot read

Before getting into the details of implementing MVCC in MySQL, there are two definitions that need to be understood:

  • Current read: The data read is the latest version, and the data read must ensure that other concurrent transactions do not modify the current data. The current read will lock the read record. For example, select… Lock in share mode, select… For update | update | insert | delete (exclusive) lock

  • Snapshot read: Every time data is modified, a snapshot record is saved in the Undo log. In this case, a snapshot is the snapshot of a certain version in the Undo log. The advantage of this approach is that the data can be read without locking, but the disadvantage is that the data may not be the latest version. For example, select * from t_user where ID =1; All queries in MVCC are quick illuminance.


Realization and principle of MVCC

MVCC in MySQL is implemented through hidden fields in row records (hide primary key row_id, transaction ID trx_id, rollback pointer roll_pointer), undo log (version chain), ReadView (consistent ReadView).

1. Hide fields

In MySQL, there are some hidden fields in each row:

Row_id: InnoDB generates a clustered index with row_ID as primary key when no primary key is defined for a database table.

Trx_id: indicates the transaction ID of the newly added or recently modified record. The transaction ID is self-increasing.

Roll_pointer: The rollback pointer points to the previous version of the current record (in undo log).

2. Version chain

Briefly mention redo log and undo log. When data is modified, the content of the modified page is recorded to the redo log (to resume operations on the database after a database downtime and restart), and the original snapshot of the data is recorded to the Undo log (for rolling back transactions). Undo log serves two purposes, in addition to rolling back transactions, as well as implementing MVCC.

As a simple example, draw the logic diagram of the undo log version chain used in MVCC:

When transaction 100 (trx_id=100) is executedInsert into T_user values(1,' zhang3 ',20);After:

When transaction 102 (trx_id=102) executesUpdate t_user set name=' id 'where id=1;After:

Update t_user set name=’ h5 ‘where id=1; After:

3, ReadView

In the example above, multiple transactions modify the data with id=1. In addition to the latest data, there are multiple version snapshots in the Undo log. Can the latest version of data be found in other transaction queries? If not, which version of the snapshot can be read? That’s up to the ReadView to decide.

ReadView is a “ReadView” created by MVCC when snapshot reading data.

There are four important variables in ReadView (I don’t know what they are, but don’t worry about the details, just define them here…). :

M_ids: List of active transaction ids, the list of transaction ids of all active (that is, uncommitted) transactions in the current system.

Min_trx_id: specifies the smallest transaction ID in m_IDS.

Max_trx_id: the id (not the largest transaction ID in M_IDS) that the system should assign to the next transaction when the ReadView is generated, i.e., the largest transaction ID in m_IDS + 1.

Creator_trx_id: transaction ID of the transaction that generated this ReadView.

ReadView has a set of algorithms for which version of data can be read when a transaction makes a snapshot read:

(1) If [trx_id recorded in the version chain is equal to the current transaction ID (trx_id = creator_trx_id)], it indicates that the version in the version chain is modified by the current transaction, so the snapshot record is visible to the current transaction.

(2) If [trx_id of the record in the version chain is less than the minimum ID of the active transaction (trx_id < min_trx_id)], it indicates that the record in the version chain has been committed, so the snapshot record is visible to the current transaction.

(3) If trx_id recorded in version chain is greater than the transaction ID to be allocated next (trx_id > max_trx_id), the snapshot record is not visible to the current transaction.

(4) When [trx_id recorded in the version chain is greater than or equal to the minimum active transaction ID] and [trx_id recorded in the version chain is less than the next transaction ID to be allocated] (min_trx_id<= trx_id< max_trx_id), If the trx_ID recorded in the version chain is in m_IDS, it indicates that the ReadView was generated before the transaction that modified the record was committed, so the snapshot record is not visible to the current transaction. Otherwise, the snapshot record is visible to the current transaction.

Select * from t_user where ID =1 when a transaction reads a snapshot from a record whose ID =1. In the snapshot of the version chain, judge the four conditions one by one from the latest record until the snapshot of a certain version is visible to the current transaction. Otherwise, compare the records of the previous version.

MVCC is mainly used to solve the problem of dirty reads at RU level and non-repeatable reads at RC level. Therefore, MVCC only takes effect at RC (dirty reads) and RR (non-repeatable reads) isolation levels. That is, MySQL only generates readViews for snapshot reads at RC and RR isolation levels. The difference is that at the RC isolation level, each snapshot read generates a new ReadView; In RR isolation, only the first snapshot read in a transaction generates a ReadView, and subsequent snapshot reads use the ReadView generated for the first time.

Or is it a little abstract? Check it out by hand, and it will be much clearer. (If you want to really understand the above algorithm, it is recommended to find an example and verify it yourself.)


Verify the principle of MVCC manually

Let’s use the example above.

Insert into T_user values(1,’ SQL ‘,20); insert into t_user values(1,’ SQL ‘,20); And committed the transaction.

Three more transactions (transaction 101, transaction 102, and transaction 103) then read and write to this data:

Time sequence The transaction of 101 The transaction of 102 The transaction of 103
t1 begin
t2 select * from t_user where id=1;
t3 begin
t4 select * from t_user where id=1;
t5 begin
t6 select * from t_user where id=1;
t7 Update t_user set name=’ id ‘where id=1;
t8 select * from t_user where id=1;
t9 select * from t_user where id=1;
t10 commit
t11 select * from t_user where id=1;
t12 Update t_user set id=1 where id=1;
t13 commit
t14 select * from t_user where id=1;

When the time point is t1 to T6, there is only one snapshot in the version chain. Trx_id is 100:

Two snapshots exist in the version chain from point t7 to point T11. Trx_id is 102 and 100.

At point T11 to point T14, there are three snapshots in the version chain. Trx_id is 103, 102, and 100:

1, the transaction isolation level is RC (read committed isolation) :

When the current transaction isolation level is RC (read committed isolation), the ReadView generated for each transaction per query looks like this.

  • At point T2, the ReadView content generated by the transaction 101 query is:

    trx_list: 101
min_trx_id:101
max_trx_id:102
creator_trx_id:101
Copy the code

    At the current point in time, there is only one snapshot (trx_id=100) in the version chain, because trx_id(100)< min_trx_id(101), which complies with rule (2) of the algorithm, so the snapshot (trx_id=100) is visible to the current transaction.

  • At point T4, the ReadView content generated during the transaction 102 query is:

    Trx_list :101,102 min_trx_id:101 max_trx_id:103 creator_trx_id:102Copy the code

    At the current point in time, there is only one snapshot (trx_id=100) in the version chain, because trx_id(100)< min_trx_id(101), which complies with rule (2) of the algorithm, so the snapshot (trx_id=100) is visible to the current transaction.

  • At point t6, the ReadView content generated by the transaction 103 query is:

    Trx_list :101,102,103 min_trx_id:101 max_trx_id:104 creator_trx_id:103Copy the code

    At the current point in time, there is only one snapshot (trx_id=100) in the version chain, because trx_id(100)< min_trx_id(101), which complies with rule (2) of the algorithm, so the snapshot (trx_id=100) is visible to the current transaction.

  • At point T8, the ReadView content generated by the transaction 101 query is:

    Trx_list :101,102,103 min_trx_id:101 max_trx_id:104 creator_trx_id:101Copy the code

    At the current point in time, there are two snapshots in the version chain (trx_id=102 -> trx_id=100). Check the snapshots from the latest one in the version chain.

    For snapshots whose trx_id=102, min_trx_id(101) <= trx_id(102) < max_trx_id(104), and trx_id(102) is in trx_list(101,102,103), Note When the current transaction generates a ReadView, the transaction that modified the record is still an active transaction (not committed yet). According to rule (4) of the algorithm, the snapshot with trx_id=102 is not visible to the current transaction. This verifies that at the RC isolation level, data modified but not committed by transaction 102 should not be visible to transaction 101.

    For a snapshot with trx_id=100, trx_id(100)< min_trx_id(101) complies with rule (2) of the algorithm, so the snapshot with trx_id=100 is visible to the current transaction.

  • At point t9, the ReadView content generated by the transaction 102 query is:

    Trx_list :101,102,103 min_trx_id:101 max_trx_id:104 creator_trx_id:102Copy the code

    At the current point in time, there are two snapshots in the version chain (trx_id=102 -> trx_id=100). Check the snapshots from the latest one in the version chain.

    For a snapshot with trx_id=102, trx_id(102) = creator_trx_id(102) complies with rule (1), so the snapshot with trx_id=102 is visible to the current transaction.

  • At point T11, the ReadView content generated by the transaction 103 query is:

    Trx_list: 101,103 min_trx_id:101 max_trx_id:104 creator_trx_id:103Copy the code

    At the current point in time, there are two snapshots in the version chain (trx_id=102 -> trx_id=100). Check the snapshots from the latest one in the version chain.

    For snapshots with trx_id=102, min_trx_id(101) <= trx_id(102) < max_trx_id(104) and trx_id(102) is not in trx_list(101,103), indicating that the current transaction generates ReadView. The transaction modifying this record is not an active transaction (already committed) and according to rule (4) of the algorithm, the snapshot of TRx_id =102 is visible to the current transaction. This verifies that at the RC isolation level, the data modified and committed by transaction 102 is visible to transaction 103.

  • At point T14, the ReadView content generated by the transaction 101 query is:

    trx_list: 101
min_trx_id:101
max_trx_id:104
creator_trx_id:101
Copy the code

    At the current point in time, there are three snapshots in the version chain (trx_id=103 -> trx_id=102 -> trx_id=100). Determine the snapshots from the latest one in the version chain.

    For snapshots with trx_id=103, min_trx_id(101) <= trx_id(103) < max_trx_id(104) and trx_id(103) is not in trx_list(101), indicating that the current transaction generates ReadView. The transaction modifying this record is not an active transaction (already committed) and according to rule (4) of the algorithm, the snapshot of TRx_id =103 is visible to the current transaction. This verifies that at the RC isolation level, the data modified and committed by transaction 103 is visible to transaction 101.

The transaction isolation level is RR (repeatable read) :

When the current transaction isolation level is RR (repeatable read), the ReadView generated for each transaction per query looks like this.

As mentioned above, at the RC isolation level, each snapshot read generates an updated ReadView; In RR isolation, only the first snapshot read in a transaction generates a ReadView, and subsequent snapshot reads use the ReadView generated for the first time.

Therefore, transaction 101 queries at time t8 and time T14 use the same ReadView as at time T2; Transaction 102 uses the same ReadView when querying at time T9 as at time T4; Transaction 103 uses the same ReadView for query at time T11 as at time T6.

Article here will be over, intentional students can follow the above steps when the transaction isolation level for RC 】 【, look verify at each point in time, each transaction query is to be found which version of the snapshot data, also can deepen the understanding (to some students want to compare the answers after deduction, I will write the answer is in the below).





  • At point T2, the ReadView content generated by the transaction 101 query is:

    trx_list: 101
min_trx_id:101
max_trx_id:102
creator_trx_id:101
Copy the code

    At the current point in time, there is only one snapshot (trx_id=100) in the version chain, because trx_id(100)< min_trx_id(101), which complies with rule (2) of the algorithm, so the snapshot (trx_id=100) is visible to the current transaction.

  • At point T4, the ReadView content generated during the transaction 102 query is:

    Trx_list :101,102 min_trx_id:101 max_trx_id:103 creator_trx_id:102Copy the code

    At the current point in time, there is only one snapshot (trx_id=100) in the version chain, because trx_id(100)< min_trx_id(101), which complies with rule (2) of the algorithm, so the snapshot (trx_id=100) is visible to the current transaction.

  • At point t6, the ReadView content generated by the transaction 103 query is:

    Trx_list :101,102,103 min_trx_id:101 max_trx_id:104 creator_trx_id:103Copy the code

    At the current point in time, there is only one snapshot (trx_id=100) in the version chain, because trx_id(100)< min_trx_id(101), which complies with rule (2) of the algorithm, so the snapshot (trx_id=100) is visible to the current transaction.

  • At point T8, the ReadView used for the transaction 101 query is the same as the ReadView generated at point T2:

    trx_list: 101
min_trx_id:101
max_trx_id:102
creator_trx_id:101
Copy the code

    At the current point in time, there are two snapshots in the version chain (trx_id=102 -> trx_id=100). Check the snapshots from the latest one in the version chain.

    For snapshots with trx_id=102, trx_id(102) >= max_trx_id(102), according to rule (3) of the algorithm, the snapshot with trx_id=102 is not visible to the current transaction. This also verifies that at the RR isolation level, data modified by transaction 102 but not committed should not be visible to transaction 101 (RC is not visible, let alone RR).

    For a snapshot with trx_id=100, trx_id(100)< min_trx_id(101) complies with rule (2) of the algorithm, so the snapshot with trx_id=100 is visible to the current transaction.

  • The ReadView used for transaction 102 queries at point T9 is the same as the ReadView generated at point T4:

    Trx_list :101,102 min_trx_id:101 max_trx_id:103 creator_trx_id:102Copy the code

    At the current point in time, there are two snapshots in the version chain (trx_id=102 -> trx_id=100). Check the snapshots from the latest one in the version chain.

    For a snapshot with trx_id=102, trx_id(102) = creator_trx_id(102) complies with rule (1), so the snapshot with trx_id=102 is visible to the current transaction.

  • At point T11, the ReadView used for the transaction 103 query is the same as the ReadView generated at point T6:

    Trx_list :101,102,103 min_trx_id:101 max_trx_id:104 creator_trx_id:103Copy the code

    At the current point in time, there are two snapshots in the version chain (trx_id=102 -> trx_id=100). Check the snapshots from the latest one in the version chain.

    For snapshots whose trx_id=102, min_trx_id(101) <= trx_id(102) < max_trx_id(104), and trx_id(102) is in trx_list(101,102,103), Note When the current transaction generates a ReadView, the transaction that modiates the record is an active transaction (not committed yet). According to rule (4) of the algorithm, the snapshot with trx_id=102 is not visible to the current transaction. This verifies that the data modified and committed by transaction 102 under RR isolation is not visible to transaction 103.

  • At point T14, the ReadView content generated by the transaction 101 query is:

    trx_list: 101
min_trx_id:101
max_trx_id:102
creator_trx_id:101
Copy the code

    At the current point in time, there are three snapshots in the version chain (trx_id=103 -> trx_id=102 -> trx_id=100). Determine the snapshots from the latest one in the version chain.

    For snapshots with trx_id=103, trx_id(103) >= max_trx_id(102), according to rule (3) of the algorithm, the snapshot with trx_id=103 is not visible to the current transaction. This verifies that the data modified and committed by transaction 103 under RR isolation is not visible to transaction 101.

    For snapshots with trx_id=102, trx_id(102) >= max_trx_id(102), according to rule (3) of the algorithm, the snapshot with trx_id=102 is not visible to the current transaction. This verifies that the data modified and committed by transaction 102 under RR isolation is not visible to transaction 101.

    For snapshots with trx_id=100, trx_id(100) < min_trx_id(101). According to rule (2) of the algorithm, snapshots with trx_id=100 are visible to the current transaction.


“Deep Understanding of MySQL MVCC Principle” by Hu Yuyang