|
背景
线上经常偶发死锁问题,当时处理一张表,也没有联表处理,但是有两个mq入口,并且消息体存在一样的情况,频率还不是很低,这么一个背景,我非常容易怀疑到,两个消息同时近到这一个事务里面导致的,但是是偶发的,又模拟不出来什么场景会导致死锁,只能进行代码分析,问题还原的方式去排查问题。
业务代码简化成下面- begin
- update test set yn = 0 where dm_code = "3";
- SELECT * from test where dm_code = '3'
- INSERT INTO demand_flow_followers (dm_code, erp )
- values
- ('3', 'a')
- ,
- ('3', 'b')
- ,
- ('3', 'c')
复制代码 也就是说先update ,select , insert 这么一个顺序
表中存在dm_code ,erp 唯一索引
如果不存在索引 第一行update 会导致行锁升级为表锁,反而不会导致问题出现,但是并发太差
结论
先说结论:
session1session2开启事务update开启事务updateinsertinsert出现死锁重点: 无论哪个事务insert,两个事务必须都update 完成,只要满足这个条件,两个insert执行的时候就会报死锁
原因:我先按照自己的理解解释下:
innodb的行锁,存在间隙锁,为啥要去有索引,如果没有索引,第一个update 就直接进行了表锁,这样导致另外一个事务无法进入,就只能进行等待了。
有索引的情况下:
两个事务都执行update,都拿到了[当前值,+∞) 的锁(记录锁+间隙锁),(update的时候,无数据命中)
第一个insert时,希望等待另外一个事务释放锁。第二个事务希望第一个事务释放锁,因此出现了死锁问题
相关知识梳理
InnoDB有三种行锁的算法:
1.Record Lock:是加在索引记录上的。
2.Gap Lock(间隙锁):对索引记录间的范围加锁,或者加在最后一个索引记录的前面或者后面
3.Next-Key Lock:前两种锁的结合,锁定一个范围,并且锁定记录本身,主要目的是解决幻读的问题。
间隙锁主要是防止幻象读,用在Repeated-Read(简称RR)隔离级别下。在Read-Commited(简称RC)下,一般没有间隙锁(有外键情况下例外,此处不考虑)。间隙锁还用于statement based replication
间隙锁有些副作用,如果要关闭,一是将会话隔离级别改到RC下,或者开启 innodb_locks_unsafe_for_binlog(默认是OFF)。
间隙锁(无论是S还是X)只会阻塞insert操作。- CREATE TABLE `test` (
- `id` bigint(20) NOT NULL,
- `k` bigint(20) DEFAULT '0',
- PRIMARY KEY (`id`),
- KEY `idx_k` (`k`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8
- INSERT into test values(2,2),(5,5),(10,10)
复制代码- select @@global.tx_isolation, @@tx_isolation;
复制代码 RR隔离级别- delete from test where k=5;
复制代码 session2
- insert into test (id,k) values (3,3)
- insert into test (id,k) values (4,4)
- insert into test (id,k) values (6,6)
- insert into test (id,k) values (7,7)
- insert into test (id,k) values (8,8)
- insert into test (id,k) values (9,9)
复制代码 上面都报错:ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
这个证明id (3,5)都被间隙锁锁住了- insert into test (id,k) values (1,1)
- insert into test (id,k) values (11,11)
- delete from test where id in (1,11)
复制代码 (3,5) 区间之外都可以执行insert,delete操作
可以看到,delete k=5的记录阻塞了k=3、4、5、6、7、8、9记录的插入操作,事实上,<strong>除了对于k=5这条记录上record lock之外,innoDB对于delete和update在辅助索引(非主键索引)上的条件时会对扫过的记录上间隙锁,为了防止幻读,会锁住k=5这条记录的前面一条记录(id=2,k=2)到后面一条记录(id=10,k=10)之间的区间,即锁住k在区间(2,10)的范围(如果没有后一条记录,一直锁到正无穷),至于在边界k=2及k=10上,由于索引内是按照主键排序的,不会锁住(id2,k=2),同理不会锁住(id>10,k=10)但是会锁住(id10,k=10)但是会锁住(id5&&id |
|