现象:在一个repeatable read级别的事务中采用先update再insert的方式,当这段代码并发执行时造成死锁;
示例如下: 如表student中只有一条记录 ,该记录的code 字段值为1,且该表在code上建立索引。
transaction A:update student set yn=1 where code = '2'; insert into student (code, …) values ('2', ...);
transaction B:update student set yn=1 where code = '3'; insert into student (code, …) values ('3', ...);
此时A、B事务会出现死锁现象。
原因:
在repeatable级别,update/select … for update/delete不存在的记录时会hold一个X(互斥)gap锁,当执行顺序如下时:T-A update student set yn=1 where code = '2'; ------T-A获得一个X的next-key锁
T-B update student set yn=1 where code = '3'; ------T-B获得一个X的next-key锁
T-B insert into student (code, …) values ('3', ...); ------T-B需获取一个insert intention gap lock,需等待T-A的next-key锁释放
T-A insert into student (code, …) values ('2', ...); ------T-A同理,等待T-B的next-key锁释放死锁产生
解决方案:
第一种方案:将事务隔离级别降低到read committed即可,此时无gap锁,T-A、T-B无冲突。
第二种方案:事务隔离级别仍然为repeatable read,但将参数innodb_locks_unsafe_for_binlog置为true。
原文链接:http://outofmemory.cn/mysql/msql-dead-lock-when-update-then-insert
打开App,阅读手记