MySQL 加锁和死锁解析

﹏ヽ暗。殇╰゛Y 2021-11-05 11:10 415阅读 0赞

[MySQL 加锁和死锁解析][MySQL]

原文: [MySQL 加锁和死锁解析][MySQL 1]

*  产生死锁的必要条件
 *  锁是加在那里的？
 *  操作与加锁的对照关系
    
     *  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（插入意向锁）
    
    ## 锁组合（属性+模式） ##
    
    > 可以任意组合
    
    ## 锁冲突矩阵 ##
    
    ![1179590-20190519220329858-803840545.png][]

## 锁是加在那里的？ ##

>  *  ***根据主键查找-锁加在主键上***  
>     如 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索引
>  *  多语句事务产生的死锁，确保每条语句操作记录的顺序性，能够极大减少死锁

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

***本文大多数都整理自《死锁-何登成 - 管中窥豹——MySQL(InnoDB)死锁分析之道》***

posted on 2019-05-20 00:07 [NET未来之路][NET] 阅读( ...) 评论( ...) [编辑][Link 1] 收藏

转载于:https://www.cnblogs.com/lonelyxmas/p/10891650.html

[MySQL]: https://www.cnblogs.com/lonelyxmas/p/10891650.html
[MySQL 1]: https://www.cnblogs.com/YangJiaXin/p/10891091.html
[1179590-20190519220329858-803840545.png]: /images/20211105/00a1c29ed0184b1dba52e303effd9200.png
[NET]: https://www.cnblogs.com/lonelyxmas/
[Link 1]: https://i.cnblogs.com/EditPosts.aspx?postid=10891650