mysql - mysql 间隙锁定

Question

我需要对 MySQL 文档摘录进行澄清:

...conflicting locks can be held on a gap by different transactions. For example, transaction A can hold a shared gap lock (gap S-lock) on a gap while transaction B holds an exclusive gap lock (gap X-lock) on the same gap...

Gap locks in InnoDB are “purely inhibitive”, which means they only stop other transactions from inserting to the gap. They do not prevent different transactions from taking gap locks on the same gap. Thus, a gap X-lock has the same effect as a gap S-lock.

我有一个表“aaa”，有两列 - 数字(主索引)和“标记” - 没有索引的 VARCHAR。现在我执行:

 Window1    
     SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
     BEGIN;
     SELECT * FROM aaa WHERE mark < 6 FOR UPDATE;
 Window2
     SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
     BEGIN;
     SELECT * FROM aaa WHERE mark < 6 LOCK IN SHARE MODE;

Window1 中的语句获取记录“mark = 6”和前面的间隙上的 x 锁。

Window2 中的 Now 语句要求在完全相同的记录和间隙上使用 s-lock，(根据文档)应该立即授予该锁 - 但事实并非如此!

我哪里出错了？

Answer 1

您有标记 < 6 的数据吗？如果是这样，第一个事务将授予 X 条记录锁以及间隙锁。第二个事务无法获取这些记录上的 S 记录锁。