What is MVCC and why are gap locks designed?

This article will take you to understand MVCC, introduce the relationship between MVCC and isolation level, from a design perspective, talk about why MVCC is designed, and what is the difference between the isolation levels of RC and RR.

The role of MVCC

MVCC makes most transaction engines that support row locks no longer simply use row locks for database concurrency control. Instead, the row lock of the database is combined with the row version number, and non-locking reading can be achieved with only a small overhead. Thus improving the concurrency performance of the database.

MVCC uses a lock-free form to solve the problem of read-write conflicts. The read here refers to the snapshot read. That is, snapshot reading implemented by MVCC! ! !

What is MVCC

Multi-version concurrency control (MVCC) is a lock-free concurrency control that resolves read-write conflicts.

Each row of records has two hidden columns: creation version number and rollback pointer. There is a transaction id after the transaction is started. Multiple concurrent transactions operate a certain row at the same time. Different transactions' update operations on the row will produce multiple versions, and then use the rollback pointer to form an undo log chain. The snapshot reading of MVCC is achieved through the transaction ID and the creation version number.

The relationship between MVCC and isolation level

MVCC is to solve the read-write problem. And through different configurations, the problem of non-repeatable reading of snapshots after the transaction is started can also be solved.

• Non-repeatable read: Some data read in the same transaction has changed, or some records have been deleted.

• Phantom reading: A transaction re-reads previously retrieved data according to the same query conditions, only to find that other transactions have inserted new data that meets the query conditions. This phenomenon is called Phantom reading.

Both RC and RR implement MVCC, but why does RR solve the problem of non-repeatable reading in RC?

You can think that the reason why RC has the problem of non-repeatable reading is just because the developers set it intentionally (setting multiple isolation levels, the user can set it according to the situation). Originally, the data has been submitted to the database, so there is no problem when RC reads it? Moreover, the isolation level of the Oracle database itself is RC.

READ-COMMITTED (Read Committed)
Read Committed RC. Under this isolation level, consistent reading can be achieved at the SQL level. Each SQL statement will generate a new ReadView. This means that other transactions were submitted between the two queries, and inconsistent data can be read.

REPEATABLE-READ (repeatable read)
Repeatable read RR, after the ReadView is created for the first time, this ReadView will be maintained until the end of the transaction, that is, Visibility does not change during transaction execution, enabling repeatable reads within a transaction.

MVCC and gap lock

MVCC lock-free solves the problem of read-write conflicts. And solves the problem of non-repeatable reading. This achieves two isolation levels: RC and RR.

And Gap lock is still essentially a lock, which will block the execution of two concurrent transactions.

So why does RR enter the gap lock? Is it just to solve the problem of phantom reading?

Note: Gap locks only exist at the RR isolation level.

Gap locks can solve the problem of phantom reading to a certain extent, but I think the introduction of gap locks is more to deal with bugs in the statement mode of binlog.

The master-slave replication of mysql database relies on binlog. Before mysql5.0, binlog mode only had statement format. The characteristics of this mode: the recording order of binlog is in the order of database transaction commit order.

When there is no gap lock, there will be the following scenario:
The master library has two transactions:

1. Transaction a first delete id<6, and then before commit;
2. Transaction b directly insert id=3, and complete the commit;
3. Transaction a commits;
The log recorded by binlog at this time is: Transaction b is executed first, and transaction a is executed (the binlog records the commit order)

Then the master database has a record of id=3 in the table at this time, but the slave database inserts first and then deletes it. In the slave database, it is Not recorded.

This leads to inconsistency between master and slave data.

In order to solve this bug, gap lock is introduced at the RR level.

[Related recommendations: mysql video tutorial]

The above is the detailed content of What is MVCC and why are gap locks designed?. For more information, please follow other related articles on the PHP Chinese website!