MySQL-锁机制、锁分类、加锁规则、加锁流程

た入场券 2022-12-04 07:46 291阅读 0赞

## 一、前言 ##

因为平常主要用的场景都是MySQL的Innodb引擎，所以主要分析MYSQL在Innodb引擎下的锁机制，稍微提及下MYISAM引擎。其中会穿插数据库事务、数据库锁标识、数据库死锁日志分析等。

## 二、MySQL锁机制概述 ##

### 2.1 锁介绍 ###

锁是计算机协调多个进程或纯线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所在有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁来解决。

**锁的运作？**  
事务T在度某个数据对象（如表、记录等）操作之前，先向系统发出请求，对其加锁，加锁后事务T就对数据库对象有一定的控制，在事务T释放它的锁之前，其他事务不能更新此数据对象。

### 2.2 锁分类 ###

锁定机制就是数据库为了保证数据的一致性而使各种共享资源在被并发访问访问变得有序所设计的一种规则。MySQL数据库由于其自身架构的特点，存在多种数据存储引擎，每种存储引擎所针对的应用场景特点都不太一样，为了满足各自特定应用场景的需求，每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计，所以各存储引擎的锁定机制也有较大区别。

#### 2.3 按封锁类型分类 ####

**(1) 排他锁：（又称写锁，X锁）**

一句总结：会阻塞其他事务读和写。

若事务T对数据对象A加上X锁，则只允许T读取和修改A，其他任何事务都不能再对加任何类型的锁，知道T释放A上的锁。这就保证了其他事务在T释放A上的锁之前不能再读取和修改A。

**(2) 共享锁：（又称读取，S锁）**

一句总结：会阻塞其他事务修改表数据。

若事务T对数据对象A加上S锁，则其他事务只能再对A加S锁，而不能X锁，直到T释放A上的锁。这就保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

X锁和S锁都是加载某一个数据对象上的。也就是数据的粒度。

#### 2.4 按封锁的数据粒度分类 ####

**(1) 行级锁定**

一句总结：行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

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

**缺陷：** 由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。

**(2) 表级锁定**

一句总结：表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

**详细：** 和行级锁定相反，表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。

**缺陷：** 锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并发度大打折扣。

**(2) 页级锁定**

一句总结：页级锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

\*\*详细：\*\*页级锁定是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。

\*\*缺陷：\*\*页级锁定和行级锁定一样，会发生死锁。

> 从这里我们应该引申去思考行锁更多的缺点：（因为我们执行sql主要依赖行锁来提高并发度）
> 
>  *  比表级锁、页级锁消耗更多内存
>  *  如果你在大部分数据上经常进行GROUP BY操作或者必须经常扫描整个表，比其它锁定明显慢很多。
>  *  更容易发生死锁。

> 其次，我们应该思考什么情况下用表锁、行锁，因为我们主要使用引擎默认是这两个，MyISAM是表级锁；InnoDb是行级锁，当然也支持表级锁，页锁是别的引擎支持，多数情况下我们也用不上，在这只是提及下。

--------------------

## 三、数据库事务机制 ##

为什么提及事务？方便大家去理解MYSQL的锁机制。

### 3.1 数据库事务 ###

什么叫事务？简称ACID。是恢复和并发控制的基本单位。

**A 事务的原子性(Atomicity)：**

> 指一个事务要么全部执行,要么不执行.也就是说一个事务不可能只执行了一半就停止了.比如你从取款机取钱,这个事务可以分成两个步骤:1划卡,2出钱.不可能划了卡,而钱却没出来.这两步必须同时完成.要么就不完成。

**C 事务的一致性(Consistency)：**

> 指事务的运行并不改变数据库中数据的一致性.例如,完整性约束了a+b=10,一个事务改变了a,那么b也应该随之改变。

**I 独立性(Isolation）:**

> 事务的独立性也有称作隔离性,是指两个以上的事务不会出现交错执行的状态.因为这样可能会导致数据不一致。

**D 持久性(Durability）:**

> 事务的持久性是指事务执行成功以后,该事务所对数据库所作的更改便是持久的保存在数据库之中，不会无缘无故的回滚。

### 3.2 事务问题 ###

**脏读（dirty read）：** A事务读取B事务尚未提交的更改数据，并在这个数据基础上操作。如果B事务回滚，那么A事务读到的数据根本不是合法的，称为脏读。在oracle中，由于有version控制，不会出现脏读。

**不可重复读（unrepeatable read）：** A事务读取了B事务已经提交的更改数据。比如A事务第一次读取数据，然后B事务更改该数据并提交，A事务再次读取数据，两次读取的数据不一样。

**幻读（phantom read）：** A事务读取了B事务已经提交的新增数据。注意和不可重复读的区别，这里是新增与删除，不可重复读是更改。这两种情况对策是不一样的，对于不可重复读，只需要采取行级锁防止该记录数据被更改或删除，然而对于幻读必须加表级锁，防止在这个表中新增一条数据。

### 3.3 事务隔离级别 ###

**(1) 读未提交(Read Uncommited，RU)**  
一句总结：读取数据一致性在最低级别，只能保证不读物理上损坏的数据，会脏读，会不可重复读，会幻读。

这种隔离级别下，事务间完全不隔离，会产生脏读，可以读取未提交的记录，实际情况下不会使用。

**(2) 读提交(Read commited，RC)**  
一句总结：读取数据一致性在语句级别，不会脏读，会不可重复读，会幻读。

仅能读取到已提交的记录，这种隔离级别下，会存在幻读现象，所谓幻读是指在同一个事务中，多次执行同一个查询，返回的记录不完全相同的现象。幻读产生的根本原因是，在RC隔离级别下，每条语句都会读取已提交事务的更新，若两次查询之间有其他事务提交，则会导致两次查询结果不一致。虽然如此，读提交隔离级别在生产环境中使用很广泛。

**(3) 可重复读(Repeatable Read, RR)**  
一句总结：读取数据一致性在事务级别，不会脏读，不会不可重复读，会幻读。

可重复读隔离级别解决了不可重复读的问题，但依然没有解决幻读的问题。不可重复读重点在修改，即读取过的数据，两次读的值不一样；而幻读则侧重于记录数目变化【插入和删除】。

**(4) 串行化(Serializable)**  
一句总结：读取数据一致性在最高级别，事务级别，不会脏读，不会不可重复读，不会幻读。

在串行化隔离模式下，消除了脏读，幻象，但事务并发度急剧下降，事务的隔离级别与事务的并发度成反比，隔离级别越高，事务的并发度越低。实际生产环境下，dba会在并发和满足业务需求之间作权衡，选择合适的隔离级别。

**注：MYSQL的默认隔离级别是RR；**

以上是MySQL锁机制的基础知识，已提及完毕！下面将详细介绍MySQL的锁机制。

--------------------

## 四、MySQL锁机制详解 ##

根据前面提到的基础点，我们可以知道，MySQL的锁机制是受各种因素影响的，主要因素如下：

(1) MySQL引擎；  
不同的数据库引擎，支持的锁细粒度不一样；  
(2) 数据库事务隔离级别；  
不同数据库事务隔离级别下，MySQL的加锁方式也不一样，后面会详细介绍；

下面我会详细介绍我们常用的数据库场景下(Innodb引擎、RR或者RC事务隔离级别)的MySQL锁机制，MyISAM引擎下的表锁不做探讨，因为用的少，另外也MyISAM表锁也相对行锁简单，大家自行去百度。

### 4.1 锁分类 ###

**锁按封锁类型划分：共享锁、排它锁**  
**锁按封锁数据细粒度划分：表锁、页锁(不做探究)、行锁**  
下面将详细介绍各种锁及各自的加锁场景和锁标识。

**注：MySQL的锁是加在索引上的。**

**锁分类：**

<table> 
 <thead> 
  <tr> 
   <th>锁</th> 
   <th>描述</th> 
   <th>锁细粒度</th> 
   <th>显示加锁方式</th> 
   <th>数据库层锁标识</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>意向锁(Intention Locks)</td> 
   <td>(1) 如果另一个任务试图在该表级别上应用共享或排它锁，则受到由第一个任务控制的表级别意向锁的阻塞。第二个任务在锁定该表前不必检查各个页或行锁，而只需检查表上的意向锁。<br>(2) 如果没有意向锁，当已经有人使用行锁对表中的某一行进行修改时，如果另外一个请求要对全表进行修改，那么就需要对所有的行是否被锁定进行扫描，在这种情况下，效率是非常低的。</td> 
   <td>表锁</td> 
   <td>数据库引擎隐式控制</td> 
   <td>(1) lock mode IS (意向共享锁) <br> (2) lock mode IX（意向互斥锁）</td> 
  </tr> 
  <tr> 
   <td>记录锁(Record Locks)</td> 
   <td>行锁也叫记录锁（Record Lock）是加到索引记录上的锁。即使定义了一个没有索引的表，InnoDB创建一个隐藏的聚集索引，并使用该索引进行记录锁定。</td> 
   <td>行锁</td> 
   <td>(1) lock in share mode <br>(2) for update</td> 
   <td>(1) lock_mode S locks rec but not gap(记录共享锁) <br> (2) lock_mode X locks rec but not gap（记录互斥锁）</td> 
  </tr> 
  <tr> 
   <td>间隙锁(Gap Locks)</td> 
   <td>Gap锁，中文名间隙锁，锁住的不是记录，而是范围,比如：(negative infinity, 10），(10, 11）区间，这里都是开区间</td> 
   <td>行锁</td> 
   <td>for update</td> 
   <td>lock_mode X locks gap before rec</td> 
  </tr> 
  <tr> 
   <td>临键锁(Next-Key Locks)</td> 
   <td>Next-Key Locks = Gap Locks + Record Locks 的结合, 不仅仅锁住记录，还会锁住间隙，比如： (negative infinity, 10】，(10, 11】区间，这些右边都是闭区间</td> 
   <td>行锁</td> 
   <td>for update</td> 
   <td>lock_mode X</td> 
  </tr> 
  <tr> 
   <td>插入意向锁(Insert Intention Locks)</td> 
   <td>(1) 对已有数据行的修改与删除，必须加强互斥锁X锁，那对于数据的插入，是否还需要加这么强的锁，来实施互斥呢？插入意向锁，孕育而生。<br> (2) 插入意向锁，是间隙锁(Gap Locks)的一种（所以，也是实施在索引上的），它是专门针对insert操作的。<br> (3) 插入意向锁的作用是为了提高并发插入的性能， 多个事务同时写入不同数据至同一索引范围（区间）内，并不需要等待其他事务完成，不会发生锁等待。</td> 
   <td>行锁</td> 
   <td>数据库引擎隐式控制</td> 
   <td>** insert intention waiting <br> (1) lock_mode X insert intention waiting <br> (2) lock_mode X locks gap before rec insert intention waiting <br></td> 
  </tr> 
  <tr> 
   <td>自增锁 (Auto-inc Locks)</td> 
   <td>(1)在InnoDB中，每个含有自增列的表都有一个自增长计数器。当对含有自增长计数器的表进行插入时，首先会执行select max(auto_inc_col) from t for update来得到计数器的值，然后再将这个值加1赋予自增长列。我们将这种方式称之为AUTO_INC Lock。<br> (2)AUTO_INC Lock是一种特殊的表锁，它在完成对自增长值插入的SQL语句后立即释放，所以性能会比事务完成后释放锁要高。由于是表级别的锁，所以在并发环境下其依然存在性能问题。</td> 
   <td>表锁</td> 
   <td>数据库引擎隐式控制</td> 
   <td>lock mode AUTO-INC waiting</td> 
  </tr> 
 </tbody> 
</table>

**显示锁、隐示锁**

(1) 显示锁(explicit lock)

> 显示的加锁，在show engine innoDB status 中能够看到 ，会在内存中产生对象，占用内存  
> eg: select … for update , select … lock in share mode

(2) 隐示锁(implicit lock)

> implicit lock 是在索引中对记录逻辑的加锁，但是实际上不产生锁对象，不占用内存空间

(3) 哪些语句会产生implicit lock 呢？

> eg: insert into xx values(xx)  
> eg: update xx set t=t+1 where id = 1 ; 会对辅助索引加implicit lock

(4) implicit lock 在什么情况下会转换成 explicit lock

> eg： 只有implicit lock 产生冲突的时候，会自动转换成explicit lock,这样做的好处就是降低锁的开销  
> eg: 比如：我插入了一条记录10，本身这个记录加上implicit lock，如果这时候有人再去更新这条10的记录，那么就会自动转换成explicit lock。

(5) 数据库怎么知道implicit lock的存在呢？如何实现锁的转化呢？

> 对于聚集索引上面的记录，有db\_trx\_id,如果该事务id在活跃事务列表中，那么说明还没有提交，那么implicit则存在  
> 对于非聚集索引：由于上面没有事务id，那么可以通过上面的主键id，再通过主键id上面的事务id来判断，不过算法要非常复杂，这里不做介绍

**InnoDB锁的互斥与兼容关系**

锁和锁之间的关系，要么是相容的，要么是互斥的。

**锁a和锁b相容是指**：操作同样一组数据时，如果事务t1获取了锁a,另一个事务t2还可以获取锁b；

**锁a和锁b互斥是指**：操作同样一组数据时，如果事务t1获取了锁a，另一个事务t2在t1释放锁a之前无法获取锁b。

(y表示兼容，n表示不兼容)

### 4.2 锁检查方式 ###

**显示锁的查看确认方式如下：**

(1) 查看引擎状态

> `show engine innodb status \G;`可以查看innodb引擎状态，从事务那一段可以看到explicit lock相关信息；

> 从innodb引擎获取的lock信息，太少了，只能看到有3 lock struct(s)，6 row lock(s)，不清楚那表申请的锁，申请什么类型的锁，不知道这些信息，就研究不明白锁到底是如何加的，mysql数据库提供一个参数innodb\_status\_output\_locks，可以打印更详细的lock信息。

> 启用innodb\_status\_output\_locks参数，默认是不开启，所以需要开启。

查看innodb\_status\_output\_locks参数命令

设置innodb\_status\_output\_locks参数命令

set global innodb_status_output_locks=on;

**设置完innodb\_status\_output\_locks参数后，再执行`show engine innodb status \G;`就能看到更加详细的加锁信息；**

(2) 数据库锁、事务相关表

> 数据库自带的库`information_schema`中存在以下三个表  
> INNODB\_LOCK\_WAITS  
> INNODB\_LOCKS  
> INNODB\_TRX

> ps: 上面这些表里看到的加锁信息比较简陋，不够详细，不方便推测加锁过程，但这种方式方便，可以快速查看事务和锁的映射关系；

**当显示锁产生时，可以通过以上方式去查看确认；**

### 4.3 MySQL加锁规则 ###

前面介绍了MySQL的锁分类，下面介绍下MySQL的加锁规则。

#### 4.3.1 测试环境 ####

**表和数据**

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);

**环境：MySQL（Innodb引擎、RR事务隔离级别）**

#### 4.3.2 加锁规则 ####

规则总结（这个规则只限于截止到现在的最新版本，即 5.x 系列 <=5.7.24，8.0 系列 <=8.0.13。）：

>  *  原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。
>  *  原则 2：查找过程中访问到的对象才会加锁。
>  *  优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
>  *  优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
>  *  一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

结合下面的加锁示例，总结MySQL（Innodb引擎、RR事务隔离级别）的加锁步骤为：

>  *  （1）找位置  
>     找到要加锁的位置，

### 4.4 加锁示例 ###

**(1) 等值查询间隙锁**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI4MzQ3NTA_size_16_color_FFFFFF_t_70_pic_center]

>  *  根据原则 1，加锁单位是 next-key lock，session A 加锁范围就是 (5,10\]；
>  *  同时根据优化 2，这是一个等值查询 (id=7)，而 id=10 不满足查询条件，next-key lock 退化成间隙锁，因此最终加锁的范围是 (5,10)。

(2) 非唯一索引等值锁  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI4MzQ3NTA_size_16_color_FFFFFF_t_70_pic_center 1]

>  *  根据原则1，优化2，锁的范围是(0,5\],(5,10)，但是根据原则2，只有访问到的对象才加锁，这个查询使用了覆盖索引，并不访问主键索引，所以主键上没加锁。
>  *  需要注意，在这个例子中，lock in share mode 只锁覆盖索引，但是如果是 for update 就不一样了。 执行 for update 时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。

(3) 主键索引范围锁  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI4MzQ3NTA_size_16_color_FFFFFF_t_70_pic_center 2]

>  *  开始执行的时候，要找到第一个 id=10 的行，因此本该是 next-key lock(5,10\]。 根据优化 1， 主键 id 上的等值条件，退化成行锁，只加了 id=10 这一行的行锁。
>  *  范围查找就往后继续找，找到 id=15 这一行停下来，因此需要加 next-key lock(10,15\]。  
>     首次 session A 定位查找 id=10 的行的时候，是当做等值查询来判断的，而向右扫描到 id=15 的时候，用的是范围查询判断

(4) 非唯一索引范围锁

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

>  *  这次 session A 用字段 c 来判断，加锁规则跟案例三唯一的不同是：在第一次用 c=10 定位记录的时候，索引 c 上加了 (5,10\] 这个 next-key lock 后，由于索引 c 是非唯一索引，没有优化规则，也就是说不会蜕变为行锁，因此最终 sesion A 加的锁是，索引 c 上的 (5,10\] 和 (10,15\] 这两个 next-key lock。

(5) 唯一索引范围锁 bug

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

>  *  session A 是一个范围查询，按照原则 1 的话，应该是索引 id 上只加 (10,15\] 这个 next-key lock，并且因为 id 是唯一键，所以循环判断到 id=15 这一行就应该停止了。
>  *  但是实现上，InnoDB 会往前扫描到第一个不满足条件的行为止，也就是 id=20。而且由于这是个范围扫描，因此索引 id 上的 (15,20\] 这个 next-key lock 也会被锁上。改成select \* from t where id>10 and id<15 for update;则只会加new-key lock(10,15\]

> PS：上述主键索引id换成非唯一索引c也是同样的效果，SQL语句中的id替换成c；所以不管是唯一索引还是非唯一索引，范围查询时都会扫描到第一个不满足条件的值为止。

**(6) 非唯一索引上存在"等值"的例子**

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

>  *  这时，session A 在遍历的时候，先访问第一个 c=10 的记录。同样地，根据原则 1，这里加的是 (c=5,id=5) 到 (c=10,id=10) 这个 next-key lock。
>  *  然后，session A 向右查找，直到碰到 (c=15,id=15) 这一行，循环才结束。根据优化 2，这是一个等值查询，向右查找到了不满足条件的行，所以会退化成 (c=10,id=10) 到 (c=15,id=15) 的间隙锁。

**(7) limit 语句加锁**

表中插入一条`insert into t values(30,10,30);`  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI4MzQ3NTA_size_16_color_FFFFFF_t_70_pic_center 6]

> 索引 c 上的加锁范围变成了从（c=5,id=5) 到（c=10,id=30) 这个前开后闭区间，如下图所示：

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

**(8) 死锁示例**

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

>  *  session A 启动事务后执行insert语句，先获取(id=8)插入意向锁Insert Intention Locks\[8\]，然后再获取到id=8的行锁;  
>     ps: session A此时显示持有id=8的行锁；
>  *  session B 的insert语句也要先获取id=8的插入意向锁，发现锁冲突，于是尝试往id=8上加共享锁，根据原则1，加锁范围是(5,8\]。  
>     ps：session B并未持有任何锁，只是要加(5,8\]范围的共享锁；
>  *  然后 session A 要再插入 (6,6,6) 这一行，先尝试往id=6上加插入意向锁，发现session B 正在等待(5,8\]范围的共享锁，于是等待id=6的插入意向锁Insert Intention Locks\[6\]；
>  *  结果就是session B等待session A的id=8的行数释放后加S(5,8\]，session A等待session B的S(5,8\]取消（或者加锁成功后释放）后再获取id=6的插入意向锁Insert Intention Locks\[6\]，最终形成死锁，innodb感知到死锁后，主动回滚了代价最小的事务；

>  *  对已有数据行的修改与删除，必须加强互斥锁X锁，那对于数据的插入，是否还需要加这么>强的锁，来实施互斥呢？插入意向锁，孕育而生。
>  *  插入意向锁，是间隙锁(Gap Locks)的一种（所以，也是实施在索引上的），它是专门针对insert操作的。
>  *  插入意向锁是数据库引擎行为；只有在RR级别下才有，因为是间隙锁的一种；

**对应的死锁日志如下：**

------------------------
    LATEST DETECTED DEADLOCK
    ------------------------
    2020-09-07 19:50:39 0x700010290000
    *** (1) TRANSACTION:
    TRANSACTION 756372, ACTIVE 8 sec inserting
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
    MySQL thread id 40, OS thread handle 123145572597760, query id 700 localhost 127.0.0.1 root update
    insert into t values(8,8,8)
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 468 page no 3 n bits 80 index PRIMARY of table `rate`.`t` trx id 756372 lock mode S waiting
    Record lock, heap no 8 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
     0: len 4; hex 80000008; asc     ;;
     1: len 6; hex 0000000b8a8f; asc       ;;
     2: len 7; hex e80000016b0110; asc     k  ;;
     3: len 4; hex 80000008; asc     ;;
     4: len 4; hex 80000008; asc     ;;
    
    *** (2) TRANSACTION:
    TRANSACTION 756367, ACTIVE 10 sec inserting
    mysql tables in use 1, locked 1
    3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
    MySQL thread id 39, OS thread handle 123145573433344, query id 701 localhost 127.0.0.1 root update
    insert into t values(6,6,6)
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 468 page no 3 n bits 80 index PRIMARY of table `rate`.`t` trx id 756367 lock_mode X locks rec but not gap
    Record lock, heap no 8 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
     0: len 4; hex 80000008; asc     ;;
     1: len 6; hex 0000000b8a8f; asc       ;;
     2: len 7; hex e80000016b0110; asc     k  ;;
     3: len 4; hex 80000008; asc     ;;
     4: len 4; hex 80000008; asc     ;;
    
    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 468 page no 3 n bits 80 index PRIMARY of table `rate`.`t` trx id 756367 lock_mode X locks gap before rec insert intention waiting
    Record lock, heap no 8 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
     0: len 4; hex 80000008; asc     ;;
     1: len 6; hex 0000000b8a8f; asc       ;;
     2: len 7; hex e80000016b0110; asc     k  ;;
     3: len 4; hex 80000008; asc     ;;
     4: len 4; hex 80000008; asc     ;;

思考：  
(1) insert数据时，为啥唯一索引冲突时，获取冲突行的行锁行为变成加区间共享锁？

### 4.5 加锁流程 ###

根据上面的加锁示例以及加锁规则，总结了下MySQL(innodb引擎、RR事务隔离级别)的加锁流程。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI4MzQ3NTA_size_16_color_FFFFFF_t_70_pic_center 9]

> **上面是直接访问的索引上加锁的加锁流程，如果加的锁是排它锁类型，那么还会在被加锁的索引记录上对应的聚集索引记录上加锁。** 参考加锁示例(2)

## 五、锁优化 ##

InnoDB行锁优化建议：

> 1.尽可能让所有数据检索都通过索引来完成，避免升级为表级锁定;

> 2.合理设计索引，可以缩小行锁的锁定范围，避免造成不必要的锁定影响其他Query执行

> 3.尽可能减少基于范围的数据检索过滤条件，避免间隙锁锁定不该锁定的记录

> 4.控制事务大小，减少锁定的资源量和锁定时间长度

> 5.使用较低级别的事务隔离

减少死锁建议：

> 1.尽可能按照相同顺序的来访问资源

> 2.在同一个事务中尽可能做到一次性锁定所有资源，减少死锁的概率

> 3.对于非常容易产生死锁的业务部分尝试升级锁的粒度，通过表级锁定来减少死锁产生的概率

系统锁定争用情况查询：

> 表级锁定：SHOW STATUS LIKE ‘table%’；Table\_locks\_immediate(表级锁定的次数)，Table\_locks\_waited(表级锁定争用次数)

> 行级锁定：SHOW STATUS LIKE ‘innodb\_row\_lock%’;Innodb\_row\_lock\_current\_waits(当前正在锁定的数量)，Innodb\_row\_lock\_time(从系统启动到现在锁定总时间长度)，Innodb\_row\_lock\_time\_avg/max(平均时间/最长的一次时间),Innodb\_row\_lock\_waits(等待次数)

参考文章：  
https://www.jianshu.com/p/7eb757b0b022  
https://segmentfault.com/a/1190000018730103?utm\_source=tag-newest

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI4MzQ3NTA_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/15bb5883f6ab427db8eae927f751dc11.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI4MzQ3NTA_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221123/6c08b35b1c8d4e8ab9c585f2fdf0f850.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI4MzQ3NTA_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221123/77697204566a48e9a3dd91a0be0776ef.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI4MzQ3NTA_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20221123/e22b0a3fa38a4c80a607f110777fdb87.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI4MzQ3NTA_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20221123/f909384f83c944489cfee12151b31358.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI4MzQ3NTA_size_16_color_FFFFFF_t_70_pic_center 5]: /images/20221123/6bb9730359264eed8635260a9e5c5cb1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI4MzQ3NTA_size_16_color_FFFFFF_t_70_pic_center 6]: /images/20221123/48389b3561cc4e22bd4096590dee6566.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI4MzQ3NTA_size_16_color_FFFFFF_t_70_pic_center 7]: /images/20221123/78d3d3a0ac3c46499661983d48b7b74a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI4MzQ3NTA_size_16_color_FFFFFF_t_70_pic_center 8]: /images/20221123/743880ca6f824b1db1ca56bf658cec1c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI4MzQ3NTA_size_16_color_FFFFFF_t_70_pic_center 9]: /images/20221123/fda735c647aa4ff5a08ced0cc20a94da.png