MySQL还有另外一种死锁情况:当前事务持有待插入记录的下一个记录的X锁(排他锁),但此时等待的队列中存在一个S锁的请求,则可能会发生死锁。首先创建一个测试用的表,并插入数据:
create table test( a int primary key) engine=innodb;
insert into test values (1),(2),(4),(5);
时间
事务A
事务B
1
begin;
2
begin;
3
select * from test where
a = 4 for update;
4
select * from test where
a<=4 lock in share mode;
#等待
5
insert into test values (3)
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction