中秋遇到mysql死锁怎么办

标签：事务中秋死锁 innodb mysql 等待 wait

文章目录

前言
一、什么是死锁
二、死锁的产生条件
三、死锁示例
四、死锁的分析和查看

1.查看最近1个死锁信息
2.查看正在运行中的事务信息
3.查看加锁信息

五、死锁的内部处理方案

1.死锁探测机制
2.锁等待超时机制

六、手动释放锁

1.表级锁手动释放
2.行级锁手动释放

七、死锁的优化策略
总结

前言

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

一、什么是死锁

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

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

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

二、死锁的产生条件

发生死锁的必要条件有4个，分别为互斥条件、不可剥夺条件、请求与保持条件和循环等待条件。

中秋遇到mysql死锁怎么办_死锁

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

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

三、死锁示例

表和数据准备：

DROP TABLE if EXISTS user;

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `account` varchar(30) DEFAULT NULL COMMENT '账号',
  `name` varchar(30) DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_account` (`account`),
  KEY `ik_name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

-- 英文名，主要是更容易验证间隙锁
INSERT INTO `user` (`id`,`account`,`name`, `age`, `email`) VALUES (3, '000003','Andi', 12, '[email protected]');
INSERT INTO `user` (`id`, `account`,`name`, `age`, `email`) VALUES (10,'000010', 'Jack', 20, '[email protected]');
INSERT INTO `user` (`id`, `account`,`name`, `age`, `email`) VALUES (20, '000020','Tom', 30, '[email protected]');
INSERT INTO `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 in set (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 in set (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 in set (0.00 sec)
 
mysql> SELECT * FROM `user` WHERE id = 3 FOR UPDATE;
1213 - Deadlock found when trying to get lock; try restarting transaction

循环等待示意图：

中秋遇到mysql死锁怎么办_mysql_02

四、死锁的分析和查看

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

show engine innodb status;

其中和死锁相关的信息：

中秋遇到mysql死锁怎么办_java_03

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

select * from information_schema.innodb_trx;

中秋遇到mysql死锁怎么办_java_04

说明：

trx_state中的LOCK WAIT表示出现锁等待
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死锁怎么办_java_05

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

等待图（wait-for graph）回路检测
等待的事务列表超过200个认为是死锁
等待的事务持有锁的总数超过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的后面

来看一个例子：

中秋遇到mysql死锁怎么办_java_06

通过 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个节点。根据等待关系画出等待图：

中秋遇到mysql死锁怎么办_mysql_07

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

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

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

中秋遇到mysql死锁怎么办_死锁_08

注意⚠️：innodb_deadlock_detect是一个全局变量，在进行变量设置的时候需要加上global。

查看是否开启死锁探测：

mysql> show global variables like 'innodb_deadlock_detect';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_deadlock_detect | ON    |
+------------------------+-------+
1 row in set (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 in set (0.01 sec)

2.锁等待超时机制

通常来说，应该启用死锁检测，并且在应用程序中尽量避免产生死锁，同时对死锁进行相应的处理，例如重新开始事务。

只有在确认死锁检测影响了系统的性能，并且禁用死锁检测不会带来负面影响时，可以尝试关闭 innodb_deadlock_detect 选项。另外，如果禁用了 InnoDB 死锁检测，需要调整参数 innodb_lock_wait_timeout 的值，以满足实际的需求。

中秋遇到mysql死锁怎么办_mysql_09

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

中秋遇到mysql死锁怎么办_java_10

查看变量 innodb_lock_wait_timeout ：

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

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

修改锁超时等待时长：

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

//修改session级别变量
mysql> set  innodb_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列）

kill id

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、通过哪些手段可以减少死锁的产生

标签：事务,中秋,死锁,innodb,mysql,等待,wait
From： https://blog.51cto.com/u_15905482/5919946