Deeply understand MySQL transaction isolation level and locking mechanism

** In depth understanding of Mysql Transaction isolation level and locking mechanism **

** summary **
Our database usually executes multiple transactions concurrently , Multiple transactions may add, delete, modify and query the same batch of data concurrently , Probably It will lead to what we call dirty writing 、 Dirty reading 、 It can't be read repeatedly 、 Read these questions . The essence of these problems is the multi transaction concurrency of database , In order to solve the problem of multi transaction concurrency , Transaction isolation mechanism is designed for database 、 Locking machine system 、MVCC Multi version concurrency control isolation mechanism , Use a set of mechanisms to solve the problem of multi transaction concurrency . Next , We will explain these machines in depth system , Let's thoroughly understand the execution principle inside the database . 

** Affairs and ACID attribute ** The business is made up of a group of SQL A logical processing unit made up of statements , The transaction has the following 4 Attributes , Usually referred to as transaction ACID attribute . Atomicity (Atomicity) ： A transaction is an atomic unit of operation , Its modification of data , Or do it all , Or none of them . Uniformity (Consistent) ： At the beginning and end of the transaction , Data must be consistent . This means that all relevant data specifications Must be applied to the modification of the transaction , To maintain the integrity of the data . Isolation, (Isolation) ： The database system provides a certain isolation mechanism , Ensure that transactions are not affected by external concurrent operations “ single state ” Environmental execution . This means that the intermediate state in the transaction process is not visible to the outside , vice versa . persistence (Durable) ： After the transaction completes , Its modification of data is permanent , Even if there is a system failure, it can keep .

** Problems caused by concurrent transaction processing **

** Update missing (Lost Update) Or dirty writing ** 
When two or more transactions select the same row , When the row is then updated based on the originally selected value , Because each transaction does not know the existence of other transactions stay , There will be lost updates – The last update covers the updates made by other firms .

** Dirty reading （Dirty Reads）**

Transaction isolation level

“ Dirty reading ”、“ It can't be read repeatedly ” and “ Fantasy reading ”, In fact, it's all about database read consistency , The database must provide a certain transaction isolation mechanism

To solve

The more strict the transaction isolation of the database , The less side effects , But the more it costs , Because the essence of transaction isolation is to make transactions to a certain extent

On “ Serialization ” Conduct , This is obviously related to “ Concurrent ” Is contradictory .

meanwhile , Different applications have different requirements for read consistency and transaction isolation , For example, many applications are right for “ It can't be read repeatedly " and “ Fantasy reading ” Does not

sensitive , Perhaps more concerned about the ability of concurrent data access .

Often look at the transaction isolation level of the current database : show variables like 'tx_isolation';

Set the transaction isolation level ：set tx_isolation='REPEATABLE-READ';

Mysql The default transaction isolation level is repeatable read , use Spring When developing programs , If you do not set the isolation level, the default is Mysql Set the interval

Off grade , If Spring If it is set, the isolation level that has been set will be used

Lock details

A lock is a mechanism by which a computer coordinates multiple processes or threads to access a resource concurrently .

In the database , In addition to traditional computing resources （ Such as CPU、RAM、I/O etc. ） Beyond contention , Data is also a resource that users need to share

Source . How to ensure the consistency of data concurrent access 、 Validity is a problem that all databases must solve , Lock conflicts also affect database concurrency

An important factor in access performance .

Lock classification

In terms of performance, it can be divided into Optimism lock ( Use version comparison to achieve ) and Pessimistic locking

From the type of database operation , It is divided into Read lock and write lock ( All belong to pessimistic lock )

Read the lock （ Shared lock ,S lock (Shared)）： For the same data , Multiple read operations can be performed simultaneously without affecting each other

Write lock （ Exclusive lock ,X lock (eXclusive)）： Before the current write operation is completed , It blocks other write and read locks

From the granularity of data operations , It is divided into Watch lock and row lock

Table locks

Lock the whole table with each operation . Low overhead , Locked fast ; A deadlock will not occur ; Large locking size , The highest probability of lock collisions , Lowest degree of concurrency ;

It is generally used in the scenario of whole table data migration .

Basic operation

1 ‐‐ Build table SQL 
2 CREATE TABLE `mylock` ( 
3 `id` INT (11) NOT NULL AUTO_INCREMENT, 
4 `NAME` VARCHAR (20) DEFAULT NULL, 
5 PRIMARY KEY (`id`) 
6 ) ENGINE = MyISAM DEFAULT CHARSET = utf8; 
7
8 ‐‐ insert data  
9 INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('1', 'a');
10 INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('2', 'b'); 
11 INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('3', 'c'); 
12 INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('4', 'd');

Add meter lock by hand

lock table The name of the table read(write), The name of the table 2 read(write);

View the lock added to the table

show open tables;

Delete table lock

unlock tables;

Row lock

Lock one row of data per operation . Spending big , Lock the slow ; A deadlock occurs ; Locking granularity minimum , The lowest probability of lock collisions , Concurrency is the highest

high .

InnoDB And MYISAM There are two big differences ：

InnoDB Support transactions （

TRANSACTION）

InnoDB Row level locking is supported

Row lock Demo

One session Enable transaction updates without committing , the other one session Updating the same record will block , Updating different records will not block

summary ：

MyISAM In the execution of the query statement SELECT front , Will automatically lock all tables involved , In execution update、insert、delete The operation will start from

Add a write lock to the table involved .

InnoDB In the execution of the query statement SELECT when ( Non serial isolation level ), No locks . however update、insert、delete The operation will add lines

lock .

In short , Namely Reading locks block writing , But it doesn't block reading . A write lock blocks both reading and writing .

Case study of row lock and transaction isolation level

Read uncommitted ：

（

1） Open a client A, And set the current transaction mode to read uncommitted（ Uncommitted read ）, Query table account Initial

value ：

set tx_isolation='read-uncommitted';

（

2） On the client side A Before the transaction is committed , Open another client B, Update table account：

3） At this time , Although the client B The business of has not been submitted , But the client A You can find B Updated data ：

 

4） Once the client B The transaction of is rolled back for some reason , All operations will be undone , That client A The data queried is actually dirty data ：

5） On the client side A Execute UPDATE statement update account set balance = balance - 50 where id =1,lilei Of balance no

Have become 350, was 400, Isn't that weird? , The data are inconsistent , It would be naive of you to think so 了 , In the application , We will

use 400-50=350, I didn't know that other sessions rolled back , To solve this problem, read committed isolation level

3、 Read submitted

（

1） Open a client A, And set the current transaction mode to read committed（ Uncommitted read ）, Query table account All records of

record ：

set tx_isolation='read-committed';

2） On the client side A Before the transaction is committed , Open another client B, Update table account：

3） At this time , client B The business of has not been submitted , client A Can't find B Updated data , Solved the problem of dirty reading ： 

4） client B Transaction commit for

5） client A Execute the same query as in the previous step , result Inconsistent with the previous step , That is to say, there is the problem of non repetition

4、 Repeatable

（

1） Open a client A, And set the current transaction mode to repeatable read, Query table account All records

set tx_isolation='repeatable-read';

2） On the client side A Before the transaction is committed , Open another client B, Update table account And submit

3） On the client side A Query table account All records , And steps （1） The results are consistent , There is no non repeatable reading problem

4） On the client side A, Then perform update account set balance = balance - 50 where id = 1,balance Did not become

400-50=350,lilei Of balance It's worth the steps 2 Medium 350 To calculate , So it is 300, The consistency of the data has not been broken . can

Used under the isolation level of repeated read MVCC(multi-version concurrency control) Mechanism ,select Operation will not update version number ,

It's snapshot reading （ Version history ）;insert、update and delete Will update version number , It's the current reading （ current version ）.

5） Reopen the client B, Insert a new data and submit

6） On the client side A Query table account All records , No new data found , So there is no unreal reading

7) Verify unreal reading

On the client side A perform update account set balance=888 where id = 4; Can update successfully , You can find the client again B New data

5、 Serialization

1） Open a client A, And set the current transaction mode to serializable, Query table account The initial value of the ：

set tx_isolation='serializable';

2） Open a client B, And set the current transaction mode to serializable, Update the same id by 1 Your records will be blocked waiting , to update id

by 2 Your record can be successful , Description in serial mode innodb The query will also be locked .

If the client A Executing a range query , Then the All lines in the range include the gap interval range where each line of record is located ( Even if this line of data

It will be locked before it is inserted , This is a clearance lock ) Will be locked . At this point, if the client B Inserting data within this range will be blocked , So I Avoid unreal reading .

This isolation level is extremely low in concurrency , It's rarely used in development .

Clearance lock (Gap Lock)

Clearance lock , Lock is the gap between two values .Mysql The default level is repeatable-read, Is there a way to solve the unreal reading problem ？ Clearance lock

In some cases, the problem of unreal reading can be solved .

hypothesis account The data in the table are as follows ：

Then the gap is id by (3,10),(10,20),(20, It's just infinite ) These three intervals ,

stay Session_1 Following execution update account set name = 'zhuge' where id > 8 and id <18;, Other Session no

The law is in this All line records included in the scope ( Include gap line records ) And the gap where the row record is located Insert or modify any data in , namely id stay

(3,20] Data cannot be modified in any interval , Pay attention to the last one 20 Also included .

The gap lock is only effective at the repeatable read isolation level

Temporary key lock (Next-key Locks)

Next-Key Locks It's a combination of row lock and clearance lock . Like this in the above example (3,20] The whole interval of can be called critical lock .

No index row lock will be upgraded to table lock

The lock is mainly added to the index , If you update a non indexed field , Row locks may become table locks

session1 perform ：update account set balance = 800 where name = 'lilei';

session2 Any operation on any row of the table will block

InnoDB The row lock of is the lock added for the index , It's not a lock on records . And the index cannot be invalidated , Otherwise, it will be upgraded from row lock to

Table locks .

You can also lock a row with lock in share mode( Shared lock ) and for update( Exclusive lock ), for example ：select * from

test_innodb_lock where a = 2 for update; So other session Only this line of data can be read , Modification will be blocked , Until locked

Yes session Submit

Conclusion

Innodb The storage engine implements row level locking , Although the performance loss in the implementation of locking mechanism may be more than table level locking

Higher , But it's much better than MYISAM The watch level is locked . When the system concurrency is high ,Innodb

The overall performance and MYISAM There will be obvious advantages in comparison .

however ,Innodb There is also a weak side to row level locking , When we don't use it properly , May let Innodb The overall performance of

Not only can it not be compared with MYISAM high , Even worse .

Line lock analysis

clear through InnoDB_row_lock State variables are used to analyze the contention of row locks on the system

 show status like 'innodb_row_lock%';

The description of each state quantity is as follows ：

Innodb_row_lock_current_waits: The number of currently waiting locks

Innodb_row_lock_time: The total length of time from system startup to lock up

Innodb_row_lock_time_avg: The average time it takes to wait

Innodb_row_lock_time_max： The longest waiting time from system startup to now Innodb_row_lock_waits: The total number of times the system has been waiting since it was started

For this 5 Two state variables , The more important thing is ：

Innodb_row_lock_time_avg （ Average waiting time ）

Innodb_row_lock_waits （ Total waiting times ）

Innodb_row_lock_time（ The total waiting time ）

Especially when waiting times are high , And every time the waiting time is not small , We need to analyze why there are so many waits in the system ,

Then according to the results of the analysis to develop optimization plan .

see INFORMATION_SCHEMA Data table related to system library lock

Deadlock

set tx_isolation='repeatable-read';

Session_1 perform ：select * from account where id=1 for update;

Session_2 perform ：select * from account where id=2 for update;

Session_1 perform ：select * from account where id=2 for update;

Session_2 perform ：select * from account where id=1 for update;

Check the recent deadlock log information ：show engine innodb status\G;

In most cases mysql Deadlock can be detected automatically and the transaction that generated the deadlock can be rolled back , But in some cases mysql Can't automatically detect deadlocks

Lock optimization suggestions

As far as possible, all data retrieval should be done through index , Avoid upgrading non indexed row locks to table locks

Design index reasonably , Try to narrow down the range of locks

Minimize the range of search conditions , Avoid gap locks

Try to control the transaction size , Reduce the amount of locked resources and the length of time , Involving transaction locking sql Try to put it at the end of the transaction

As low level transaction isolation as possible