mysql lock - yaokun123/php-wiki GitHub Wiki

mysql的锁机制

一、为什么需要加锁

首先，为什么要加锁？我想我不用多说了，想象接下来的场景你就能 GET 了。

你在商场的卫生间上厕所，此时你一定会做的操作是啥？锁门。如果不锁门，上厕所上着上着，
啪一下门就被打开了，可能大概也许似乎貌似有那么一丁点的不太合适。

数据也是一样，在并发的场景下，如果不对数据加锁，会直接破坏数据的一致性，并且如果你的业务涉及到钱，那后果就更严重了。

二、锁的分类

在 InnoDB 中，都有哪些锁？其实你应该已经知道了很多了，例如面试中会问你存储引擎 MyISAM 和 InnoDB 的区别，你会说 MyIASM 只有表锁，但是 InnoDB 同时支持行锁和表锁。你可能还会被问到乐观锁和悲观锁的区别是啥。

锁的概念、名词很多，如果你没有对锁构建出一个完整的世界观，那么你理解起来就会比较有阻碍，接下来我们把这些锁给分一下类。

2.1、按照锁的粒度

按照锁的粒度进行划分可以分为：

  • 表锁
  • 行锁

这里就不讨论页锁了，页锁是 BDB（BerkeleyDB） 存储引擎中才有的概念，我们这里主要讨论 InnoDB 存储引擎。

表锁

表锁由 MySQL Server 实现，一般在执行 DDL 语句时会对整个表进行加锁，比如说 ALTER TABLE 等操作。在执行 SQL 语句时，也可以明确指定对某个表进行加锁。

mysql> lock table user read(write);# 分为读锁和写锁Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where id = 100; # 成功
mysql> select * from role where id = 100; # 失败，未提前获取该 role的读表锁(这里只能使用你锁的表)
mysql> update user  set name = 'Tom' where id = 100; # 失败，未提前获得user的写表锁
mysql> unlock tables; # 显示释放表锁Query OK, 0 rows affected (0.00 sec)

表锁使用的是一次性锁技术，也就是说，在会话开始的地方使用 lock 命令将后续需要用到的表都加上锁，在表释放前，只能访问这些加锁的表，不能访问其他表，直到最后通过 unlock tables 释放所有表锁。

除了使用 unlock tables 显示释放锁之外，会话持有其他表锁时执行lock table 语句会释放会话之前持有的锁；会话持有其他表锁时执行 start transaction 或者 begin 开启事务时，也会释放之前持有的锁。

行锁

行锁则是锁定某一行或者某几行，或者行与行之间的间隙。行锁则是存储引擎实现，不同的引擎实现的不同。后续没有特别说明，则行锁特指 InnoDB 实现的行锁。

在了解 InnoDB 的加锁原理前，需要对其存储结构有一定的了解。InnoDB 是聚簇索引，也就是 B+树的叶节点既存储了主键索引也存储了数据行。而 InnoDB 的二级索引的叶节点存储的则是主键值，所以通过二级索引查询数据时，还需要拿对应的主键去聚簇索引中再次进行查询。

下面以两条 SQL 的执行为例，讲解一下 InnoDB 对于单行数据的加锁原理。

mysql> update user set age = 10 where id = 49;
mysql> update user set age = 10 where name = 'Tom';

第一条 SQL 使用主键索引来查询，则只需要在 id = 49 这个主键索引上加上写锁；

第二条 SQL 则使用二级索引来查询，则首先在 name = Tom 这个索引上加写锁，然后由于使用 InnoDB 二级索引还需再次根据主键索引查询，所以还需要在 id = 49 这个主键索引上加写锁

也就是说使用主键索引需要加一把锁，使用二级索引需要在二级索引和主键索引上各加一把锁。

根据索引对单行数据进行更新的加锁原理了解了，那如果更新操作涉及多个行呢，比如下面 SQL 的执行场景。

update user set age = 10 where id > 49;

上述 SQL 的执行过程如下图所示。MySQL Server 会根据 WHERE 条件读取第一条满足条件的记录，然后 InnoDB 引擎会将第一条记录返回并加锁，接着 MySQL Server 发起更新改行记录的 UPDATE 请求，更新这条记录。一条记录操作完成，再读取下一条记录，直至没有匹配的记录为止。

在 MySQL 的常用引擎中 InnoDB 支持行锁，而 MyISAM 则只能使用 MySQL Server 提供的表锁。

2.2、锁的模式

锁的模式有：

  • 读锁
  • 写锁
  • 读意向锁
  • 写意向锁
  • 自增锁(auto_inc)

下面我们依次来看。

读、写锁

读锁，又称共享锁（Share locks，简称 S 锁），加了读锁的记录，所有的事务都可以读取，但是不能修改，并且可同时有多个事务对记录加读锁。

写锁，又称排他锁（Exclusive locks，简称 X 锁），或独占锁，对记录加了排他锁之后，只有拥有该锁的事务可以读取和修改，其他事务都不可以读取和修改，并且同一时间只能有一个事务加写锁。

读写意向锁

由于表锁和行锁虽然锁定范围不同，但是会相互冲突。所以当你要加表锁时，势必要先遍历该表的所有记录，判断是否加有排他锁。这种遍历检查的方式显然是一种低效的方式，MySQL 引入了意向锁，来检测表锁和行锁的冲突。

意向锁也是表级锁，也可分为读意向锁（IS 锁）和写意向锁（IX 锁）。当事务要在记录上加上读锁或写锁时，要首先在表上加上意向锁。这样判断表中是否有记录加锁就很简单了，只要看下表上是否有意向锁就行了。

意向锁之间是不会产生冲突的，也不和 AUTO_INC 表锁冲突，它只会阻塞表级读锁或表级写锁，另外，意向锁也不会和行锁冲突，行锁只会和行锁冲突。

自增锁

AUTOINC 锁又叫自增锁（一般简写成 AI 锁），是一种表锁，当表中有自增列（AUTOINCREMENT）时出现。当插入表中有自增列时，数据库需要自动生成自增值，它会先为该表加 AUTOINC 表锁，阻塞其他事务的插入操作，这样保证生成的自增值肯定是唯一的。AUTOINC 锁具有如下特点：

注意：

AUTO_INC 锁互不兼容，也就是说同一张表同时只允许有一个自增锁；
自增值一旦分配了就会 +1，如果事务回滚，自增值也不会减回去，所以自增值可能会出现中断的情况。

显然，AUTOINC 表锁会导致并发插入的效率降低，为了提高插入的并发性，MySQL 从 5.1.22 版本开始，引入了一种可选的轻量级锁（mutex）机制来代替 AUTOINC 锁，可以通过参数 innodbautoinclockmode 来灵活控制分配自增值时的并发策略。具体可以参考 MySQL 的 AUTOINCREMENT Handling in InnoDB 一文，链接在文末。

下图是这几种锁的组合下相互互斥、兼容的情况

2.3、行锁的类型

根据锁的粒度可以把锁细分为表锁和行锁，行锁根据场景的不同又可以进一步细分，依次为

  • Next-Key Lock
  • Gap Lock 间隙锁
  • Record Lock 记录锁
  • 插入意向 GAP 锁。

不同的锁锁定的位置是不同的，比如说记录锁只锁住对应的记录，而间隙锁锁住记录和记录之间的间隔，Next-Key Lock 则所属记录和记录之前的间隙。不同类型锁的锁定范围大致如下图所示。

下面我们来依次了解一下不同的类型的锁。

记录锁

记录锁是最简单的行锁，并没有什么好说的。上边描述 InnoDB 加锁原理中的锁就是记录锁，只锁住 id = 49 或者 name = 'Tom' 这一条记录。

当 SQL 语句无法使用索引时，会进行全表扫描，这个时候 MySQL 会给整张表的所有数据行加记录锁，再由 MySQL Server 层进行过滤。但是，在 MySQL Server 层进行过滤的时候，如果发现不满足 WHERE 条件，会释放对应记录的锁。这样做，保证了最后只会持有满足条件记录上的锁，但是每条记录的加锁操作还是不能省略的。

所以更新操作必须要根据索引进行操作，没有索引时，不仅会消耗大量的锁资源，增加数据库的开销，还会极大的降低了数据库的并发性能。

间隙锁

还是最开始更新用户年龄的例子，如果 id = 49 这条记录不存在，这个 SQL 语句还会加锁吗？答案是可能有，这取决于数据库的隔离级别。这种情况下，在 RC 隔离级别不会加任何锁，在 RR 隔离级别会在 id = 49 前后两个索引之间加上间隙锁。

间隙锁是一种加在两个索引之间的锁，或者加在第一个索引之前，或最后一个索引之后的间隙。这个间隙可以跨一个索引记录，多个索引记录，甚至是空的。使用间隙锁可以防止其他事务在这个范围内插入或修改记录，保证两次读取这个范围内的记录不会变，从而不会出现幻读现象。

值得注意的是，间隙锁和间隙锁之间是互不冲突的，间隙锁唯一的作用就是为了防止其他事务的插入，所以加间隙 S 锁和加间隙 X 锁没有任何区别。

Next-Key 锁

Next-key锁是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。假设一个索引包含 15、18、20 ，30，49，50 这几个值，可能的 Next-key 锁如下：

(-∞, 15]，(15, 18]，(18, 20]，(20, 30]，(30, 49]，(49, 50]，(50, +∞)

通常我们都用这种左开右闭区间来表示 Next-key 锁，其中，圆括号表示不包含该记录，方括号表示包含该记录。前面四个都是 Next-key 锁，最后一个为间隙锁。和间隙锁一样，在 RC 隔离级别下没有 Next-key 锁，只有 RR 隔离级别才有。还是之前的例子，如果 id 不是主键，而是二级索引，且不是唯一索引，那么这个 SQL 在 RR 隔离级别下就会加如下的 Next-key 锁 (30, 49](49, 50)

此时如果插入一条 id = 31 的记录将会阻塞住。之所以要把 id = 49 前后的间隙都锁住，仍然是为了解决幻读问题，因为 id 是非唯一索引，所以 id = 49 可能会有多条记录，为了防止再插入一条 id = 49 的记录。

插入意向锁

插入意向锁是一种特殊的间隙锁（简写成 II GAP）表示插入的意向，只有在 INSERT 的时候才会有这个锁。注意，这个锁虽然也叫意向锁，但是和上面介绍的表级意向锁是两个完全不同的概念，不要搞混了。

插入意向锁和插入意向锁之间互不冲突，所以可以在同一个间隙中有多个事务同时插入不同索引的记录。譬如在上面的例子中，id = 30 和 id = 49 之间如果有两个事务要同时分别插入 id = 32 和 id = 33 是没问题的，虽然两个事务都会在 id = 30 和 id = 50 之间加上插入意向锁，但是不会冲突。

插入意向锁只会和间隙锁或 Next-key 锁冲突，正如上面所说，间隙锁唯一的作用就是防止其他事务插入记录造成幻读，正是由于在执行 INSERT 语句时需要加插入意向锁，而插入意向锁和间隙锁冲突，从而阻止了插入操作的执行。

三、常见加锁场景分析

3.1、隔离等级对加锁的影响

  • 读未提交（Read Uncommitted 后续简称 RU）：可以读到未提交的读，基本上不会使用该隔离等级，所以暂时忽略。
  • 读已提交（Read Committed 后续简称 RC）：存在幻读问题，对当前读获取的数据加记录锁。
  • 可重复读（Repeatable Read 后续简称 RR）：不存在幻读问题，对当前读获取的数据加记录锁，同时对涉及的范围加间隙锁，防止新的数据插入，导致幻读。
  • 序列化（Serializable）：从 MVCC 并发控制退化到基于锁的并发控制，不存在快照读，都是当前读，并发效率急剧下降，不建议使用。

这里说明一下，RC 总是读取记录的最新版本，而 RR 是读取该记录事务开始时的那个版本，虽然这两种读取的版本不同，但是都是快照数据，并不会被写操作阻塞，所以这种读操作称为 快照读（Snapshot Read）

MySQL 还提供了另一种读取方式叫当前读（Current Read），它读的不再是数据的快照版本，而是数据的最新版本，并会对数据加锁，根据语句和加锁的不同，又分成三种情况：

  • SELECT ... LOCK IN SHARE MODE：加共享(S)锁
  • SELECT ... FOR UPDATE：加排他(X)锁
  • INSERT / UPDATE / DELETE：加排他(X)锁

当前读在 RR 和 RC 两种隔离级别下的实现也是不一样的：RC 只加记录锁，RR 除了加记录锁，还会加间隙锁，用于解决幻读问题。

3.2、不同 SQL 语句对加锁的影响

不同的 SQL 语句当然会加不同的锁，总结起来主要分为五种情况：

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

其中，当前读的 SQL 语句的 where 从句的不同也会影响加锁，包括是否使用索引，索引是否是唯一索引等等。

3.3、当前数据对加锁的影响

SQL 语句执行时数据库中的数据也会对加锁产生影响。

比如一条最简单的根据主键进行更新的 SQL 语句，如果主键存在，则只需要对其加记录锁，如果不存在，则需要在加间隙锁。

至于其他非唯一性索引更新或者插入时的加锁也都不同程度的受到现存数据的影响，后续我们会一一说明。

3.4、具体场景分析

https://zhuanlan.zhihu.com/p/149228460

四、分析

  1. 组合一：id主键+RC

结论：id是主键时，此SQL只需要在id=10这条记录上加X锁即可。

  1. 组合二：id唯一索引+RC

结论：若id列是unique列，其上有unique索引。那么SQL需要加两个X锁，一个对应于id unique索引上的id = 10的记录，另一把锁对应于聚簇索引上的[name='d',id=10]的记录、

  1. 组合三：id非唯一索引+RC

结论：若id列上有非唯一索引，那么对应的所有满足SQL查询条件的记录，都会被加锁。同时，这些记录在主键索引上的记录，也会被加锁。

  1. 组合四：id无索引+RC

结论：若id列上没有索引，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL Server层面进行的。因此每条记录，无论是否满足条件，都会被加上X锁。但是，为了效率考量，MySQL做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。同时，优化也违背了2PL的约束。

  1. 组合五：id主键+RR

与 组合一：id主键+RC 一致

  1. 组合六：id唯一索引+RR

与 组合二：id唯一索引+RC 一致。

  1. 组合七：id非唯一索引+RR

结论：Repeatable Read隔离级别下，id列上有一个非唯一索引，对应SQL：delete from t1 where id = 10; 首先，通过id索引定位到第一条满足查询条件的记录，加记录上的X锁，加GAP上的GAP锁，然后加主键聚簇索引上的记录X锁，然后返回；然后读取下一条，重复进行。直至进行到第一条不满足条件的记录[11,f]，此时，不需要加记录X锁，但是仍旧需要加GAP锁，最后返回结束。 什么时候会取得gap lock或nextkey lock 这和隔离级别有关,只在REPEATABLE READ或以上的隔离级别下的特定操作才会取得gap lock或nextkey lock。

  1. 组合八：id无索引+RR

结论：在Repeatable Read隔离级别下，如果进行全表扫描的当前读，那么会锁上表中的所有记录，同时会锁上聚簇索引内的所有GAP，杜绝所有的并发 更新/删除/插入 操作。当然，也可以通过触发semi-consistent read，来缓解加锁开销与并发影响，但是semi-consistent read本身也会带来其他问题，不建议使用。

  1. 组合九：Serializable

结论：在MySQL/InnoDB中，所谓的读不加锁，并不适用于所有的情况，而是隔离级别相关的。Serializable隔离级别，读不加锁就不再成立，所有的读操作，都是当前读。

https://zhuanlan.zhihu.com/p/75673270

五、死锁案例

  1. 不同表相同记录行锁冲突

这种情况很好理解，事务A和事务B操作两张表，但出现循环等待锁情况。

  1. 相同表记录行锁冲突

这种情况比较常见，之前遇到两个job在执行数据批量更新时，jobA处理的的id列表为[1,2,3,4]，而job处理的id列表为[8,9,10,4,2]，这样就造成了死锁。

  1. 不同索引锁冲突

这种情况比较隐晦，事务A在执行时，除了在二级索引加锁外，还会在聚簇索引上加锁，在聚簇索引上加锁的顺序是[1,4,2,3,5]，而事务B执行时，只在聚簇索引上加锁，加锁顺序是[1,2,3,4,5]，这样就造成了死锁的可能性。

  1. gap锁冲突

innodb在RR级别下，如下的情况也会产生死锁，比较隐晦。不清楚的同学可以自行根据上节的gap锁原理分析下。

六、如何尽可能避免死锁

  • 以固定的顺序访问表和行。比如对第2节两个job批量更新的情形，简单方法是对id列表先排序，后执行，这样就避免了交叉等待锁的情形；又比如对于3.1节的情形，将两个事务的sql顺序调整为一致，也能避免死锁。

*大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。

*在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。

*降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。

*为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大。

七、如何查看锁

从InnoDB1.0开始，在INFORMATION_SCHEMA架构下添加了表INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS。（详情见附录）通过这三张表，用户可以更简单地监控当前事务并分析可能存在的锁问题。

八、mysql是如何预防死锁的？

  1. innodb_lock_wait_timeout 等待锁超时回滚事务

直观方法是在两个事务相互等待时，当一个等待时间超过设置的某一阀值时，对其中一个事务进行回滚，另一个事务就能继续执行。

  1. wait-for graph算法来主动进行死锁检测