我在一个事务里执行了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 (?,?,?,?)
空表的时候并发会发生死锁, 但是当表中有数据的时候就不会. 不知道哪里的问题?
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
@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需要结合主键，我在第一个回答的里面写的闭合区间并不正确。
@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(?, ?);
现象就是我上面描述的那样, 原因我还在找.
报错信息: 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官方指南
