What is a MVCC

Concurrency Control. MVCC, for 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.

As we know, Innodb storage engine is used in mysql database. Innodb storage engine supports transactions, so when multiple threads execute transactions at the same time, there may be concurrency problems. You need a way to control concurrency, and MVCC does just that.

Mysql lock and transaction isolation levels

Before understanding the principle of MVCC mechanism, we need to understand Mysql lock mechanism and transaction isolation level, leaving MyISAM storage engine alone, Innodb storage engine, respectively, there are row lock and table lock two kinds of lock, table lock is a single operation to lock the whole table, so the lock granularity is the largest, but also the lowest performance, will not appear deadlock. A row lock is a row that is locked at a time. In this way, the granularity of the lock is small and the concurrency is high, but deadlocks can occur.

Innodb row locks are divided into shared locks (read locks) and exclusive locks (write locks). When a transaction has a read lock on a row, other transactions are allowed to read the row but are not allowed to write the row, and other transactions are not allowed to write the row but can read the row.

When a transaction locks a row, other transactions are not allowed to write the row, but can read the row, and other transactions are not allowed to lock the row.

Mysql transaction isolation levels are as follows:

  1. Read Uncommitted: A transaction can read data that has not been committed by another transaction, resulting in dirty reads. For example, in A payroll table, transaction A starts first, and then executes the query on the salary of employee 1, assuming that the salary is 1000. At this time, transaction B also starts, performs the update operation, and reduces the salary of employee 1 by 100, but does not commit the transaction. After the query operation of transaction A is performed, the updated data of transaction B can be read. If transaction B is rolled back, transaction A will read dirty data. It is also possible to have A phantom read when transaction A performs an update operation.
  2. Read Committed: A transaction can only read the data that has been modified by another committed transaction, and the transaction can query for the latest value each time the data has been modified and committed by another transaction. For the same example, this time the transaction isolation level is set to read committed data. In the case that transaction B does not commit the transaction, transaction A cannot read the updated data of transaction B, thus avoiding dirty data generation. However, when transaction B commits and transaction A executes the same data, it will find that the data has changed. This is called unrepeatable read, which means that the same query is executed multiple times in the same transaction, and the results are inconsistent. At the same time, the illusion condition still exists.
  3. Repeatable read: a transaction first read after a record, even if the other transactions to modify the value of the record and submit, read this again after the transaction record, reading is still the first read value, rather than read every time of different data, this is repeatable read, solved non-repeatable this isolation level, but still there will be a magic to read.
  4. Serialization: At this isolation level, operations on the same record are serial, so there are no dirty reads, phantom reads, etc., but this is not a concurrent transaction.

Mysql的undo log

MVCC relies on the undo log of Mysql. The undo log records the operation of the database. Because the undo log is a logical log, it can be understood that when deleting a record, the undo log records a corresponding insert record, and when updating a record, The undo log records a reverse update record. When a transaction fails and needs to be rolled back, the UNDO log can be read to roll back. MVCC uses the Undo log.

Realization principle of MVCC

The implementation of MVCC takes advantage of database implicit fields, undo log and ReadView. DB_TRX_ID (last modified transaction ID), DB_ROLL_PTR (rollback pointer to the previous version of this record), DB_ROW_ID (increment ID), DB_ROW_ID (increment ID if the table does not have a primary key, Default to this ID resume cluster index) these several hidden fields.

The undo log is generated when the transaction is rolled back, and can be discarded immediately after the transaction is committed. The undo log is generated when the transaction is rolled back, and can be discarded immediately after the transaction commits. The undo log generated when the transaction is update or DELETE; 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. MVCC uses the Update undo log.

In fact, undo log records a version chain. Suppose there is one record in the database as follows:

​

Now there is A transaction A that modifies this record and changes its name to Tom. At this time, the operation flow is as follows:

  • Transaction A first places A row lock on that record
  • This record is then copied to the Undo log as an old version
  • Change the name of the row to Tom, then change the DB_TRX_ID value of the row to transaction A’s ID, assuming transaction A’s ID is 1. Change the DB_POLL_PTR value of the row to the undo log record
  • After the transaction commits, the lock is released

The situation is as follows:

​

At this time, there is another transaction B to modify the record and change the age to 28. In this case, the operation flow is as follows:

  • Transaction B places a row lock on the changed row record
  • Copy the row record to the Undo log as an old version. If the undo log has already been recorded, a new undo log is inserted to the front of the undo log as the head of the linked list
  • Change the age of the row to 28. Then change the DB_TRX_ID value of the row to the ID of transaction B, assuming transaction B’s ID is 2. Change the DB_POLL_PTR value of the row to the undo log record
  • The lock is released after the transaction commits

The situation is as follows:

​

As can be seen from the above, the modification of the same row by different transactions or the same transaction will make the undo log of the row form a version chain. The first chain of the undo log is the latest old record, and the last chain is the earliest old record.

Now let’s assume that both transaction A and transaction B are uncommitted. At this time, transaction C changes the name of the record Tom to 30 and commits the transaction. Transaction C’s ID is 3, and again, inserts A record into the Undo log. The DB_TRX_ID in the undo log version link is 3.

Snapshot is a snapshot of data triggered by the query operation at the start of the transaction. Unlocked reads are snapshot reads by default at the repeatable read isolation level. There is also a current read relative to snapshot reads and update operations are current reads. A Read View is generated during a snapshot Read. At the moment when the transaction performs a snapshot Read, a snapshot of the current database is generated to record and maintain the IDS of the currently active transactions. Since transaction ids are self-incrementing, the newer the transaction, the larger the ID. The Read view complies with the visibility algorithm. The Read view records not only the active transaction ID but also the maximum transaction ID. The snapshot Read view needs to be compared with the Read View to obtain the visibility result.

The Read view compares the ID of the current transaction with the ID of the active transaction in the system as follows:

First, there is a Read view that generates an array of transaction ids, tentatively called ID_list, that are active in the system at that time

The Read view will then record the smallest transaction ID in the ID_list, tentatively called low_id

Finally, the Read View will record a transaction ID that has not been assigned to the system at the time the Read View was generated, namely the current maximum transaction ID+1, temporarily called high_id

  • If the current transaction ID is less than low_id, the current transaction is visible
  • If the current transaction ID is greater than high_id, the current transaction is not visible
  • The current transaction is greater than low_id and less than high_id, then check whether it is in ID_list. If yes, it indicates that the active transaction has not been committed, and the current transaction is not visible, but visible to the active transaction itself. If not in ID_list, the current transaction is visible

If the result is not visible, you need to run the DB_ROLL_PTR command to undo log to retrieve the DB_TRX_ID of the record for comparison. 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.