Mvcc multi version concurrency control mechanism of MySQL

zfcq 2022-01-27 07:26:27 阅读数:578

mvcc multi version concurrency control

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 :

trx_id

This id The transaction used to store every time a cluster index record is modified id.

roll_pointer

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 )
undo journal .png

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 .
copyright:author[zfcq],Please bring the original link to reprint, thank you. https://en.javamana.com/2022/01/202201270726219456.html