MySQL中多并发更新单条记录引发的死锁分析_多线程更新数据库一条记录

2401_85111662

于 2024-06-20 14:18:14 发布

阅读量738

点赞数 21

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/2401_85111662/article/details/139831492

版权

MySQL thread id 4, OS thread handle 0x7fe8b1f21700, query id 33 localhost root Sending data
select * from test.test01 lock in share mode
------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 8 page no 3 n bits 72 index GEN_CLUST_INDEX of table test.test01 trx id 20747 lock mode S waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 6; hex 000000000411; asc ;;
1: len 6; hex 000000001d76; asc v;;
2: len 7; hex c2000001760110; asc v ;;
3: len 8; hex 7a68616e6773616e; asc zhangsan;;
4: len 20; hex 323031372d31322d31392d32302e303020202020; asc 2017-12-19-20.00 ;;
5: len 12; hex 757365723031202020202020; asc user01 ;;

—TRANSACTION 20746, ACTIVE 14 sec
2 lock struct(s), heap size 360, 5 row lock(s)
MySQL thread id 5, OS thread handle 0x7fe8b1edf700, query id 31 localhost root cleaning up


###### 1.3 行锁的三种算法


InnoDB存储引擎有三种行锁算法：


* Record Lock：单个行记录上锁
* Gap Lock：间隙锁，锁定一个范围，但不包含记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。该锁只会在隔离级别是RR或者以上的级别内存在。间隙锁的目的是为了让其他事务无法在间隙中新增数据
* Next-key Lock：Gap Lock + Record Lock，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。next-key锁是InnoDB默认的锁


###### 1.3.1 Next-key lock


Next-Key lock是结合了Gap Lock和Record Lock的锁机制，采用的是左开右闭规则，假如一个索引有10、11、13和20这四个值，那么该索引可能被Next-Key Locking的区间为：

(-∞,10]、(10,11]、(11,13]、(13,20]、(20,+∞]


以下为例，向表T1(name primary key,id key)插入id=10，因为next-key lock是左开右闭，id=6本身没有加锁、id=10本身加锁了，所以加锁区间为(6,10]


![在这里插入图片描述](https://img-blog.csdnimg.cn/4fe7ebb4fbc84725be140c582a9735f9.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAc29saWhhd2s=,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center)


###### 1.3.2 幻读问题


幻读问题是指在同一事务下，连续执行两次相同的SQL语句可能导致不同的结果，第二次执行的SQL语句可能返回之前不存在的行。在默认的事务隔离级别Repeatable READ下，InnoDB存储引擎采用Next-Key locking机制来避免幻读问题。在隔离级别READ Committed下，采用的是record lock，可以在应用层面指定share mode实现next-key lock机制：

SELECT * FROM table WHERE col=xxx LOCK IN SHARE MODE;


如果通过索引查询一个值，并对该行加上一个S-LOCK，即使查询的值不存在，锁定的也是一个范围。因此如果没有返回任何行，新插入的值一定是唯一的。如果在SELECT … LOCK IN SHARE MODE时候有多个并发操作会导致死锁，只有一个事务的插入操作会成功，其余的事务会抛出死锁的错误。如下所示：


![在这里插入图片描述](https://img-blog.csdnimg.cn/164489e6630e410c9ecab13781de0e52.png?x-oss-process=image/watermark,type\_d3F5LXplbmhlaQ,shadow\_50,text\_Q1NETiBAc29saWhhd2s=,size\_20,color\_FFFFFF,t\_70,g\_se,x\_16#pic\_center = 90%x90)


###### 1.4 死锁


死锁是指两个或两个以上的事务在执行过程过程中，因争夺锁资源而造成的一种互相等待的现象。解决死锁问题的最简单的一种方法是超时，即当两个事务互相等待时，当其中一个等待时间超过设定的阈值时会进行回滚，另一个等待的事务就能继续执行，在innodb存储引擎中，通过innodb\_lock\_wait\_timeout参数设置超时时间。超时机制简单粗暴，但是如果超时的事务所占的权重较大，执行了很多更新操作，回滚将占用很长时间。除了超时机制，数据库还普遍采用waits-for graph的方式进行死锁检测，waits-for graph机制要求数据库保存两种信息：锁的信息链表和事务等待链表，通过上述链表构造出一张图，如果存在回路，则说明存在死锁。


![在这里插入图片描述](https://img-blog.csdnimg.cn/20aab23862a64c71946a9131ff18eefe.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAc29saWhhd2s=,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center)


如图所示，在事务等待列表中有4个事务T1/T2/T3/T4，事务T2对row1占用X-lock、事务T1对row2占用S-lock。事务T1需要等待事务T2中的row1资源，事务T2需要等待T1和T4占用的row2资源，最终waits-for graph图如上图所示。从图中可以发现存在回路(T1,T2)，表示存在死锁。waits-for graph是主动的死锁检测机制，在每个事务请求锁并发生等待时都会判断是否存在回路，若存在则有死锁，一般情况下InnoDB存储引擎会选择undo log最小的事务进行回滚。如果发生了死锁，可以使用show engine innodb status命令来确定最后一个死锁产生的原因。


##### 2、多并发更新单条记录引起的死锁


上面介绍了InnoDB存储引擎中的锁类型以及死锁检测机制，下面来看下在实际开发过程中遇到的多并发更新单条记录引发的死锁问题。


###### 2.1 场景重现


1）表结构

CREATE TABLE t2 (
a int(11) NOT NULL DEFAULT 0,
b int(11) DEFAULT NULL,
c int(11) DEFAULT NULL,
d int(11) DEFAULT NULL,
PRIMARY KEY (a),
UNIQUE KEY uk_bc (b,c)
);


2）并发执行三个session




| 序号 | Session A | Session B | Session C |
| --- | --- | --- | --- |
| 1 | BEGIN;INSERT INTO t2 VALUES(123,22,12,11); |  |  |
| 2 |  | BEGIN;INSERT INTO t2 VALUES(123,22,12,11); |  |
| 3 |  |  | BEGIN;INSERT INTO t2 VALUES(123,22,12,11); |
| 4 | ROLLBACK; |  |  |
| 5 |  |  | DEADLOCK; |


###### 2.2 死锁分析


###### 2.2.1 死锁日志


上述场景出现的deadlock日志如下：

LATEST DETECTED DEADLOCK

2022-03-19 22:14:44 7f229eae0700
*** (1) TRANSACTION:
TRANSACTION 21771, ACTIVE 129 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 2, OS thread handle 0x7f229eb22700, query id 49 localhost root update
INSERT INTO t2 VALUES(123,22,12,11)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 21 page no 3 n bits 72 index PRIMARY of table test.t2 trx id 21771 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 21772, ACTIVE 112 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 3, OS thread handle 0x7f229eae0700, query id 50 localhost root update
INSERT INTO t2 VALUES(123,22,12,11)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 21 page no 3 n bits 72 index PRIMARY of table test.t2 trx id 21772 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 21 page no 3 n bits 72 index PRIMARY of table test.t2 trx id 21772 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)


* 事务21771想获取资源“space id 21 page no 3”上的lock\_mode X insert intention waiting
* 事务21772已经拥有资源“space id 21 page no 3”上的S-lock，同时也想获得lock\_mode X insert intention waiting


###### 2.2.2 死锁分析


* 该场景中出现三个事务：事务1（21770）、事务2（21771）和事务3（21772），分别执行相同的操作，插入同一条记录
* 三个事务依次执行insert操作，由于(b,c)是唯一索引，所以后两个事务会出现唯一键冲突，但此时要注意的是事务一还没有提交，所以并不会立即报错，insert语句本来加的是隐式锁，在出现唯一键冲突时，事务1的隐式锁升级为显示锁(LOCK\_REC\_NOT\_GAP->LOCK\_REC->LOCK\_X)。事务2和事务3为了判断是否出现唯一键冲突，必须进行一次当前读，加的锁是Next-key锁，所以进入锁等待（LOCK\_REC\_GAP->LOCK\_REC->LOCK\_S->LOCK\_WAIT）

MySQL [test]> select * from information_schema.innodb_locks;
±-------------±------------±----------±----------±------------±-----------±-----------±----------±---------±----------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
±-------------±------------±----------±----------±------------±-----------±-----------±----------±---------±----------+
| 21772:21:3:2 | 21772 | S | RECORD | test.t2 | PRIMARY | 21 | 3 | 2 | 123 |
| 21770:21:3:2 | 21770 | X | RECORD | test.t2 | PRIMARY | 21 | 3 | 2 | 123 |
| 21771:21:3:2 | 21771 | S | RECORD | test.t2 | PRIMARY | 21 | 3 | 2 | 123 |
±-------------±------------±----------±----------±------------±-----------±-----------±----------±---------±----------+
3 rows in set (0.00 sec)


* 事务1回滚，此时事务2和事务3成功获取记录上的S-LOCK（LOCK\_REC\_GAP->LOCK\_REC->LOCK\_S）
* 事务2和事务3继续执行插入操作，需要依次请求记录上的插入意向锁（LOCK\_INSERT\_INTENTION->LOCK\_GAP->LOCK\_X），插入意向锁和S锁冲突，所以事务2等待事务3，事务3等待事务2，形成死锁。


![在这里插入图片描述](https://img-blog.csdnimg.cn/24907e00e0104436b12317dec00239a6.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAc29saWhhd2s=,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center)


##### 3、总结


多并发场景下更新单条记录引发的死锁问题，背后的处理机制是LOCK\_INSERT\_INTENTION和LOCK\_S之间的出现的锁资源冲突。本文基于基哥分享的死锁场景和死锁分析过程总结，这是一个很有意思的死锁场景。




---



> 
> 参考资料
> 
> 
> 


1. 《MySQL技术内幕：InnoDB存储引擎》，姜承尧著
2. https://www.cnblogs.com/drizzle-xu/p/9713018.html
3. https://blog.csdn.net/cy973071263/article/details/105188519
4. https://breachdirectory.com/blog/mysql-deadlocks-101/



> 
> 转载请注明原文地址：<https://blog.csdn.net/solihawk/article/details/123676871>  
>  文章会同步在公众号“牧羊人的方向”更新，感兴趣的可以关注公众号，谢谢！  
>  ![在这里插入图片描述](https://img-blog.csdnimg.cn/20210422085432861.jpg)
> 
> 
> 







### 最后

> **笔者已经把面试题和答案整理成了面试专题文档**

![image](https://img-blog.csdnimg.cn/img_convert/003574396cbd2576415aa8d298fb0ed9.webp?x-oss-process=image/format,png)

 ![image](https://img-blog.csdnimg.cn/img_convert/7db1fa8de39b68cb775f041be4cbe796.webp?x-oss-process=image/format,png)

![image](https://img-blog.csdnimg.cn/img_convert/80ae46933cb8dde62d49e497abe1cc8a.webp?x-oss-process=image/format,png)

 ![image](https://img-blog.csdnimg.cn/img_convert/f9c4bde92f4714124eb2f5ffde1b84c6.webp?x-oss-process=image/format,png)

![image](https://img-blog.csdnimg.cn/img_convert/779a085f62e1beb8b2ec76fa71a37f51.webp?x-oss-process=image/format,png)

 ![image](https://img-blog.csdnimg.cn/img_convert/1587c505851aadfc592e98c1db6456b6.webp?x-oss-process=image/format,png)

-5qmrYfIp-1718864281521)]

 [外链图片转存中...(img-gVDMuYBq-1718864281522)]

[外链图片转存中...(img-dl6giLmW-1718864281522)]

 [外链图片转存中...(img-qi82ROV8-1718864281523)]

[外链图片转存中...(img-dRXp1RQm-1718864281523)]

 [外链图片转存中...(img-4PHIOZ7B-1718864281524)]

2401_85111662

关注

21
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
MySQL中多并发更新单条记录引发的死锁分析_多线程更新数据库一条记录

asc;;1: len 6;asc v;;2: len 7;asc v;;3: len 8;;4: len 20;;5: len 12;asc user01;;
复制链接

扫一扫