执行以下代码后,
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 并没有拥有插入意向锁。那么,事务是在成功插入数据之后释放了插入意向锁吗?
1
izgnod 2020-07-11 09:04:20 +08:00 via iPhone
有主键索引和辅助索引的插入,插入前获取辅助索引的插入意向锁,插入后获取主键的记录锁。你这个事只有主键所以只需要记录锁。
|
2
JasonLaw OP @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 | +--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-------------+-----------------------+-----------+------------------------+-------------+-----------+ |