MySQL死锁详解及检测和避免

深藏阁楼爱情的钟 2023-10-08 15:50 55阅读 0赞

上一篇博客我们知道的Mysql事务的隔离机制和实现，以及锁的详细解析

链接: [详解MySQL脏读幻读不可重复读及事务的隔离级别和MVCC、LBCC实现，还有锁的详解][MySQL_MVCC_LBCC]  
在我们使用锁的时候，有一个问题是需要注意和避免的，我们知道，排它锁有互斥的特性。一个事务或者说一个线程持有锁的时候，会阻止其他的线程获取锁，这个时候会造成阻塞等待，如果循环等待，会有可能造成死锁。

这个问题我们需要从几个方面来分析，一个是锁为什么不释放，第二个是被阻塞了怎么办，第三个死锁是怎么发生的，怎么避免。

#### 文章目录 ####

*  锁的释放与阻塞
 *  死锁的发生和检测
 *  查看锁信息（日志)
 *  死锁的避免

## 锁的释放与阻塞 ##

> 回顾:锁什么时候释放?  
> 事务结束(commit,rollback)﹔  
> 客户端连接断开。

如果一个事务一直未释放锁，其他事务会被阻塞多久?会不会永远等待下去?  
如果是，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题,甚至拖跨数据库。

线上怕不怕这个错？

[Err] 1205 - Lock wait timeout exceeded; try restarting transaction

MySQL有一个参数来控制获取锁的等待时间，默认是50秒。

show VARIABLES like "innodb_lock_wait_timeout";

对于死锁，是无论等多久都不能获取到锁的，这种情况，也需要等待50秒钟吗?那不是白白浪费了50秒钟的时间吗?

## 死锁的发生和检测 ##

演示一下，开两个会话：  
方便对时间线的提现，这里用图片，有兴趣的可以跟着模仿一下  
栗子一：  
![在这里插入图片描述][9c198c579a364973ad1b4f284f9fd886.png]  
栗子二：  
![在这里插入图片描述][8e8071e4833f467abc361ad82c2132e9.png]  
在第一个事务中，检测到了死锁，马上退出了，第二个事务获得了锁，不需要等待50秒:

[Err] 1213 - Deadlock found when trying to get lock; try restarting transaction

为什么可以直接检测到呢?是因为死锁的发生需要满足一定的条件，对于我们程序员来说，有明确的条件，意味着能判定，所以在发生死锁时，InnoDB一般都能通过算法(wait-for graph)自动检测到。  
那么死锁需要满足什么条件?死锁的产生条件，因为锁本身是互斥的:  
(1)同一时刻只能有一个事务持有这把锁;  
(2）其他的事务需要在这个事务释放锁之后才能获取锁，而不可以强行剥夺;  
(3）)当多个事务形成等待环路的时候，即发生死锁。

理发店有两个总监。一个负责剪头的Tony老师，一个负责洗头的Kelvin老师。Tony老师不能同时给两个人剪头，这个就叫`互斥`。

Tony在给别人在剪头的时候，你不能让他停下来帮你剪头，这个叫`不能强行剥夺`。  
如果Tony的客户对Kelvin说:你不帮我洗头我怎么剪头? Kelvin 的客户对Tony说:你不帮我剪头我怎么洗头?这个就叫`形成等待环路`。  
实际上，发生死锁的情况非常多，但是都满足以上3个条件。  
这个也是**表锁是不会发生死锁的原因，因为表锁的资源都是一次性获取的**。

如果锁一直没有释放，就有可能造成大量阻塞或者发生死锁，造成系统吞吐量下降,这时候就要查看是哪些事务持有了锁。

## 查看锁信息（日志) ##

首先,SHow STATUS命令中，包括了一些行锁的信息:

show status like 'innodb_row_lock_%';

![在这里插入图片描述][c741fe52f5184e77b91f0ba48f13dfc5.png]

> lnnodb\_row\_lock\_current\_waits:当前正在等待锁定的数量;  
> lnnodb\_row\_lock\_time :从系统启动到现在锁定的总时间长度，单位ms;  
> Innodb\_row\_lock\_time\_avg :每次等待所花平均时间;  
> Innodb\_row\_lock\_time\_max:从系统启动到现在等待最长的一次所花的时间;  
> lnnodb\_row\_lock\_waits :从系统启动到现在总共等待的次数。

SHOW命令是一个概要信息。InnoDB还提供了三张表来分析事务与锁的情况:

select * from information_schema.INNODB_TRX; --当前运行的所有事务﹐还有具体的语句

![在这里插入图片描述][e39e0b2323944051988ff017379b49b0.png]

select* from information_schema.INNODB_LOCKS; --当前出现的锁

![在这里插入图片描述][189ffea5569c48d39d4cd67b386f2213.png]

select * from information_schema.INNODB_LOCK_WAITS; --锁等待的对应关系

![在这里插入图片描述][b48a4269ff974bf5a9589768baf67085.png]  
更加详细的锁信息，开启标准监控和锁监控:  
额外的监控肯定会消耗额外的性能

set GLOBAL innodb_status_output=ON;
    set GLOBAL innodb_status_output_locks=ON;

通过分析锁日志，找出持有锁的事务之后呢?  
如果一个事务长时间持有锁不释放，可以kill事务对应的线程ID，也就是INNODB\_TRX表中的trx\_mysql\_thread\_id，例如执行kill 4，kill 7， kill 8。  
当然，死锁的问题不能每次都靠kill线程来解决，这是治标不治本的行为。我们应该尽量在应用端，也就是在编码的过程中避免。  
有哪些可以避免死锁的方法呢?

## 死锁的避免 ##

1、在程序中，操作多张表时，尽量以相同的顺序来访问（避免形成等待环路)  
2、批量操作单张表数据的时候，先对数据进行排序(避免形成等待环路);  
3、申请足够级别的锁，如果要操作数据，就申请排它锁;  
4、尽量使用索引访问数据，避免没有where条件的操作，避免锁表;  
5、如果可以，大事务化成小事务;  
6、使用等值查询而不是范围查询查询数据，命中记录，避免间隙锁对并发的影响。

[MySQL_MVCC_LBCC]: https://blog.csdn.net/weixin_44688973/article/details/125540791
[9c198c579a364973ad1b4f284f9fd886.png]: https://img-blog.csdnimg.cn/9c198c579a364973ad1b4f284f9fd886.png
[8e8071e4833f467abc361ad82c2132e9.png]: https://img-blog.csdnimg.cn/8e8071e4833f467abc361ad82c2132e9.png
[c741fe52f5184e77b91f0ba48f13dfc5.png]: https://img-blog.csdnimg.cn/c741fe52f5184e77b91f0ba48f13dfc5.png
[e39e0b2323944051988ff017379b49b0.png]: https://img-blog.csdnimg.cn/e39e0b2323944051988ff017379b49b0.png
[189ffea5569c48d39d4cd67b386f2213.png]: https://img-blog.csdnimg.cn/189ffea5569c48d39d4cd67b386f2213.png
[b48a4269ff974bf5a9589768baf67085.png]: https://img-blog.csdnimg.cn/b48a4269ff974bf5a9589768baf67085.png