超全面的MySQL语句加锁分析

作者：微信小助手

发布时间：2019-05-10T09:13:45

说在前面的话

本文是用来系统阐述在MySQL中，不同语句在各种条件下的加锁情况，并不是解释各种锁是什么（或者说加锁的本质是什么），大家如果不理解什么是MVCC、ReadView、正经记录锁、gap锁、next-key锁、插入意向锁这些概念的，可以参考MySQL的官方文档，或者直接参照《MySQL是怎样运行的：从根儿上理解MySQL》这本小册（里边有比官方文档更贴心，更详细的解释，文章中涉及到的所有概念均在小册中有详细解释。

建议：

1. 本篇文章不适合碎片化时间阅读，最好使用电脑观看，或者将字体跳到最小效果好一些

2. 可能一下子看不完，关注 + 收藏 + 好看 + 转发一波

3. 不要跳着看

事前准备

建立一个存储三国英雄的hero表：

CREATE TABLE hero (
 number INT,
 name VARCHAR(100),
 country varchar(100),
 PRIMARY KEY (number),
 KEY idx_name (name)
) Engine=InnoDB CHARSET=utf8;

然后向这个表里插入几条记录：

INSERT INTO hero VALUES
 (1, 'l刘备', '蜀'),
 (3, 'z诸葛亮', '蜀'),
 (8, 'c曹操', '魏'),
 (15, 'x荀彧', '魏'),
 (20, 's孙权', '吴');

然后现在hero表就有了两个索引（一个二级索引，一个聚簇索引），示意图如下：

语句加锁分析

其实啊，“XXX语句该加什么锁”本身就是个伪命题，一条语句需要加的锁受到很多条件制约，比方说：

事务的隔离级别
语句执行时使用的索引（比如聚簇索引、唯一二级索引、普通二级索引）
查询条件（比方说=、=<、>=等等）
具体执行的语句类型

在继续详细分析语句的加锁过程前，大家一定要有一个全局概念：加锁只是解决并发事务执行过程中引起的脏写、脏读、不可重复读、幻读这些问题的一种解决方案（MVCC算是一种解决脏读、不可重复读、幻读这些问题的一种解决方案），一定要意识到加锁的出发点是为了解决这些问题，不同情景下要解决的问题不一样，才导致加的锁不一样，千万不要为了加锁而加锁，容易把自己绕进去。当然，有时候因为MySQL具体的实现而导致一些情景下的加锁有些不太好理解，这就得我们死记硬背了～

我们这里把语句分为3种大类：普通的SELECT语句、锁定读的语句、INSERT语句，我们分别看一下。

普通的SELECT语句

普通的SELECT语句在：

READ UNCOMMITTED隔离级别下，不加锁，直接读取记录的最新版本，可能发生脏读、不可重复读和幻读问题。
READ COMMITTED隔离级别下，不加锁，在每次执行普通的SELECT语句时都会生成一个ReadView，这样解决了脏读问题，但没有解决不可重复读和幻读问题。
REPEATABLE READ隔离级别下，不加锁，只在第一次执行普通的SELECT语句时生成一个ReadView，这样把脏读、不可重复读和幻读问题都解决了。
不过这里有一个小插曲：
```
# 事务T1，REPEATABLE READ隔离级别下
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM hero WHERE number = 30;
Empty set (0.01 sec)

# 此时事务T2执行了：INSERT INTO hero VALUES(30, 'g关羽', '魏'); 并提交

mysql> UPDATE hero SET country = '蜀' WHERE number = 30;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM hero WHERE number = 30;
+--------+---------+---------+
| number | name | country |
+--------+---------+---------+
| 30 | g关羽 | 蜀 |
+--------+---------+---------+
1 row in set (0.01 sec)
```
在REPEATABLE READ隔离级别下，T1第一次执行普通的SELECT语句时生成了一个ReadView，之后T2向hero表中新插入了一条记录便提交了，ReadView并不能阻止T1执行UPDATE或者DELETE语句来对改动这个新插入的记录（因为T2已经提交，改动该记录并不会造成阻塞），但是这样一来这条新记录的trx_id隐藏列就变成了T1的事务id，之后T1中再使用普通的SELECT语句去查询这条记录时就可以看到这条记录了，也就把这条记录返回给客户端了。因为这个特殊现象的存在，你也可以认为InnoDB中的MVCC并不能完完全全的禁止幻读。
SERIALIZABLE隔离级别下，需要分为两种情况讨论：

在系统变量autocommit=0时，也就是禁用自动提交时，普通的SELECT语句会被转为SELECT ... LOCK IN SHARE MODE这样的语句，也就是在读取记录前需要先获得记录的S锁，具体的加锁情况和REPEATABLE READ隔离级别下一样，我们后边再分析。
在系统变量autocommit=1时，也就是启用自动提交时，普通的SELECT语句并不加锁，只是利用MVCC来生成一个ReadView去读取记录。
为啥不加锁呢？因为启用自动提交意味着一个事务中只包含一条语句，一条语句也就没有啥不可重复读、幻读这样的问题了。

锁定读的语句

我们把下边四种语句放到一起讨论：

语句一：SELECT ... LOCK IN SHARE MODE;
语句二：SELECT ... FOR UPDATE;
语句三：UPDATE ...
语句四：DELETE ...

我们说语句一和语句二是MySQL中规定的两种锁定读的语法格式，而语句三和语句四由于在执行过程需要首先定位到被改动的记录并给记录加锁，也可以被认为是一种锁定读。

READ UNCOMMITTED/READ COMMITTED隔离级别下

在READ UNCOMMITTED下语句的加锁方式和READ COMMITTED隔离级别下语句的加锁方式基本一致，所以就放到一块儿说了。值得注意的是，采用加锁方式解决并发事务带来的问题时，其实脏读和不可重复读在任何一个隔离级别下都不会发生（因为读-写操作需要排队进行）。

对于使用主键进行等值查询的情况

使用SELECT ... LOCK IN SHARE MODE来为记录加锁，比方说：
```
SELECT * FROM hero WHERE number = 8 LOCK IN SHARE MODE;
```
这个语句执行时只需要访问一下聚簇索引中number值为8的记录，所以只需要给它加一个S型正经记录锁就好了，如图所示：
使用SELECT ... FOR UPDATE来为记录加锁，比方说：
```
SELECT * FROM hero WHERE number = 8 FOR UPDATE;
```
这个语句执行时只需要访问一下聚簇索引中number值为8的记录，所以只需要给它加一个X型正经记录锁就好了，如图所示：

小贴士：为了区分S锁和X锁，我们之后在示意图中就把加了S锁的记录染成蓝色，把加了X锁的记录染成紫色。
使用UPDATE ...来为记录加锁，比方说：
```
UPDATE hero SET country = '汉' WHERE number = 8;
```
这条UPDATE语句并没有更新二级索引列，加锁方式和上边所说的SELECT ... FOR UPDATE语句一致。
如果UPDATE语句中更新了二级索引列，比方说：
```
UPDATE hero SET name = 'cao曹操' WHERE number = 8;
```
该语句的实际执行步骤是首先更新对应的number值为