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 分析的厉害，佩服