MySQL锁（深入浅出）

引言

为了解决多个进程访问内存或磁盘中的同一份数据造成的冲突，通常有两种解决方案，一种是多版本；另一种就是锁。MySQL作为一种关系型数据库，其实也是通过这两种方式来解决数据访问冲突的。MySQL数据多版本叫MVCC，同时MySQL使用了各种类型的锁来保证数据一致性。

锁的介绍

锁的分类：按行为分类

我们对数据库的操作分为读和写，MySQL在这个层面把锁分成了共享锁（Shared Lock）和独占锁（Exclusive Lock）也称为S锁和X锁。对于相同的数据S锁可以存在多个，但是不能和X锁共存；X锁本身也不可以多个共存。MySQL为了协调表和行数据读写关系还对两个动作设计了锁，那就是“想读”和“想写”；“想读”对应的是意向共享锁（Intention Shared Lock），“想写”对应的是意向独占锁（Intention Exclusive Lock），对于这两个意向锁我们后面再介绍具体用途。

概括来讲：MySQL从用户行为上分了四类锁

读：S锁

写：X锁

想读：IS锁

想写：IX锁

锁的分类：按加锁对象

按照加锁对象的不同，分为表锁（对一个表加锁）和行锁（对某行数据加锁）。

MySQL的MyISAM、MEMORY、MERGE三种存储引擎只支持表锁，只有InnoDB支持行锁。接下来我们主要结束下InnoDB中的一些表锁和行锁。

前面按照行为和对象给锁分类，都只是MySQL中锁的一些归类，并不代表具体的锁，MySQL中具体用到的锁我们接下来详细介绍。MySQL有四大存储引擎，MyISAM、MEMORY、MERGE、InnoDB，其中只有InnoDB支持事务和行锁，使用也最为广泛，我们接下来主要介绍InnoDB引擎中的锁。

InnoDB中的表锁：

1.MDL锁

我们在执行DML（SELECT INSERT UPDATE DELETE）语句时，是不会对表加S、X锁的；但是当我们执行DDL语句时，MySQL会加元数据锁（MetadataLock）简称MDL锁。

这个时候大家就需要注意了，如果有事务在对表执行DML操作，就会阻塞我们的DDL操作，同时MDL锁，又会阻塞后面的事务。

在使用“锁定读”或者写操作时，需要给InnoDB中行添加对应的S、X锁，在给行记录添加锁之前，需要对应的先给表添加IS和IX锁。IS和IX锁的作用是在需要添加表级别S、X锁时，判断表内是否有某些行数据存在S、X锁（避免遍历表的所有行）。

这里额外解释一下锁定读：

我们想在读取记录时就为数据加锁的语句称为锁定读，MySQL支持下面两种锁定读的语句：

SELECT...LOCK IN SHARE MODE; 对记录加S锁

SELECT...FOR UPDATE; 对记录加X锁

2.自增主键相关的锁

我们经常会用到自增索引，AUTO_INCREMENT。为了保证插入的主键是连续递增的，当我们执行Insert语句时，“可能会”给表添加表锁，锁的名字叫：AUTO-INC。不过这个表锁在分配完ID后就释放了，不用等到事务提交。这里为什么说“可能会”。因为如果我们Insert语句插入的数据是可以确定条数的，其实可以使用一个单独的锁，为本次插入生成需要的自增主键，然后就立马释放锁。采用轻量级锁还是AUTO-INC也是可以配置的，MySQL有一个系统变量：innodb_autoinc_lock_mode。如果设置为0表示一律使用AUTO-INC锁；如果为1表示插入记录数确定时采用轻量级锁，不确定时采用AUTO-INC锁；如果为2表示一律采用轻量级锁。我们通常会设置为1

InnoDB中的行锁

1.对MySQL中的一条记录加锁，称为记录锁（LOCK_REC_NOT_GAP），记录锁有S、X两种

2. MySQL为了解决在REPEATABLE READ隔离级别下幻读的问题，引入了间隙锁（LOCK_GAP）。举一个例子，假设我们表中有两条记录：

id：5 name：小明

id：8 name：小张

当我们在5和8两条记录中间加GAP锁的时候，两条记录之间是不允许插入数据的。这里有个小问题，中间的记录可以有“间隙”，那第一条记录和最后一条记录怎么办呢？MySQL会给表生成两条伪记录，Infimum和Supermum，前者代表页面中的最小记录，后者代表页的最大记录，gap锁是为了防止插入的数据引起幻读而设计的。

3.如果即要锁住一条记录又要锁住记录的“间隙”，需要Next-key Lock。Next-key Lock本质上就是记录锁+间隙锁

4.插入意向锁，当我们在事务中想要插入一条记录时，需要判断此间隙是否存在gap锁，如果没有的话直接插入就可以了，如果存在gap锁，需要把这个插入数据的意向记录下来，并标记当前处于等待状态，我们把记录插入意向的锁结构称为插入意向锁（LOCK_INSERT_INTENTION）。当gap锁被释放的时候，此区间的插入意向锁们，会同时执行插入操作。

5.延迟加锁：MySQL对INSERT语句采用了延迟加锁的机制。

当执行一条INSERT语句时，（当然，如果插入间隙存在“间隙锁”，需要等到间隙锁的释放，并生成插入意向锁）对于主键索引来说是不加锁的，但是会为当前记录生成一个事务ID（trx_id），当其他事务想要对此语句加S、X锁时，首先看一下当前记录的trx_id是否活跃，如果已经完成则直接加锁，如果还没有完成就为当前记录上的trx_id代表的事务添加一把X锁，同时自己创建一个意向锁。

如果是二级索引，本身没有trx_id，但是二级索引的每一页都有当前页最大的trx_id，如果当前页最大trx_id小于当前最小的活跃trx_id则说明该页所做的修改已经提交，如果不是的话需要通过二级索引找到主键索引，对其做上一步的判断操作。

常用SQL的加锁过程

我们先构造一张curriculum(课程表)：其中id是主键 name是二级索引

我们分别介绍下各种语句的加锁情况，默认是在REPESTABLE READ隔离级别和INNODB引擎下

1.普通查询：select * from curriculum where id = 3;

在REPESTABLE READ隔离级别下，只在第一次查询时，生成读的“数据视图”，来避免脏读、不可重复读和幻读。

但是这里有一种情况下是存在幻读的：

事务一：

• 第一步：select * from curriculum where id = 2;
• 第二步：update curriculum set name="音乐1" where id = 2；
• 第三步：select * from curriculum where id = 2;

事务二：

• insert into curriculum value （2，“音乐0”，“t2”）

他们的执行顺序是:

事务一第一步 --> 事务二执行完 -->事务一第二步 --> 事务一第三步。

因为第一步执行完事务一并没对表里的记录加锁，事务二可以成功执行INSERT语句。这个时候事务一执行第二步，此时UPDATE语句需要更新“当前读”的数据，因此会更新事务二插入的id=2的这条记录；然后此时id=2这条记录的最新trx_id变成了事务一的trx_id，所以第三部就查询到了id=2的记录，也就出现了两次查询结果不一致的情况。

2.锁定读、UPDATE、DELETE：这几条语句的加锁方式基本是一样的，我们就只举一个UPDATE的例子

update curriculum set name = "音乐" where id >2 and id <=6;

• 第一步：读取（2，6]区间内的第一条主键索引，也就是id=3的记录，为id=3的主键索引记录加X型“Next-key Lock”
• 第二步：因为读的是主键索引所以没有“索引下推”
• 第三步：因为读的是主键索引所以没有“回表”
• 第四部：id=3符合（2，6]的查询区间，所以锁不释放
• 第五步：MySQL的“serve层”判断到当前记录符合条件，所以继续持有前面加的锁
• 继续主键索引的下一条记录：id=5过程和id=3完全相同
• 继续主键索引的下一条记录：id=7
• 第一步：读取到id=7的记录，并对此主键索引记录加X型“Next-key Lock”
• 第二步：因为读的是主键索引所以没有“索引下推”
• 第三步：因为读的是主键索引所以没有“回表”
• 第四步：判断扫描区间为（1，6] ,超出了扫描区间，所以释放id=7主键索引记录的锁，给server层返回更新完毕
• 第五步：server层接受到引擎返回的更新完毕，结束

3.锁定读、UPDATE、DELETE等值条件：

update curriculum set name = "音乐" where id = 2；

id=2的记录不存在，最终会生成区间（1，3）的X型“间隙锁”。

前面讲到的都是唯一索引，如果是二级索引呢？

其实二级索引的加锁判断条件和一级索引相同；同时如果二级索引符合“索引下推”条件，产生回表操作之后，一级索引也会按照同样的规则加锁。

INSERT 语句的锁在前文延迟加锁时已经说过了，就不再重复赘述了。

查看锁的情况，及死锁处理

1.查看获取锁失败或者阻塞的事务：

SELECT * FROM information_schema.INNODB_TRX \G;

然后通过下面这个表可以查询到事务的阻塞先后关系

SELECT * FROM information_schema.INNODB_LOCK_WAITS \G;

注意：不过INNODB_TRX和INNODB_LOCK_WAITS在MySQL8.0的版本已经不再支持了。

2.SHOW ENGINE INNODB STATUS;查看锁的情况及分析死锁信息

SHOW ENGINE INNODB STATUS;返回的信息会比较多，具体如何查看本文就不描述了，大家可以自行搜索相关资料。

3.如何排查MDL锁有没有阻塞我们的SQL

• 第一步：SHOW PROCESSLIST;
• 第二步：查看State列是否有Waiting for table metadata lock如果存在，说明有MDL锁等待
• 第三步：SELECT * FROM sys.schema_table_lock_waits; 查看造成阻塞的process id，直接kill即可

补充：show processlist展示的内容较多可以参照官网的解（https://dev.mysql.com/doc/refman/5.7/en/thread-information.html）

4.执行SELECT * FROM.INNODB_LOCK_WAITS;查看某个表是否被锁定的情况，其中blocking_pid展示的是阻塞的process id，某些情况下可以手动kill掉

给我们带来的一些启发

1.MySQL事务在真正执行锁定读、UPDATE、DELETE操作前才对相应的索引加行锁，所以尽量把这些语句放在事务的靠后的位置，离commit越近越好，这样锁的时间越短。

2.MySQL在处理INSERT语句时采用的延迟加锁的设计，我们在开发中也可以借鉴。通过一个有序列及对象状态，判断上一次INSERT是否需要加锁，这样可以提高并发。

3.MySQL的间隙锁我们可以学习到，对于不存在的数据我们也可以加锁，在一些业务场景中，可能会有用。

4.大表和小表的DML操作都要谨慎，在业务低峰期进行。

参考资料： 《高性能MySQL》 《MySQL是怎样运行的》 MySQL官网（https://dev.mysql.com/doc/refman/5.7/en/）