@
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 |
+--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-------------+-----------------------+-----------+------------------------+-------------+-----------+