编程知识 cdmana.com

Multi version concurrency control (mvcc) of MySQL

One 、 Snapshot read and current read

Read the snapshot (SnapShot Read) It's a consistent, unlocked read , yes InnoDB One of the core reasons why concurrency is so high .

stay READ COMMITTED Under transaction isolation level , Consistent read without lock refers to , Always read the latest snapshot data of the locked row , So other transactions modify that row of data , The transaction can also read , It fits RC There is the problem of unreal reading under the isolation level ;

stay REPEATABLE READ Under transaction isolation level , Consistent read without lock refers to , Data read by transaction , Or data that already existed before the transaction started , Either the data inserted or modified by the transaction itself .( This isolation level is described below );

Simple without lock SELECT All belong to snapshot reading , for example :

SELECT * FROM t WHERE id=1;

The snapshot read corresponds to the current read (Current Read), The current read is to read the latest data , Instead of historical versions of the data . The lock SELECT It belongs to the current reading , for example :

SELECT * FROM t WHERE id=1 LOCK IN SHARE MODE;
SELECT * FROM t WHERE id=1 FOR UPDATE;

SELECT...FOR UPDATE Add a... To the read row record X lock , Other transactions can't put any lock on the locked row .

SELECT...LOCK IN SHARE MODE Add a... To the read row record S lock , Other transactions can add S lock , But if you add X lock , It will be blocked .

Two 、 Multi version concurrency control based on snapshot reading

The full English name of multi version concurrency control technology is :Multiversion Concurrency Control, abbreviation MVCC, By saving historical versions of the data , The concurrency control of database can be realized by managing multiple versions of data rows . In this way, we can determine whether the data is displayed by comparing the version numbers , When reading data, you don't need to lock to ensure the isolation effect of transactions ( It can be understood that it can be understood as a joy watch lock ).

Multi version concurrency control (MVCC) Only repeatable (REPEATABLE READ) Submit and read (READ COMMITTED) Working at two isolation levels , The other two isolation levels are the same as MVCC Are not compatible , Because I didn't submit to read (READ UNCOMMITTED), Always read the latest data lines , Instead of data lines that match the current transaction version ; And serializable (SERIALIZABLE) Will lock all read lines .

MySQL Most of the transactional storage engines implemented are not simple row level locks . Based on the consideration of improving concurrent performance , They generally implement multi version concurrency control at the same time (MVCC). Not only is MySQL, Include Oracle、PostgreSQL And other database systems have been implemented MVCC, But their implementation mechanisms are not the same , because MVCC There is no uniform implementation standard , There is typically optimism (optimistic) Concurrency control and pessimism (pessimistic) concurrency control .

3、 ... and 、 What problems are solved by multi version concurrency control ?

1. The problem of blocking between reading and writing

adopt MVCC It can make reading and writing not block each other , That is, reading does not block writing , Writing doesn't block reading , In this way, the ability of concurrent transaction processing can be improved .

Improve the evolution of concurrency :

  • Ordinary lock , Serial only ;
  • Read-write lock , Read concurrency can be realized ;
  • Data multi version concurrency control , Read and write concurrency can be realized .

2. Reduced the probability of deadlock

because InnoDB Of MVCC Adopted the optimistic lock way , There is no need to lock when reading data , For write operations , Lock only the necessary lines .

3. Solve the problem of consistent reading

Consistent reads are also known as snapshot reads , When we query a snapshot of a database at a certain point in time , You can only see the result of the transaction commit update before this point in time , You can't see the update result of transaction commit after this point in time .

Four 、InnoDB Of MVCC How it works ?

1. InnoDB How to store multiple versions of a record ?

Transaction version number : Every time a transaction is opened , We all get a transaction from the database ID( The transaction version number ), This business ID It's self growing , adopt ID size , We can then determine the chronological order of transactions .

Hidden columns of row records : InnoDB The leaf segment of the page stores the data page , Row records are saved in the data page , There are some important hidden fields in the row record :

  • DB_ROW_ID:6-byte, Hidden lines ID, Used to generate the default cluster index . If we don't specify the cluster index when we create the data table , At this time InnoDB You'll use this to hide ID To create a clustered index . Clustering index can improve the efficiency of data search .
  • DB_TRX_ID:6-byte, The transaction that manipulates this data ID, That is, the last transaction to insert or update the data ID.(InnoDB Insertion 、 to update 、 Delete will update the transaction ID, Simultaneous deletion marks a special bit as deleted )
  • DB_ROLL_PTR:7-byte, rollback pointer , That's what points to this record Undo Log Information .

Undo Log: InnoDB Save the snapshot of row records in Undo Log in , We can find them in the rollback segment , As shown in the figure below , The rollback pointer concatenates all snapshot records of a data row through a linked list structure , The records of each snapshot are kept at that time db_trx_id, It's also the transaction that operates this data at that time point ID. So if we're looking for a snapshot of history , You can find it by traversing the rollback pointer .



Reference link :MySQL Multi version concurrency control of (MVCC)

版权声明
本文为[JMCui]所创,转载请带上原文链接,感谢
https://cdmana.com/2020/12/20201225134524408i.html

Scroll to Top