[MySQL] Multi-version concurrency control

The implementation of most transactional storage engines in MySQL is not a simple row-level lock. Based on the consideration of improving concurrency performance, they generally implement multi-version concurrency control (MVCC) at the same time. Not only MySQL, but other database systems such as Oracle and PostgreSQL also implement MVCC, but their implementation mechanisms are different because there is no unified standard for MVCC.

You can think of MVCC as a variant of row-level locking, but it avoids locking operations in many cases, so the overhead is lower. Although the implementation mechanisms are different, most of them implement non-blocking read operations, and write operations only lock necessary rows.

MVCC is implemented by saving a snapshot of data at a certain point in time. In other words, no matter how long it takes to execute, the data seen by each transaction is consistent. Depending on the time when the transaction starts, the data seen by each transaction on the same table at the same time may be different.

The MVCC implementation of different storage engines is different, typically optimistic concurrency control and pessimistic concurrency control. Below we illustrate how MVCC works through a simplified version of InnoDB's behavior.

InnoDB's MVCC is implemented by saving two hidden columns behind each row of records. Of these two columns, one holds the creation time of the row, and the other holds the expiration time (or deletion time) of the row. Of course, what is stored is not the actual time value, but the system version number. Every time a new transaction is started, the system version number is automatically incremented. affairs. The system version number at the start of the transaction will be used as the version number of the transaction, which is used to compare with the version number of each row of records queried. Let's take a look at how MVCC operates specifically under the REPEATABLE READ isolation level.

SELECT

InnoDB will check each row of records based on the following two conditions:

• InnoDB only searches for data rows whose version number is earlier than the current transaction version (That is, the system version number of the row is less than or equal to the transaction). This ensures that the rows read by the transaction either already exist before the transaction starts, or have been inserted or modified by the transaction itself.

• The deleted version of the row is either undefined or greater than the current transaction version number. This ensures that the rows read by the transaction were not deleted before the transaction started.

Only records that meet the above two conditions can be returned as query results.

INSERT

InnoDB saves the current system version number as the row version number for each row inserted.

DELETE

InnoDB saves the current system version number as the row deletion identifier for each deleted row.

UPDATE

InnoDB inserts a new row of records, saves the current system version number as the row version number, and saves the current system version number to the original row as the row deletion identifier.

Save these two additional system version numbers so that most data reading operations can be done without locking. This design makes the data reading operation very simple, the performance is very good, and it also ensures that only rows that meet the standards are read. The disadvantages are that each row of records requires additional storage space, more checking, and some additional maintenance.

MVCC only works under two isolation levels: REPEATABLE READ and READ COMMITTED. The other two isolation levels are incompatible with MVCC because READ UNCOMMITTED always reads the latest data row, not the data row that conforms to the current transaction version. SERIALIZABLE will lock all rows read.

Note: MVCC does not have a formal specification, so the implementation of each storage engine and database system is different. No one can say that other methods are wrong.

The implementation of most MySQL transactional storage engines is not a simple row-level lock. Based on the consideration of improving concurrency performance, they generally implement multi-version concurrency control (MVCC) at the same time. Not only MySQL, but other database systems such as Oracle and PostgreSQL also implement MVCC, but their implementation mechanisms are different because there is no unified standard for MVCC.

You can think of MVCC as a variant of row-level locking, but it avoids locking operations in many cases, so the overhead is lower. Although the implementation mechanisms are different, most of them implement non-blocking read operations, and write operations only lock necessary rows.

MVCC is implemented by saving a snapshot of data at a certain point in time. In other words, no matter how long it takes to execute, the data seen by each transaction is consistent. Depending on the time when the transaction starts, the data seen by each transaction on the same table at the same time may be different.

The MVCC implementation of different storage engines is different, typically optimistic concurrency control and pessimistic concurrency control. Below we illustrate how MVCC works through a simplified version of InnoDB's behavior.

InnoDB's MVCC is implemented by saving two hidden columns behind each row of records. Of these two columns, one holds the creation time of the row, and the other holds the expiration time (or deletion time) of the row. Of course, what is stored is not the actual time value, but the system version number. Every time a new transaction is started, the system version number is automatically incremented. affairs. The system version number at the start of the transaction will be used as the version number of the transaction, which is used to compare with the version number of each row of records queried. Let's take a look at how MVCC operates specifically under the REPEATABLE READ isolation level.

SELECT

InnoDB will check each row of records based on the following two conditions:

• InnoDB only searches for data rows whose version number is earlier than the current transaction version (That is, the system version number of the row is less than or equal to the transaction). This ensures that the rows read by the transaction either exist before the transaction starts, or are inserted or modified by the transaction itself.

• The deleted version of the row is either undefined or greater than the current transaction version number. This ensures that the rows read by the transaction were not deleted before the transaction started.

Only records that meet the above two conditions can be returned as query results.

INSERT

InnoDB saves the current system version number as the row version number for each row inserted.

DELETE

InnoDB saves the current system version number as the row deletion identification for each deleted row.

UPDATE

InnoDB inserts a new row of records, saves the current system version number as the row version number, and saves the current system version number to the original row as the row deletion identifier.

Save these two additional system version numbers so that most data reading operations can be done without locking. This design makes the data reading operation very simple, the performance is very good, and it also ensures that only rows that meet the standards are read. The disadvantages are that each row of records requires additional storage space, more checking, and some additional maintenance.

MVCC only works under two isolation levels: REPEATABLE READ and READ COMMITTED. The other two isolation levels are incompatible with MVCC because READ UNCOMMITTED always reads the latest data row, not the data row that conforms to the current transaction version. SERIALIZABLE will lock all rows read.

Note: MVCC does not have a formal specification, so the implementation of each storage engine and database system is different. No one can say that other methods are wrong.

The above is the content of [MySQL] multi-version concurrency control. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!