MySQL 加锁和死锁解析

男娘i 2023-02-11 10:18 25阅读 0赞

![format_png][]

点击上方蓝字关注我们

![format_png 1][]

##  ##

*  产生死锁的必要条件
 *  常规锁模式
 *  锁的属性
 *  锁组合（属性+模式）
 *  锁冲突矩阵
 *  锁是加在那里的？
 *  操作与加锁的对照关系
 *   *  那些操作会加GAP锁？
     *  如何去掉GAP锁？
     *  Insert
     *  Delete
     *  Update
     *  GAP锁
     *  什么时候加next-key lock？
     *  Insert Intention Lock
 *  总结

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

##     ##

##  ##

产生死锁的必要条件

*  多个并发事务（2个或者以上）
 *  每个事物都持有了锁（或者是已经在等待锁）
 *  每个事务都需要再继续持有锁（为了完成事务逻辑，还必须更新更多的行）
 *  事物之间产生加锁的循环等待，形成死锁

##     ##

##  ##

常规锁模式

*  LOCK\_S（读锁，共享锁）
 *  LOCK\_X（写锁，排它锁）

##     ##

##  ##

锁的属性

*  LOCK \_REC\_NOT\_GAP（锁记录）
 *  LOCK\_GAP（锁记录前的GAP）
 *  LOCK\_ORDINARY（同时锁记录+记录前的GAP，Next key锁）
 *  LOCK\_INSERT\_INTETION（插入意向锁）

##     ##

##  ##

锁组合（属性+模式）

> 可以任意组合

##     ##

##  ##

锁冲突矩阵

![format_png 2][]

##     ##

##  ##

锁是加在那里的？

>  *  ***根据主键查找-锁加在主键上***  
>     如 begin;select \* from tt\_copy where id=4 for update;  
>     加锁情况
> 
> > index PRIMARY of table `test`.`tt_copy` trx id 1101588 lock\_mode X locks rec but not gap

>  *  ***根据普通索引查找-锁加在普通索引和主键上***  
>     如 begin;select \* from tt\_copy force index(idx\_a) where a=4 for update;  
>     加锁情况
> 
> > index idx\_a of table `test`.`tt_copy` trx id 1101590 lock\_mode X locks rec but not gap  
> > index PRIMARY of table `test`.`tt_copy` trx id 1101590 lock\_mode X locks rec but not gap

##     ##

##  ##

操作与加锁的对照关系

##     ##

***以下没特殊说明都为RC隔离级别***

### Insert ###

*  无Unique key，插入后 ：无论RC或RR隔离级别都是对主键加 LOCK\_X+LOCK\_REC\_NOT\_GAP
 *  有Unique key

> 插入前，唯一约束检查：LOCK\_S+LOCK\_ORDINARY  
> 插入前，插入的位置有GAP锁：LOCK\_INSERT\_INTETION  
> 插入后，新数据插入：LOCK\_X+LOCK\_REC\_NOT\_GAP

### Delete ###

> 满足删除条件的所有记录：LOCK\_X+LOCK\_REC\_NOT\_GAP

### Update ###

\*\*Update操作分解 \*\*

>  *  Step 1：定位到 *下一条*满足查询条件的记录（查询过程，类似于Select/Delete）
>  *  Step 2：删除当前定位到的记录（标记为删除状态）
>  *  Step 3：拼装更新后项，根据更新后项定位到 *新的插入位置*
>  *  Step 4：在新的插入位置，判断是否存在 Unique 冲突（ *存在Unique Key 时*）
>  *  Step 5：插入更新后项（不存在Unique冲突时）
>  *  Step 6： *重复Step 1 到Step 5 的操作*，直至扫描完整个查询范围

**Update操作分析**

>  *  Step 1，Step 2：*Delete*
>  *  Step 3，Step 4，Step 5：*Insert*

**Update**

*  无Unique key：

>  *  查询范围中的所有记录，LOCK\_X + LOCK\_REC\_NOT\_GAP

*  有Unique key：

>  *  查找满足条件的记录：查询范围内的所有记录， LOCK\_X + LOCK\_REC\_NOT\_GAP
>  *  更新后项存在唯一性冲突：冲突项上的加锁，*LOCK\_S + LOCK\_ORDINARY*
>  *  更新后项不存在唯一性冲突：更新位置后项加锁，LOCK\_S + LOCK\_GAP （省略）
>  *  实际更新操作：可看做插入了一条新纪录，LOCK\_X + LOCK\_REC\_NOT\_GAP

### GAP锁 ###

#### 那些操作会加GAP锁？ ####

>  *  Read Committed （RC） ） ：Unique Key 唯一约束检查；Purge操作；
>  *  Repeatable Read （RC ）：RC的基础上，所有需要加锁的索引范围扫描和索引查找（Update/Delete…)
>  *  还有一种会加GAP锁：RR隔离级别下，对有唯一索引的表执行insert on duplicate update操作，除了会对新插入的记录加x not gap外，还会对相邻记录加x gap

#### 如何去掉GAP锁？ ####

> change the transaction isolation level to ***READ COMMITTED*** or enable the ***innodb\_locks\_unsafe\_for\_binlog*** system variable (which is now deprecated)

### 什么时候加next-key lock？ ###

> By default, InnoDB operates in ***REPEATABLE READ*** transaction isolation level. In this case, InnoDB uses ***next-key locks*** for ***searches and index scans***, which prevents phantom rows

### Insert Intention Lock ###

> An insert intention lock is a type of gap lock set by ***INSERT*** operations prior to（在...之前） row insertion.

##     ##

##  ##

总结

**• 原则之一**

>  *  要分析一个死锁，必须深入业务，了解整个事务的逻辑（闭门无法造车）

**• 原则之二**\`

>  *  GAP锁很复杂，为了减少GAP锁，减少GAP导致的死锁，尽量选择Read Committed隔离级别（RC + row based binlog，基本上能够解决所有问题，无需使用Repeatable Read）
>  *  适当的 减少Unique 索引，能够减少GAP锁导致的死锁（根据业务情况而定）

**• 原则之三**

>  *  在MySQL 中，以不同索引的过滤条件， 来操作相同的记录（Update/Delete ），很容易产生死  
>     锁。

**• 原则之四**

>  *  RC隔离级别下，如果死锁中出现Next Key（Gap锁），说明表中一定存在unique索引
>  *  多语句事务产生的死锁，确保每条语句操作记录的顺序性，能够极大减少死锁

[文末推荐，点我][Link 1]

![format_png 3][]

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL3N6X21tYml6X3BuZy9YWnAyS1NyaWJpYkxKOGR5ZmZEYmxZVFNhNUNqelI1eENRVHQ1WDE1bEk5eDN1VWJWUk1hNmVBQlJiV1VrYkRZU1dXbFBMVzdFZkxzb3JoMlFmZXZoa2VnLzY0MA?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy94aWNXWVRTSUN6UnRWNk9oV2ZJdFFzNUhvN2lhYndpY1kxNU5pYXFpY1hLajNIZWY0Uk1LQWlhRWZpYlBLWlg5U3pEd05JaHFVSjVzaWJXVG80ZmtrS0pVTWM4ckl3LzY0MA?x-oss-process=image/format,png
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy94aWNXWVRTSUN6UnRWNk9oV2ZJdFFzNUhvN2lhYndpY1kxNXk5ZFBZRVo5aWFPb2lhRFRuUlFzNDJIMXA5dUVoQXczTGIzcGtHMjFsUmliWGhZdEV5b2tsVTNLdy82NDA?x-oss-process=image/format,png
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzU2NDc4MjE2Ng%3D%3D&chksm=fc44f0d4cb3379c2d5db7f24e694c0c8cbb906f4254c4d23b473ec6e9b219e7480ec87dfbee4&idx=1&mid=2247484909&scene=21&sn=38d374e43275769bb08dd15191945d49#wechat_redirect
[format_png 3]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy94aWNXWVRTSUN6UnZ6aGRLck1pY1NQSWliZzhQRWJPa2E4bmpjOHJFZmFLa09YTHAyd21wckxpY0JRaWNYMWlhdVFzWEdTTDlpYXhreFZTb3RQQ2F2U2hva1NvVHcvNjQw?x-oss-process=image/format,png