咱们使用 MySQL 大概率上都会遇到死锁问题,这实在是个令人非常头痛的问题。本文将会对死锁进行相应介绍,对常见的死锁案例进行相关分析与探讨,以及如何去尽可能避免死锁给出一些建议。
1、什么是死锁
死锁是并发系统中常见的问题,同样也会出现在数据库MySQL的并发读写请求场景中。当两个及以上的事务,双方都在等待对方释放已经持有的锁或因为加锁顺序不一致造成循环等待锁资源,就会出现“死锁”。常见的报错信息为 Deadlock found when trying to get lock...
举例来说 A 事务持有 X1 锁 ,申请 X2 锁,B事务持有 X2 锁,申请 X1 锁。A 和 B 事务持有锁并且申请对方持有的锁进入循环等待,就造成了死锁。
2.4、插入意向锁( Insert Intention lock )
插入意向锁是在插入一行记录操作之前设置的一种间隙锁,这个锁释放了一种插入方式的信号,即多个事务在相同的索引间隙插入时如果不是插入间隙中相同的位置就不需要互相等待。假设某列有索引值2,6,只要两个事务插入位置不同(如事务 A 插入3,事务 B 插入4),那么就可以同时插入。
LOCK WAIT 表示正在等待锁,2 lock struct(s) 表示 trx->trx_locks 锁链表的长度为2,每个链表节点代表该事务持有的一个锁结构,包括表锁,记录锁以及自增锁等。本用例中 2locks 表示 IX 锁和lock_mode X (Next-key lock)
1 row lock(s) 表示当前事务持有的行记录锁/ gap 锁的个数。
MySQL thread id 37, OS thread handle 140445500716800, query id 1234 127.0.0.1 root updating
MySQL thread id 37 表示执行该事务的线程 ID 为 37 (即 show processlist; 展示的 ID )
delete from student where stuno=5 表示事务1正在执行的 sql,比较难受的事情是 show engine innodb status 是查看不到完整的 sql 的,通常显示当前正在等待锁的 sql。
***** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw****.****student trx id 2322 lock_mode X waiting
RECORD LOCKS 表示记录锁, 此条内容表示事务 1 正在等待表 student 上的 idx_stuno 的 X 锁,本案例中其实是 Next-Key Lock 。
事务2的 log 和上面分析类似:
2.***** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw****.****student trx id 2321 lock_mode X
显示事务 2 的 insert into student(stuno,score) values(2,10) 持有了 a=5 的 Lock mode X
LOCK_gap,不过我们从日志里面看不到事务2执行的
delete from student where stuno=5;
这点也是造成 DBA 仅仅根据日志难以分析死锁的问题的根本原因。
3.***** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw****.****student trx id 2321 lock_mode X locks gap before rec insert intention waiting
表示事务 2 的 insert 语句正在等待插入意向锁
lock_mode X locks gap before rec insert intention waiting ( LOCK_X + LOCK_REC_gap )
4、经典案例分析
4.1、事务并发 insert 唯一键冲突
表结构和数据如下所示:
测试用例如下:
日志分析如下:
事务 T2 insert into t7(id,a) values (26,10) 语句 insert 成功,持有 a=10 的 排他行锁( Xlocks rec but no 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 ) 这是一个间隙锁会申请锁住(,10],(10,20]之间的 gap 区域。
事务 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 。
4.2、先 update 再 insert 的并发死锁问题
表结构如下,无数据:
测试用例如下:
死锁分析:
可以看到两个事务 update 不存在的记录,先后获得间隙锁( gap 锁),gap 锁之间是兼容的所以在update环节不会阻塞。两者都持有 gap 锁,然后去竞争插入意向锁。当存在其他会话持有 gap 锁的时候,当前会话申请不了插入意向锁,导致死锁。