【MySQL】死锁案例之三

千家信息网最后更新 2024年11月24日【MySQL】死锁案例之三一 前言
死锁，其实是一个很有意思，也很有挑战的技术问题，大概每个DBA和部分开发朋友都会在工作过程中遇见过。关于死锁我会持续写一个系列的案例分析，希望能够对想了解死锁的朋友有所帮助。
二 背景知识
2.1 insert 锁机制
在分析死锁案例之前，我们先学习一下背景知识 insert 语句的加锁策略。我们先来看看官方定义:

sess1

sess2

begin;

delete from t8 where b = 1;

begin;

insert into t8 values (NULL,1);

commit；

update t8 set

T2

begin;

begin;

insert into t7(id,a) values(26,10);

insert into t7(id,a) values(30,10);

insert into t7(id,a) values(40,9);

3.3 死锁日志

1. ------------------------
   
2. LATEST DETECTED DEADLOCK
   
3. ------------------------
   
4. 2017-09-17 15:15:03 7f78eac15700
   
5. *** (1) TRANSACTION:
   
6. TRANSACTION 462308661, ACTIVE 6 sec inserting
   
7. mysql tables in use 1, locked 1
   
8. LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
   
9. MySQL thread id 3796966, OS thread handle 0x7f78ead9d700, query id 781045166 localhost root update
   
10. insert into t7(id,a) values(30,10)
    
11. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    
12. RECORD LOCKS space id 231 page no 4 n bits 72 index `ua` of table `test`.`t7` trx id 462308661 lock mode S waiting
    
13. *** (2) TRANSACTION:
    
14. TRANSACTION 462308660, ACTIVE 43 sec inserting, thread declared inside InnoDB 5000
    
15. mysql tables in use 1, locked 1
    
16. 4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 2
    
17. MySQL thread id 3796960, OS thread handle 0x7f78eac15700, query id 781045192 localhost root update
    
18. insert into t7(id,a) values(40,9)
    
19. *** (2) HOLDS THE LOCK(S):
    
20. RECORD LOCKS space id 231 page no 4 n bits 72 index `ua` of table `test`.`t7` trx id 462308660 lock_mode X locks rec but not gap
    
21. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    
22. RECORD LOCKS space id 231 page no 4 n bits 72 index `ua` of table `test`.`t7` trx id 462308660 lock_mode X locks gap before rec insert intention waiting
    
23. *** WE ROLL BACK TRANSACTION (1)

日志分析
我们从时间线维度分析:
T2 insert into t7(id,a) values(26,10)语句insert 成功，持有a=10 的X 行锁(X locks rec but not gap)
T1 insert into t7(id,a) values(30,10),因为T2 的第一条insert已经插入a=10的记录，T1的 insert a=10 则发生唯一约束冲突，需要申请对冲突的唯一索引加上S Next-key Lock (也即是 lock mode S waiting ) 这是一个间隙锁会申请锁住[4,10],[10,20]之间的gap区域。从这里会发现，即使是RC事务隔离级别，也同样会存在Next-Key Lock锁，从而阻塞并发。
T2 insert into t7(id,a) values(40,9) 该语句插入的a=9 的值在 T1申请的gap锁[4,10]之间，故需T2的第二条insert语句要等待T1的S-Next-key Lock锁释放，在日志中显示lock_mode X locks gap before rec insert intention waiting

四 总结
首先感谢登博和姜承尧两位德艺双馨的MySQL技术大牛对死锁技术知识的无私分享。本文案例和知识点一方面从官方文档获取，另一方面是根据两位大牛的分享整理，算是站在巨人的肩膀上的学习总结。在研究分析死锁案例的过程中，insert 的意向锁 和 gap 锁这种类型的锁是比较难分析的，相信通过上面的分析总结大家能够学习到 insert的锁机制 ，如何加锁，如何进行 insert 方面死锁分析。
如果各位觉得阅读本文能够有所收获 欢迎 打赏一瓶饮料