Causes and solutions of MySQL deadlock

osc_bzc91lgy 2020-11-09 13:38:55
causes solutions mysql deadlock


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,5for 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(30NOT NULL COMMENT ‘uniq.a’,
    b varchar(30NOT NULL COMMENT ‘uniq.b’,
    c varchar(30NOT NULL COMMENT ‘uniq.c’,
    x varchar(30NOT 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 
版权声明
本文为[osc_bzc91lgy]所创,转载请带上原文链接,感谢

  1. 【计算机网络 12(1),尚学堂马士兵Java视频教程
  2. 【程序猿历程,史上最全的Java面试题集锦在这里
  3. 【程序猿历程(1),Javaweb视频教程百度云
  4. Notes on MySQL 45 lectures (1-7)
  5. [computer network 12 (1), Shang Xuetang Ma soldier java video tutorial
  6. The most complete collection of Java interview questions in history is here
  7. [process of program ape (1), JavaWeb video tutorial, baidu cloud
  8. Notes on MySQL 45 lectures (1-7)
  9. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  10. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  11. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  12. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  13. 【递归,Java传智播客笔记
  14. [recursion, Java intelligence podcast notes
  15. [adhere to painting for 386 days] the beginning of spring of 24 solar terms
  16. K8S系列第八篇(Service、EndPoints以及高可用kubeadm部署)
  17. K8s Series Part 8 (service, endpoints and high availability kubeadm deployment)
  18. 【重识 HTML (3),350道Java面试真题分享
  19. 【重识 HTML (2),Java并发编程必会的多线程你竟然还不会
  20. 【重识 HTML (1),二本Java小菜鸟4面字节跳动被秒成渣渣
  21. [re recognize HTML (3) and share 350 real Java interview questions
  22. [re recognize HTML (2). Multithreading is a must for Java Concurrent Programming. How dare you not
  23. [re recognize HTML (1), two Java rookies' 4-sided bytes beat and become slag in seconds
  24. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  25. RPC 1: how to develop RPC framework from scratch
  26. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  27. RPC 1: how to develop RPC framework from scratch
  28. 一次性捋清楚吧,对乱糟糟的,Spring事务扩展机制
  29. 一文彻底弄懂如何选择抽象类还是接口,连续四年百度Java岗必问面试题
  30. Redis常用命令
  31. 一双拖鞋引发的血案,狂神说Java系列笔记
  32. 一、mysql基础安装
  33. 一位程序员的独白:尽管我一生坎坷,Java框架面试基础
  34. Clear it all at once. For the messy, spring transaction extension mechanism
  35. A thorough understanding of how to choose abstract classes or interfaces, baidu Java post must ask interview questions for four consecutive years
  36. Redis common commands
  37. A pair of slippers triggered the murder, crazy God said java series notes
  38. 1、 MySQL basic installation
  39. Monologue of a programmer: despite my ups and downs in my life, Java framework is the foundation of interview
  40. 【大厂面试】三面三问Spring循环依赖,请一定要把这篇看完(建议收藏)
  41. 一线互联网企业中,springboot入门项目
  42. 一篇文带你入门SSM框架Spring开发,帮你快速拿Offer
  43. 【面试资料】Java全集、微服务、大数据、数据结构与算法、机器学习知识最全总结,283页pdf
  44. 【leetcode刷题】24.数组中重复的数字——Java版
  45. 【leetcode刷题】23.对称二叉树——Java版
  46. 【leetcode刷题】22.二叉树的中序遍历——Java版
  47. 【leetcode刷题】21.三数之和——Java版
  48. 【leetcode刷题】20.最长回文子串——Java版
  49. 【leetcode刷题】19.回文链表——Java版
  50. 【leetcode刷题】18.反转链表——Java版
  51. 【leetcode刷题】17.相交链表——Java&python版
  52. 【leetcode刷题】16.环形链表——Java版
  53. 【leetcode刷题】15.汉明距离——Java版
  54. 【leetcode刷题】14.找到所有数组中消失的数字——Java版
  55. 【leetcode刷题】13.比特位计数——Java版
  56. oracle控制用户权限命令
  57. 三年Java开发,继阿里,鲁班二期Java架构师
  58. Oracle必须要启动的服务
  59. 万字长文!深入剖析HashMap,Java基础笔试题大全带答案
  60. 一问Kafka就心慌?我却凭着这份,图灵学院vip课程百度云