上篇文章 MySQL 事物隔离级别与锁（上） 中，我们主要介绍了在 Read UnCommitted 和 Read Committed 事物隔离级别下执行特定 SQL 的加锁情况，在本篇文章中，我们将重点分析 MySQL InnoDB 默认的事务隔离级别 Repeatable Read 在执行特定 SQL 时的加锁情况。
案例数据
我们会从执行以下 SQL 语句开始，分析 Repeatable Read 隔离级别下的加锁情况。
| SQL 语句 |
|---|
SELECT * FROM table WHERE column = ?; |
SELECT * FROM table WHERE column < ?; |
SELECT * FROM table WHERE column = ? LOCK IN SHARE MODE; |
SELECT * FROM table WHERE column < ? LOCK IN SHARE MODE; |
SELECT * FROM table WHERE column = ? FOR UPDATE; |
SELECT * FROM table WHERE column < ? FOR UPDATE; |
下文事例都将基于表 employees 中的数据：
| id（主键） | name（非索引） | employee_number （唯一索引） |
age（普通索引） |
|---|---|---|---|
| 1 | Alice | 1001 | 30 |
| 5 | Bob | 1020 | 25 |
| 13 | Charlie | 1010 | 35 |
| 14 | David | 1035 | 25 |
| 25 | Eve | 1040 | 32 |
Repeatable Read
| SQL 语句 | 锁情况 |
|---|---|
SELECT * FROM table WHERE column = ?; |
不加锁 |
SELECT * FROM table WHERE column < ?; |
不加锁 |
SELECT * FROM table WHERE column = ? LOCK IN SHARE MODE; |
意向共享锁（IS） 和 行级共享锁（S） 或 临键锁（Next- Key Lock） 或 间隙锁（Gap Lock） |
SELECT * FROM table WHERE column < ? LOCK IN SHARE MODE; |
意向共享锁（IS） 和 行级共享锁（S） 或 临键锁（Next- Key Lock） 或 间隙锁（Gap Lock） |
SELECT * FROM table WHERE column = ? FOR UPDATE; |
意向排他锁（IX） 和 行级排他锁（X） 或 临键锁（Next-Key Lock） 或 间隙锁（Gap Lock） |
SELECT * FROM table WHERE column < ? FOR UPDATE; |
意向排他锁（IX） 和 行级排他锁（X） 或 临键锁（Next-Key Lock） 或 间隙锁（Gap Lock） |
可以看到 Repeatable Read 支持 Net-Key Lock 和 Gap Lock，并且不同的 WHERE 条件可能导致不同的加锁方案，查询条件 column 是否是索引、是否是唯一索引、查询的记录是否存在，都会影响加锁方案。接下来，我们会分析查询条件为聚簇索引、唯一索引、普通索引 和 非索引的情况下，对应的不同加锁方案。
聚簇索引列查询
等值查询
查询有记录
| 时间线 | 事务A | 事务B |
|---|---|---|
| T1 | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
|
| T2 | START TRANSACTION; |
|
| T3 | SELECT * FROM employees WHERE id = 5 LOCK IN SHARE MODE; |
|
| T4 | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
|
| T5 | START TRANSACTION; |
|
| T6 | UPDATE employees SET name = 'Robert' WHERE id = 5; (被阻塞，因事务A锁定记录) |
|
| T7 | COMMIT; (事务A提交，释放锁) |
|
| T8 | COMMIT; (事务B提交) |
事物 A 在 T1 的查询条件 id = 5 记录存在，id 索引上首先会添加 Next-Key Lock，范围是（1, 5]，由于 id = 5 的记录存在，并且 id 是唯一索引，Next-Key Lock 会退化为行记录锁，所以这里会添加行级共享锁 S 和 表级意向共享锁 IS，行级锁会添加在聚簇索引 id 上，事物 B 在 T6 尝试获取此行 id = 5 的排他锁 X 导致冲突，操作被阻塞。
查询无记录
| 时间线 | 事务A | 事务B |
|---|---|---|
| T1 | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
|
| T2 | START TRANSACTION; |
|
| T3 | SELECT * FROM employees WHERE id = 2 LOCK IN SHARE MODE; |
|
| T4 | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
|
| T5 | START TRANSACTION; |
|
| T6 | INSERT INTO employees (id, name, employee_number, age) VALUES (3, 'Frank', 1051, 30); (被阻塞，因事务A的 Gap Lock) |
|
| T7 | COMMIT; (事务A提交，释放锁) |
|
| T8 | COMMIT; (事务B提交) |
事物 A 在 T1 的查询条件 id = 2 的记录是不存在的，id 索引上首先会添加 Next-Key Lock，范围是（1, 5]，由于 id = 5 的记录不存在，并且 id 是唯一索引，Next-Key Lock 会退化为 Gap Lock，所以这里会添加表级意向共享锁 IS 和 Gap Lock，Gap 的范围为（1, 5），事物 B 在 T6 尝试插入 id = 3 的记录导致冲突，操作被阻塞。
范围查找
| 时间线 | 事务A | 事务B |
|---|---|---|
| T1 | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
|
| T2 | START TRANSACTION; |
|
| T3 | SELECT * FROM employees WHERE id >= 5 AND id < 32 LOCK IN SHARE MODE; |
|
| T4 | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
|
| T5 | START TRANSACTION; |
|
| T6 | INSERT INTO employees (id, name, employee_number, age) VALUES (10, 'Frank', 1050, 30); (阻塞) |
|
| T7 | COMMIT; (事务A提交，释放锁) |
|
| T8 | COMMIT; (事务B提交) |
事物 A 在 id 索引上首先会添加 Next-Key Lock，范围是 (1, 5]，(5, 13]，(13,14]，(14, 25]，(25, 32]，由于 id = 5 的记录存在，并且 id 是唯一索引，最先被扫描到的 Next-Key Lock (1, 5] 会退化为行记录锁，由于 id = 32 不满足 id < 32，最后被扫描到的 Next-Key Lock (25, 32] 会退化为 Gap Lock，所以这里会添加表级意向共享锁 IS 、行级共享锁 S、 Gap Lock，Gap 的范围为(5, 13]，(13,14]，(14, 25]，(25, 32)，行级锁会被添加在 id = 5 的记录上，事物 B 在 T6 尝试插入 id = 10 的记录导致锁冲突，操作被阻塞。
| 时间线 | 事务A | 事务B |
|---|---|---|
| T1 | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
|
| T2 | START TRANSACTION; |
|
| T3 | SELECT * FROM employees WHERE id <= 13 LOCK IN SHARE MODE; |
|
| T4 | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
|
| T5 | START TRANSACTION; |
|
| T6 | INSERT INTO employees (id, name, employee_number, age) VALUES (10, 'Frank', 1050, 30); (阻塞) |
|
| T7 | COMMIT; (事务A提交，释放锁) |
|
| T8 | COMMIT; (事务B提交) |
事物 A 在 id 索引上首先会添加 Next-Key Lock，范围是 (-∞, 1]， (1, 5]，(5, 13] ，id = 13 的记录存在，但这里的 (5, 13] 并不会退化为记录锁，所以这里会添加**表级意向共享锁 IS、 Next-Key Lock，范围为 (-∞, 1]， (1, 5]，(5, 13]**，事物 B 在 T6 尝试插入 id = 10 的记录导致锁冲突，操作被阻塞。
唯一索引列查询
等值查询
查询有记录
| 时间线 | 事务A | 事务B |
|---|---|---|
| T1 | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
|
| T2 | START TRANSACTION; |
|
| T3 | SELECT * FROM employees WHERE employee_number = 1010 LOCK IN SHARE MODE; |
|
| T4 | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
|
| T5 | START TRANSACTION; |
|
| T6 | UPDATE employees SET name = 'Robert' WHERE id = 13; (被阻塞，因事务A锁定记录) |
|
| T7 | COMMIT; (事务A提交，释放锁) |
|
| T8 | COMMIT; (事务B提交) |
事物 A 在 T3 的查询条件 employee_number = 1010 的记录是存在的，employee_number 索引上首先会添加 Next-Key Lock，范围是（1001, 1010]，employee_number = 1010 的记录存在，并且 employee_number 是唯一索引，Next-Key Lock 会退化为行记录锁，所以这里会添加行级共享锁 S 和 表级意向共享锁 IS，行级锁会添加在聚簇索引 id = 13 和 唯一索引 employee_number = 1010 上，事物 B 在 T6 尝试更新 id = 13 的记录导致锁冲突，操作被阻塞。
查询无记录
| 时间线 | 事务A | 事务B |
|---|---|---|
| T1 | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
|
| T2 | START TRANSACTION; |
|
| T3 | SELECT * FROM employees WHERE employee_number = 1011 LOCK IN SHARE MODE; |
|
| T4 | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
|
| T5 | START TRANSACTION; |
|
| T6 | INSERT INTO employees (id, name, employee_number, age) VALUES (6, ‘Robert’, 1012, 30);` (被阻塞，因事务A 的 Gap Lock) |
|
| T7 | COMMIT; (事务A提交，释放锁) |
|
| T8 | COMMIT; (事务B提交) |
事物 A 在 T3 的查询条件 employee_number = 1011 的记录是不存在的，employee_number 索引上首先会添加 Next-Key Lock，范围是（1010, 1020]，employee_number = 1011 的记录不存在，Next-Key Lock 会退化为 Gap Lock，所以这里会添加表级意向共享锁 IS 和 Gap Lock，Gap 的范围为（1010, 1020），事物 B 在 T6 尝试插入 employee_number = 1012 的记录导致冲突，操作被阻塞。
范围查询
| 时间线 | 事务A | 事务B |
|---|---|---|
| T1 | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
|
| T2 | START TRANSACTION; |
|
| T3 | SELECT * FROM employees WHERE employee_number < 1011 LOCK IN SHARE MODE; |
|
| T4 | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
|
| T5 | START TRANSACTION; |
|
| T6 | INSERT INTO employees (id, name, employee_number, age) VALUES (6, ‘Robert’, 1012, 30);` (被阻塞，因事务A 的 Gap Lock) |
|
| T7 | COMMIT; (事务A提交，释放锁) |
|
| T8 | COMMIT; (事务B提交) |
事物 A 在 employee_number 索引上首先会添加 Next-Key Lock，范围是 (-∞, 1001]， (1001, 1010]， (1010, 1020]， employee_number = 1020 的记录不满足 employee_number < 1011，Next-Key Lock (1010, 1020] 会退化为 Gap Lock，所以这里会添加**表级意向共享锁 IS、 Gap Lock，Gap 范围为 (-∞, 1001]， (1001, 1010] ， (1010, 1020)**，事物 B 在 T6 尝试插入 employee_number = 1012 的记录导致锁冲突，操作被阻塞。
| 时间线 | 事务A | 事务B |
|---|---|---|
| T1 | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
|
| T2 | START TRANSACTION; |
|
| T3 | SELECT * FROM employees WHERE employee_number >= 1040 LOCK IN SHARE MODE; |
|
| T4 | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
|
| T5 | START TRANSACTION; |
|
| T6 | UPDATE employees SET name = 'Robert' WHERE id = 25; (被阻塞，因事务A锁定记录) |
|
| T7 | COMMIT; (事务A提交，释放锁) |
|
| T8 | COMMIT; (事务B提交) |
事物 A 在 employee_number 索引上首先会添加 Next-Key Lock，范围是 (1035, 1040]， (1040, +∞]， employee_number = 1040 的记录存在，并且 employee_number 是唯一索引，Next-Key Lock (1035, 1040] 会退化为行记录锁，所以这里会添加**行级共享锁 S、Next-Key Lock 和 表级意向共享锁 IS，行级锁会添加在聚簇索引 id = 25 和 唯一索引 employee_number = 1040 上，Next- Key Lock 范围为 (1040, +∞]**，事物 B 在 T6 尝试更新 id = 25 的记录导致锁冲突，操作被阻塞。
非唯一索引列查询
等值查询
查询有记录
| 时间线 | 事务A | 事物C |
|---|---|---|
| T1 | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
|
| T2 | START TRANSACTION; |
|
| T3 | SELECT * FROM employees WHERE age = 25 LOCK IN SHARE MODE; |
|
| T4 | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
|
| T5 | START TRANSACTION; |
|
| T6 | INSERT INTO employees (name, employee_number, age) VALUES ('Frank', 1050, 26); (被阻塞，因事务A的 Gap Lock) |
|
| T7 | COMMIT; (事务A提交，释放锁) |
|
| T8 | COMMIT; (事务B提交) |
事物 A 在 age 索引上首先会添加 Next-Key Lock，范围是（∞, 25]， 由于 age = 25 的记录存在且 age 是非唯一索引，还需添加 Gap Lock（遍历至下一个不符合条件的 age 索引） 和 对应的行记录锁，所以这里会添加 **Next-Key Lock 、 Gap Lock、行级共享锁 S 和 表级意向共享锁 IS，锁定 age = 25 记录对应的 id = 5 和 id = 14 的聚簇索引记录，索引 age 上的 Next-Key Lock 范围为（∞, 25]（age 并非唯一索引，所以这里的 Next-Key Lock 并不会退化为行记录锁），索引 age 上的 Gap Lock 范围为（25, 30）**。事物 B 在 T6 尝试插入 age = 26 的记录导致冲突，操作被阻塞。
下边界插入
下边界如果是闭区间，则无法修改、删除，有机会插入成功，如果是开区间，则可以修改、删除，有机会插入成功。
| 时间线 | 事务A | 事务B |
|---|---|---|
| T1 | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
|
| T2 | START TRANSACTION; |
|
| T3 | SELECT * FROM employees WHERE age = 25 LOCK IN SHARE MODE; |
|
| T4 | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
|
| T5 | START TRANSACTION; |
|
| T6 | INSERT INTO employees (id, name, employee_number, age) VALUES (6, 'Frank', 1050, 30); (插入成功) |
|
| T7 | COMMIT; (事务A提交，释放锁) |
|
| T8 | COMMIT; (事务B提交) |
插入 age = 30 的记录一定会成功嘛？不一定都会成功。
| 时间线 | 事务A | 事务B |
|---|---|---|
| T1 | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
|
| T2 | START TRANSACTION; |
|
| T3 | SELECT * FROM employees WHERE age = 25 LOCK IN SHARE MODE; |
|
| T4 | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
|
| T5 | START TRANSACTION; |
|
| T6 | INSERT INTO employees (id, name, employee_number, age) VALUES (0, 'Frank', 1050, 30); (阻塞) |
|
| T7 | COMMIT; (事务A提交，释放锁) |
|
| T8 | COMMIT; (事务B提交) |
我们来分析看看为什么事物 B 此时插入的记录被阻塞，上一条的插入却成功了。普通索引是有序存储的，当普通索引值相同时，则会按照聚簇（主键）索引排序，索引 age 的排序情况如下，括号表示事物 A 在 T3 操作添加的 Gap Lock 范围（25, 30）
| index age | (25 | 25 | 30) | 32 | 35 |
|---|---|---|---|---|---|
| primary id | ( 5 | 14 | 1 ) | 25 | 13 |
我们假设插入 age = 30 和 id = 0 可以成功，那么这条记录会在（25, 30）之间，所以操作是会阻塞的，索引 age 的排序情况应该如下：
| index age | (25 | 25 | 30 ☆ | 30) | 32 | 35 |
|---|---|---|---|---|---|---|
| primary id | ( 5 | 14 | 0 ☆ | 1 ) | 25 | 13 |
我们假设插入 age = 30 和 id = 6 可以成功，这条记录是在（25, 30）之外的，所以操作是可以成功的，索引 age 的排序情况应该如下：
| index age | (25 | 25 | 30) | 30 ☆ | 32 | 35 |
|---|---|---|---|---|---|---|
| primary id | ( 5 | 14 | 1 ) | 1 ☆ | 25 | 13 |
上边界插入
与下边界规则相同
查询无记录
| 时间线 | 事务A | 事务B |
|---|---|---|
| T1 | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
|
| T2 | START TRANSACTION; |
|
| T3 | SELECT * FROM employees WHERE age = 27 LOCK IN SHARE MODE; |
|
| T4 | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
|
| T5 | START TRANSACTION; |
|
| T6 | INSERT INTO employees (name, employee_number, age) VALUES ('Frank', 1050, 26); (被阻塞，因事务A的 Gap Lock) |
|
| T7 | COMMIT; (事务A提交，释放锁) |
|
| T8 | COMMIT; (事务B提交) |
事物 A 在 age 索引上首先会添加 Next-Key Lock，范围是（25, 30]，由于 age = 27 的记录不存在，Next-Key Lock 会退化为 Gap Lock。所以这里会添加 表级意向共享锁 IS 和 Gap Lock ，索引 age 上的 Gap Lock 范围为（25, 30），事物 B 在 T6 尝试插入 age = 26 的记录导致冲突，操作被阻塞。
范围查询
| 时间线 | 事务A | 事务B |
|---|---|---|
| T1 | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
|
| T2 | START TRANSACTION; |
|
| T3 | SELECT * FROM employees WHERE age >= 25 AND age < 30 LOCK IN SHARE MODE; |
|
| T4 | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
|
| T5 | START TRANSACTION; |
|
| T6 | INSERT INTO employees (name, employee_number, age) VALUES ('Frank', 1050, 26); (被阻塞，因事务A的 Gap Lock) |
|
| T7 | COMMIT; (事务A提交，释放锁) |
|
| T8 | COMMIT; (事务B提交) |
事物 A 在 age 索引上首先会添加 Next-Key Lock，范围是 (-∞, 25]， (25, 30]，由于 age 是非唯一索引，所以 Next-Key Lock 不会退化为 Gap Lock 和 行级锁，这里会添加**Next-Key Lock 和 表级意向共享锁 IS，Next- Key Lock 范围为 (-∞, 25]， (25, 30]**，事物 B 在 T6 尝试插入 age = 26 的记录导致冲突，操作被阻塞。
无索引列
等值查询
查询有记录
| 时间线 | 事务A | 事务B |
|---|---|---|
| T1 | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
|
| T2 | START TRANSACTION; |
|
| T3 | SELECT * FROM employees WHERE name = 'Bob' LOCK IN SHARE MODE; |
|
| T4 | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
|
| T5 | START TRANSACTION; |
|
| T6 | UPDATE employees SET name = 'Robert' WHERE age = 25; (阻塞) |
|
| T7 | COMMIT; (事务A提交，释放锁) |
|
| T8 | COMMIT; (事务B提交) |
事物 A 查询的 name = ‘Bob’ 记录是存在的，由于 name 是非索引列，查询会进行全表扫描，所以这里会添加表级**意向共享锁 IS、行级共享锁 S、Next-Key Lock，锁定 id=1、5、13、14、25 的聚簇索引记录，每行聚簇索引都会添加 Next-Key Lock，范围分别为 （-∞,1]、（1，5]、（5，13]、（13，14]、（14，25]、（25，+∞]**，整个表被锁住了，事物 B 的更新操作被阻塞。
查询无记录
如果事物 A 查询的 name = ‘ob’ 记录不存，由于 name 是非索引列，查询也会进行全表扫描，锁添加情况同 name = ‘Bob’ 查询到记录的情况一致 。应该避免这种情况发生，这会严重降低整个系统的处理能力。
范围查找
同样会进行全部扫描，每行记录都会添加Next-Key Lock，最终锁住整个表。
FOR UPDATE
FOR UPDATE 在不同列类型的查询条件下，锁定的记录与 LOCK IN SHARE MODE 一致，只是锁的类型变成了 IX 和 X。
Serializable
| SQL 语句 | 锁情况 |
|---|---|
SELECT * FROM table WHERE column = ?; |
意向共享锁（IS） 和 行级共享锁（S） 或 临键锁（Next- Key Lock） 或 间隙锁（Gap Lock） |
SELECT * FROM table WHERE column < ?; |
意向共享锁（IS） 和 行级共享锁（S） 或 临键锁（Next- Key Lock） 或 间隙锁（Gap Lock） |
SELECT * FROM table WHERE column = ? LOCK IN SHARE MODE; |
意向共享锁（IS） 和 行级共享锁（S） 或 临键锁（Next- Key Lock） 或 间隙锁（Gap Lock） |
SELECT * FROM table WHERE column < ? LOCK IN SHARE MODE; |
意向共享锁（IS） 和 行级共享锁（S） 或 临键锁（Next- Key Lock） 或 间隙锁（Gap Lock） |
SELECT * FROM table WHERE column = ? FOR UPDATE; |
意向排他锁（IX） 和 行级排他锁（X） 或 临键锁（Next-Key Lock） 或 间隙锁（Gap Lock） |
SELECT * FROM table WHERE column < ? FOR UPDATE; |
意向排他锁（IX） 和 行级排他锁（X） 或 临键锁（Next-Key Lock） 或 间隙锁（Gap Lock） |
在 Serializable 隔离级别下，普通查询与显示 LOCK IN SHARE MODE 查询相同，执行不同 SQL 语句下的加锁情况与在 Repeatable Read 隔离级别下类似。