MySQL InnoDB 是否在成功插入数据之后释放了插入意向锁？

資深大佬 : JasonLaw 17

执行以下代码后，

create table t(id int primary key); 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(6); -- is blocked

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  |                  2132 |        49 |       56 | test          | t           | NULL           | NULL              | NULL       |       140043381460688 | TABLE     | IX                     | GRANTED     | NULL                   | | INNODB | 140043377180872:2:4:3:140043381457776 |                  2132 |        49 |       56 | test          | t           | NULL           | NULL              | PRIMARY    |       140043381457776 | RECORD    | X,GAP,INSERT_INTENTION | WAITING     | 10                     | | INNODB | 140043377180024:1063:140043381454544  |       421518353890680 |        48 |      105 | test          | t           | NULL           | NULL              | NULL       |       140043381454544 | TABLE     | IS                     | GRANTED     | NULL                   | | INNODB | 140043377180024:2:4:1:140043381451552 |       421518353890680 |        48 |      105 | test          | t           | NULL           | NULL              | PRIMARY    |       140043381451552 | RECORD    | S                      | GRANTED     | supremum pseudo-record | | INNODB | 140043377180024:2:4:3:140043381451552 |       421518353890680 |        48 |      105 | test          | t           | NULL           | NULL              | PRIMARY    |       140043381451552 | RECORD    | S                      | GRANTED     | 10                     | +--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+------------------------+-------------+------------------------+

我们可以看到 session 2 正在等待插入意向锁，因为这个插入意向锁跟 session 1 正在拥有的“下一键锁(5, 10]”冲突。这跟我预想的一样。但是如果我将 schedule 修改为

create table t(id int primary key); insert into t values (5),(10);  -- session 1 start transaction; insert into t values(6);  -- session 2 start transaction; select * from t where id > 8 for share; -- is not blocked

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 | 140043377180024:1063:140043381454544  |                  2147 |        48 |      125 | test          | t           | NULL           | NULL              | NULL       |       140043381454544 | TABLE     | IX        | GRANTED     | NULL                   | | INNODB | 140043377180872:1063:140043381460688  |       421518353891528 |        49 |       86 | test          | t           | NULL           | NULL              | NULL       |       140043381460688 | TABLE     | IS        | GRANTED     | NULL                   | | INNODB | 140043377180872:2:4:1:140043381457776 |       421518353891528 |        49 |       86 | test          | t           | NULL           | NULL              | PRIMARY    |       140043381457776 | RECORD    | S         | GRANTED     | supremum pseudo-record | | INNODB | 140043377180872:2:4:3:140043381457776 |       421518353891528 |        49 |       86 | test          | t           | NULL           | NULL              | PRIMARY    |       140043381457776 | RECORD    | S         | GRANTED     | 10                     | +--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+------------------------+

session 1 并没有拥有插入意向锁。那么，事务是在成功插入数据之后释放了插入意向锁吗？

大佬有話說 (4)

資深大佬 : izgnod

有主键索引和辅助索引的插入，插入前获取辅助索引的插入意向锁，插入后获取主键的记录锁。你这个事只有主键所以只需要记录锁。
主資深大佬 : JasonLaw

@izgnod #1 我做了以下实验，事实并不是你所说的那样，并不是“插入前获取辅助索引的插入意向锁，插入后获取主键的记录锁”。而且问题中的两个 schedules 也能证明你所说的是不正确的。

create table t2(id int primary key, value int, index ix_t2_value(value));
insert into t2 values (5,10),(10,5);

— session 1
start transaction;
select * from t2 where id = 3 for share;

— session 2
start transaction;
insert into t2 values (2, 8); — 等待获取 PRIMARY 索引上的插入意向锁(-∞ , 5)

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 | 140311361761480:1063:140311280045776 | 2072 | 50 | 14 | test | t2 | NULL | NULL | NULL | 140311280045776 | TABLE | IX | GRANTED | NULL |
| INNODB | 140311361761480:2:4:2:140311280042864 | 2072 | 50 | 14 | test | t2 | NULL | NULL | PRIMARY | 140311280042864 | RECORD | X,GAP,INSERT_INTENTION | WAITING | 5 |
| INNODB | 140311361760632:1063:140311280039632 | 421786338471288 | 48 | 22 | test | t2 | NULL | NULL | NULL | 140311280039632 | TABLE | IS | GRANTED | NULL |
| INNODB | 140311361760632:2:4:2:140311280036640 | 421786338471288 | 48 | 22 | test | t2 | NULL | NULL | PRIMARY | 140311280036640 | RECORD | S,GAP | GRANTED | 5 |
+——–+—————————————+———————–+———–+———-+—————+————-+—————-+——————-+————+———————–+———–+————————+————-+———–+

—

create table t2(id int primary key, value int, index ix_t2_value(value));
insert into t2 values (5,10),(10,5);

— session 1
start transaction;
select * from t2 where value = 3 for share;

— session 2
start transaction;
insert into t2 values (8, 2); — 等待获取 ix_t2_value 索引上的插入意向锁(-∞, (5, 10))

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 | 140311361761480:1063:140311280045776 | 2073 | 50 | 18 | test | t2 | NULL | NULL | NULL | 140311280045776 | TABLE | IX | GRANTED | NULL |
| INNODB | 140311361761480:2:5:3:140311280042864 | 2073 | 50 | 18 | test | t2 | NULL | NULL | ix_t2_value | 140311280042864 | RECORD | X,GAP,INSERT_INTENTION | WAITING | 5, 10 |
| INNODB | 140311361760632:1063:140311280039632 | 421786338471288 | 48 | 26 | test | t2 | NULL | NULL | NULL | 140311280039632 | TABLE | IS | GRANTED | NULL |
| INNODB | 140311361760632:2:5:3:140311280036640 | 421786338471288 | 48 | 26 | test | t2 | NULL | NULL | ix_t2_value | 140311280036640 | RECORD | S,GAP | GRANTED | 5, 10 |
+——–+—————————————+———————–+———–+———-+—————+————-+—————-+——————-+————-+———————–+———–+————————+————-+———–+
資深大佬 : izgnod

@JasonLaw 之前遇到过类似问题，我回答的也有些模糊，晚点我试一下，希望帮到你
主資深大佬 : JasonLaw

@izgnod #3 好的，期待你的回复。