MVCC Multi version concurrency control mechanism
- Full English name ：Multi-Version Concurrency Control
- MVCC The isolation will not be guaranteed by locking and mutual exclusion , Avoid frequent locking and mutual exclusion .
- In the serialization isolation level, in order to ensure high isolation, all operations are separated It is realized by locking and mutual exclusion
- Mysql At the read committed and repeatable read isolation levels MVCC Mechanism .
- Lock mechanism can control concurrent operation , But its system overhead is large , and MVCC Can replace row level lock in most cases , Use MVCC, Can reduce its system overhead .
MVCC Realization way ： Version chain （undo journal ）
- stay InnoDB In the engine list , It has two necessary hidden columns in its clustered index record ：
This id The transaction used to store every time a cluster index record is modified id.
Every time a cluster index record is modified , The old version will be written into undo In the log . This roll_pointer It's storing a pointer , It points to the location of the last version of this cluster index record , Use it to get the record information of the previous version .( Pay attention to the insert operation undo Logs don't have this property , Because it doesn't have the old version )
MVCC How to query ： Consistency view read-view
- The difference between committed reads and repeatable reads is that they generate ReadView The strategy is different . Read committed is that each query will generate a new read-view, Can be read repeatedly. All queries are generated when the first query is used read-view.
- All uncommitted transactions when the query is executed by id Array （ The smallest in the array id by min_id） And the largest transaction that has been created id（max_id）
- Anything in business sql The query results need to be from the corresponding Start with the latest version in the data chain read-view Do a comparison to get the final snapshot result .
Version chain alignment rules ：
- If row Of trx_id It falls on the green part ( trx_id<min_id ), Indicates that this version was generated by a committed transaction , This data is visible ;
- If row Of trx_id It falls on the red part ( trx_id>max_id ), Indicates that this version is generated by a transaction that will be started in the future , It's invisible ( if row Of trx_id It's that your own affairs are visible at present ）;
- If row Of trx_id It falls on the yellow part (min_id <=trx_id<= max_id), There are two situations
- if row Of trx_id In the view array , Indicates that this version is generated by uncommitted transactions , invisible ( if row Of trx_id It's that your own affairs are visible at present );
- if row Of trx_id Not in the view array , Indicates that this version is generated by committed transactions , so .