Mvcc multi version concurrency control

One 、 Summary

MVCC, Full name Multi-Version Concurrency Control, Multi version concurrent control .MVCC It's a method of concurrency control , Generally in the database management system , Realize concurrent access to database , Implement transaction memory in programming language .MVCC stay MySQL InnoDB In order to improve the database concurrency performance , Deal with reading in a better way - Write conflict , Even if there is a conflict between reading and writing , Can also do without lock , Non blocking concurrent read .

MVCC The purpose of multi version concurrency control , Implementation in database , It's to solve the conflict between reading and writing , Its implementation principle mainly depends on the 3 Implicit fields ,undo log ,read view To achieve .

Two 、3 Implicit fields

In addition to our custom fields, each line of records , And the database is implicitly defined DB_TRX_ID,DB_ROLL_PTR,DB_ROW_ID Etc

1、DB_TRX_ID

6byte, Recently revised ( modify / Insert ) Business ID： Record create this record / The transaction that last modified the record ID

2、DB_ROLL_PTR

7byte, rollback pointer , Point to the previous version of this record （ Store in rollback segment in ）

3、DB_ROW_ID

6byte, Implied self increasing ID（ Hide primary key ）, If the data table has no primary key ,InnoDB Will automatically use DB_ROW_ID Generate a clustered index

DB_ROW_ID Is the only implicit primary key generated by the database for this row by default ,DB_TRX_ID Is the transaction currently operating the record ID, and DB_ROLL_PTR It's a rollback pointer , Used for coordination undo journal , Point to the previous old version .

3、 ... and 、undo log

undo log There are two main types ：

1、insert undo log

On behalf of business in insert When new records are made undo log, Only when the transaction is rolled back , And it can be discarded immediately after the transaction is committed .

2、update undo log

The business is going on update or delete When the undo log; Not only is it necessary when the transaction is rolled back , You also need to read the snapshot ; So you can't delete , Only if a fast read or transaction rollback does not involve the log , The corresponding log will be purge Thread unified cleaning .

purge Threads ：

From the previous analysis, we can see that , In order to achieve InnoDB Of MVCC Mechanism , Update or delete operation is just to set the old record deleted_bit, It doesn't really delete obsolete records .

To save disk space ,InnoDB Specialized purge Thread to clean up deleted_bit by true The record of . In order not to affect MVCC Normal operation of ,purge The thread itself maintains a read view（ This read view Equivalent to the oldest active transaction in the system read view）; If a record of deleted_bit by true, also DB_TRX_ID be relative to purge Thread read view so , Then this record must be cleared safely .

Yes MVCC The essence of helping is update undo log ,undo log In fact, there is rollback segment Middle old record chain .

Four 、read view

What is? Read View, To put it bluntly Read View It is the read view produced when the transaction is performing a snapshot read operation (Read View), At the moment the snapshot of the transaction is read , A snapshot of the current database system will be generated , Record and maintain the current active transactions of the system ID( When each transaction starts , Will be assigned a ID, This ID Is increasing , So the latest business ,ID The bigger the value is. )

So we know Read View It's mainly used for visibility judgment , That is, when we perform a snapshot read for a transaction , Create a Read View Read view , Compare it to a condition to determine which version of the data can be seen by the current transaction , It could be the latest data , It may also be recorded in this line undo log Some version of the data in it .

Read View Follow a visibility algorithm , Mainly in the latest record of the data to be modified DB_TRX_ID（ The current transaction ID） out , With other active transactions of the system ID To compare （ from Read View maintain ）, If DB_TRX_ID Follow Read View There are some comparisons between the properties of , Not in line with visibility , Then go through DB_ROLL_PTR Roll back the pointer to get it out Undo Log Medium DB_TRX_ID Compare again , That is, traversing the list of DB_TRX_ID（ From the beginning to the end of the chain , That is, from the latest revision ）, Until you find something that meets certain conditions DB_TRX_ID, So this DB_TRX_ID The old record is the latest and old version that the current transaction can see .