中秋遇到mysql死锁怎么办

文章目录

前言

中秋放假期间，线上mysql数据库突然提示出现死锁异常怎么办？是不是内心突然慌的一批，假期再也不能愉快的玩耍了。
莫慌莫慌，今天老万教你遇到了mysql死锁应该怎么办。

一、什么是死锁

所谓死锁：是指多个事务在并发执行过程中由于相互持有对方需要的锁，都在等待资源变的可用而不会主动释放自身持有的锁，从而导致循环等待的情况。

通常表级锁不会产生死锁，所以解决死锁主要还是针对于最常用的InnoDB。

官方文档：
Innodb死锁:https://dev.mysql.com/doc/refman/8.0/en/innodb-deadlocks.html

二、死锁的产生条件

发生死锁的必要条件有4个， 分别为

互斥条件、不可剥夺条件、请求与保持条件和循环等待条件

。

从这几点来看，mysql中的死锁产生条件和java程序中死锁产生条件是一致的。

但是java程序中的死锁往往会产生更严重的后果，而mysql中的死锁由于数据库内部的死锁处理机制，一般不会产生很严重的影响。

三、死锁示例

表和数据准备：

DROPTABLEifEXISTSuser;CREATETABLE`user`(`id`bigint(20)NOTNULLAUTO_INCREMENTCOMMENT'主键ID',`account`varchar(30)DEFAULTNULLCOMMENT'账号',`name`varchar(30)DEFAULTNULLCOMMENT'姓名',`age`int(11)DEFAULTNULLCOMMENT'年龄',`email`varchar(50)DEFAULTNULLCOMMENT'邮箱',PRIMARYKEY(`id`),UNIQUEKEY`uk_account`(`account`),KEY`ik_name`(`name`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;-- 英文名，主要是更容易验证间隙锁INSERTINTO`user`(`id`,`account`,`name`,`age`,`email`)VALUES(3,'000003','Andi',12,'[email protected]');INSERTINTO`user`(`id`,`account`,`name`,`age`,`email`)VALUES(10,'000010','Jack',20,'[email protected]');INSERTINTO`user`(`id`,`account`,`name`,`age`,`email`)VALUES(20,'000020','Tom',30,'[email protected]');INSERTINTO`user`(`id`,`account`,`name`,`age`,`email`)VALUES(30,'000030','Tom',60,'[email protected]');

事务A：

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
 
mysql> SELECT * FROM `user` WHERE id=3 FOR UPDATE;
+----+---------+------+-----+--------------+
|id| account | name | age | email        |
+----+---------+------+-----+--------------+
|3| 000003  | Andi |12|[email protected] |
+----+---------+------+-----+--------------+
1 row inset(0.01 sec)
 
mysql> SELECT * FROM `user` WHERE id=10 FOR UPDATE;
+----+---------+------+-----+---------------+
|id| account | name | age | email         |
+----+---------+------+-----+---------------+
|10| 000010  | Jack |20|[email protected] |
+----+---------+------+-----+---------------+
1 row inset(2.57 sec)

事务B：

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
 
mysql> SELECT * FROM `user` WHERE id=10 FOR UPDATE;
+----+---------+------+-----+---------------+
|id| account | name | age | email         |
+----+---------+------+-----+---------------+
|10| 000010  | Jack |20|[email protected] |
+----+---------+------+-----+---------------+
1 row inset(0.00 sec)
 
mysql> SELECT * FROM `user` WHERE id=3 FOR UPDATE;1213 - Deadlock found when trying to get lock; try restarting transaction

循环等待示意图：

四、死锁的分析和查看

1.查看最近1个死锁信息

showengineinnodbstatus;

其中和死锁相关的信息：

2.查看正在运行中的事务信息

select*from information_schema.innodb_trx;

说明：

1. trx_state中的LOCK WAIT表示出现锁等待
2. trx_query中可以查看导致锁等待的sql语句

3.查看加锁信息

-- 查看加锁信息（MySQL5.X）select*from information_schema.innodb_locks;-- 查看锁等待（MySQL5.X）select*from information_schema.innodb_lock_waits;--查看加锁信息（MySQL8.0）SELECT*FROM performance_schema.data_locks;--查看锁等待（MySQL8.0）SELECT*FROM performance_schema.data_lock_waits;

五、死锁的内部处理方案

mysql内部采用2种机制解决死锁问题：

• 死锁探测机制 innodb_deadlock_detect 默认开启
• 锁等待超时机制 innodb_lock_wait_timeout

1.死锁探测机制

当启用死锁检测(默认情况下)时，InnoDB 会自动检测事务死锁并回滚一个或多个事务以打破死锁。InnoDB 尝试选择要回滚的小事务，其中事务的大小由插入、更新或删除的行数决定。如果使用

innodb_deadlock_detect 

变量禁用死锁检测，则 InnoDB 依赖于

innodb_lock_wait_timeout

设置，在发生死锁的情况下回滚事务。

当检测到死锁后，就会出现下面这个提示：

mysql中的死锁探测机制有3种判定方式：

1. 等待图（wait-for graph）回路检测
2. 等待的事务列表超过200个认为是死锁
3. 等待的事务持有锁的总数超过1,000,000

官网说明：If the LATEST DETECTED DEADLOCK section of InnoDB Monitor output includes a message stating

TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION

, this indicates that the number of transactions on the wait-for list has reached a limit of 200. A wait-for list that exceeds 200 transactions is treated as a deadlock and the transaction attempting to check the wait-for list is rolled back. The same error may also occur if the locking thread must look at more than 1,000,000 locks owned by transactions on the wait-for list.

除了超时机制，当前数据库都普遍采用等待图（

wait-for graph

）的方式来进行死锁检测。

wait-for graph要求数据库保存以下两种信息:

• 锁的信息链表
• 事务等待链表

通过上述链表可以构造出一张图，而在这个图中若存在回路，就代表存在死锁，因此资源间相互发生等待。在 wait-for graph中,事务为图中的节点。而在图中,事务T1指向T2边的定义为:

• 事务T1等待事务T2所占用的资源
• 事务T1最终等待T2所占用的资源,也就是事务之间在等待相同的资源,而事务T1发生在事务T2的后面

来看一个例子：

通过 Transaction Wait Lists中可以看到共有4个事务t1、t2、t3、t4。
通过Lock List列表，可以看到加锁的等待顺序。
在row1上，t1:s等待t2:x释放独占锁，才能添加共享锁。
在row2上，t1:s和t4:s持有相同的共享锁。
t2:x需要等待t1:s和t4:s释放共享锁后，才能添加独占锁。
t3:x需要等待t1:s和t4:s释放共享锁，并且t2:x释放独占锁后，才能添加独占锁。

故在wait-for graph中应有4个节点。根据等待关系画出等待图：

通过上图可以发现存在回路(t1,t2)，因此存在死锁。可以发现wait-for graph是一种较为主动的死锁检测机制，在每个事务请求锁并发生等待时都会判断是否存在回路，若存在则有死锁，通常来说InnoDB存储引擎选择回滚undo量最小的事务。

关闭死锁探测：
对于高并发的系统，当大量线程等待同一个锁时，死锁检测可能会导致性能的下降。此时，如果禁用死锁检测，而改为依靠参数 innodb_lock_wait_timeout 执行发生死锁时的事务回滚可能会更加高效。

在 MySQL 8.0 中，增加了一个新的动态变量：innodb_deadlock_detect，可以用于控制 InnoDB 是否执行死锁检测。该参数的默认值为 ON，即打开死锁检测。

注意⚠️：

innodb_deadlock_detect

是一个全局变量，在进行变量设置的时候需要加上

global

。

查看是否开启死锁探测：

mysql> show global variables like 'innodb_deadlock_detect';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_deadlock_detect | ON    |
+------------------------+-------+
1 row inset(0.02 sec)

关闭死锁检测：

mysql>set global innodb_deadlock_detect=off;
Query OK, 0 rows affected (0.01 sec)

检测是否成功关闭：

mysql> show global variables like 'innodb_deadlock_detect';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_deadlock_detect | OFF   |
+------------------------+-------+
1 row inset(0.01 sec)

2.锁等待超时机制

通常来说，应该启用死锁检测，并且在应用程序中尽量避免产生死锁，同时对死锁进行相应的处理，例如重新开始事务。
只有在确认死锁检测影响了系统的性能，并且禁用死锁检测不会带来负面影响时，可以尝试关闭

innodb_deadlock_detect

选项。另外，如果禁用了 InnoDB 死锁检测，需要调整参数

innodb_lock_wait_timeout 

的值，以满足实际的需求。

默认的锁等待超时时间是50s，当发生超时后，就出现下面这个提示：

查看变量

innodb_lock_wait_timeout 

：

//查看全局变量
mysql> show global variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout |50|
+--------------------------+-------+
1 row inset(0.02 sec)

//查看session级别变量
mysql> show  variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout |50|
+--------------------------+-------+
1 row inset(0.02 sec)

修改锁超时等待时长：

//修改全局变量
mysql>set global innodb_lock_wait_timeout=30;
Query OK, 0 rows affected (0.00 sec)

//修改session级别变量
mysql>setinnodb_lock_wait_timeout=30;
Query OK, 0 rows affected (0.00 sec)

再次查看变量，发现超时时间都变为30s。

注意⚠️：

innodb_lock_wait_timeout 

参数分为session级别和global级别，如果发现锁等待超时时间一直没有设置成功，检测参数级别是否正确。

六、手动释放锁

手动解除正在死锁的状态有两种方法：

1.表级锁手动释放

1.查询是否锁表

show OPEN TABLES where In_use >0;

2.查询进程（如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程）

show processlist

3.杀死进程id（就是上面命令的id列）

killid

2.行级锁手动释放

1.查看下正在等待锁的事务

SELECT*FROM INFORMATION_SCHEMA.INNODB_TRX;

其中

trx_state

中的

LOCK WAIT

表示出现锁等待。

2.杀死进程id（就是上面命令的

trx_mysql_thread_id

列）

kill 线程ID

七、死锁的优化策略

Mysql中的死锁并不可怕，因为Mysql会通过内部的

死锁探测机制

和

锁等待超时机制

自动回滚事务释放锁。

除非它们非常频繁，以至于您根本无法运行某些事务

。

最简单的死锁异常处理方式：

重试

，可以通过捕捉死锁异常，进行指定次数的重试操作。

死锁的优化：

空间维度

和

时间维度

空间维度：

减少锁的范围，保持加锁顺序

• 采用乐观锁，避免加锁，类似java中的cas机制
• 尽量通过索引来检索，缩小锁的范围
• 统一事务中数据操作的顺序，避免出现循环等待
• 不要对不存在的记录执行update、delete操作，避免出现无意义的间隙锁

时间维度：

减少加锁时间

• 控制事务的大小，避免大事务长时间持有锁
• 涉及事务加锁操作，尽量放在事务的最后执行
• 尽可能使用低级别的事务隔离机制

总结

本文主要是对mysql的死锁相关问题进行了介绍。
1、死锁产生的原因
2、为什么mysql中的死锁一般不会产生非常严重的影响
3、mysql内部对死锁的两种处理机制：死锁探测机制

innodb_deadlock_detect

和锁等待超时机制

innodb_lock_wait_timeout

4、怎么查看锁的相关信息，怎么分析死锁
5、怎么手动释放锁
6、通过哪些手段可以减少死锁的产生