请教一个死锁的问题
发布于 1 年前 作者 dlyt 1530 次浏览 来自 问答

我在一个事务里执行了3条 sql 分别是:

select * from table where mobile = ? for update
select prizeId,count(*) as amount from table where prizeRegion = ? group by prizeRegion,prizeId for update
insert into table (mobile,prizeId,prizeName,prizeRegion) values (?,?,?,?)

空表的时候并发会发生死锁, 但是当表中有数据的时候就不会. 不知道哪里的问题?

11 回复

Innodb Gap锁了解下

空表的时候,只有一个区间,select for update锁住了这个区间,事务没有结束的话,另外事务中的insert拿不到排他锁。

create table xxx(id bigint unsigned primary key, number int, index idx_number (`number`) );
insert into xxx values(1, 1), (5, 5), (10, 10);
commit;

事务A
select * from xxx where number = 5 for update; // 事务A不commit,针对区间(1, 5] 与 (5, 10]之间的number无法insert

事务B
select * from xxx where number = 4 for update; // 事务B不commit,针对区间(1,5]之间的number无法insert

事务C
delete from xxx;
select * from xxx where number = 5 for update; // 事务C不commit,(-inf, +inf)之间的number都无法insert

@Shasharoman 第二句sql查询的是全表, 应该也只有一个区间, 但是当表中有数据的时候并不会发生死锁. 不知道跟空表的时候有什么区别?

@dlyt 不太理解你说的查询的是全表

Gap锁涉及的区间划分,依赖表中已存在的数据,第二条SQL中where prizeRegion = ?,如果prizeRegion是int型非唯一索引,表中没有数据的时候自然是只有一个区间(-inf, +inf),如果表中有一条值为5的数据,那么就是两个区间(-inf, 5)、(5, +inf),如果表中有两条值分别为5、10的数据,那么就存在三个区间 (-inf, 5)、(5, 10)、(10, +inf)。

触发Gap锁时,锁住的是值左右的区间,比如where prizeRegion = 5 for update,那么5左右的区间就是(-inf, 5)、(5, 10),也就这满足这两个区间的值无法获得排他锁,所以insert需要等待上锁事务释放锁。

另外,对于边界值能否insert需要结合主键,我在第一个回答的里面写的闭合区间并不正确。

@Shasharoman prizeRegion 是 varchar 型非索引, 值只有一个. 所以第二条sql查询的结果是全表的数据.

@dlyt 没有索引,整张表都被锁了,你还能insert?

create table x(y varchar(4), z varchar(4));
insert into x values('a', 'a'), ('a', 'b'), ('a', 'c');
commit;

-- 事务A,模仿你的查询
select z, count(*) as count from x where y = 'a' group by y, z for update;

-- 事务B,事务A不提交,这里将进入等待
insert into x values('a', 'd');

因为回答这个问题,我又复习了一遍这块知识,算有些收获,不知道朋友你有没有收获?

@Shasharoman 有收获的.

你说的我能理解, 我纠结的是下面 3 条 sql 在一个事务中, 并发请求的时候如果表中没有数据就会发生死锁, 但有数据就不会. 比如当表中无数据的时候,我并发请求 5 次, 只会 insert 一条记录, 其他的会报死锁错误然后回滚. 之后并发请求就不会出现这个问题. 问题在第一条 sql. 我不知道具体什么原因导致的这个现象?

select * from x where z = ? for update;
select z, count(*) as count from x where y = ? group by y, z for update;
insert into x values(?, ?);

@dlyt 应该是我误解了“死锁”的意思,能贴一下具体错误信息么,我也想看看究竟是啥现象以及原因?

现象就是我上面描述的那样, 原因我还在找.

报错信息: ER_LOCK_DEADLOCK: Deadlock found when trying to get lock; try restarting transaction

@dlyt 再次深入后,大概搞清楚了原因,同样是上面的x表结构,先看一下现象:

create table x(y varchar(4), z varchar(4));

-- 语句后的数字表示执行顺序
-- 事务A
select * from x for update; -- 1,由于表中没有数据,这里没有行锁,但需要x表的意图锁(IX)
insert into x values('0', '0'); -- 3,这里insert需要表的X锁,与事务B在表上的IX锁冲突,所以这里会进入等待

-- 事务B
select * from x for update; -- 2,同1,需要x表的意图锁,表的IX锁可以并存,故不会进入等待
insert into x values('0', '0'); -- 4,这里insert需要表的X锁,与事务A在表上的IX锁冲突,mysql在这里判断出现死锁,报死锁错误

如果表中有数据,select for update的IX锁虽然可以并存,但行锁冲突,所以才没有出现死锁现象:

insert into x values('0', '0');

-- 事务A
select * from x for update; -- 表上加IX锁,('0', '0')行X锁

-- 事务B
select * from x for update; -- 在行('0', '0')上拿不到X锁,这里会进入等待,后续insert就不会出现死锁
delete from x;

-- 事务A
select * from x for update; -- ok

-- 事务B
select * from x for update; -- IX可并存,ok

-- 事务C
lock tables x write; -- X锁和IX锁互斥,进入等待

详细内容可以参考MySQL官方指南

@Shasharoman 大概明白了, 感谢老哥.

@Shasharoman 分析的厉害,佩服

回到顶部