浅谈MySQL的各种锁

红太狼 2023-03-04 08:26 3阅读 0赞

# 浅谈MySQL的各种锁 #

MySQL的锁是个老生常谈的话题。本文将对MySQL/innodb常见的锁做一个大致的分类，并结合SQL实例进行分析。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpcHNob3BfZmluX2Rldg_size_16_color_FFFFFF_t_70]

## 锁的模式 ##

### S锁（共享锁） ###

又称读锁，若记录被某事务加上了S锁，允许其它事务对其加S锁,但不允许其他事务再加X锁。  
加锁方式：select…lock in share mode

### X锁（排他锁） ###

又称写锁，若记录被某事务加上了X锁，不允许其他事务再加S锁或者X锁  
加锁方式：select…for update  
简记：读读可以并行，读写/写写互斥

### IS（意向共享锁）和IX（意向排他锁） ###

这两种锁可以理解为由S锁和X锁衍生出来的表级锁，表示某个表上有没有记录被上了S锁或X锁。如果有，那么对应的锁表(lock table)操作将会被阻塞。事务在请求记录的S锁或X锁之前，需要先获取表级的IS锁或IX锁，而事务在执行锁表（lock tables xxx write）前，如果判断有其它事务持有该表的IX锁，则必须等待。这种机制行得MySQL能很好地同时支持表锁和行锁。  
这里做进一步的说明：由于行级的写锁和表级的写锁不兼容，拿表锁时怎么判断表面里有没有行锁存在呢？如果遍历每一行去查找行锁势必效率太低？因此便有了意向共享锁和意向排他锁。这两种意向锁是表级锁，表示对应的表中有没有数据行被锁定。这样，当有事务想执行锁表操作时，只需要判断有没有其它事务持有IS和IX，某种程序上IS和IX是为锁表(lock tables)而设计的。

### Auto-Inc锁（自增锁） ###

AUTO-INC锁是一种特殊的表级锁，在事务涉及对自增（AUTO\_INCREMENT）列申请自增id时，会产生自增锁。说到自增锁，不得不谈参数：innodb\_autoinc\_lock\_mode，该参数控制着在向有auto\_increment 列的表插入数据时，自增锁的行为；  
通过对它的设置可以达到性能与安全(主从的数据一致性)的平衡。该参数有如下取值：  
0：traditonal，每次都会需要拿自增锁，且必须等待当前SQL执行完成后或者回滚掉才会释放  
1：consecutive， mysql的默认模式，会产生一个轻量锁，simple insert（insert into …values…）会获得批量的锁，保证连续插入，不需要等待语句执行完成便可释放锁。对于bulk insert（如insert into … select …from …）还是需要等待语句执行完才释放锁。  
2：interleaved，所有insert种类的SQL都可以立马获得锁并释放，该模式的效率最高。但当binlog\_format为statement时，极可能引起主备的数据不一致。

各种锁模式的兼容矩阵  
![在这里插入图片描述][20200802214450986.png]

## 全局锁 ##

全局锁就是对整个数据库实例（注意并不是单单对schema）加锁。MySQL 提供加全局读锁的命令：Flush tables with read lock (FTWRL)。加了全局锁之后其他事务的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。  
FTWRL会无视正在执行中的DML，写事务在提交之前，允许其它线程执行FTWRL，且写事务的提交语句会因此被阻塞。  
FTWRL会被lock table 语句（不管是read还是write）阻塞，反过来，lock table for read不被FTWRL而阻塞，lock table for write会被FTWRL阻塞  
全局锁主要用于数据库备份，以保证数据库在整体上的逻辑一致性。使备份得到的库是同一个逻辑时间点

## 表级锁 ##

### 表锁 ###

顾名思义，就是给表加读锁或写锁。采用lock tables xxx read/write进行加锁。有个点要注意：lock tables除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。即执行完lock tables test write后会阻塞其它事务读test表，但是也限制了当前线程只能写test表，不能写其它表。除非使用unlock table释放表锁，或使用lock tables申请其它表的表锁。

### MDL（元数据锁） ###

MDL全称为metadata lock，即元数据锁。MDL锁主要解决DDL与DML同时执行的冲突问题，保证DDL操作与DML操作之间的一致性。元数据锁是server层的锁，表级锁，在MySQL 5.5 版本引入，每执行一条DML、DDL语句时都会申请MDL锁，DML操作需要MDL读锁，DDL操作需要MDL写锁MDL加锁过程是系统自动控制，无法直接干预，MDL读写锁的兼容模式也与其它读写锁一样：读读兼容，读写/写写互斥，申请MDL锁的操作会形成一个队列，这里有个坑，一旦DDL因为申请MDL写锁而阻塞，会同时阻塞后续其它线程该表的所有操作(包括不加锁的select)。事务一旦申请到MDL锁后，直到事务执行完才会将锁释放。另外MySQL5.6引入了on-line DDL，DDL的执行只需要在开始和结束阶段申请MDL写锁，其它阶段都降级为MDL读锁，使得DDL和DML可以同时进行。

## 行级锁 ##

### Record Lock(记录锁) ###

记录锁, 仅仅锁住数据表中的一行，即在单条索引记录上加锁。这是面积最小的锁。当update语句走主键索引或唯一索引，并且能命中记录时，加的就是记录锁。

### GAP Lock(间隙锁) ###

在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。GAP锁会阻止其它事务该间隙写入数据(索引项)，但GAP和本身不互斥。  
MySQL引入GAP锁，主要是解决可重复读的问题，GAP锁是在被扫描到的索引项的左右两边的间隙加锁，这样可以防止新插入数据进入该区间，使两次读的结果不一致。也就是Gap锁(和next-key锁)的引入，赋予了MySQL可重复读的能力。（下面举例说明）

### Next-key Lock(临键锁) ###

由记录锁和GAP锁组成，左开右闭区间。主要产生于范围查询，对查找过程中访问到的对象加锁。对于等值条件查询，如果命中普通索引，退化为Gap锁,如果命中唯一索引，退化为记录锁。  
Next-key锁有一个问题：对于范围查询会访问到不满足条件的第一个值为止。不管是否唯一索引，均会对访问到的索引项进行加锁，对于唯一索引而言，有点bug的味道。

### Insert Intention Locks(插入意向锁) ###

1）插入意向锁和上面的意向锁不是同一类型的锁，不可混淆。插入意向锁包含些一系列行为，理解起来稍微有点复杂。  
2）插入意向锁是一种Gap锁，或者说是对GAP锁的一种优化，并使之趋近于记录锁的性能，该锁作用于对事务对目标索引间隙的插入操作。插入意向锁允许不同事务向同一索引间隙插入不同的索引记录。因此大大提升了插入的性能。  
举例：假设有一个记录索引包含键值4和7，不同的事务分别插入5和6，每个事务都会产生一个加在4-7之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。但如果两个事务要同时写入相同的数据5，将发生了唯一键冲突，由于前面的事务还没有提交，所以后面的事务还不能报违反唯一键约束，这时后面的事务将会在重复的索引记录上加读锁。如果前面的事务回滚，则后面的事务即获取到S锁；如果前面的事务提交，则后面的事务会提示违反唯一键约束。

各种行锁的兼容矩阵：  
![在这里插入图片描述][20200802214358226.png]

锁模式可以和锁类型进行桥接或组合，组成多种多样的锁形态，如Gap锁和S锁形成\{S,Gap\}，和X锁形成\{X,Gap\}等。

## 不同SQL的锁 ##

上面介绍了常见的锁，下面分析不同SQL（本质是看SQL走什么样的索引）的加锁情况。  
先创建数据表：

CREATE TABLE test (
    	id  int(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
    	c  int(11) NOT NULL default 0,
    	d  int(11) NOT NULL default 0,
    	e  int(11) NOT NULL default 0,
    	f  varchar(16) NOT NULL DEFAULT '' ,
    	PRIMARY KEY (`id`),
    	UNIQUE INDEX `uniq_c` (`c`) ,
    	INDEX `idx_d` (`d`) 
    );

初始化数据如下：  
![在这里插入图片描述][20200802205319437.png]

索引项意示图：  
![在这里插入图片描述][20200802220917976.jpg]

其中白色数据项索引的键值，橙色数据项代码索引项存储的内容，对于非主键索引，存的是主键的值，对于主键索引，存的记录本身。MySQL的锁是通过对索引项执行加锁实现的，根据innodb索引结构，对于非主键索引，索引项包括索引值和主键。而对于主键索引，索引项可以理解为只包含主键本身。注(ABCDE只是给索引项编号，方便引述，没特别意义)

### 先解释RR级别为何要引入Gap锁 ###

上面说到的Gap锁和next-key锁，只有RR及以上的隔离级别会用到，那么问题来了，RR模式下，为何要加Gap锁和next-key锁呢？  
其实很简单，就是Gap锁和next-key的引入，赋予了RR隔离级别的可重复读的能力。我们用反证法来证明：  
![在这里插入图片描述][20200802205855316.png]  
假设T1时刻索引项d=10的左右间隙没有被加锁，那么T2时间Session2的insert语句没有被阻塞，这样T3时刻session1再次执行查询会造成两次查询结果不一致，违反可重复读的原则和要求。  
注：对于非唯一索引而言，索引项d(10,10)的右边间隙除了d大于是10的索引项，还包括d=10，但主键id>10的索引项。

### 不同SQL加锁规则示例 ###

谈到加锁规则，都无法脱离事务隔离级别。其中：  
read uncommitted隔离级别极少用到，不展加讨论，SERIALIZABLE隔离级别，普通select会被升级为select… lock in share mode,其它基本等于同RR，而RC比RR少了间隙锁,临键锁，下面主要分析RR隔离级别下的锁情况。

普通select不加锁，不作讨论。  
select…for update/update/delete等语句,如果带相同的条件，走相同的索引，加锁情况可以视为相同  
select…lock in share mode在相同条件下X锁降为S锁。  
下面主要以update为例：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpcHNob3BfZmluX2Rldg_size_16_color_FFFFFF_t_70 1]  
说明：  
1)上面的普通索引:GAP Lock:((10,10),(20,20))指索引项B和C之间的间隙。  
2)范围查询会产生next-key锁，next-key锁会对查询访问到的索引项加锁，next-key锁的加锁范围会覆盖到不满足条件的第一个值为止。不管是否唯一索引，其加锁范围基本相同。这一点对于唯一索引或主键索引，MySQL并没有做专门的优化。  
再来看insert的加锁情况  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpcHNob3BfZmluX2Rldg_size_16_color_FFFFFF_t_70 2]  
注：插入意向锁对间隙(1,10)加锁，通过兼容矩阵，该间隙区间不能再加Gap锁，但插入锁向锁与插入锁向锁之前并不冲突，插入成功(不管事务是否已提交）后会对记录加X锁。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpcHNob3BfZmluX2Rldg_size_16_color_FFFFFF_t_70]: /images/20230208/ee7645d7c6ab4048b44d79e36a50507f.png
[20200802214450986.png]: /images/20230208/aacf9b347c97459597062f75ff577353.png
[20200802214358226.png]: /images/20230208/26ddcdeb3ac4409dbc15049f1b3de793.png
[20200802205319437.png]: /images/20230208/1ca83510626c4a37a5e7529613eececb.png
[20200802220917976.jpg]: /images/20230208/d9858bd289f24a90af0087156c10af34.png
[20200802205855316.png]: /images/20230208/499e48fcd5ff4a389ebe02e7eb8f5a53.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpcHNob3BfZmluX2Rldg_size_16_color_FFFFFF_t_70 1]: /images/20230208/f02c13e157b642968c755ed2e23a6b77.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpcHNob3BfZmluX2Rldg_size_16_color_FFFFFF_t_70 2]: /images/20230208/4368e753f352480fb6877a226e637266.png