MySQL死锁的原因和处理方法

短命女 2023-09-26 23:02 85阅读 0赞

### MySQL死锁的原因和处理方法 ###

*  表的死锁
 *   *  产生原因
     *  解决方案
 *  行级锁死锁
 *   *  产生原因1
     *  解决方案1
     *  产生原因2
     *  产生原因3
     *  解决方案

# 表的死锁 #

## 产生原因 ##

用户A访问表A(锁住了表A),然后又访问表B；另一个用户B访问表B(锁住了表B),然后企图访问表A;这时用户A由于用户B已经锁住表B,它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这样死锁就产生了。  
![在这里插入图片描述][77bae92c1d4043b3b2db4059bf2db8bd.png]

## 解决方案 ##

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

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

# 行级锁死锁 #

## 产生原因1 ##

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

## 解决方案1 ##

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

## 产生原因2 ##

两个事务分别想拿到对方持有的锁，互相等待，于是产生死锁。  
![在这里插入图片描述][f2664dbaeaee42c98992e8856a26fdce.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恰好都持有了第一把锁，请求加第二把锁，死锁就发生了。  
![在这里插入图片描述][dc64f827c45549eea9e9b1cdef0c2297.png]

## 解决方案 ##

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

**参考资料**：[Java面试题300集：百度MySQL面试题之说一下MySQL死锁的原因][Java_300_MySQL_MySQL]

[77bae92c1d4043b3b2db4059bf2db8bd.png]: https://img-blog.csdnimg.cn/77bae92c1d4043b3b2db4059bf2db8bd.png
[f2664dbaeaee42c98992e8856a26fdce.png]: https://img-blog.csdnimg.cn/f2664dbaeaee42c98992e8856a26fdce.png
[dc64f827c45549eea9e9b1cdef0c2297.png]: https://img-blog.csdnimg.cn/dc64f827c45549eea9e9b1cdef0c2297.png
[Java_300_MySQL_MySQL]: https://www.bilibili.com/video/BV1yh41177Kj/?spm_id_from=333.337.search-card.all.click&vd_source=a1af903e57688df870625021f7f3a7eb