Click blue above “Java Sunflower treasure dian ”, choice “ Set to star ”
source ：r6d.cn/qDxd
Mysql Lock type
One 、 Lock type introduction ：
MySQL There are three levels of lock ： Page level 、 Table level 、 Row level .
Table lock ： Low overhead , Locked fast ; A deadlock will not occur ; Large locking size , The highest probability of lock collisions , Lowest degree of concurrency .
Row-level locks ： Spending big , Lock the slow ; A deadlock occurs ; Locking granularity minimum , The lowest probability of lock collisions , The highest degree of concurrency .
Page lock ： Cost and lock time are between table lock and row lock ; A deadlock occurs ; Lock granularity is between table lock and row lock , The concurrency is average
Algorithm ：
next KeyLocks lock , Lock the record at the same time ( data ), And lock the front of the record Gap
Gap lock , Don't lock records , Just record the front Gap
Recordlock lock （ Lock data , No lock Gap）
So actually Next-KeyLocks=Gap lock + Recordlock lock
Two 、 Deadlock causes and examples
1、 The reasons causing ：
The so-called deadlock ： Two or more processes in the process of execution , A phenomenon of waiting for each other caused by competing for resources , If there is no external force , They will not be able to move forward . At this point, the system is said to be in a deadlock state or the system has produced a deadlock , These processes that are always waiting for each other are called deadlock processes . Table level locks do not create deadlocks . So the solution to deadlock is mainly for the most common InnoDB.
The key to deadlock is ： Two ( Or more ) Of Session The order of locking is different .
So the key to solving deadlock is ： Make a difference session Lock in order
2、 Generate examples ：
Case a
demand ： Split the investment into several random distributions to borrowers .
At first, the idea of business process is like this ：
After investors invest , Divide the amount randomly into several parts , Then randomly select a few from the borrower's list , And then through the bars select for update To update the balance in the borrower's statement, etc .
For example, two users invest at the same time ,A The user amount is randomly divided into 2 Share , To the borrower 1,2
B The user amount is randomly divided into 2 Share , To the borrower 2,1
Because the order of locking is different , Deadlock, of course, soon appeared .
The improvement of this problem is very simple , Just lock all the borrowers assigned directly at one time .
Select * from xxx where id in (xx,xx,xx) for update
stay in The list values in it mysql It will automatically sort from small to large , Lock is also a small to big lock
for example （ The following conversation id Primary key ）：
Session1:
mysql> select * from t3 where id in (8,9) for update;
+----+--------+------+---------------------+
| id | course | name | ctime |
+----+--------+------+---------------------+
| 8 | WA | f | 2016-03-02 11:36:30 |
| 9 | JX | f | 2016-03-01 11:36:30 |
+----+--------+------+---------------------+
rows in set (0.04 sec)
Session2:
select * from t3 where id in (10,8,5) for update;
Lock waiting ……
Actually at this point id=10 This record is not locked , but id=5 Has been locked up , The lock is waiting id=8 Here Don't believe it look at
Session3:
mysql> select * from t3 where id=5 for update;
Lock waiting
Session4:
mysql> select * from t3 where id=10 for update;
+----+--------+------+---------------------+
| id | course | name | ctime |
+----+--------+------+---------------------+
| 10 | JB | g | 2016-03-10 11:45:05 |
+----+--------+------+---------------------+
row in set (0.00 sec)
On the other session in id=5 It can't be locked , however id=10 It can be locked .
Case 2
In development , Often do this kind of judgment needs ： Query based on field values （ There is an index ）, If it doesn't exist , The insert ; Otherwise update .
With id Take the primary key as an example , Not yet id=22 The line of
Session1:
select * from t3 where id=22 for update;
Empty set (0.00 sec)
session2:
select * from t3 where id=23 for update;
Empty set (0.00 sec)
Session1:
insert into t3 values(22,'ac','a',now());
Lock waiting ……
Session2:
insert into t3 values(23,'bc','b',now());
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
When locking existing rows ( Primary key ),mysql Just the row lock .
When locking a nonexistent row ( Even if the condition is a primary key ),mysql It will lock in a certain range （ Yes gap lock ）
The locked range is ：
( Infinitesimal or less than locked in the table id The maximum of , Infinity or greater than the lock in the table id The minimum value of )
Such as ： If there is an existing one in the table id by （11 , 12）
So lock it （12, infinity ）
If the table already has id by （11 , 30）
So lock it （11,30）
The solution to this deadlock is ：
insert into t3(xx,xx) on duplicate key update `xx`='XX';
use mysql Special grammar to solve this problem . because insert Statement for primary keys , The inserted line, whether it exists or not , There will be only row locks
Case three
mysql> select * from t3 where id=9 for update;
+----+--------+------+---------------------+
| id | course | name | ctime |
+----+--------+------+---------------------+
| 9 | JX | f | 2016-03-01 11:36:30 |
+----+--------+------+---------------------+
row in set (0.00 sec)
Session2:
mysql> select * from t3 where id<20 for update;
Lock waiting
Session1:
mysql> insert into t3 values(7,'ae','a',now());
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
This is the same as the other cases I , It's just session1 It's out of order ,
Session2 Waiting for the Session1 Of id=9 Lock of ,session2 Again 1 To 8 Lock of （ Be careful 9 To 19 The scope of is not session2 Lock the ）, Last ,session1 You have to wait for a new line to be inserted session2, So the deadlock happened .
This is generally not going to happen in business requirements , Because you're locked id=9, But I want to insert id=7 The line of , That's a little bit of a jump , Of course, there must be solutions , That's business requirements , Avoid writing like this .
Case four
In general , Two session Through a sql Hold a lock , Then they visit each other's locked data to generate deadlock .
Case 5
Two single sql Statement involves the same locking data , But the lock sequence is different , Leading to deadlock .
Case 6
The deadlock scenario is as follows ：
Table structure ：
CREATE TABLE dltask (
id bigint unsigned NOT NULL AUTO_INCREMENT COMMENT ‘auto id’,
a varchar(30) NOT NULL COMMENT ‘uniq.a’,
b varchar(30) NOT NULL COMMENT ‘uniq.b’,
c varchar(30) NOT NULL COMMENT ‘uniq.c’,
x varchar(30) NOT NULL COMMENT ‘data’,
PRIMARY KEY (id),
UNIQUE KEY uniq_a_b_c (a, b, c)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’deadlock test’;
a,b,c The three column , Combined into a unique index , The primary key index is id Column .
Transaction isolation level ：
RR (Repeatable Read)
There is only one for each transaction SQL：
delete from dltask where a=? and b=? and c=?;
SQL Implementation plan of ：
Deadlock log ：
as everyone knows ,InnoDB Delete a record on , It's not really a physical deletion , Instead, the status is identified as deleted .( notes ： These records are marked as deleted , The follow-up will be done backstage Purge Operation to recycle , Physical delete . however , Records that delete status are stored in the index for a while .) stay RR Under isolation level , The unique index satisfies the query condition , But it's deleting records , How to lock ？InnoDB The processing policies here are different from the previous two , Or a combination of the first two strategies ： For deleted records that meet the conditions ,InnoDB It will add... To the record next key lock X( Add... To the record itself X lock , And lock in front of the record GAP, Prevent the insertion of new records that meet the conditions .) Unique Inquire about , Three situations , Corresponding to three locking strategies , Summarized below ：
here , We see that next key lock , Is it familiar ？ by the way , Transactions in the front deadlock 1, Business 2 A lock in a waiting state , Are all next key lock . Understand these three locking strategies , In fact, a certain concurrency scenario is constructed , The cause of the deadlock is already on the horizon . however , There is also a prerequisite strategy that needs to be introduced , That's it InnoDB Deadlock prevention strategy adopted internally .
Find records that meet the criteria , And the records are valid , Add... To the record X lock ,No Gap lock (lock_mode X locks rec but not gap);
Find records that meet the criteria , But the record is invalid ( Records identified as deleted ), Add... To the record next key lock ( And lock the record itself , And before recording Gap：lock_mode X);
No records meeting the criteria were found , Add to the first record that does not meet the conditions Gap lock , Ensure that no records meet the conditions are inserted (locks gap before rec);
Deadlock prevention strategy
InnoDB Inside the engine ( Or all of the databases inside ), There are many types of locks ： Transaction lock ( Row lock 、 Table locks ),Mutex( Protect internal shared variable operations )、RWLock( Also known as Latch, Protect internal page reading and modification ).
InnoDB Each page is 16K, When reading a page , You need to add S lock , When updating a page , You need to add X lock . In any case , Operate a page , Will lock the page , After the page lock is added , Only index records stored in the page will not be modified concurrently .
therefore , To modify a record ,InnoDB How to deal with it internally ：
According to the given query condition , Find the corresponding record page ;
Add... To the page X lock (RWLock), Then look for records that meet the conditions on the page ;
In the case of page lock , Apply transaction lock to the records that meet the conditions ( Row lock ： According to whether the record meets the query criteria , Whether the record has been deleted , They correspond to the above mentioned 3 One of the locking strategies );
Relative to transaction locks , A page lock is a lock held for a short time , And transaction locks ( Row lock 、 Table locks ) It's a long held lock . therefore , To prevent deadlock between page lock and transaction lock .InnoDB Made a deadlock prevention strategy ： Lock holding transactions ( Row lock 、 Table locks ), You can wait to get the page lock ; But vice versa , Hold page lock , Cannot wait to hold transaction lock .
According to deadlock prevention strategy , Hold page lock , When adding locks , If the row lock needs to wait . Release the page lock , Then wait for the row lock . here , Row lock acquisition without any lock protection , So after the uplink lock is added , Records may have been modified concurrently . therefore , At this point to add back the page lock , Re judge the state of the record , Under the protection of page lock again , Lock records . If the record is not concurrently modified , Then the second lock can be completed quickly , Because you already hold the same pattern of locks . however , If the record has been modified concurrently , that , It is possible to lead to the deadlock mentioned earlier in this article .
The above InnoDB Deadlock prevention processing logic , Corresponding function , yes row0sel.c::row_search_for_mysql(). Interested friends , You can track the process of debugging this function , Very complicated , But it's focused on InnoDB The essence of .
Analyze the cause of deadlock
So much bedding has been done , With Delete Operation of the 3 Kind of lock logic 、InnoDB After preparing the knowledge of deadlock prevention strategy , Then I will look back to analyze the deadlock problem mentioned in this article , It'll come in handy , Half the work, twice the work .
First , hypothesis dltask There's only one record in ：(1, ‘a’, ‘b’, ‘c’, ‘data’). Three concurrent transactions , Do the following at the same time SQL：
delete from dltask where a=’a’ and b=’b’ and c=’c’;
And the following concurrent execution logic is generated , A deadlock will occur ：
The concurrent process analyzed above , The reason for deadlock in deadlock log is fully demonstrated . Actually , According to the business 1 step 6, And business 0 step 3/4 The order between them is different , There is another possibility in the deadlock log , That's the business 1 The lock mode of waiting is on record X lock + No Gap lock (lock_mode X locks rec but not gap waiting). The second situation , It's also ” Mioclear ” In the deadlock case given by the students , Use MySQL 5.6.15 The cause of deadlock caused by version test .
This kind of deadlock , A few premises that come into being ：
Delete operation , The deletion of the equivalent query on the unique index is targeted ;( Delete under scope , It can also create deadlocks , But the scene of deadlock , With the scenario analyzed in this article , Somewhat different )
There are at least 3 individual ( Or more ) Concurrent delete operations for ;
Concurrent delete operations , It is possible to delete to the same record , And ensure that the deleted records must exist ;
The isolation level of the transaction is set to Repeatable Read, At the same time innodb_locks_unsafe_for_binlog Parameters ( This parameter defaults to FALSE);(Read Committed Isolation level , Because you can't add Gap lock , There will be no next key, So there is no deadlock )
It uses InnoDB Storage engine ;( crap ！MyISAM The engine doesn't have a row lock at all )
How to detect whether code runs deadlock
https://blog.csdn.net/kk185800961/article/details/79528841
https://blog.csdn.net/yucaifu1989/article/details/79400446
Recommended reading
Code comparison tool , I'll use this 6 individual
Share my favorite 5 A free online SQL Database environment , It's so convenient ！
Spring Boot A combination of three moves , Hand in hand to teach you to play elegant back-end interface
MySQL 5.7 vs 8.0, You choose the one ？ Net friend ： I'm going to stay where I am ~
Last , I recommend you an interesting and interesting official account ： The guy who wrote the code ,7 Old programmers teach you to write bug, reply interview | resources Send you a complete set of Development Notes There's a surprise
Sweep yards attention