Mysql锁看这篇就够了
共 7118字，需浏览 15分钟
·
2021-09-11 03:06
当数据库中有多个操作需要修改同一数据时，不可避免的会产生数据的脏读。这时就需要数据库具有良好的并发控制能力，这一切在 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 锁是系统默认会加的，但却是你不能忽略的一个机制,比如下面这个例子，经常看到有人掉到这个坑里：给一个小表加个字段，导致整个库挂了。你肯定知道，给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据。在对大表操作的时候，需要特别小心,以免对线上服务造成影响。而实际上，即使是小表操作不慎也会出问题。我们来看一下下面的操作序列，假设表 t 是一个小表。
备注：这里的实验环境是 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写操作;
在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 a | Session 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，从而形成死锁。
如何避免死锁
1.如上面的案例一和案例二所示，对索引加锁顺序的不一致很可能会导致死锁，所以如果可以，尽量以相同的顺序来访问索引记录和表。在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能；
2.为表添加合理的索引，如果不走索引将会为表的每一行记录加锁，死锁的概率就会大大增大；
3.避免大事务，尽量将大事务拆成多个小事务来处理；因为大事务占用资源多，耗时长，与其他事务冲突的概率也会变高；
4.避免在同一时间点运行多个对同一表进行读写的脚本，特别注意加锁且操作数据量比较大的语句；我们经常会有一些定时脚本，避免它们在同一时间点运行；
5.设置锁等待超时参数：innodb_lock_wait_timeout(默认50s)，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。
往期推荐
