编程知识 cdmana.com

Brief introduction of transaction characteristics, isolation level and mvcc multi version concurrency control of MySQL database

The nature of transactions

Database if transaction support , Just meet the following four features (ACID).

Atomicity (A:Atomicity)

In a transaction , Multiple sql operation , Or make it together ( All data operations succeeded ), Or roll back together ( One of them didn't succeed , Other data operations are restored to the start state together ).

Uniformity (C:Consistency)

Data is accurate before modification , After modification, it should be accurate . It refers to data from a consistent state , Switch to another state of consistency .( for example :A and B All in all 10 An apple ,A I transferred some apples to B, When the business is over ,A and B In all, or 10 An apple ).

Isolation, (I:Isolation)

When the database has multiple transactions to execute together , There is no interaction between the various transactions , Each transaction does not perceive that there are other transactions being performed .

persistence (D:Durability)

After the database transaction is committed , Data modification is permanent , Even if the database restarts , Turn off... In case of other problems , After opening again , The modification of the data submitted before is still in effect ( for example : The program performs data operations , After the transaction is committed , Suddenly, the database server is down and unavailable , After recovery , The data is still in the modified state ).

Transaction isolation level

Read uncommitted

Two transactions operate the database at the same time , One of the things that changed the database , Not submit , Another thing can read the modified data .

Read submitted

There are two things , The first transaction queries a piece of data , The second transaction modifies the data after it is committed , The first transaction queries the data again , The results of two queries before and after the first transaction are inconsistent .

Repeatable (mysql Default isolation level , adopt MVCC<Mutil-Version Concurrency Control> Mechanism realization )

There are two things , The first transaction queries a piece of data , The second transaction modifies the data after it is committed ( For performance reasons , Using the optimistic lock as the theoretical basis MVCC< Multi version concurrency control > To achieve ), The first transaction queries the data again , The results of the two queries before and after the first transaction are not the same .

Serialization ( solve Fantasy reading The problem of )

Repeatable read can limit two transactions to modify and delete data simultaneously, resulting in inconsistent queries , But there is no limit to insert( The row lock can only lock the line , New data cannot be limited ).

The case scene of fantasy reading : There are two things , The number of data in the first data query list , The second transaction adds data to the table and commits the transaction , The first transaction re queries the number of data , The first transaction has inconsistent data between the two queries .

Serialization : There are two things , The first transaction queries data , The second transaction adds data to the table , Will report a mistake ( Table lock ), Can't insert data , The first transaction queries the data again , The two results are consistent . Serialization has low concurrency .

MVCC Introduction of the principle :

Through transaction id Control different versions of data ( Because through transactions id Control data , So in the following data id It will be repeated ),sql When querying , The last two hidden columns will not show . The transaction start order corresponding to the following table is consistent with the order of number size , In the end, everything ends together .( Through the concurrency situation, it is simply explained that MVCC principle )

Business id=1 insert data ; Business id=3 insert data ; Business id=4 Delete data ; Business id=5 Modifying data ; The final data in the table are as follows :

id data Update transactions ID( Hide columns ) Delete transaction ID( Hide columns )
1 Zhang San 1 4
2 Xiao Ming 1 empty
3 Li Si 1 empty
4 Wang Wu 3 empty
2 Xiao Ming 111 5 empty

Business id=2, Query full table data , The query data is as follows :

start transactionselect * from a; //(1) 

select * from a; //(2) 

commit

hypothesis :

Business id=2 in (1) The query occurs in a transaction id=1 after  ;

Business id=2 in (2) The query occurs in a transaction id=3 After inserting data ;

Business id=2 The results of both queries are the same ( Through transaction id Do the limit , Only queries less than or equal to the current transaction id The data of )

id data Update transactions ID( Hide columns ) Delete transaction ID( Hide columns )
1 Zhang San 1 empty
2 Xiao Ming 1 empty
3 Li Si 1 empty

hypothesis :

Business id=2 in (1) The query occurs in a transaction id=1 after  ;

Business id=2 in (2) The query occurs in a transaction id=4 After deleting data ;

Business id=2 The results of both queries are the same ( Through transaction id Do the limit , Only queries less than or equal to the current transaction id The data of )

id data Update transactions ID( Hide columns ) Delete transaction ID( Hide columns )
1 Zhang San 1 4
2 Xiao Ming 1 empty
3 Li Si 1 empty

hypothesis :

Business id=2 in (1) The query occurs in a transaction id=1 after  ;

Business id=2 in (2) The query occurs in a transaction id=5 After modifying data ( Modification is also equivalent to inserting a piece of data , Data transactions id Is different );

Business id=2 The results of both queries are the same ( Through transaction id Do the limit , Only queries less than or equal to the current transaction id The data of )

id data Update transactions ID( Hide columns ) Delete transaction ID( Hide columns )
1 Zhang San 1 empty
2 Xiao Ming 1 empty
3 Li Si 1 empty

版权声明
本文为[Cheng Yu]所创,转载请带上原文链接,感谢

Scroll to Top