MySQL中的死锁

亦凉 2024-04-07 15:38 53阅读 0赞

#### MySQL中的死锁 ####

*  一. 概述
 *   *   *  排他锁和索引的关系：
 *  二. 案例
 *   *   *  案例1：事物之间对资源访问顺序的交替（常见）
         *  案例二：并发修改同一记录
         *  案例三：索引不当导致全表扫描
         *  如何尽可能避免死锁

## 一. 概述 ##

**定义**：  
是指两个或两个以上的进程在执行过程中,**因争夺资源而造成的一种互相等待的现象**，  
若无外力作用，它们都将无法推进下去；

**表级锁不会产生死锁**，所以解决死锁主要还是针对于使用 `InnoDB` 引擎的 **行级的排他锁** 的情况。（MyISam没有行级锁）

**《操作系统》中：**

*  **死锁的必要条件**（其中一个不成立就不会死锁）：  
    1.**互斥条件**：临界资源只能被一个线程占用，这里指的是**排他锁**，上了排他锁之后不能再上锁！  
    2.**请求保持条件**：【线程阻塞时】，对已获得的资源不释放（自己不放）  
    3.**不剥夺条件**：线程已获得的资源不能被抢走（不能被抢）  
    4.**循环等待条件**：线程形成头尾相接的循环等待资源的关系
 *  **如何避免死锁**：  
    1.互斥条件：无法破坏，用锁就是为了要互斥（临界资源需要互斥访问，一次仅允许一个进程使用的资源即临界资源）  
    2.请求保持条件：一次性申请所有资源  
    3.不剥夺条件： 若线程申请不到资源，则主动释放资源  
    4.循环等待条件： 多用户访问资源时，按照相同资源访问顺序进行处理，破坏循环等待条件；

**注意**：  
Mysql **`InnoDB`** 引擎默认的修改数据语句，**update,delete,insert都会自动给涉及到的数据加上行级 排他锁**，而select语句默认不会加任何锁类型；

所以加过排他锁的数据行在其他事务中是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据（类似临界资源）；

#### 排他锁和索引的关系： ####

**（1）delete from msg where id=2；**  
id是主键（**聚簇索引**），因此直接用排他锁 锁住整行记录即可 ；  
![在这里插入图片描述][ad9e1a91f0724dbf8d209edf491d392c.png]

**（2) delete from msg where token=’ cvs’;**  
由于token是**二级索引**，因此首先锁住二级索引(两行)，接着会用排他锁 锁住相应主键所对应的记录；  
![在这里插入图片描述][953be3db09314c03bfe52e332a48f08a.png]  
**（3) delete from msg where message=‘订单号是多少’**；  
message **没有索引**，所以走的是全表扫描过滤。这时表上的各个记录都将添加上X锁，即行级锁上升为表级锁。  
![在这里插入图片描述][d1dd640d22eb48249fe09fc82cf9f264.png]  
由此可得出，建立**聚簇索引**，将大幅提升该查询语句的性能，降低了锁定资源的时间，同时也**减少了锁定资源的范围**，这样就**降低了资源循环等待事件发生的概率**；

## 二. 案例 ##

#### 案例1：事物之间对资源访问顺序的交替（常见） ####

即用户A 锁住资源A，然后访问资源B，  
而用户B 锁住资源B，然后访问资源A，造成循环等待----死锁；  
![在这里插入图片描述][96a0f6a1903b42e88013313412f137d5.png]

**如**：  
事务A先对id=1的删除，再对token='asd’的更新；  
事务B先对token=’ask‘的更新，再对 id='1’的删除；  
由于在InnoDB中，进行update、delete、insert这三个DML语句时会自动加上行级别排他锁，所以不需要解锁后才能再执行DML，最后造成死锁；  
![在这里插入图片描述][ba2d35fc82b24644956e518dfb7ac4c1.png]

**解决方法**：多用户操作多表资源时，按照**相同资源访问顺序**进行处理。

#### 案例二：并发修改同一记录 ####

用户A拿到共享锁访问资源A时企图修改资源A，此时用户B拿到独占锁修改资源A时企图访问B  
![在这里插入图片描述][2d72acd8637e4ce885521616fae75d28.png]  
用户A查询一条纪录，然后修改该条纪录；这时用户B修改该条纪录，这时用户A的事务里锁的性质由查询的共享锁企图上升到独占锁，而用户B里的独占锁由于A有共享锁存在所以必须等A释放掉共享锁，而A由于B的独占锁而无法上升的独占锁也就不可能释放共享锁，于是出现了死锁。这种死锁由于比较隐蔽，但在稍大点的项目中经常发生。  
　一般更新模式由一个事务组成，此事务读取记录，获取资源（页或行）的共享 (S) 锁，然后修改行，此操作要求锁转换为排它 (X) 锁。如果两个事务获得了资源上的共享模式锁，然后试图同时更新数据，则一个事务尝试将锁转换为排它 (X) 锁。共享模式到排它锁的转换必须等待一段时间，因为一个事务的排它锁与其它事务的共享模式锁不兼容；发生锁等待。第二个事务试图获取排它 (X) 锁以进行更新。由于两个事务都要转换为排它 (X) 锁，并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。

**解决方法**：  
a. 使用乐观锁进行控制。

乐观锁大多是基于数据版本（Version）记录机制实现。即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个“version”字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。乐观锁机制避免了长事务中的数据库加锁开销（用户A和用户B操作过程中，都没有对数据库数据加锁），大大提升了大并发量下的系统整体性能表现。Hibernate 在其数据访问引擎中内置了乐观锁实现。需要注意的是，由于乐观锁机制是在我们的系统中实现，来自外部系统的用户更新操作不受我们系统的控制，因此可能会造成脏数据被更新到数据库中。

b. 使用悲观锁进行控制。

悲观锁大多数情况下依靠数据库的锁机制实现，如Oracle的Select … for update语句，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。如一个金融系统，当某个操作员读取用户的数据，并在读出的用户数据的基础上进行修改时（如更改用户账户余额），如果采用悲观锁机制，也就意味着整个操作过程中（从操作员读出数据、开始修改直至提交修改结果的全过程，甚至还包括操作员中途去煮咖啡的时间），数据库记录始终处于加锁状态，可以想见，如果面对成百上千个并发，这样的情况将导致灾难性的后果。所以，采用悲观锁进行控制时一定要考虑清楚。

#### 案例三：索引不当导致全表扫描 ####

如果在事务中执行了一条不满足条件的语句，执行全表扫描，**行级锁上升为表级锁**，多个这样的事务执行后，就很容易产生死锁和阻塞。![在这里插入图片描述][f7f97f992b5f4fef814f124c6cfdecb8.png]

**解决方法**：  
SQL语句中不要使用太复杂的关联多表的查询；使用“执行计划”对SQL语句进行分析，对于有全表扫描的SQL语句，建立相应的索引进行优化。

#### 如何尽可能避免死锁 ####

1）以固定的顺序访问表和行；

2）大事务拆小，**大事务更倾向于死锁**，如果业务允许，将大事务拆小；

3）在同一个事务中，尽可能做到**一次锁定所需要的所有资源**，减少死锁概率；

4）降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁；

5）为表添加合理的**索引**，使用索引会只锁定一行，减少锁定资源的范围，避免全表扫描即上升为表级锁，能够减少发生死锁的概率；

参考：  
https://longcj.blog.csdn.net/article/details/122932768  
https://blog.csdn.net/BaiHuaXiu123/article/details/54015279

[ad9e1a91f0724dbf8d209edf491d392c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/83bbc84295e6462f9fcb3a28f69c0471.png
[953be3db09314c03bfe52e332a48f08a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/079feed88680405d9a379c8ccc5b01ca.png
[d1dd640d22eb48249fe09fc82cf9f264.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/075b9620190a4e9f9866d65aac407737.png
[96a0f6a1903b42e88013313412f137d5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/cb8d0f5f99db4c9d8747e837abeba96c.png
[ba2d35fc82b24644956e518dfb7ac4c1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/cc7d272bebef41f3aacf7856ed773d63.png
[2d72acd8637e4ce885521616fae75d28.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/6845996631654669ba451be706af3b85.png
[f7f97f992b5f4fef814f124c6cfdecb8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/0185c0aabc4f4f9e86c3ad220f7ebc97.png