MySQL锁那些事儿-mysql行锁和表锁

本文转载自微信公众号「码虫甲」，作者码虫甲。转载本文请联系码虫甲公众号。

背景

当数据库中有多个操作需要修改同一数据时，不可避免的会产生数据的脏读。这时就需要数据库具有良好的并发控制能力，这一切在 MySQL 中都是由服务器和存储引擎来实现的。解决并发问题最有效的方案是引入了锁的机制，锁在功能上分为共享锁 (shared lock) 和排它锁 (exclusive lock) 即通常说的读锁和写锁; 锁的粒度上分行锁和表锁，表级锁MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁(meta data lock，MDL)

行锁种类

Next-Key Lock：锁定一个范围，并且锁定记录本上;
Gap Lock：间隙锁，锁定一个范围，但不包含记录本上;
Record Lock：单个行记录上的锁;

加锁规则

虽然 MySQL 的锁各式各样，但是有些基本的加锁原则是保持不变的，譬如：快照读是不加锁的，更新语句肯定是加排它锁的，RC 隔离级别是没有间隙锁的等等。这些规则整理如下：

常见语句的加锁

SELECT ... 语句正常情况下为快照读，不加锁;
SELECT ... LOCK IN SHARE MODE 语句为当前读，加 S 锁;
SELECT ... FOR UPDATE 语句为当前读，加 X 锁;
常见的 DML 语句(如 INSERT、DELETE、UPDATE)为当前读，加 X 锁;
常见的 DDL 语句(如 ALTER、CREATE 等)加表级锁，且这些语句为隐式提交，不能回滚;

表锁

表锁(分 S 锁和 X 锁)
意向锁(分 IS 锁和 IX 锁)
自增锁(一般见不到，只有在 innodb_autoinc_lock_mode = 0 或者 Bulk inserts 时才可能有)

行锁分析

行锁都是加在索引上的，最终都会落在聚簇索引上;
加行锁的过程是一条一条记录加的;

锁冲突

S 锁和 S 锁兼容，X 锁和 X 锁冲突，X 锁和 S 锁冲突;

不同隔离级别下的锁

上面说 SELECT ... 语句正常情况下为快照读，不加锁;但是在 Serializable 隔离级别下为当前读，加 S 锁;
RC 隔离级别下没有间隙锁和 Next-key 锁

SQL 的加锁分析

我们使用下面这张 students 表为例，其中 id 为主键，no(学号)为二级唯一索引，name(姓名)和 age(年龄)为二级非唯一索引，score(学分)无索引。

我们只分析最简单的一种 SQL，它只包含一个 WHERE 条件，等值查询或范围查询。虽然 SQL 非常简单，但是针对不同类型的列，我们还是会面对各种情况：

聚簇索引，查询命中：UPDATE students SET score = 100 WHERE id = 15;
聚簇索引，查询未命中：UPDATE students SET score = 100 WHERE id = 16;
二级非唯一索引，查询命中：UPDATE students SET score = 100 WHERE name = 'Tom';
二级非唯一索引，查询未命中：UPDATE students SET score = 100 WHERE name = 'John';
无索引：UPDATE students SET score = 100 WHERE score = 22;
聚簇索引，范围查询：UPDATE students SET score = 100 WHERE id <= 20;
二级索引，范围查询：UPDATE students SET score = 100 WHERE age <= 23;

聚簇索引，查询命中

语句 UPDATE students SET score = 100 WHERE id = 15 在 RC 和 RR 隔离级别下加锁情况一样，都是对 id 这个聚簇索引加 X 锁，如下：

聚簇索引，查询未命中

如果查询未命中记录，在 RC 和 RR 隔离级别下加锁是不一样的，因为 RR 有 GAP 锁。语句 UPDATE students SET score = 100 WHERE id = 16 在 RC 和 RR 隔离级别下的加锁情况如下(RC 不加锁)：

二级非唯一索引，查询命中

如果查询命中的是二级非唯一索引，在 RR 隔离级别下，还会加 GAP 锁。语句 UPDATE students SET score = 100 WHERE name = 'Tom' 加锁如下：

数一数上方右图中的锁你可能会觉得一共加了 7 把锁，实际情况不是，要注意的是 (Tom, 37) 上的记录锁和它前面的 GAP 锁合起来是一个 Next-key 锁，这个锁加在 (Tom, 37) 这个索引上，另外 (Tom, 49) 上也有一把 Next-key 锁。那么最右边的 GAP 锁加在哪呢?右边已经没有任何记录了啊。其实，在 InnoDB 存储引擎里，每个数据页中都会有两个虚拟的行记录，用来限定记录的边界，分别是：Infimum Record 和 Supremum Record，Infimum 是比该页中任何记录都要小的值，而 Supremum 比该页中最大的记录值还要大，这两条记录在创建页的时候就有了，并且不会删除。上面右边的 GAP 锁就是加在 Supremum Record 上。所以说，上面右图中共有 2 把 Next-key 锁，1 把 GAP 锁，2 把记录锁，一共 5 把锁。

二级非唯一索引，查询未命中

如果查询未命中记录，RR 隔离级别会加 GAP 锁，RC 无锁。语句 UPDATE students SET score = 100 WHERE name = 'John' 加锁情况如下：

无索引

如果 WHERE 条件不能走索引，MySQL 会如何加锁呢?有的人说会在表上加 X 锁，也有人说会根据 WHERE 条件将筛选出来的记录在聚簇索引上加上 X 锁，那么究竟如何，我们看下图：

在没有索引的时候，只能走聚簇索引，对表中的记录进行全表扫描。在 RC 隔离级别下会给所有记录加行锁，在 RR 隔离级别下，不仅会给所有记录加行锁，所有聚簇索引和聚簇索引之间还会加上 GAP 锁。

语句 UPDATE students SET score = 100 WHERE score = 22 满足条件的虽然只有 1 条记录，但是聚簇索引上所有的记录，都被加上了 X 锁。那么，为什么不是只在满足条件的记录上加锁呢?这是由于 MySQL 的实现决定的。如果一个条件无法通过索引快速过滤，那么存储引擎层面就会将所有记录加锁后返回，然后由 MySQL Server 层进行过滤，因此也就把所有的记录都锁上了。

聚簇索引，范围查询

上面所介绍的各种情况其实都是非常常见的 SQL，它们有一个特点：全部都只有一个 WHERE 条件，并且都是等值查询。那么问题来了，如果不是等值查询而是范围查询，加锁情况会怎么样呢?

SQL 语句为 UPDATE students SET score = 100 WHERE id <= 20，按理说我们只需要将 id = 20、18、15 三条记录锁住即可，但是看右边的图，在 RR 隔离级别下，我们还把 id = 30 这条记录以及 (20, 30] 之间的间隙也锁起来了，很显然这是一个 Next-key 锁。

二级索引，范围查询

然后我们把范围查询应用到二级非唯一索引上来，SQL 语句为：UPDATE students SET score = 100 WHERE age <= 23，加锁情况如下图所示：

可以看出和聚簇索引的范围查询一样，除了 WHERE 条件范围内的记录加锁之外，后面一条记录也会加上 Next-key 锁，这里有意思的一点是，尽管满足 age = 24 的记录有两条，但只有第一条被加锁，第二条没有加锁，并且第一条和第二条之间也没有加锁。

metadata lock

元数据锁(meta data lock，MDL)不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更,删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。因此，在 MySQL 5.5 版本中引入了 MDL,当对一个表做增删改查操作的时候，加 MDL 读锁;当要对表做结构变更操作的时候，加 MDL 写锁。读锁之间不互斥，因此可以有多个线程同时对一张表增删改查。读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性,因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

虽然 MDL 锁是系统默认会加的，但却是你不能忽略的一个机制,比如下面这个例子，经常看到有人掉到这个坑里：给一个小表加个字段，导致整个库挂了。你肯定知道，给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据。在对大表操作的时候，需要特别小心,以免对线上服务造成影响。而实际上，即使是小表操作不慎也会出问题。

我们来看一下下面的操作序列，假设表 t2 是一个小表。备注：这里的实验环境是 MySQL 5.7

Session a	Session b	Session c	Session d
begin; select * from t2 limit 1;
	select * from t2 limit 1;
		alter table t2 add f int;(blocked)
			select * from t2 limit 1;(blocked)

我们可以看到 session A 先启动，这时候会对表 t加一个 MDL 读锁。由于 session B 需要的也是MDL 读锁，因此可以正常执行

之后 session C 会被 blocked，是因为 session A 的 MDL 读锁还没有释放，而 session C 需要 MDL 写锁，因此只能被阻塞。

如果只有 session C 自己被阻塞还没什么关系，但是之后所有要在表 t 上新申请 MDL 读锁的请求也会被session C 阻塞。前面我们说了，所有对表的增删改查操作都需要先申请 MDL 读锁，就都被锁住，等于这个表现在完全不可读写了。

如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新 session 再请求的话，这个库的线程很快就会爆满。

解决长事务，事务不提交，就会一直占着 MDL 锁;

Online DDL的过程是这样的：

1. 拿MDL写锁

2. 降级成MDL读锁

3. 真正做DDL

4. 升级成MDL写锁

5. 释放MDL锁

1、2、4、5如果没有锁冲突，执行时间非常短。第3步占用了DDL绝大部分时间，这期间这个表可以正常读写数据，是因此称为“online ”

我们上面的例子，是在第一步就堵住了，拿不到MDL写锁

在MySQL中使用 create table2 as select * from table1时，语句执行完成之前 table1的mdl锁不会释放，且因持续持有S锁造成阻塞table1写操作;

insert into table1 select * from table2

在MySQL中使用insert into table1 select * from table2时，会对table2进行加锁，这个加锁分以下几种情况：

1.后面不带查询条件，不带排序方式

insert into table1 select * from table2：此时MySQL是逐行加锁，每一行都锁

2.查询使用主键排序

insert into table1 select * from table2 order by id：使用主键排序时，MySQL逐行加锁，每一行都锁

3.使用非主键排序

insert into table1 select * from table2 order by modified_date：使用非主键排序时，MySQL锁整个表

4.在查询条件中使用非主键筛选条件，

insert into table1 select * from table2 where modified_date>='2017-10-01 00:00:00'

使用非主键筛选条件时，MySQL逐行加锁，每一行都锁

结论：该句式导致写阻塞

建议：select ...outfile是不阻塞dml操作的，可以用select...into outfile 和 load data infile 的组合来代替insert...select完成插入操作。

实际场景中如何避免锁的资源竞争

让 SELECT 速度尽量快，尽量减少大的复杂的Query，将复杂的Query分拆成几个小的Query分步进行;

尽可能地建立足够高效的索引，让数据检索更迅速;

使用EXPLAIN SELECT来确定对于你的查询中使用的索引跟预期一致

死锁案例

案例1

Session a	Session b
update students set score=100 where id=20;
	pdate students set score=100 where id=30;
update students set score=100 where id=30;
	update students set score=100 where id=20;

死锁的根本原因是有两个或多个事务之间加锁顺序的不一致导致的，这个死锁案例其实是最经典的死锁场景。

首先，事务 A 获取 id = 20 的锁(lock_mode X locks rec but not gap)，事务 B 获取 id = 30 的锁;然后，事务 A 试图获取 id = 30 的锁，而该锁已经被事务 B 持有，所以事务 A 等待事务 B 释放该锁，然后事务 B 又试图获取 id = 20 的锁，这个锁被事务 A 占有，于是两个事务之间相互等待，导致死锁。

案例2

Session aSession b

update students set score=100 where id<30; update students set score=100 where age>23;

这个案例里每个事务都只有一条 SQL 语句，但可能会导致死锁问题，其实说起来，这个死锁和案例一并没有什么区别，只不过理解起来要更深入一点。要知道在范围查询时，加锁是一条记录一条记录挨个加锁的，所以虽然只有一条 SQL 语句，如果两条 SQL 语句的加锁顺序不一样，也会导致死锁。

在案例一中，事务 A 的加锁顺序为：id = 20 -> 30，事务 B 的加锁顺序为：id = 30 -> 20，正好相反，所以会导致死锁。这里的情景也是一样，事务 A 的范围条件为 id < 30，加锁顺序为：id = 15 -> 18 -> 20，事务 B 走的是二级索引 age，加锁顺序为：(age, id) = (24, 18) -> (24, 20) -> (25, 15) -> (25, 49)，其中，对 id 的加锁顺序为 id = 18 -> 20 -> 15 -> 49。可以看到事务 A 先锁 15，再锁 18，而事务 B 先锁 18，再锁 15，从而形成死锁。

如何避免死锁

如上面的案例一和案例二所示，对索引加锁顺序的不一致很可能会导致死锁，所以如果可以，尽量以相同的顺序来访问索引记录和表。在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能;

为表添加合理的索引，如果不走索引将会为表的每一行记录加锁，死锁的概率就会大大增大;

避免大事务，尽量将大事务拆成多个小事务来处理;因为大事务占用资源多，耗时长，与其他事务冲突的概率也会变高;

避免在同一时间点运行多个对同一表进行读写的脚本，特别注意加锁且操作数据量比较大的语句;我们经常会有一些定时脚本，避免它们在同一时间点运行;

设置锁等待超时参数：innodb_lock_wait_timeout(默认50s)，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。