MySQL 插入意向锁的作用是什么？

[JasonLaw]

我查看了MySQL :: MySQL 8.0 Reference Manual :: 15.7.1 InnoDB Locking - Insert Intention Locks，里面有这么一段话。

The following example demonstrates a transaction taking an insert intention lock prior to obtaining an exclusive lock on the inserted record. The example involves two clients, A and B.

Client A creates a table containing two index records (90 and 102) and then starts a transaction that places an exclusive lock on index records with an ID greater than 100. The exclusive lock includes a gap lock before record 102:

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);

mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id  |
+-----+
| 102 |
+-----+

Client B begins a transaction to insert a record into the gap. The transaction takes an insert intention lock while it waits to obtain an exclusive lock.

mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);

但是我不太理解插入意向锁存在的意义是什么？它的作用是什么呢？能够防止什么操作并发执行？就像上面的例子，如果 Client B 不需要在获取独占锁前获取插入意向锁，而是直接获取独占锁，Client B 也会因为 Client A 已经拥有的间隙锁(90, 102)而等待。是我哪里理解错了吗？

[JasonLaw]

@TouchQQ 你说插入意向锁是用来解决并发问题的，它解决的是什么并发问题呢？还有“首先插入意向锁是一种类型的间隙锁，锁模式是 IX， 而一般说的间隙锁（ Gap Locks ）锁模式是 X”这句话，我不太理解，可以具体解释一下吗？

[JasonLaw]

@luckyrayyy @showhand @taoprogramer @TouchQQ @makese @salamanderMH @qsfun 大家好，查阅了很多资料之后，我整理[插入意向锁真的是间隙锁吗？ - 简书]( https://www.jianshu.com/p/a449eda843b9)这篇文章，欢迎查看，如果有错误的话，欢迎指出。

[JasonLaw]

@luckyrayyy @showhand @taoprogramer @TouchQQ @makese @salamanderMH @qsfun

上一条有点问题，再打扰一次🤣

插入意向锁真的是间隙锁吗？ - 简书 https://www.jianshu.com/p/a449eda843b9

[JasonLaw]

上面那篇文章是不正确的，我准备取消那篇文章的发布。不过我在这里贴出两个问题，[mysql - Is insert intention lock truly a gap lock? - Stack Overflow]( https://stackoverflow.com/questions/62712571/is-insert-intention-lock-truly-a-gap-lock)和[mysql - Does transaction release insert intention lock after insertion? - Stack Overflow]( https://stackoverflow.com/questions/62737123/does-transaction-release-insert-intention-lock-after-insertion)。欢迎大家解答这两个问题，谢谢。

[salamanderMH]

意向锁和 Gap Locks 的作用： https://github.com/CyC2018/CS-Notes/blob/master/notes/%E6%95%B0%E6%8D%AE%E5%BA%93%E7%B3%BB%E7%BB%9F%E5%8E%9F%E7%90%86.md#%E5%9F%BA%E6%9C%AC%E6%80%9D%E6%83%B3