From a design point of view, why MVCC was designed, and what is the difference between the isolation level of RC and RR?

MVCC role

MVCC enables most transaction engines that support row locking to no longer simply use row locking to control the concurrency of the database, ** but to combine the row locking and the version number of the database, with very little overhead, it can achieve unlocked read. ** improves the concurrency performance of the database.

**MVCC solves read-write conflicts without locking. Read refers to snapshot read. ** that is MVCC implementation snapshot read!!

What is a 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: a create version number and a rollback pointer. There is a transaction ID after the transaction is started. Multiple concurrent transactions operate on a row at the same time. Different transactions produce multiple versions of the update operation on that row, and then form an undo log chain by rolling back Pointers. The snapshot read of MVCC is the snapshot read through the transaction ID and create version number.

Relationship between MVCC and isolation level

MVCC is designed to solve read-write problems. In addition, different configurations can solve the problem that snapshot reads cannot be repeated after transactions are enabled.

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

  • Phantom: when a transaction re-reads previously retrieved data according to the same query criteria, only to find that other transactions insert new data that meets the query criteria, this phenomenon is called phantom.

RC and RR both implement MVCC, but why does RR solve the problem of RC being unrepeatable?

You can assume that RC has a non-repeatable read problem because the developers deliberately set it up (with multiple isolation levels that users can set as appropriate). The data has been submitted to the database, but RC can read it without any problem. Furthermore, the isolation level of Oracle database itself is RC.

READ-COMMITTED RC reads. At this isolation level, consistent reads can be done at the SQL level, where a new ReadView is created each time an SQL statement is COMMITTED. This means that there are other transactions committed between the two queries and inconsistent data can be read.

REPEATABLE READ RR, after the first creation of the ReadView, the ReadView is maintained until the end of the transaction, that is, visibility does not change during the execution of the transaction, thus implementing REPEATABLE reads within the transaction.

MVCC and gap lock

**MVCC lock-free resolves read-write conflicts. ** Also solves the non-repeatable read problem. The RC and RR isolation levels are realized.

A gap lock is essentially a lock that blocks the execution of two concurrent transactions.

So why did RR enter the gap lock, just to solve the illusion problem?

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

To some extent, the gap lock can solve the illusion problem, but I think the gap lock was introduced to deal with the bug of binlog statement mode.

The primary/secondary replication of mysql databases relies on binlog. Prior to mysql5.0, binlog only had statement format. This mode is characterized by the order in which binlogs are recorded according to the order in which database transactions are committed.

When there is no gap lock, there is a scenario where the master library has two transactions:

SQL > delete (id<6); Insert id=3; insert id=3; 3, transaction A commit; Transaction B is executed first and transaction A is executed in binlog.

Then, there is a record in the primary database table with id=3, but the secondary database is inserted first and then deleted, there is no record in the secondary database.

This results in inconsistencies between master and slave data.

To address this bug, the RR level introduced gap locking.

Link: www.jianshu.com/p/fbec6d1fa…