MySQL 相关项目
执行以下代码后，
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 并没有拥有插入意向锁。那么，事务是在成功插入数据之后释放了插入意向锁吗？
Select Language