Understand how InnoDB MVCC works

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. 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 see how MVCC operates under the repeatable read isolation level.

SELECT

InnoDB will check each row record according to the following two conditions

a. InnoDB only looks for data rows whose version is earlier than the current transaction version (that is, , the system version number of the row is less than or equal to the system version number of 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.

b. 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 newly inserted row

DELETE

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

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 read 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 disadvantage is that each row of records requires additional storage space, more row checking work, and some additional maintenance work.

MVCC only works under the two isolation levels of REPEATABLE READ (repeatable read) and READ COMMITTED (read commit). 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 locks all rows read.

Related learning recommendations: mysql database

The above is the detailed content of Understand how InnoDB MVCC works. For more information, please follow other related articles on the PHP Chinese website!