Most of Mysql’s transactional storage engines are not simple row-level locking. Mysql is not the only database that supports MVVC. Orcale, PostgreSql and others implement MVVC, but they do it in different ways because there is no unified specification for MVVC.
In fact, MVVC can be understood as a variation of the row level lock, it will be selective lock, avoid the lock operation in most cases, so save the CPU overhead, although each database implementation is different, but are concurrent read, serial write (necessary records).
MVVC is implemented by keeping a snapshot of the data at a point in time, that is, each transaction should see the same data no matter how much code is executed and for how long. Without MVVC, according to the isolation level of Mysql, we can find that the data queried by each transaction may be inconsistent between different transactions. Please refer to transaction characteristics and isolation level for clarification.
Although different storage engines have different ways to implement MVVC, the most classic are optimistic concurrency control and pessimistic concurrency control. Let’s explain how they implement concurrency control respectively.
The InnoDB storage engine does this by keeping two fields (hidden) at the end of each row, one for the creation time of the row and the other for the expiration time of the row. Of course, the storage is not the actual time value, but the system version number, every time a transaction is started, the system version number will be superimposed, the entire transaction will take the current version number as the basic condition for judgment.
Query: InnoDB queries each row based on two criteria: 1.InnoDB searches for rows whose version number is less than or equal to the current transaction version. 2. The deleted version of data is either not specified or must be larger than the current transaction version number to ensure that the data read by the transaction is not deleted before the transaction is started.
Insert: InnoDB will save the current system version number in a new row as the row version number when inserting.
Delete: InnoDB saves the current system version number of the data to be deleted as delete.
Modified: InnoDB does not insert a new record, save the current system version number as the row version number, and save the current system version number to the original row as delete mark.
Keeping these two additional version numbers allows most read operations to go unlocked, improves system performance, and ensures that only conforming records are supervised, but these two fields take up additional storage space.
Pessimistic concurrency control is very simple, that is, lock the specified row, only one transaction can be executed at a time, the other food is blocked, so the message is not very good, but there is no need to add additional fields.
Repeatable Read ** MVVC only works at ** Read COMMITTED and REPEATable Read ** isolation levels. If you read the transaction characteristics and isolation levels above, you will understand why.