• If the profile
    • What is a MVCC
    • What are current and snapshot reads?
    • Relationship between current read, snapshot read, and MVCC
  • MVCC implementation principle
    • Implicit field
    • The undo log
    • Read View
    • The whole process
  • MVCC related issues
    • How does RR address non-repeatable reads on the basis of the RC level?
    • What is the difference between InnoDB snapshot reads at RC and RR levels?

I. Summary of premises

What is a MVCC?

Concurrency Control MVCC is a multi-version Concurrency Control. MVCC is a method of concurrency control, which can realize concurrent access to database in database management system and transaction memory in programming language.

The implementation of MVCC in MySQL InnoDB is mainly to improve the performance of database concurrency, with a better way to deal with read-write conflict, even if there is read and write conflict, can also do not lock, non-blocking concurrent read

What are current and snapshot reads?

MySQL InnoDB: Current read and snapshot read

  • Select lock in share mode, select for update; Update, INSERT,delete(exclusive lock) operations are a type of current read, why are they called current read? It reads the latest version of the record and locks the read record to ensure that other concurrent transactions cannot modify the current record.

  • Snapshot read without lock select is snapshot read, that is, non-blocking read without lock. The prerequisite for snapshot reads is that the isolation level is not serial. Snapshot reads at the serial level are degraded to current reads. The reason for the occurrence of snapshot read is to improve concurrency performance. The implementation of snapshot read is based on multi-version concurrency control, namely MVCC. MVCC can be considered as a variant of row lock, but it avoids locking operations and reduces overhead in many cases. Since it is based on multiple versions, snapshot reads may not necessarily read the latest version of data, but may read the previous historical version

To put it bluntly, MVCC is designed to achieve read-write conflict without locking, and this read refers to snapshot read, not current read. Current read is actually a locking operation, which is the implementation of pessimistic locking

Relationship between current read, snapshot read, and MVCC

  • Specifically, MVCC multi-version concurrency control refers to the concept of “maintaining multiple versions of a data so that read and write operations do not conflict”. It’s just an ideal concept
  • In MySQL, to achieve such an ideal concept of MVCC, we need MySQL to provide specific functions to achieve it, and snapshot read is one of the specific non-blocking read functions of MySQL to achieve the ideal model of MVCC. In contrast, the current read is the concrete implementation of pessimistic locking
  • To be more detailed, snapshot reading itself is also an abstract concept, further research. The specific implementation of MVCC model in MySQL is completed by three implicit fields, undo log, Read View, etc. For details, see the following IMPLEMENTATION principle of MVCC

What problem does MVCC solve and what are the benefits?

There are three database concurrency scenarios:
  • Read-read: There are no problems and no concurrency control is required
  • Read-write: Has thread safety issues, may cause transaction isolation issues, may encounter dirty reads, unreal reads, unrepeatable reads
  • Write – write: There are thread safety issues and there may be update loss issues, such as type 1 update loss, type 2 update loss
What are the benefits of MVCC?

Multi-version concurrency control (MVCC) is a lock-free concurrency control used to resolve read-write conflicts. This means that a transaction is assigned a time-stamp of unidirectional growth, a version is saved for each change, and the version is associated with the transaction timestamp. The read operation reads only the snapshot of the database before the transaction started. So MVCC can solve the following problems for databases

  • When concurrent read and write operations are performed on a database, neither write operation nor read operation is blocked, which improves the performance of concurrent read and write operations
  • At the same time, it can solve the problem of transaction isolation such as dirty read, unreal read and unrepeatable read, but it cannot solve the problem of update loss
So just to summarize

In short, MVCC is a solution to the problem of read-write conflict that is not satisfied with the use of pessimistic locking in the database. So in the database, because of MVCC, we can form two combinations:

  • MVCC resolves read/write conflicts, while pessimistic locks resolve write conflicts
  • MVCC + optimistic lock MVCC solve read/write conflict, optimistic lock solve write conflict this combination can maximize the performance of the database concurrency, and solve read/write conflict, and write conflict caused by the problem

Ii. Realization principle of MVCC

The purpose of MVCC is multi-version concurrency control. The realization of MVCC in the database is to solve Read and write conflicts. Its realization principle is mainly based on the three implicit fields in the record, undo log and Read View. So let’s take a look at this three-point concept

Implicit field

Each row contains DB_TRX_ID,DB_ROLL_PTR,DB_ROW_ID, and other fields that are implicitly defined by the database

  • DB_TRX_ID 6byte, Last modified (modified/inserted) transaction ID: Specifies the ID of the transaction in which the record was created or last modified

  • DB_ROLL_PTR 7byte, a rollback pointer to the last version of this record (stored in the rollback segment)

  • DB_ROW_ID 6byte, implied increment ID (hidden primary key). InnoDB automatically generates a cluster index DB_ROW_ID if the table does not have a primary key

  • In fact, there is a deletion flag hidden field. If the record is updated or deleted, it does not mean that the record is actually deleted, but that the deletion flag has changed

As shown above, DB_ROW_ID is the only implicit primary key that the database generates by default for this record, DB_TRX_ID is the transaction ID that operates on this record, and DB_ROLL_PTR is a rollback pointer used with undo logs to point to the previous version

The undo log

Undo logs are classified into two types:

  • Insert undo log represents the Undo log generated when a transaction inserts a new record, is only needed when a transaction is rolled back, and can be discarded immediately after a transaction is committed
  • Update undo log Indicates the undo log generated when a transaction is updated or deleted. Not only when a transaction is rolled back, but also when a snapshot is read; The purge thread will purge the log only if the log is not involved in a quick read or transaction rollback

purge

  • From the previous analysis, we can see that in order to implement InnoDB’s MVCC mechanism, the update or delete operation is only to set the old record deleted_bit, and does not really delete the old record.
  • To save disk space, InnoDB has a purge thread to purge records whose deleted_bit is true. To keep the MVCC in order, the Purge thread maintains a Read view of its own (the read view of the oldest active transaction on the system); If a record’s deleTED_bit is true and DB_TRX_ID is visible relative to the Purge thread’s read view, the record must be safe to purge.

Help MVCC is the essence of the update undo log, undo the log, in fact, there is a rollback segment in the chain, record its execution process is as follows:

The persion table inserts a new record (name = Jerry, age = 24, primary key = 1, transaction ID = 1, rollback pointer = NULL)

Transaction 1 changes the name of the record to Tom

  • When transaction 1 modifies the row (record) data, the database first places an exclusive lock on the row

  • Then copy the row to the Undo log as the old record, even if there is a copy of the current row in the Undo log

  • After copying, change the name of the line to Tom, and change the transaction ID of the hidden field to the ID of the current transaction 1. By default, we start from 1, then increase gradually, and the rollback pointer points to the copy record copied to undo log, which means that my last version is it

  • After the transaction commits, the lock is released

Alter table person; alter table person; alter table age

  • The database also locks the row while transaction 2 modifies its data

  • Then copy the data in the row to the Undo log as the old record. If the row has an Undo log, the latest old data is inserted in the first undo log of the row as the head of the linked list

  • Change the age of the row to 30, and change the transaction ID of the hidden field to the ID of the current transaction 2, which is 2. The rollback pointer points to the copy of the record that was just copied to undo log

  • The transaction commits, releasing the lock

From the above, we can see that the modification of the same record by different transactions or the same transaction will cause the undo log of the record to become a linear list of record versions. In other words, the linked list, the first link of the undo log is the latest old record. The tail of the undo log is the oldest record (which may have been purge’d by the purge thread). The first insert into the undo log would have been deleted and lost after the transaction was committed, but it is included here for illustration purposes).

Read View

What is Read View?

When a snapshot is Read, a snapshot of the current database system is generated. The ID of the current active transaction is recorded and maintained. (When each transaction is started, Each transaction is assigned an ID, which is incremented, so the latest transaction has a larger ID.)

So we know that Read View is mainly used for visibility judgment, that is, when a transaction performs a snapshot Read, we create a Read View of the record, which is compared to a condition to determine which version of the data can be seen by the current transaction. It could also be a version of the undo log recorded in the row.

The Read View follows a visibility algorithm that takes the DB_TRX_ID (the current transaction ID) from the latest record of the data to be modified and compares it with the ids of other currently active transactions on the system (maintained by the Read View). If DB_TRX_ID is not visible, use the DB_ROLL_PTR rollback pointer to retrieve the DB_TRX_ID from Undo Log. Until a DB_TRX_ID is found, the old record where the DB_TRX_ID resides will be the latest old version visible to the current transaction

So what is this judgment condition?

The changes_visible method shows how DB_TRX_ID is compared to some properties of Read View

Before I show you, let me simplify a Read View. We can simply think of a Read View as having three global properties

Trx_list is a list of values, Up_limit_id Records the lowest transaction ID in the trx_LIST. Low_limit_id ReadView Generates the next transaction ID that has not been assigned to the system at that time. That is, the maximum transaction ID that has occurred so far +1

  • DB_TRX_ID < up_limit_id = DB_TRX_ID = up_limit_id = up_limit_id = up_limit_id = up_limit_id = up_limit_id = up_limit_id = up_limit_id = up_limit_id = up_limit_id
  • Then judge DB_TRX_ID greater than or equal to low_limit_id, if greater than or equal to represents DB_TRX_ID’s record in the Read View generated before, that affirmation is not visible on the current affairs, if less than the judgment to the next
  • Trx_list. Contains (DB_TRX_ID) trx_list. Contains (DB_TRX_ID) trx_list. If not, your transaction was committed before the Read View was generated, and the result of your modification is visible to the current transaction

The whole process

Now that we understand the concepts of implicit fields, undo log, and Read View, we can look at the overall flow of an MVCC implementation

What is the overall process? We can simulate it

  • When transaction 2 performs a snapshot Read on a row, the database generates a Read View for that row. Assume transaction ID 2 is active, transaction 1 and transaction 3 are still active, and transaction 4 committed an update just before transaction 2 reads the snapshot, so Read View records the ID of transaction 1 and transaction 3. It’s maintained on a list, let’s call it trx_List

  • Not only does the Read View maintain the active transaction ID of the system at the time of transaction 2’s snapshot Read, but it also has two properties, up_limit_id, which records the smallest transaction ID in the trx_List. Low_limit_id (Record the maximum transaction ID in the trx_list list. The next transaction ID has not been assigned at the time the snapshot is read. So in this example up_limit_id is 1, low_limit_id is 4 + 1 = 5, trx_list is 1,3, Read View as shown below

  • In our example, only transaction 4 modified the row record and committed the transaction before transaction 2 performed the snapshot read. Therefore, the undo log of the current data in the current row is shown in the figure below. When transaction 2 reads a snapshot of this record, it compares the DB_TRX_ID of this record with up_limit_ID, LOW_limit_ID, and trx_list to determine which version of this record is visible to transaction 2.

  • DB_TRX_ID = up_limit_id(1); DB_TRX_ID = up_limit_id(1); DB_TRX_ID = low_limit_id(5); Finally determine whether 4 in trx_list active transactions, finally found the transaction ID of 4 transaction is not in the list of currently active transactions, in line with the visibility condition, so the transaction submitted 4 modified when reading the latest results of transaction 2 snapshot is visible, so the transaction 2 can read the latest data record is the transaction submitted version 4, The version committed by transaction 4 is also the latest version from a global perspective

  • It is the difference in the generation time of the Read View that causes the difference in the Read results at the RC and RR levels

Iii. MVCC related issues

How does RR address non-repeatable reads on the basis of the RC level?

Table 1 Shows the differences between current reads and snapshot reads at the RR level.

Table 2:

In the order shown in Table 2, transaction B’s snapshot read after transaction A commits and the current read are both real-time new data 400. Why is this?

  • The only difference is that transaction B in Table 1 snapshot reads the amount data once before transaction A changes the amount, whereas transaction B in Table 2 does not snapshot read the amount before transaction A changes the amount.

So we know that the result of snapshot reads in a transaction is very dependent on the place where the first snapshot read occurs in the transaction, that is, the place where the first snapshot read occurs in a transaction is very important, it has the ability to determine the subsequent snapshot read results of the transaction

The test here is update, and delete and update are the same. If the snapshot read of transaction B is after transaction A, the snapshot read of transaction B can also read the latest data

What is the difference between InnoDB snapshot reads at RC and RR levels?

It is the difference in the generation time of the Read View that causes the difference in the Read results at the RC and RR levels

  • The first snapshot Read of a record by a transaction at the RR level creates a snapshot and a Read View, which records other transactions that are currently active in the system. The same Read View is used when snapshot reads are invoked later. Therefore, as long as snapshot reads are used by the current transaction before other transactions commit updates, Subsequent snapshot reads use the same Read View, so subsequent changes are invisible;
  • That is, when a RR snapshot Read generates a Read View, the Read View records snapshots of all other active transactions at that time. The changes made to these transactions are not visible to the current transaction. Changes made by transactions created before the Read View are visible
  • In RC level transactions, each snapshot Read creates a new snapshot and Read View, which is why we see updates committed by other transactions in RC level transactions

At the RC isolation level, each snapshot Read generates and retrieves the latest Read View. In RR isolation, a Read View is created only for the first snapshot Read in the same transaction, and all subsequent snapshot reads obtain the same Read View.