ToB企服应用市场:ToB评测及商务社交产业平台

标题: MySQL中多并发更新单条记录引发的死锁分析_多线程更新数据库一条记录 [打印本页]

---

作者: 吴旭华    时间: 2024-7-20 09:23
标题: MySQL中多并发更新单条记录引发的死锁分析_多线程更新数据库一条记录
总结

上述知识点，囊括了目前互联网企业的主流应用技能以及能让你成为“香饽饽”的高级架构知识，每个笔记内里几乎都带有实战内容。
很多人担心学了轻易忘，这里教你一个方法，那就是重复学习。
打个比方，如果你正在学习 spring 注解，突然发现了一个注解@Aspect，不知道干什么用的，你可能会去查看源码大概通过博客学习，花了半小时终于弄懂了，下次又看到@Aspect 了，你有点郁闷了，上次似乎在哪哪哪学习，你快速打开网页花了五分钟又学会了。
从半小时和五分钟的对比中可以发现多学一次就离真正掌握知识又近了一步。

人的天性就是轻易遗忘，只有不停加深印象、重复学习才能真正掌握，以是很多书我都是推荐大家多看几遍。哪有那么多天才，他只是比你多看了几遍书。
   本文已被CODING开源项目：【一线大厂Java面试题剖析+核心总结学习笔记+最新解说视频+实战项目源码】收录
  必要这份体系化的资料的朋友，可以点击这里获取
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

2. ###### 1.3 行锁的三种算法
5. InnoDB存储引擎有三种行锁算法：
8. * Record Lock：单个行记录上锁
9. * Gap Lock：间隙锁，锁定一个范围，但不包含记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。该锁只会在隔离级别是RR或者以上的级别内存在。间隙锁的目的是为了让其他事务无法在间隙中新增数据
10. * Next-key Lock：Gap Lock + Record Lock，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。next-key锁是InnoDB默认的锁
13. ###### 1.3.1 Next-key lock
16. Next-Key lock是结合了Gap Lock和Record Lock的锁机制，采用的是左开右闭规则，假如一个索引有10、11、13和20这四个值，那么该索引可能被Next-Key Locking的区间为：

复制代码

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

2. 以下为例，向表T1(name primary key,id key)插入id=10，因为next-key lock是左开右闭，id=6本身没有加锁、id=10本身加锁了，所以加锁区间为(6,10]
5. 
8. ###### 1.3.2 幻读问题
11. 幻读问题是指在同一事务下，连续执行两次相同的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;

2. 如果通过索引查询一个值，并对该行加上一个S-LOCK，即使查询的值不存在，锁定的也是一个范围。因此如果没有返回任何行，新插入的值一定是唯一的。如果在SELECT … LOCK IN SHARE MODE时候有多个并发操作会导致死锁，只有一个事务的插入操作会成功，其余的事务会抛出死锁的错误。如下所示：
5. 
8. ###### 1.4 死锁
11. 死锁是指两个或两个以上的事务在执行过程过程中，因争夺锁资源而造成的一种互相等待的现象。解决死锁问题的最简单的一种方法是超时，即当两个事务互相等待时，当其中一个等待时间超过设定的阈值时会进行回滚，另一个等待的事务就能继续执行，在innodb存储引擎中，通过innodb\_lock\_wait\_timeout参数设置超时时间。超时机制简单粗暴，但是如果超时的事务所占的权重较大，执行了很多更新操作，回滚将占用很长时间。除了超时机制，数据库还普遍采用waits-for graph的方式进行死锁检测，waits-for graph机制要求数据库保存两种信息：锁的信息链表和事务等待链表，通过上述链表构造出一张图，如果存在回路，则说明存在死锁。
14. 
17. 如图所示，在事务等待列表中有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命令来确定最后一个死锁产生的原因。
20. ##### 2、多并发更新单条记录引起的死锁
23. 上面介绍了InnoDB存储引擎中的锁类型以及死锁检测机制，下面来看下在实际开发过程中遇到的多并发更新单条记录引发的死锁问题。
26. ###### 2.1 场景重现
29. 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. 2）并发执行三个session
7. | 序号 | Session A | Session B | Session C |
8. | --- | --- | --- | --- |
9. | 1 | BEGIN;INSERT INTO t2 VALUES(123,22,12,11); |  |  |
10. | 2 |  | BEGIN;INSERT INTO t2 VALUES(123,22,12,11); |  |
11. | 3 |  |  | BEGIN;INSERT INTO t2 VALUES(123,22,12,11); |
12. | 4 | ROLLBACK; |  |  |
13. | 5 |  |  | DEADLOCK; |
16. ###### 2.2 死锁分析
19. ###### 2.2.1 死锁日志
22. 上述场景出现的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)

2. * 事务21771想获取资源“space id 21 page no 3”上的lock\_mode X insert intention waiting
3. * 事务21772已经拥有资源“space id 21 page no 3”上的S-lock，同时也想获得lock\_mode X insert intention waiting
6. ###### 2.2.2 死锁分析
9. * 该场景中出现三个事务：事务1（21770）、事务2（21771）和事务3（21772），分别执行相同的操作，插入同一条记录
10. * 三个事务依次执行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)

2. * 事务1回滚，此时事务2和事务3成功获取记录上的S-LOCK（LOCK\_REC\_GAP->LOCK\_REC->LOCK\_S）
3. * 事务2和事务3继续执行插入操作，需要依次请求记录上的插入意向锁（LOCK\_INSERT\_INTENTION->LOCK\_GAP->LOCK\_X），插入意向锁和S锁冲突，所以事务2等待事务3，事务3等待事务2，形成死锁。
6. 
9. ##### 3、总结
12. 多并发场景下更新单条记录引发的死锁问题，背后的处理机制是LOCK\_INSERT\_INTENTION和LOCK\_S之间的出现的锁资源冲突。本文基于基哥分享的死锁场景和死锁分析过程总结，这是一个很有意思的死锁场景。
17. ---
21. >
22. > 参考资料
23. >
24. >
25. >
30. # **学习分享，共勉**
32. 这里是小编拿到的学习资源，其中包括“中高级Java开发面试高频考点题笔记300道.pdf”和“Java核心知识体系笔记.pdf”文件分享，内容丰富，**囊括了JVM、锁、并发、Java反射、Spring原理、微服务、Zookeeper、数据库、数据结构等大量知识点。同时还有Java进阶学习的知识笔记脑图（内含大量学习笔记）！**
34. > **资料整理不易，读者朋友可以转发分享下！**
36. **Java核心知识体系笔记.pdf**
38. 
40. **中高级Java开发面试高频考点题笔记300道.pdf**
42. 
44. **架构进阶面试专题及架构学习笔记脑图**
46. 
48. **Java架构进阶学习视频分享**
50. > **本文已被[CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】](https://bbs.csdn.net/forums/4f45ff00ff254613a03fab5e56a57acb)收录**
52. **[需要这份系统化的资料的朋友，可以点击这里获取](https://bbs.csdn.net/forums/4f45ff00ff254613a03fab5e56a57acb)**
55. **Java架构进阶学习视频分享**
57. > **本文已被[CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】](https://bbs.csdn.net/forums/4f45ff00ff254613a03fab5e56a57acb)收录**
59. **[需要这份系统化的资料的朋友，可以点击这里获取](https://bbs.csdn.net/forums/4f45ff00ff254613a03fab5e56a57acb)**

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

---

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)

Powered by Discuz! X3.4