MySQL InnoDB 加锁实践分析

2020-01-20 约 4415 字预计阅读 9 分钟

在使用 MySQL 和存储引擎是 InnoDB 的情况下，当我们想从一个 SQL 语句分析出这个语句对应的加锁情况需要掌握哪些知识呢？在这篇文章我就想总结一下 InnoDB 的锁类型和这些不同的锁用在什么场景。影响加锁的因素有这几个：

事务隔离级别事务的隔离级别以及他们能解决的事务问题这里就不做介绍了，这里我们只针对常用的 Read Committed, Repeatable Read 分析。
SQL 语句本身(一致性非锁定读还是一致性锁定读；DML(update,insert,delete)) 普通的 SELECT 对应的就是一致性非锁定读，即不需要加锁，InnoDB 使用 MVCC(multiversion concurrency control)来增加读操作的并发性, MVCC是指，InnoDB 使用基于时间点的快照来获取查询结果，读取时在访问的表上不加锁，因此，在事务T1读取的同一时刻，事务 T2 可以自由的修改事务 T1 所读取的数据。 SELECT for share 和 SELECT for update 对应的则是锁定读，即在搜索到的每条索引记录(index record) 加共享锁或排他锁。
使用的索引类型(主键索引，唯一索引，非唯一索引，没有用到索引)

InnoDB 锁类型

InnoDB 一共有8种锁类型，其中，意向锁(Intention Locks)和自增锁(AUTO-INC Locks)是表级锁，剩余全部都是行级锁。此外，共享锁或排它锁(Shared and Exclusive Locks)尽管也作为8种锁类型之一，它却并不是具体的锁，它是锁的模式，用来“修饰”其他各种类型的锁。

共享锁或排它锁(Shared and Exclusive Locks) 它并不是一种锁的类型，而是其他各种锁的模式，每种锁都有shard或exclusive两种模式。 S 锁和 X 锁兼容情况如下假设 T1 持有数据行 r 上的 S 锁，则当 T2 请求 r 上的锁时：
1. T2 请求 r 上的 S 锁，则，T2 立即获得 S 锁。T1 和T2 同时都持有 r 上的 S 锁。
2. T2 请求 r 上的 X 锁，则，T2 无法获得 X 锁。T2 必须要等待直到 T1 释放 r 上的 S 锁。假设 T1 持有 r 上的X锁，则当 T2 请求 r 上的锁时： T2 请求 r 上的任何类型的锁时，T2 都无法获得锁，此时，T2 必须要等待直到 T1 释放 r 上的 X 锁
意向锁(Intention Locks) 表锁。含义是已经持有了表锁，稍候将获取该表上某个/些行的行锁。有 shard 或 exclusive 两种模式。 LOCK_MODE分别是：IS 或 IX
意向锁的目的是告知其他事务，某事务已经锁定了或即将锁定某个/些数据行。事务在获取行锁之前，首先要获取到意向锁，即：
1. 事务在获取行上的 S 锁之前，事务必须首先获取表上的 IS 锁或表上的更强的锁。
2. 事务在获取行上的 X 锁之前，事务必须首先获取表上的 IX 锁。意向锁和任何行锁都是兼容的，它只会阻塞全表请求(如 lock tables…write)
索引记录锁(Record Locks) 即行锁，在某个索引的特定索引记录（或称索引条目、索引项、索引入口）上设置锁。有 shard 或 exclusive 两种模式。 LOCK_MODE分别是：S,REC_NOT_GAP 或 X,REC_NOT_GAP
间隙锁(Gap Locks) 索引记录之间的间隙上的锁，锁定尚未存在的记录，即索引记录之间的间隙。有 shard 或 exclusive 两种模式，二者等价。 LOCK_MODE分别是：S,GAP 或 X,GAP
gap lock 锁住的间隙可以是第一个索引记录前面的间隙，或相邻两条索引记录之间的间隙，或最后一个索引记录后面的间隙。 gap lock 存在的唯一目的就是阻止其他事务向gap中插入数据行，它用于在隔离级别为RR时，阻止幻影行(phantom row)的产生；隔离级别为RC时，搜索和索引扫描时，gap lock 是被禁用的，只在外键约束检查和重复 key 检查时 gap lock 才有效，正是因为此，RC 时会有幻影行问题。
Next-Key Locks next-key lock 是 (索引记录上的索引记录锁) + (该索引记录前面的间隙上的锁) 二者的合体，它锁定索引记录以及该索引记录前面的间隙。有 shard 或e xclusive两种模式。 LOCK_MODE分别是：S 或 X
当 InnoDB 搜索或扫描索引时，InnoDB 在它遇到的索引记录上所设置的锁就是 next-key lock，它会锁定索引记录本身以及该索引记录前面的 gap(“gap” immediately before that index record)。即：如果事务T1 在索引记录r 上有一个next-key lock，则 T2 无法在紧靠着 r 前面的那个间隙中插入新的索引记录(gap immediately before r in the index order)。 next-key lock 还会加在 supremum pseudo-record(索引中的伪记录(pseudo-record)，代表此索引中可能存在的最大值) 上，该 next-key lock实际上锁定了“此索引中可能存在的最大值”前面的间隙，也就是此索引中当前实际存在的最大值后面的间隙。
插入意向锁(Insert Intention Locks) INSERT 操作插入成功后，会在新插入的行上设置 index record lock，但在插入行之前，INSERT 操作会首先在索引记录之间的间隙上设置 insert intention lock，该锁的范围是(插入值, 向下的一个索引值)。有 shard 或 exclusive 两种模式，二者等价。 LOCK_MODE分别是：S,GAP,INSERT_INTENTION 或 X,GAP,INSERT_INTENTION
insert intention lock 相互不会阻塞，即多个事务向同一个 index gap 并发进行插入时，多个事务无需相互等待，极大提高了插入的并发性。那么为什么需要 insert intenton lock 呢？答案就是上面我们提到的 gap lock 或 next-key lock 会阻塞 insert intention lock, 这个特性解决了 RR 隔离级别中的 phantom row。
自增锁(AUTO-INC Locks) 表锁。向带有AUTO_INCREMENT列的表时插入数据行时，事务需要首先获取到该表的AUTO-INC表级锁，以便可以生成连续的自增值。插入语句开始时请求该锁，插入语句结束后释放该锁(不是事务结束) 我们日常开发中大部分场景都是 simple-inserts, 即待插入的条数提前可以确定，那么自增值的个数也可以别提前确定就不需要 auto-inc 锁。
空间索引(Predicate Locks for Spatial Indexes) 平时很少用到，这里不涉及

加锁分析

这里使用的环境是 MySQL 8.0.19
测试表如下

1
2
3
4
5
6
7
8
9


 CREATE TABLE `test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `n` int NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_n` (`n`),
  KEY `index_b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

没有使用索引，全表扫描
这种情况下，InnoDB 会在所有记录上加行锁

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test where a='a' for update;
Empty set (0.00 sec)

# 查看加锁情况，结果如下  
# 可以看到全部记录加了 next-key lock(X)  
+----------+------------+-----------+-----------+-------------+------------------------+
| trans_id | index_name | lock_type | lock_mode | lock_status | lock_data              |
+----------+------------+-----------+-----------+-------------+------------------------+
|     2091 | NULL       | TABLE     | IX        | GRANTED     | NULL                   |
|     2091 | PRIMARY    | RECORD    | X         | GRANTED     | supremum pseudo-record |
|     2091 | PRIMARY    | RECORD    | X         | GRANTED     | 1                      |
|     2091 | PRIMARY    | RECORD    | X         | GRANTED     | 2                      |
+----------+------------+-----------+-----------+-------------+------------------------+

上面看到了，查询条件无法使用索引则不得不全部行加锁，可见索引的重要性。

使用了唯一索引首先如果是等值查询，只需要索引记录锁(index record lock)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20


mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test where n=5 for update;
+----+------+------+---+
| id | a    | b    | n |
+----+------+------+---+
|  1 | s    | ss   | 5 |
+----+------+------+---+
1 row in set (0.00 sec)

# 查看加锁情况，结果如下
# 在 index_n 上只加了 index record lock(X,REC_NOT_GAP)  
+----------+------------+-----------+---------------+-------------+-----------+
| trans_id | index_name | lock_type | lock_mode     | lock_status | lock_data |
+----------+------------+-----------+---------------+-------------+-----------+
|     2116 | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|     2116 | index_n    | RECORD    | X,REC_NOT_GAP | GRANTED     | 5, 1      |
|     2116 | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 1         |
+----------+------------+-----------+---------------+-------------+-----------+

范围查询，对符合条件的每条记录加 next-key lock 和在第一个不满足条件的索引记录上设置next-key lock

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31


# 目前表中的所有记录  
mysql> select * from test;
+----+------+------+----+
| id | a    | b    | n  |
+----+------+------+----+
|  1 | s    | ss   |  5 |
|  2 | d    | dd   | 10 |
|  4 | g    | gg   | 20 |
|  6 | l    | ll   | 50 |
+----+------+------+----+

mysql> select * from test where n > 1 and n <=15 for update;
+----+------+------+----+
| id | a    | b    | n  |
+----+------+------+----+
|  1 | s    | ss   |  5 |
|  2 | d    | dd   | 10 |
+----+------+------+----+
  
# 加锁情况如下  
# 可以看到符合条件(5，10)的记录都被加上了 next-key lock(X)， 不符合条件的第一条(20)也加上了 next-key lock  
+----------+------------+-----------+---------------+-------------+-----------+
| trans_id | index_name | lock_type | lock_mode     | lock_status | lock_data |
+----------+------------+-----------+---------------+-------------+-----------+
|     2119 | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|     2119 | index_n    | RECORD    | X             | GRANTED     | 5, 1      |
|     2119 | index_n    | RECORD    | X             | GRANTED     | 10, 2     |
|     2119 | index_n    | RECORD    | X             | GRANTED     | 20, 4     |
|     2119 | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 1         |
|     2119 | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 2         |
+----------+------------+-----------+---------------+-------------+-----------+

使用非唯一索引 InnoDB 会锁住索引本身，还会锁住索引记录前面的间隙会对所有满足条件的记录即加 next-key lock。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19


mysql> select * from test where b > 'gg' for update;
+----+------+------+----+
| id | a    | b    | n  |
+----+------+------+----+
|  6 | l    | ll   | 50 |
|  1 | s    | ss   |  5 |
+----+------+------+----+

# 加锁情况如下，可以看到在非唯一索引 index_b 加的是 next-key lock    
+----------+------------+-----------+---------------+-------------+------------------------+
| trans_id | index_name | lock_type | lock_mode     | lock_status | lock_data              |
+----------+------------+-----------+---------------+-------------+------------------------+
|     2159 | NULL       | TABLE     | IX            | GRANTED     | NULL                   |
|     2159 | index_b    | RECORD    | X             | GRANTED     | supremum pseudo-record |
|     2159 | index_b    | RECORD    | X             | GRANTED     | 'ss', 1                |
|     2159 | index_b    | RECORD    | X             | GRANTED     | 'll', 6                |
|     2159 | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 1                      |
|     2159 | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 6                      |
+----------+------------+-----------+---------------+-------------+------------------------+

在等值条件下，除了加 next-key lock 还会在搜索第一个不满足条件的索引记录上加 gap lock

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


mysql> select * from test where b = 'gg' for update;
+----+------+------+----+
| id | a    | b    | n  |
+----+------+------+----+
|  4 | g    | gg   | 20 |
+----+------+------+----+
  
# 加锁情况如下    
# 可以看到在第一条不满足条件的记录('ll') 的索引上加了 gap lock  
+----------+------------+-----------+---------------+-------------+-----------+
| trans_id | index_name | lock_type | lock_mode     | lock_status | lock_data |
+----------+------------+-----------+---------------+-------------+-----------+
|     2161 | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|     2161 | index_b    | RECORD    | X             | GRANTED     | 'gg', 4   |
|     2161 | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 4         |
|     2161 | index_b    | RECORD    | X,GAP         | GRANTED     | 'll', 6   |
+----------+------------+-----------+---------------+-------------+-----------+

上面分析的情况都是在事务隔离级别为 Reaptable Read 的情况下，我们说过加锁情况跟事务隔离级别是密切相关的。下面我们分析在 Read Committed 下的加锁情况

RC 事务隔离级别
RC 下不加 next-key 或者 gap lock，只加 index record lock(当然无论何种隔离级别，PRIMARY 上的 index record lock 总是会加的)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26


 mysql> set session transaction isolation level read committed;
 Query OK, 0 rows affected (0.00 sec)
 mysql> select @@session.transaction_isolation;
 +---------------------------------+
 | @@session.transaction_isolation |
 +---------------------------------+
 | READ-COMMITTED                  |
 +---------------------------------+

 mysql> begin;
 Query OK, 0 rows affected (0.00 sec)
 mysql> select * from test where b='gg' for update;
 +----+------+------+----+
 | id | a    | b    | n  |
 +----+------+------+----+
 |  4 | g    | gg   | 20 |
 +----+------+------+----+

 # 加锁情况如下，我们看到只有 index reord lock(X,REC_NOT_GAP)     
 +----------+------------+-----------+---------------+-------------+-----------+
 | trans_id | index_name | lock_type | lock_mode     | lock_status | lock_data |
 +----------+------------+-----------+---------------+-------------+-----------+
 |     2125 | NULL       | TABLE     | IX            | GRANTED     | NULL      |
 |     2125 | index_b    | RECORD    | X,REC_NOT_GAP | GRANTED     | 'gg', 4   |
 |     2125 | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 4         |
 +----------+------------+-----------+---------------+-------------+-----------+

查看 insert intention lock
上面我们看到了 X，X,GAP, X,REC_NOT_GAP 这几种锁都是锁定读和 update/delete 中很常见的锁。下面我们分析一下插入时加的锁

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28


# 我们首先开启事务 T1 更新一条记录  
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update test set a='kk' where b='gg';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0
# 上面 update 的加速情况我们现在应该很清楚了，('dd''gg']有锁，('gg', 'ss')有锁 
# 那么如果我们插入一条记录 b='jj' 是不是插需要锁呢？

# 开启事务 T2 插入一条记录
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test(a, b , n) values('j', 'jj', 50);
# 这里我们发现等待锁的获取直到超时退出

# 这时的加锁情况如下   
# 我们清楚的看到次此时 T2(2127) 等待获取 insert intention lock，所以无法插入，和我们的预期完全一致。这样就防止了 phantom row 的产生。      
+----------+------------+-----------+------------------------+-------------+-----------+
| trans_id | index_name | lock_type | lock_mode              | lock_status | lock_data |
+----------+------------+-----------+------------------------+-------------+-----------+
|     2127 | NULL       | TABLE     | IX                     | GRANTED     | NULL      |
|     2127 | index_b    | RECORD    | X,GAP,INSERT_INTENTION | WAITING     | 'ss', 1   |
|     2126 | NULL       | TABLE     | IX                     | GRANTED     | NULL      |
|     2126 | index_b    | RECORD    | X                      | GRANTED     | 'gg', 4   |
|     2126 | PRIMARY    | RECORD    | X,REC_NOT_GAP          | GRANTED     | 4         |
|     2126 | index_b    | RECORD    | X,GAP                  | GRANTED     | 'ss', 1   |
+----------+------------+-----------+------------------------+-------------+-----------+

注意: performance_schema.data_locks 显示的并不是全部的加锁，SQL实际执行时所没有使用的索引加上的锁则不会显示在表中(实际上是加了锁)
下面的这个例子说明了这种情况
``` # 事务 T1 mysql> begin; Query OK, 0 rows affected (0.02 sec)

mysql> update test set b='gh' where n=20;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 查看加锁情况     
# 可以看到表里只有 index_n 加了锁 
+----------+------------+-----------+---------------+-------------+-----------+
| trans_id | index_name | lock_type | lock_mode     | lock_status | lock_data |
+----------+------------+-----------+---------------+-------------+-----------+
|     2143 | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|     2143 | index_n    | RECORD    | X,REC_NOT_GAP | GRANTED     | 20, 4     |
|     2143 | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 4         |
+----------+------------+-----------+---------------+-------------+-----------+

# 事务 T2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update test set b='gg' where b='gh';
如果 T1 不提交，上面的更新会等待锁直到超时    

# 加锁情况如下  
# 显然在 T1(2143) 中 index_b 加了 next-key lock 但并未显示出来    
+----------+------------+-----------+---------------+-------------+-----------+
| trans_id | index_name | lock_type | lock_mode     | lock_status | lock_data |
+----------+------------+-----------+---------------+-------------+-----------+
|     2148 | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|     2148 | index_b    | RECORD    | X             | WAITING     | 'gh', 4   |
|     2143 | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|     2143 | index_n    | RECORD    | X,REC_NOT_GAP | GRANTED     | 20, 4     |
|     2143 | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 4         |
|     2143 | index_b    | RECORD    | X,REC_NOT_GAP | GRANTED     | 'gh', 4   |
+----------+------------+-----------+---------------+-------------+-----------+
```

目录

MySQL InnoDB 加锁实践分析

InnoDB 锁类型

加锁分析