编程知识 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 , Read .........

版权声明
本文为[Irving the procedural ape]所创,转载请带上原文链接,感谢
https://cdmana.com/2020/12/20201225140309604C.html

Scroll to Top