1 Why MVCC

Concurrency Control MVCC is a multi-version Concurrency Control optimization solution for database Concurrency without locking. Its lock-free optimization is aimed at reading without locking. Its basic idea is that any modification of the database will not directly overwrite the original data, but generate new copies, and connect the copies of the new and old versions through a linked list. Each copy will contain the version number of the modified data record. When reading data, determine which version of the linked list data is visible to the current transaction based on the comparison of version numbers. Before the emergence of this technology, only between read and read can be concurrent, after the introduction of multi-version control, read – read, read – write, write – read can be concurrent, read without locking, read and write conflict, greatly improve the concurrency of database system.

In mysql InnoDB, MVCC only works under READ COMMITE and REPEATABLE READ isolation levels. READ UNCOMMITED Total Reading of the latest version of data does not require MVCC to control the concurrency of multiple versions. SERIALIZABLE locks all READ operations and also does not require MVCC. The MVCC mechanism in mysql InnoDB storage engine is simply implemented by implicit column + undo log + read view.

Implicit columns, undo log, Read View, snapshot read, and current read

Implicit in 2.1

InnoDB adds three columns to the first row: DB_TRX_ID, DB_ROLL_PTR, and DB_ROW_ID (InnoDB multi-versioning).

  • DB_TRX_ID specifies the transaction ID of the row that was recently updated or added to the database. Also known as the transaction ID.

  • DB_ROLL_PTR indicates the rollback pointer. It points to the undo log that records the last update or new operation. The undo log can restore data to the previous state

  • DB_ROW_ID Specifies the id that innoDB automatically creates when there is no clustered index

2.2 the undo log

Undo log is mainly used for two purposes: one is to achieve atomicity of transactions in InnoDB, to ensure that partially dropped data can be rolled back, and the other is to realize the storage of multi-version data in MVCC mechanism. The undo log is a logical log that records the reverse of the previous operation.

2.3 read the view

Read Veiw is a read view created when InnoDB performs snapshot reads (described later on snapshot reads, also known as consistent Read). When a snapshot read is performed, the system creates a read view for it and compares the latest data with the DB_TRX_ID and Read View in the undo log chain. To determine which version of the undo log can be seen by the current transaction.

2.4 Snapshot Read and Current Read

Read the snapshot

Simple SELECT statement (without SELECT… lock in share model, select … For update) are snapshot read. Consistent Read is called consistent Read on mysql’s official website, because consistent read is snapshot-based, it is usually called snapshot read. This kind of read does not add read lock, but uses implicit column + undo log + read view to obtain the required data. Snapshot reading is actually an implementation of the MVCC mechanism in InnoDB.

The current reading

All reading operations that need to be locked (refer to Innodb locking reads in mysql official website) are shown as follows

  • select … lock in share model
  • select … for update
  • update …
  • insert …
  • delect …

3 Innodb MVCC snapshot read process

From the previous brief introduction, we learned that snapshot reads are actually an implementation of the MVCC mechanism in InnoDB. Now take a look at the overall process of MVCC snapshot reads. The judgment that data is visible when snapshot reads depends on the ReadView data structure, which contains the following key attributes.

  • M_ids Set of transactions that were active (started but not committed) when the current snapshot was created.
  • M_low_limit_id High watermark. The maximum ID of the current transaction in the system is increased by one, that is, the ID of the next transaction to be opened.
  • M_up_limit_id Low watermark, the minimum ID of the current active transaction. Transactions smaller than this ID are committed transactions.
  • M_creator_trx_id Specifies the transaction ID for creating the read View.

For consistent Read, InnoDB uses DB_TRX_ID (the transaction id of the most recently updated or newly added row in the implicit column) as its data. Transaction ID) is then compared with m_ids, m_low_limit_id, m_up_limit_id, m_creator_trx_id in ReadView to determine whether the current version of the data is visible to the ReadView ReadView. The general process of the processing algorithm is as follows:

1, DB_TRX_ID ≥ M_LOW_limit_ID (high watermark), DB_TRX_ID data is not visible to the current transaction, indicating that this data will be updated by future transactions.

DB_TRX_ID < m_up_limit_id (low watermark), DB_TRX_ID data is visible to the current transaction, indicating that this data is updated by a committed transaction.

If DB_TRX_ID is in m_IDS (Active Transaction Set), it indicates a transaction that has not yet been committed. The data used for DB_TRX_ID is not visible to the current transaction. If DB_TRX_ID is not in m_IDS, the transaction has been committed and DB_TRX_ID data is visible to the current transaction.

Reference documentation

The InnoDB Storage Engine

Mysql5.7 ReadView source