Shulou(Shulou.com)06/01 Report--

This article is about how InnoDB MVCC works. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

InnoDB's MVCC is achieved by keeping two hidden columns behind each row of records. These two columns, one saves the creation time of the row, and the other saves 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. Each time you start a new transaction, the system version number is automatically incremented. The system version number at the beginning of the transaction is used as the version number of the transaction, which is compared with the version number of each row of records queried. Let's see how MVCC operates at the repeatable isolation level.

SELECT

InnoDB checks each row of records according to the following two conditions

A. InnoDB only looks for 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), which ensures that the rows read by the transaction either exist before the transaction starts, or are 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 are not deleted before the transaction begins.

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

INSERT

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

DELETE

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

UPDATE

In order to insert a row of new records, InnoDB saves the current system version number as the line version number and saves the current system version number to the original line as the line deletion ID.

Save these two additional system version numbers so that most reads are unlocked. This design makes it easy to read data, has good performance, and ensures that only rows that meet the standard will be read. The deficiency is that each row of records requires additional storage space, more line checking work, and some additional maintenance work.

MVCC only works under two isolation levels: REPEATABLE READ (repeatable) and READ COMMITTED (read commit). The other two isolation levels are not compatible with MVCC because READ UNCOMMITTED (read uncommitted) always reads the latest rows of data rather than rows that match the current transactional version. SERIALIZABLE (serialization) locks all rows read.

Thank you for reading! About the working principle of InnoDB MVCC is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, you can share it and let more people see it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.