本文讲的是Locking Reads，关于Consistent Nonlocking Reads，请查看MySQL中MVCC的正确打开方式。
唯一索引 + 唯一搜索条件
我们先创建表及初始化数据。
CREATE TABLE `t` (
`id` int NOT NULL,
PRIMARY KEY (`id`)
);
insert into t values (5), (10);
执行以下代码。
-- session 1
start transaction;
select * from t where id = 5 for share;
-- session 2
delete from t where id = 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 | 140043377180872:1063:140043381460688 | 2083 | 49 | 19 | test | t | NULL | NULL | NULL | 140043381460688 | TABLE | IX | GRANTED | NULL |
| INNODB | 140043377180872:2:4:2:140043381457776 | 2083 | 49 | 19 | test | t | NULL | NULL | PRIMARY | 140043381457776 | RECORD | X,REC_NOT_GAP | WAITING | 5 |
| INNODB | 140043377180024:1063:140043381454544 | 421518353890680 | 48 | 47 | test | t | NULL | NULL | NULL | 140043381454544 | TABLE | IS | GRANTED | NULL |
| INNODB | 140043377180024:2:4:2:140043381451552 | 421518353890680 | 48 | 47 | test | t | NULL | NULL | PRIMARY | 140043381451552 | RECORD | S,REC_NOT_GAP | GRANTED | 5 |
+--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
因为session 1执行select * from t where id = 5 for share之后，会拥有表级别的共享意向锁和id为5的那个索引记录的共享锁，所以session 2虽然获取到了表级别的独占意向锁，但是它无法获取到id为5的那个索引记录的独占锁。session 1对应的事务结束之前，任何其他事务都不能够修改或删除id为5那行，因此保证了可重复读。
唯一索引 + 非唯一搜索条件
我们先创建表及初始化数据。
CREATE TABLE `t` (
`id` int NOT NULL,
PRIMARY KEY (`id`)
);
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 (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:140043381457776 | 2084 | 49 | 23 | test | t | NULL | NULL | PRIMARY | 140043381457776 | 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 |
+--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+------------------------+-------------+------------------------+
因为session 1执行select * from t where id > 8 for share之后，会拥有表级别的共享意向锁、(5, 10]及(10, +∞)这两个下一键锁，所以session 2虽然获取到了表级别的独占意向锁，但是它无法获取到(5, 10)这个插入意向锁。session 1对应的事务结束之前，任何其他事务都无法插入数据到(5, +∞)，因此保证了可重复读。
非唯一索引
我们先创建表及初始化数据。
CREATE TABLE `t` (
`id` int NOT NULL,
`k` int DEFAULT NULL,
`v` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `ix_t_k` (`k`)
);
insert into t(k, id, v) values(5, 27, 92), (10, 5, 92);
执行以下代码。
-- session 1
start transaction;
select * from t where k > 8 for share;
-- session 2
start transaction;
insert into t(k, id, v) values(5, 28, 92);
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:1072:140043381454544 | 2311 | 48 | 210 | test | t | NULL | NULL | NULL | 140043381454544 | TABLE | IX | GRANTED | NULL |
| INNODB | 140043377180024:11:5:3:140043381451552 | 2311 | 48 | 210 | test | t | NULL | NULL | ix_t_k | 140043381451552 | RECORD | X,GAP,INSERT_INTENTION | WAITING | 10, 5 |
| INNODB | 140043377180872:1072:140043381460688 | 421518353891528 | 49 | 148 | test | t | NULL | NULL | NULL | 140043381460688 | TABLE | IS | GRANTED | NULL |
| INNODB | 140043377180872:11:5:1:140043381457776 | 421518353891528 | 49 | 148 | test | t | NULL | NULL | ix_t_k | 140043381457776 | RECORD | S | GRANTED | supremum pseudo-record |
| INNODB | 140043377180872:11:5:3:140043381457776 | 421518353891528 | 49 | 148 | test | t | NULL | NULL | ix_t_k | 140043381457776 | RECORD | S | GRANTED | 10, 5 |
| INNODB | 140043377180872:11:4:3:140043381458120 | 421518353891528 | 49 | 148 | test | t | NULL | NULL | PRIMARY | 140043381458120 | RECORD | S,REC_NOT_GAP | GRANTED | 5 |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+------------------------+-------------+------------------------+
因为session 1执行select * from t where k > 8 for share之后，会拥有表级别的共享意向锁、((5, 27), (10, 5)]下一键锁、id为5的那个索引记录的共享锁及((10, 5), +∞)下一键锁，所以session 2虽然获取到了表级别的独占意向锁，但是它无法获取到((5, 27), (10, 5))这个插入意向锁。session 1对应的事务结束之前，任何其他事务都无法插入数据到((5, 27), +∞)，因此保证了可重复读。
参考资料
- MySQL :: MySQL 8.0 Reference Manual :: 15.7 InnoDB Locking and Transaction Model
- database deadlocks - MySQL gap lock reasoning - Stack Overflow
- locking - Rules of Mysql Gap-lock/Next-key Locks - Stack Overflow
- mysql - Why Next-Key lock is called this way? - Stack Overflow
- mysql - What's the difference between Next-Key Locks And Gap Lock? - Stack Overflow
- MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁 - 51CTO.COM
- mysql - Is insert intention lock truly a gap lock? - Stack Overflow
- mysql - Does transaction release insert intention lock after insertion? - Stack Overflow
