InnoDB LOCK_MODE X,GAP,INSERT_INTENTION 到底是什么？
JasonLaw 2020-07-04 16:52:41 +08:00 1379 次点击这是一个创建于 1997 天前的主题，其中的信息可能已经有所发展或是发生改变。
首先执行以下代码，
CREATE TABLE `t` ( `id` int NOT NULL, PRIMARY KEY (`id`) ); insert intot 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 也没有搜索到相关内容。
第 1 条附言 2020-07-04 23:24:32 +08:00
Google搜索“InnoDB LOCK_MODE X,GAP,INSERT_INTENTION”时，的确没有找到相关的资料，但是搜索“InnoDB LOCK_MODE X,GAP,INSERT_INTENTION锁”时，让我找到了MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁 - 51CTO.COM，里面有讲到X,GAP,INSERT_INTENTION。
 | 1 F281M6Dh8DXpD1g2 2020-07-04 16:56:36 +08:00 关键词意向锁 b+ 树存储并发症 |
 | 2 limuyan44 2020-07-04 17:20:09 +08:00 2 条都说错了，官方文档有，google 也有，甚至只要把你的标题拿到 Google 搜一下也有。 |
| 4 limuyan44 2020-07-04 23:48:16 +08:00 |
 | 5 JasonLaw OP @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`得知？ |
Select Language