java八股文面试[数据库]——MySQL死锁的原因和处理方法

浅浅的花香味﹌ 2024-03-07 01:41 96阅读 0赞

###### 1) 表的死锁 ######

**产生原因**:

用户A访问表A（锁住了表A），然后**又访问表B**；另一个用户B访问表B（锁住了表B），然后企图**访问表A**；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了。

用户A--》A表（表锁）--》B表（表锁）

用户B--》B表（表锁）--》A表（表锁）

**解决方案**：

这种死锁比较常见，是由于**程序的BUG产生的**，除了调整的程序的逻辑**没有其它的办法**。

仔细分析程序的逻辑，对于数据库的多表操作时，**尽量按照相同的顺序进行处理**，尽量避免同时锁定两个资源，如操作A和B两张表时，总是按先A后B的顺序处理， **必须同时锁定两个资源时**，要保证在任何时刻都应该按照相同的顺序来锁定资源。

###### 2) 行级锁死锁 ######

产生原因1：

如果在**事务中**执行了**一条没有索引条件的查询**，引发**全表扫描**，把行级锁上升为**全表记录锁定**（等价于表级锁），多个这样的事务执行后，就很容易产生死锁和阻塞，最终应用系统会越来越慢，发生阻塞或死锁。

**解决方案：**

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

产生原因2：

\- 两个事务**分别想拿到对方持有的锁**，互相等待，于是产生死锁

![image.png][]

产生原因3：

每个事务**只有一个SQL**,但是有些情况还是会发生死锁.

1. 事务1,从**name**索引出发 , 读到的\[hdc, 1\], \[hdc, 6\]均满足条件, 不仅会加**name索引上**的记录X锁, 而且会加**聚簇索引上的记录X锁**, 加锁顺序为先\[1,hdc,100\], 后\[6,hdc,10\]  
2. 事务2，从**pubtime**索引出发，\[10,6\],\[100,1\]均**满足过滤条件**，同样也会加**聚簇索引上的记录**X锁，加锁顺序为\[6,hdc,10\]，后\[1,hdc,100\]。  
3. 但是加锁时发现跟事务1的**加锁顺序正好相反**，两个Session恰好都持有了第一把锁，请求加第二把锁，死锁就发生了。

![image.png][image.png 1]

**解决方案:**

如上面的原因2和原因3, 对索引加锁顺序的**不一致**很可能会导致死锁，所以如果可以，尽量以相**同的顺序**来访问索引记录和表。在程序以批量方式处理数据的时候，如果事先对**数据排序**，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能；

###### 如何避免死锁 ######

为了避免死锁，可以采取以下措施：

1 保持**锁的顺序一致**。在多个事务请求资源的情况下，要保持锁的请求顺序一致，这可以避免死锁的发生。

2 尽量**缩短事务的持有时间**。如果事务持有锁的时间过长，会增加死锁的风险。因此，在处理事务时，应尽量缩短事务的持有时间，尽快释放锁。

3 尽量减少**事务的嵌套**。如果事务嵌套层数过多，会增加死锁的风险。因此，在编写事务时，应尽量减少事务的嵌套层数，避免死锁的发生。

4 使用**超时机制**。如果一个事务等待锁的时间过长，可以使用超时机制来终止该事务，从而避免死锁的发生。

###### 如何解决死锁 ######

如果避免死锁失败，可能需要采取以下措施来解决死锁：

1 **检测死锁**。数据库管理系统通常提供**死锁检测机制**，可以检测出死锁的发生，并且可以中止其中一个事务，从而解除死锁。

2 **优化锁的使用**。如果死锁的发生频率较高，可能需要重新设计**数据库架构**，优化锁的使用，从而降低死锁的发生率。

3 **重构事务**。如果死锁的发生频率较高，可能需要重构事务，重新设计事务的逻辑，从而避免死锁的发生。

4 **增加资源**。如果死锁的发生频率较高，可能需要增加资源，例如增加服务器的处理能力或增加数据库的缓存空间，从而降低死锁的发生率。

###### innoDB死锁检测原理 ######

innoDB 检测死锁通过**等待图**来检测死锁，如果等待图有**回路**，则表示有循环依赖，相互等待的情况，则表示有死锁。

等待图需要通过**2张表来构建**，当前**锁的信息表** 和 当前**事务等待表**。

1.  当前锁的信息表

select * from performance_schema.data_locks;

![format_png][]

这个图可以看出，线程 61 获得了id = 1 的排他锁，而 线程 63 获得了 id = 100 的排他锁。

1.  当前事务等待表

select * from performance_schema.data_lock_waits;

![format_png 1][]

这个图可以看出，在线程 63（也就是获得了 id = 100 排他锁的线程）在尝试获取 id = 1 时 被 block 了，block 他的线程是线程 61，因为线程 61 正在获得 id = 1 的排他锁。

如果在继续执行线程 61 尝试获取 id = 100 的记录，会获取如下的结果

![cdd72d7b0b0e5d09fa66cd37c03bd944.png][]

通过这两张表， innoDB 可以绘出如下的图

1.  首先有多少个事务，就有多少个点，在这个例子里，有2个事务，线程 61 和 63
2.  线程 61 在等待 线程 63，尝试获取 id = 100 的记录，所以 61 - 63 有一条边
3.  线程 63 在等待 线程 61，尝试获取 id = 1 的记录，所以 63 - 61 有一条边

最后判断图中是否有回路，如果有回路，则认为有死锁，若发现有死锁，回滚 undo 量最小的记录的事务。

![417a9ae6a2d443e2682f6c1961f03d72.png][]

知识来源：马士兵教育

[百度安全验证][Link 1]

[【数据库】数据库死锁 - 知乎][-]

[image.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/07/743e66047c144559b0796f9f587b868e.png
[image.png 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/07/3ef89a131870452f8239a2c939b8f797.png
[format_png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/07/7e236ba43c9b45978d51692f1bba6a02.png
[format_png 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/07/8c8e7f3705614ffb815a9f5f5aeb33da.png
[cdd72d7b0b0e5d09fa66cd37c03bd944.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/07/3cf92c1ae2a84730ad1abd717f77e5ad.png
[417a9ae6a2d443e2682f6c1961f03d72.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/07/9cc8ece5f2b347fc8452b593c0f57094.png
[Link 1]: https://baijiahao.baidu.com/s?id=1768638762293699606&wfr=spider&for=pc
[-]: https://zhuanlan.zhihu.com/p/476525859