InnoDB LOCK_MODE X,GAP,INSERT_INTENTION 到底是什么？

[JasonLaw]

首先执行以下代码，

CREATE TABLE `t` (
  `id` int NOT NULL,
  PRIMARY KEY (`id`)
);

insert into t values (5), (10);

-- session 1
start transaction;
select * from t where id > 8 for share;

-- session 2
start transaction;
insert into t values (9);

此时，select * from performance_schema.data_locks的输出为：

+--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+------------------------+-------------+------------------------+
| ENGINE | ENGINE_LOCK_ID                        | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE              | LOCK_STATUS | LOCK_DATA              |
+--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+------------------------+-------------+------------------------+
| INNODB | 140043377180872:1063:140043381460688  |                  2084 |        49 |       23 | test          | t           | NULL           | NULL              | NULL       |       140043381460688 | TABLE     | IX                     | GRANTED     | NULL                   |
| INNODB | 140043377180872:2:4:3:140043381458464 |                  2084 |        49 |       25 | test          | t           | NULL           | NULL              | PRIMARY    |       140043381458464 | RECORD    | X,GAP,INSERT_INTENTION | WAITING     | 10                     |
| INNODB | 140043377180024:1063:140043381454544  |       421518353890680 |        48 |       52 | test          | t           | NULL           | NULL              | NULL       |       140043381454544 | TABLE     | IS                     | GRANTED     | NULL                   |
| INNODB | 140043377180024:2:4:1:140043381451552 |       421518353890680 |        48 |       52 | test          | t           | NULL           | NULL              | PRIMARY    |       140043381451552 | RECORD    | S                      | GRANTED     | supremum pseudo-record |
| INNODB | 140043377180024:2:4:3:140043381451552 |       421518353890680 |        48 |       52 | test          | t           | NULL           | NULL              | PRIMARY    |       140043381451552 | RECORD    | S                      | GRANTED     | 10                     |
+--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+------------------------+-------------+------------------------+

X,GAP,INSERT_INTENTION这个LOCK_MODE到底是什么呢？在文档没有找到相关的描述，Google 也没有搜索到相关内容。

[liprais]

关键词意向锁
b+ 树存储并发症

[limuyan44]

2 条都说错了，官方文档有，google 也有，甚至只要把你的标题拿到 Google 搜一下也有。

[JasonLaw]

@limuyan44 你说官方文档有，可以发一下相关的链接吗？我实在是找不到有官方文档说这个的。

[limuyan44]

https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html

[JasonLaw]

@limuyan44 我不太明白你是怎么从 https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html 得知 X,GAP,INSERT_INTENTION 是什么类型的锁的，难道是从`trx id 8731 lock_mode X locks gap before rec insert intention waiting`得知？