MySql死锁分析

最新推荐文章于 2024-07-01 14:06:48 发布

戎·码一生

最新推荐文章于 2024-07-01 14:06:48 发布

阅读量410

点赞数 2

分类专栏：数据库文章标签： mysql 数据库死锁

本文链接：https://blog.csdn.net/gogokongyin/article/details/110223183

版权

数据库专栏收录该内容

11 篇文章 1 订阅

订阅专栏

背景

在我们使用锁的时候，有一个问题是需要注意和避免的，我们知道，排他锁有互斥的特性。一个事务或者说一个线程持有锁的时候，会阻塞其他线程的获取锁，这个时候会造成线程的阻塞和等待，如果说是循环等待，就有可能造成死锁。

1、死锁可能的现象

网页响应变慢，出现接口超时；
cpu飙升；
程序运行的过程中：某条sql的执行后一直未成功，不提交，程序也不报错；

锁的释放时机：

锁并不是用完就释放了，锁的释放时机：

1. 执行commit语句或者rollback；
2. 退出数据库；
3. 客户端断开连接；

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

Mysql有一个参数来控制获取锁的等待时间：默认是50s

show VARIABLES like 'innodb_lock_wait_timeout';  -- 查看数据库的超时时间
set global innodb_lock_wait_timeout=2; -- 设置数据库的超时时间，单位是s（秒）

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

2、死锁的发生与检测

死锁实例分析：

Session1	Session2
begin; select * from goods where id = 3 for update; //语句1
	begin; delete from goods where id = 4; //语句3
update goods set num = 1 where id = 4; //语句2 (等待id=4的行锁)
	delete from goods where id = 3; //语句4 (等待id=3行锁)

分析：上面两个会话因为互相等待行锁而导致了死锁。session1中语句1获得了id=3的行锁，等待session2中语句3中id=4的行锁，session2中语句3获得了id=4的行锁，等待session1语句1中id=3的行锁；发生了死锁。

2.1、死锁的条件

因为死锁的发生需要满足一定的条件，所以就可以根据条件对其进行检测。

( 1 ) 互 斥条件 ：一个资源同一个时刻只能被一个线程访问；
( 2 ) 请 求保持-占有等待 ：一个线程因请求资源而阻塞的时候，对已有的资源保持不放；
( 3 ) 不 剥夺-不可抢占 ：一个线程获取的资源，在没有使用完之前，不能强行剥夺；
( 4 ) 循环等待：多个线程循环等待资源；

如果发现满足这四个条件就说明发生了死锁，只要破坏其中一个条件即可解除死锁。

2.2、死锁检测

这里如果开启了死锁检测的情况下，数据库就能够自动检测到死锁的发生，并自动解除死锁，参数设置如下：

show VARIABLES like 'innodb_deadlock_detect'; -- 查看死锁检测的开关是否打开
set global innodb_deadlock_detect=on;         -- 设置死锁的开关

3、死锁的分析

方法一： 通过show processlist可以看到数据库中运行的线程情况。如果确定是那个sql发生了死锁，直接kill掉pid即可，对应的是结果的id列。

show processlist  -- 显示mysql线程的信息

方法二： Innodb还提供了两张表来分析事务与锁的情况。

第一张表：INNODB_TRX表， 记录着当前事务运行的状态，对应的事务id，线程id，锁等待情况，以及具体的sql语句等等，非常详尽。

select * from information_schema.INNODB_TRX; -- 当前运行的持有锁活跃的事务信息

分析：针对开篇的死锁的例子，通过这个表可以清楚的看到有两个事务处于等待状态lock wait：

等待事务1 ：事务id为：329507，执行的线程id为：18，执行的sql语句为：delete from goods where id = 3;

等待事务2：事务id：329506，执行的线程id：19，执行的sql为：update goods set num = 1 where id = 4;信息非常清楚；

第二张表：INNODB_LOCK_WAITS，可以详细地看到事务之间锁等待的对应事务id关系，线程pid关系：

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

分析：从上面的结果可以看出有两个事务相互等待行锁Record发生了死锁：

等待事务1：锁住的表名为goods，通过主键索引实现的行锁，执行的事务id为：329506，执行的线程id为：19，阻塞事务id：329507，阻塞线程id：18，执行的sql语句为：update goods set num = 1 where id = 4；

等待事务2：锁住的表名为goods，通过主键索引实现的行锁，执行的事务id为：329507,执行的线程id为：18，阻塞事务id：329506，阻塞线程id：19，执行的sql语句为：delete from goods where id = 3；

可以看到死锁对应关系一目了然，并且也给出了解除死锁的方案：kill query 18；kill掉阻塞线程id即可，释放锁后，破坏了无限等待的条件即可解除死锁。

方法三： 通过show engine innodb status命令 查看数据库日志 ，分析死锁的情况。

show engine innodb status;  -- 查看数据库日志

前提是要打开数据库的锁日志监控，截取部分死锁日志如下：

------------------------
LATEST DETECTED DEADLOCK  
------------------------
---TRANSACTION 329506, ACTIVE 2708 sec starting index read -- 事务id
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 19, OS thread handle 123145451286528, query id 552 localhost root updating
update goods set num = 1 where id = 4  -- 执行的sql语句
------- TRX HAS BEEN WAITING 2631 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 61 page no 4 n bits 72 index PRIMARY of table `kinginfo`.`goods` trx id 329506 lock_mode X locks rec but not gap waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 7; compact format; info bits 32
0: len 4; hex 80000004; asc     ;;
1: len 6; hex 000000050723; asc      #;;
2: len 7; hex 020000013d20b2; asc     =  ;;
3: len 5; hex 62616f6d61; asc baoma;;
4: len 4; hex 80000000; asc     ;;
5: len 5; hex 99a47a1533; asc   z 3;;
6: SQL DEFAULT;

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

分析：通过查看数据库的状态，日志说明了事务TRANSACTION 329506 在执行update goods set num = 1 where id = 4的时候持有的行锁RECORD LOCKS，不是间隙锁but not gap waiting，但是还的等待事务2的锁：同理，事务TRANSACTION 329507持有了行锁以及等待事务329506的锁；

查看数据库死锁概况

如果存在死锁，就会造成吞吐量下降，这时候就需要看看那些事务拥有了锁。平时，我们可以通过一些命令查看数据库中历史出现的锁信息：

show status like 'innodb_row_lock_%';

参数解释：

Innodb_row_lock_current_waits:当前正在等待锁定的数量;
Innodb_row_lock_time :从系统启动到现在锁定的总时间长度，单位 ms;
Innodb_row_lock_time_avg :每次等待所花平均时间;
Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间;
Innodb_row_lock_waits :从系统启动到现在总共等待的次数。

分析：例子中关闭自动死锁检测后，这里查询的到的结果表示：mysql运行到目前为止，发现有一个死锁等待，历史一共出现过3次，总共等待的时间为6639ms，平均时间2213ms；

4、应用层针对死锁的解决方案

当然，死锁的问题不能每次都依靠kill线程来完成，这是治标不治本的行为。我们应该尽量在应用端，也就是在编码的过程中避免，常用解决 方案如下：

( 1 ) 互 斥条件 ：一个资源同一个时刻只能被一个线程访问；
- 解决方案：互斥是锁的特性，无法去除，但是我们可以减少锁的冲突，减小锁粒度；
( 2 ) 请 求保持-占有等待 ：一个线程因请求资源而阻塞的时候，对已有的资源保持不放；
- 解决方案-破除无限 等待：
  - 统一分配资源：避免一个线程在一个锁内占用多个资源，如果多个，可以使用统一分配资源的方式来进行；
  - trylock()思想：使用超时锁try lock(timeout)代替使用内部锁，加不上就放弃；
( 3 ) 不 剥夺-不可抢占 ：一个线程获取的资源，在没有使用完之前，不能强行剥夺；
- 解决方案：可抢占 tryLock()思想；
( 4 ) 循环等待：多个线程循环等待资源；
- 解决方案： 顺序加锁：解决循环等待的问题