MySQL数据库的全局锁、表级锁和行级锁的全面深度解析

喜欢ヅ旅行 2022-09-06 15:19 132阅读 0赞

> 详细介绍了MySQL数据库中的全局锁、表级锁和行级锁的概念、使用方式，以及相关特性。

**和程序中的锁一样，数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当数据出现并发访问的时候，数据库需要合理地控制资源的访问规则，而锁就是用来实现这些访问规则的重要数据结构。**

**根据加锁的范围，MySQL里面的锁大致可以分成全局锁、表级锁和行级锁三类。MyISAM支持全局锁和表级锁(table-level locking)。InnoDB 支持全局锁、行级锁(row-level locking)和表级锁，默认为行级锁。**

**MySQL的隔离级别，有时候就会利用锁来实现：[4种隔离级别以及MVCC一致性视图的实现原理][4_MVCC]。**

### 文章目录 ###

*  1 全局锁
 *  2 表级锁
 *   *  2.1 表锁
     *  2.2 MDL
 *  3 行级锁
 *   *  3.1 特性
     *  3.2 意向锁
     *  3.3 锁释放
     *  3.4 死锁
     *  3.5 行锁实现
     *  3.6 间隙锁和临键锁
     *  3.6 加锁规则

# 1 全局锁 #

**全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是`Flush tables with read lock` ，即FTWRL。**

**当你需要让整个库处于只读状态的时候，可以使用这个命令，之后将会禁止该数据库中的数据更新和表的结构修改操作，一般在对整个库进行逻辑备份时使用。**

**在对整个库进行逻辑备份时，如果不加全局锁，由于数据库的备份不可能一瞬间完成，那么将可能造成最终数据不一致的问题。**

比如有一个用户余额表，一个商品表，逻辑是用户先在余额表中扣款，然后在商品表中添加购买的商品。假设在此处操作的余额表扣款之前发起了数据库备份，首先备份的余额表，那么此时备份的余额表并没有在此次交易中扣款，在备份余额表之后，余额表发起了扣款，并且商品表增加了商品信息，此时备份到商品表，那么备份的商品表中增加了本次交易的商品。最终结果就是在备份的数据之中，余额表并没有扣款，但是商品表中增加了商品，这样商家就承担了损失。而如果是先添加商品，然后扣款的话，那么最终可能导致商品没有添加，但是被扣了款，那么用户就会来找你了！

也就是说，不加锁的话，备份系统备份的得到的库不是一个逻辑时间点，这个视图是逻辑不一致的。

**对于整个数据库加全局锁的确会非常的影响性能：**

1.  整个数据库变得只读，那么正常的业务逻辑肯定会受到影响，业务基本上就得停摆；
2.  如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
3.  如果你在从库上备份，那么备份期间从库不能执行主库同步过来的binlog，会导致主从延迟。

对于事务性执行引擎来说，还可以使用官方自带的逻辑备份工具是`mysqldump`，他利用了MVCC的一致性视图的原理，当`mysqldump`使用参数`–single-transaction`的时候，导数据之前就会启动一个事务，来确保拿到一致性视图，而由于`MVCC`的支持，这个过程中数据是可以正常更新的。

对于`MyISAM`这种不支持事务的引擎，如果备份过程中有更新，总是只能取到最新的数据，那么就破坏了备份的一致性。这时，我们就需要使用FTWRL命令而不能使用mysqldump了。

还有一种方法，那就是使用`set global readonly=true`的方式，让整个数据库变得只读，但这种方式并不推荐：

1.  在有些系统中，`readonly` 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此修改 global 变量的方式影响面更大，不建议适用。
2.  在异常处理机制上有差异，如执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。

记住，业务的更新不只是增删改数据（DML），还有可能是加字段等修改表结构的操作（DDL）。不论是哪种方法，一个库被全局锁上之后，你要对里面任何一个表做更改操作，都会被锁住的。

# 2 表级锁 #

**表锁是 MySQL 中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分 MySQL 引擎支持，最常使用的 MyISAM 与 InnoDB 都支持表级锁定。当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并大度大打折扣。**

**默认使用表级锁定的主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎。**

**另外，在MySQL 5.5版本中引入了元数据锁（MDL，meta data lock），也算作一种表级锁。**

## 2.1 表锁 ##

**MySQL的表锁有两种模式:表共享读锁(Table Read Lock)和表独占写锁(Table Write Lock)。表锁是ＭySQL Server负责的实现的。**

**某个请求对于某个表加了读锁之后，其他请求可以继续获取读锁，但不能获取写锁（当一个请求在读数据时，其他请求也可以读，但是不能写），获取写锁的请求会阻塞。某个请求对于某个表加了写锁之后，其他请求既不能获取写锁也不能获取读锁，都会被阻塞（当一个请求在写数据时，其他请求不能执行任何操作）。**

对于一个或者多个表主动加读/写表锁的命令是：

LOCK TABLES tablename1 READ/WRITE,tablename2 READ/WRITE,……

**释放表锁：使用`UNLOCK TABLES`主动释放所有获取的表锁，也可以在客户端断开的时候自动释放。UNLOCK TABLES会隐含地提交事务，应放在COMMIT之后。**

**在用LOCK TABLES给表显式加表锁时，必须同时取得所有涉及表的锁，并且该语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象**。举个例子, 如果在某个线程A中执行lock tables t1 read, t2 write，那么在“unlock tables“主动释放锁之前：

1.  其他线程写t1、读写t2的语句都会被阻塞。
2.  当前线程只能访问显式加锁的表，不能访问未加锁的表，即该线程只能访问t1和t2表。
3.  对于某个表加的什么锁，只有对应锁的权限。比如线程A对t1加的是读锁，那么只能执行查询操作，而不能执行更新操作。

**如果是MyISAM存储引擎，那么在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁**，这个过程并不需要用户干预，因此用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。给MyISAM表显示加锁，一般是为了一定程度模拟事务操作，实现对某一时间点多个表的一致性读取，因为使用该语句之后，需要一次性获取这些表的全部表锁。

## 2.2 MDL ##

**另一类表级的锁是MDL（metadata lock)。MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。**

简单的说，**MySQL 5.5 之后加入的元数据锁（MDL）就是用来管理数据库的增删改查操作（Data Manipulation Language）和表的结构修改操作（Data Definition Language）的一致性**。

**MDL分为两种：**

1.  当对一个表做增删改查操作的时候，加MDL读锁。表示只允许多个线程并发的对表数据进行增删改查，但不允许任何线程对表结构的修改。
2.  当对表做结构变更操作的时候，加了MDL写锁。当前线程可以进行增删改查和表结构修改，不允许其他线程进行任何的操作。

**MDL的特性：**

1.  读锁之间不互斥，因此你可以有多个线程尝试同时对一张表增删改查，但如果此时有线程对该表加了表锁，那么又会走表锁的逻辑了，但是MDL读锁的获取是不会影响的。
2.  读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

**需要注意的是：**

1 如果有请求在申请MDL写锁，但此前有请求已经申请到了MDL读锁，那么当前请求会被阻塞，并且后面的即使是申请MDL读锁的请求也会被阻塞，此时将会导致这个表现在完全不可以并发的读写了。

2 事务中的MDL锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。因此，一定要注意长事务的优化。

# 3 行级锁 #

**顾名思义，行锁就是针对数据表中行记录的锁。这很好理解，比如事务A更新了一行，而这时候事务B也要更新同一行，则必须等事务A的操作完成后才能进行更新。**

**MySQL的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如MyISAM引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度。InnoDB是支持行锁的，这也是MyISAM被InnoDB替代的重要原因之一。**

**行级锁定最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。**

**虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。**

## 3.1 特性 ##

InnoDB同样实现了以下两种类型的行锁：

1.  **共享锁（S）**：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
2.  **排他锁（X）**：允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。

**对于表级锁中的共享锁和排它锁，也可以用S锁和X锁的简称来替代！对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他行锁；对于普通SELECT语句，InnoDB不会加任何锁，当然我们也可以显式的给SELECT语句加行锁：**

1.  共享行锁：`SELECT........LOCK IN SHARE MODE`，允许事务去读一行，阻止其他事务对该数据进行修改
2.  排它行锁：`SELECT........FOR UPDATE`，允许事务去读取更新数据，阻止其他事务对数据进行查询或者修改。

**另外，对于SELECT加锁之后，都是使用的“当前读”，即读取当前数据的最新版本。这里就和MVCC以及事务隔离级别那里联系起来了。**

## 3.2 意向锁 ##

**在MySQL Server中有表锁：**

1.  `LOCK TABLE my_table_name READ`： 用**读锁锁表**，会阻塞其他事务修改表数据。
2.  `LOCK TABLE my_table_name WRITE`;：用**写锁锁表**，会阻塞其他事务读和写。

**Innodb引擎又支持行锁，行锁分为：**

1.  **共享锁**，一个事务对一行的共享只读锁。
2.  **排它锁**，一个事务对一行的排他读写锁。

**而实际上，在添加行锁之前，请求还需要获取对应表的意向锁（Intention Locks），即意向锁是在添加行锁之前添加，并且意向锁是InnoDB自动获取和释放的，不需用户干预。**

**意向锁同样分为两种：**

1.  **意向共享锁（IS）**：事务打算给当前表的数据行加共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
2.  **意向排他锁（IX）**：事务打算给当前表的数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

**那么意向锁有什么用呢，IX和IS锁定的主要目的是表明有人正在锁定行，或者将要锁定表中的行，这样可以更好、更高效的检测行级锁和表级锁的存在，避免冲突。**

**假设事务A锁住了表中的一行，让这一行只能读，不能写。之后，事务B尝试申请整个表的写锁。如果事务B申请成功，那么理论上它就能修改表中的任意一行，这与A持有的行锁是冲突的。数据库需要避免这种冲突，就是说要让B的申请被阻塞，直到A释放了行锁。此时：**

1.  如果没有表意向锁的话，则需要遍历所有整个表判断是否有行锁的存在，以免发生冲突。
2.  如果有了表意向锁，只需要判断该意向锁与即将添加的表级锁是否兼容即可。因为意向锁的存在代表了，有行级锁的存在或者即将有行级锁的存在。因而无需遍历整个表，即可获取结果。

**实际上IX，IS就是表级锁，不会和行级的X，S锁发生任何冲突，IX和IS之间也不会发生冲突，IX和IS只会和表级的X，S锁发生冲突，行级别的X和S按照普通的共享、排他规则即可。意向锁IX、IS和表级锁X、S的兼容性如下：**

![在这里插入图片描述][8c7a11406b6b4db5bb032c09465d0571.png_pic_center]

可以看到：

1.  获取了S之后，可以获取S、IX、IS，不能获取X。
2.  获取了X之后，不能获取X、S、IX、IS。
3.  获取了IS之后，可以获取IS、IX、S，不能获取X。
4.  获取了IX之后，可以获取IS、IX，不能获取S、X。

## 3.3 锁释放 ##

**在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放（COMMIT或者ROLLBACK），这就是两阶段锁协议。**

**知道了这个设定之后，如果我们的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。**

假设你负责实现一个电影票在线交易业务，顾客A要在影院B购买电影票。我们简化一点，这个业务需要涉及到以下操作：

1.  从顾客A账户余额中扣除电影票价；
2.  给影院B的账户余额增加这张电影票价；
3.  记录一条交易日志。

也就是说，要完成这个交易，我们需要update两条记录，并insert一条记录。当然，为了保证交易的原子性，我们要把这三个操作放在一个事务中。那么，你会怎样安排这三个语句在事务中的顺序呢？

如果同时有另外一个顾客C要在影院B买票，那么这两个事务冲突的部分就是语句2了。因为它们要更新同一个影院账户的余额，需要修改同一行数据。

根据两阶段锁协议，不论你怎样安排语句顺序，所有的操作需要的行锁都是在事务提交的时候才释放的。所以，如果你把语句2安排在最后，比如按照3、1、2这样的顺序，那么影院账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待，提升了并发度。

**但这并没有完全解决问题，因为又涉及到了数据库死锁的困扰！**

## 3.4 死锁 ##

**死锁这个东西，作为Java程序员应该都是听过的很多次的了。当并发系统中不同线程之间出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。**

**Java程序会出现死锁，那么MySQL数据库中的锁同样会出现死锁。MyISAM中是不会产生死锁的，因为 MyISAM 总是一次性获得所需的全部锁，要么全部满足，要么全部等待，也称为deadlock free。而在 InnoDB 中，事务中的行锁是在执行sql的时候逐步获得的，就造成了死锁的可能。**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzc2NzAxNQ_size_16_color_FFFFFF_t_70_pic_center]  
**如图中的时候，事务A在等待事务B释放id=2的行锁，而事务B在等待事务A释放id=1的行锁。事务A和事务B在互相等待对方的资源释放，而又持有对方所需要的资源，这就进入了死锁状态。当出现死锁以后，有两种策略：**

1.  一种策略是，直接进入等待，直到**超时**。这个超时时间可以通过参数`innodb_lock_wait_timeout`来设置。
2.  另一种策略是，发起**死锁检测**，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数`innodb_deadlock_detect`设置为`on`，表示开启这个逻辑。

在InnoDB中，`innodb_lock_wait_timeout`的默认值是`50s`，一旦数据库请求锁超过这个时间就会报错。

**如果采用第一个策略，当出现死锁以后，第一个被锁住的线程默认要过50s才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。**

但是这个参数并不是只用来解决死锁问题，我们并不能直接把这个时间设置成一个很小的值，比如1s。这样当出现死锁的时候，确实很快就可以解开，但**如果不是死锁，而是在并发访问比较高的情况下出现的锁等待呢？所以，超时时间设置太短的话，会出现很多误伤。**

正常情况下我们还是要采用第二种策略，即：**主动死锁检测**，而且`innodb_deadlock_detect`的默认值本身就是`on`。死锁检测是一个**MySQL Server层**的自动检测机制，可以及时发现两个或者多个session间互斥资源的申请造成的死锁，且会自动回滚一个（或多个）事物代价相对较小的session，让执行代价最大的先执行。

该参数默认就是打开的，按理说也是必须要打开的，甚至在其他数据库中没有可以使其关闭的选项。因为主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有**额外负担**的。

**因为每当一个事务需要加锁并且他所依赖的资源被锁住了的时候，那么它就要看看和锁住该资源的事务等待的资源之间是否形成了循环等待，也就是死锁。每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是O(n)的操作。假设有1000个并发线程要同时更新同一行，那么死锁检测操作就是100万这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的CPU资源。因此，你就会看到CPU利用率很高，但是每秒却执行不了几个事务。**

怎么解决由这种热点行更新导致的性能问题呢？问题的症结在于，死锁检测要耗费大量的CPU资源。

1.  一种是临时关闭死锁检测关掉。但是这种操作本身带有一定的风险，因为业务设计的时候一般不会把死锁当做一个严重错误，毕竟出现死锁了，就回滚，然后通过业务重试一般就没问题了，这是业务无损的。而关掉死锁检测意味着可能会出现大量的超时，这是业务有损的。
2.  另一个思路是控制并发度。比如在业务层控制并发，或者借助中间件队列机制来实现，但这增加了代码复杂度，或者通过将一行改成逻辑上的多行来减少锁冲突。比如影院的账户分散为10行数据，影院的账户总额等于这10个记录的值的总和。这样每次更新随即支行数据即可，但这会增加业务复杂度。

**死锁检测和锁超时可同时存在，如果在锁申请超时的时间内无法检测到死锁，那么自然会因为锁申请超时而抛出异常。**

**与锁申请超时还有一个非常关键的参数是：`innodb_rollback_on_timeout`，该参数的决定了当前请求锁超时之后，回滚的是整个事物，还是仅当前语句，默认值是off，也就是回滚当前语句（放弃当前语句的锁申请），而不是整个事物，当前的事物还在继续，连接也还在，这里与死锁自动监测机制打开之后会主动牺牲一个事物不同，锁超时后并不会主动牺牲其中任何一个事物，之前获取的锁也不会释放，直到事务提交或回滚或会话超时。建议打开这个选项（on），也就是一旦锁申请超时，就回滚整个事物。**

如果出现死锁，可以用`SHOW INNODB STATUS`命令来确定最后一个死锁产生的原因和改进措施。

## 3.5 行锁实现 ##

**Mysql的行锁是通过通过给索引上的索引项加锁来实现的，即是行锁是加在索引相应的行上的，要是对应的SQL语句没有走索引，则会全表扫描，行锁则无法实现，取而代之的是表锁。**

**InnoDB这种行锁实现的特点意味着：只有通过索引条件检索数据，并且执行时真正使用到了索引，InnoDB才使用行级锁，否则，InnoDB 将使用表锁。**

**不论是使用主键索引、唯一索引还是普通索引，当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行。**

## 3.6 间隙锁和临键锁 ##

**幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行，幻读仅专指“新插入的行”，如果是修改而造成的查询不一致则不算幻读。**

**行锁（Record Lock）只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB只好引入新的锁，也就是间隙锁(`Gap Lock`)。顾名思义，对于键值在条件范围内但并不存在的记录，叫做`间隙（GAP)`，间隙锁，锁的就是两个值之间的间隙。**

**注意，间隙锁在可重复读的隔离级别下才有效。可重复读隔离级别遵守两阶段锁协议，所有加锁的资源，都是在事务提交或者回滚的时候才释放的。**

**跟行锁有冲突关系的是“另外一个行锁”，但是间隙锁不一样，跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。**

**使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁**，比如：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzc2NzAxNQ_size_16_color_FFFFFF_t_70_pic_center 1]  
假如表里没有c=7的记录，那么这两个sql语句加的就是间隙锁，如果比7小的最大值是5，比7大的最小值是10，那么这个间隙锁的范围就是(5,10)，如果session A先加间隙锁，那么session B的操作也不会被阻塞，因为**间隙锁的获取之间都不存在冲突关系，跟间隙锁存在冲突关系的是“往这个间隙中插入一个记录”这个操作。**

**默认间隙锁都是开区间，而间隙锁和行锁合称`next-key lock`，即临键锁。每个`next-key lock`是前开后闭区间。**

**如果表结构如下：**

CREATE TABLE `t` (
      `id` int(11) NOT NULL,
      `c` int(11) DEFAULT NULL,
      `d` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `c` (`c`)
    ) ENGINE=InnoDB;
    
    insert into t values(0,0,0),(5,5,5),
    (10,10,10),(15,15,15),(20,20,20),(25,25,25);

那么建表之后，如果用select \* from t for update要把整个表所有记录锁起来，就形成了**7**个next-key lock，分别是 `(-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +suprenum]`。为什么最后不是`+∞`呢，因为`+∞`是开区间。**实现上，InnoDB给每个索引加了一个不存在的最大值suprenum，这样才符合我们前面说的“都是前开后闭区间”。**

**间隙锁和next-key lock的引入，帮我们解决了幻读的问题，但同时也带来了一些“困扰”，比如死锁。**

如下语句：

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

1.  session A 执行select … for update语句，由于id=9这一行并不存在，因此会加上**间隙锁(5,10)**。
2.  session B 执行select … for update语句，同样会加上**间隙锁(5,10)**，间隙锁之间的加锁不会冲突，因此这个语句可以执行成功。
3.  session B 试图插入一行(9,9,9)，被session A的间隙锁挡住了，只好进入等待。
4.  session A试图插入一行(9,9,9)，被session B的间隙锁挡住了，只好进入等待。

**至此，两个session进入互相等待状态，形成死锁。当然，InnoDB的死锁检测马上就发现了这对死锁关系，让session A的insert语句报错返回了。**

**间隙锁的引入，还可能会导致同样的语句锁住更大的范围，在锁定的时候无法插入锁定键值范围内的任何数据，这其实是影响了并发度的。比如上面的语句中，假设session A试图插入一行(7,7,7)，由于间隙锁的存在，虽然(9,9,9)和(7,7,7)不冲突，但仍然会造成死锁，并且最终只有一个会插入成功。**

## 3.6 加锁规则 ##

**下面的规则都是在RR级别隔离级别下的的规则，RC级别下的间隙锁无效，判断的时候可以依次根据规则判断！**

1.  加锁的基本单位是next-key lock，即临键锁。next-key lock是前开后闭区间，即前间隙锁+行锁。如果该值不存在，则是下一个值的行锁+前间隙锁。
2.  查找过程中访问（遍历）到的对象都会加锁。
3.  索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。但如果这一行不存在，那就是间隙锁。
4.  索引上的等值查询，从第一个满足条件的记录（可以不存在）向右遍历到最后一个值不满足等值条件的时候，该next-key lock退化为间隙锁。
5.  唯一索引上的范围查询会访问到不满足条件的第一个值为止，加锁范围也会蔓延到该值的范围。主键索引也是唯一索引的一种。范围的端点被认为是等值查询。

如果查询走的二级索引，那么主键索引同样会被加上满足条件的行锁。如果走主键索引，那么主键同样可能会被添加间隙锁，比如主键范围查询，或者不存在的值的查询。

如果有二级索引覆盖，那么`lock in share mode`只锁覆盖索引，但如果使用`for update`，或者是`insert、update、delete`等语句，则系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。

删除数据的时候，如果使用了`limit`，那么在查找时满足条件的语句条数足够，则不会继续向后查找，因此在删除数据的时候尽量加limit。这样不仅可以控制删除数据的条数，让操作更安全，还可以减小加锁的范围。

加next-key lock的操作，实际上是**分为两步**，先是加间隙锁，加锁成功之后（间隙锁的获取之间都不存在冲突关系），然后加行锁，这时候可能会被阻塞。

Gap是一个**动态**的概念，间隙锁的范围可能随着数据的改变而改变。比如5,10,15三个数据，间隙为（10,15），那么删除10的数据之后，间隙变成了（5,15）。

加锁期间，除了insert语句不能插入范围内的数据之外，update语句想要将其他的值更改为锁定范围内的值或者将锁定的字段范围内的值改为其他值也会被阻塞，delete语句删除的数据中如果包含在锁定范围内也会被阻塞。

**行锁是逐行上锁的一个过程**，扫描一条上一条，直到所有行扫描完。如果某个条件行没有索引，那么就是全表扫描，锁住的是隐藏的rowid聚簇索引，也就是主键索引，GAP间隙也会被锁住（如果是RR级别）。实际上就相当于锁全表了，锁整张表会造成程序的执行效率会很低。

**另外，对于RC隔离级别，在锁定了全部数据之后，会将每条数据返回给MySQL Server层做判断，如果不符合查询条件要求的，则会提前释放该数据相关的行锁，不过加锁的过程是不可避免的。对于RR隔离级别，则不会提前释放任何锁，除非事务结束。来自官网：https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html**

基于“**行锁是逐行上锁的一个过程**”，如果是`in`子句，则会对`in`里面的每一个值都上锁。默认情况下，MySQL会对in内部的条件按从小到大来排序并查找，而如果使用了`order by xx desc`，则上锁的顺序是从大到小倒序的。因此，比如同时执行`select id from t where c in(5,20,10) lock in share mode;`和`select id from t where c in(5,20,10) order by c desc for update;`两条sql就可能造成**死锁**。

基于“**行锁是逐行上锁的一个过程**”，如果是\*\*>或者<范围查询\*\*，索引搜索就是 “找到第一个值，然后向左或向右遍历”，`order by desc` 就是要用最大的值来找第一个，并向左遍历；`order by`就是要用最小的值来找第一个，并向右遍历；

**参考资料：**

1.  《 MySQL 技术内幕: InnoDB 存储引擎》
2.  《高性能 MySQL》
3.  《MySQL实战45讲 | 极客时间 | 丁奇》

> 如有需要交流，或者文章有误，请直接留言。另外希望点赞、收藏、关注，我将不间断更新各种Java学习博客！

[4_MVCC]: https://blog.csdn.net/weixin_43767015/article/details/118931265
[8c7a11406b6b4db5bb032c09465d0571.png_pic_center]: /images/20220829/cd1ef49e562e425f8febfba86721b00e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzc2NzAxNQ_size_16_color_FFFFFF_t_70_pic_center]: /images/20220829/607326bc96044af184a508a95c63b018.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzc2NzAxNQ_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20220829/b54f76221e4a415992280190806a09af.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzc2NzAxNQ_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20220829/5d07b18e74e8469c88f1be1e6aa77cd5.png