如何处理MySQL死锁

背景

一个死锁在MySQL发生在两个或多个事务相互持有和锁请求，创建依赖的循环。在交易系统中，死锁是生活中不可或缺的事实，并非完全可以避免的。

写在前面
来自percona官方文档https://www.percona.com/blog/2014/10/28/how-to-deal-with-mysql-deadlocks/
link

Mysql5.6

只能使用SHOW ENGINE INNODB STATUS命令查看最新的死锁。
但是，使用Percona Toolkit的pt-deadlock-logger，
您可以按给定的时间间隔从SHOW ENGINE INNODB STATUS中检索死锁信息，
并将其保存到文件或表中以进行后期诊断。

使用MySQL 5.6，您可以启用一个新变量innodb_print_all_deadlocks，使InnoDB中的所有死锁都记录在mysqld错误日志中。
(5.7.26默认OFF)

在进行所有诊断之前，最重要的做法是让应用程序捕获死锁错误（MySQL错误编号1213）并通过重试事务进行处理。

如何诊断MySQL死锁

show engine innodb status的局限性

仅显示在两个事务中执行的最后一条语句

MySQL死锁可能涉及两个以上的事务，但是“最新检测到的DEADLOCK”部分仅显示最后两个事务。此外，它仅显示在两个事务中执行的最后一条语句，并锁定创建周期的两个事务的锁。缺少的是可能已经真正获得了锁的早期语句。我将展示一些有关如何收集遗漏语句的提示。

例子

1 141013 6:06:22
2 *** (1) TRANSACTION:
3 TRANSACTION 876726B90, ACTIVE 7 sec setting auto-inc lock
4 mysql tables in use 1, locked 1
5 LOCK WAIT 9 lock struct(s), heap size 1248, 4 row lock(s), undo log entries 4
6 MySQL thread id 155118366, OS thread handle 0x7f59e638a700, query id 87987781416 localhost msandbox update
7 INSERT INTO t1 (col1, col2, col3, col4) values (10, 20, 30, 'hello')
8 *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
9 TABLE LOCK table `mydb`.`t1` trx id 876726B90 lock mode AUTO-INC waiting
10 *** (2) TRANSACTION:
11 TRANSACTION 876725B2D, ACTIVE 9 sec inserting
12 mysql tables in use 1, locked 1
13 876 lock struct(s), heap size 80312, 1022 row lock(s), undo log entries 1002
14 MySQL thread id 155097580, OS thread handle 0x7f585be79700, query id 87987761732 localhost msandbox update
15 INSERT INTO t1 (col1, col2, col3, col4) values (7, 86, 62, "a lot of things"), (7, 76, 62, "many more")
16 *** (2) HOLDS THE LOCK(S):
17 TABLE LOCK table `mydb`.`t1` trx id 876725B2D lock mode AUTO-INC
18 *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
19 RECORD LOCKS space id 44917 page no 529635 n bits 112 index `PRIMARY` of table `mydb`.`t2` trx id 876725B2D lock mode S locks rec but not gap waiting
20 *** WE ROLL BACK TRANSACTION (1)

解析

第1行给出了发生死锁的时间。如果您的应用程序代码捕获并记录了应该发生的死锁错误，则可以将此时间戳与应用程序日志中的死锁错误时间戳进行匹配。您将拥有回滚的事务。从那里，检索该事务中的所有语句。

第3和11行记录交易号和激活时间。如果您定期记录SHOW ENGINE INNODB STATUS输出（这是一种很好的做法），则可以使用事务号搜索以前的输出，以希望从同一事务中看到更多的语句。ACTIVE秒可以提示事务是单语句还是多语句。

第4和12行，正在使用和锁定的表仅针对当前语句。因此，使用1个表并不一定意味着该事务仅涉及1个表。

第5和13行，这值得一提，因为它告诉您事务已进行了多少更改，即“撤消日志条目”，以及它持有多少行锁，即“行锁”。此信息暗示了交易的复杂性。

第6和14行记录线程ID，连接主机和连接用户。如果您将不同的MySQL用户用于不同的应用程序功能，这是另一个好的做法，那么您可以根据连接的主机和用户来判断事务来自哪个应用程序区域。

第9行，对于第一个事务，它仅显示它正在等待的锁，在这种情况下，是表t1上的AUTO-INC锁。其他可能的值是S（用于共享锁）和X（用于排他式有无锁）。

第16和17行针对第二笔交易，显示了它持有的锁，在这种情况下，是TRANSACTION（1）等待的AUTO-INC锁。

第18和19行显示了TRANSACTION（2）正在等待哪个锁。在这种情况下，它是另一个表的主键上的共享而不是间隙记录锁定。InnoDB中共享记录锁定的来源很少：
1）使用SELECT…LOCK IN SHARE MODE
2）在外键引用记录上使用
3）INSERT INTO…SELECT，源表上的共享锁定
当前trx（2）语句是对表t1的简单插入，因此消除了1和3。通过检查SHOW CREATE TABLE t1，您可以确认S锁是由于对父表t2的外键约束所致。

例2：在MySQL社区版本中，每个记录锁都有打印的记录内容：

1 2014-10-11 10:41:12 7f6f912d7700
2 *** (1) TRANSACTION:
3 TRANSACTION 2164000, ACTIVE 27 sec starting index read
4 mysql tables in use 1, locked 1
5 LOCK WAIT 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
6 MySQL thread id 9, OS thread handle 0x7f6f91296700, query id 87 localhost ro ot updating
7 update t1 set name = 'b' where id = 3
8 *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
9 RECORD LOCKS space id 1704 page no 3 n bits 72 index `PRIMARY` of table `tes t`.`t1` trx id 2164000 lock_mode X locks rec but not gap waiting
10 Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bit s 0
11 0: len 4; hex 80000003; asc ;;
12 1: len 6; hex 000000210521; asc ! !;;
13 2: len 7; hex 180000122117cb; asc ! ;;
14 3: len 4; hex 80000008; asc ;;
15 4: len 1; hex 63; asc c;;
16
17 *** (2) TRANSACTION:
18 TRANSACTION 2164001, ACTIVE 18 sec starting index read
19 mysql tables in use 1, locked 1
20 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
21 MySQL thread id 10, OS thread handle 0x7f6f912d7700, query id 88 localhost r oot updating
22 update t1 set name = 'c' where id = 2
23 *** (2) HOLDS THE LOCK(S):
24 RECORD LOCKS space id 1704 page no 3 n bits 72 index `PRIMARY` of table `tes t`.`t1` trx id 2164001 lock_mode X locks rec but not gap
25 Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bit s 0
26 0: len 4; hex 80000003; asc ;;
27 1: len 6; hex 000000210521; asc ! !;;
28 2: len 7; hex 180000122117cb; asc ! ;;
29 3: len 4; hex 80000008; asc ;;
30 4: len 1; hex 63; asc c;;
31
32 *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
33 RECORD LOCKS space id 1704 page no 3 n bits 72 index `PRIMARY` of table `tes t`.`t1` trx id 2164001 lock_mode X locks rec but not gap waiting
34 Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bit s 0
35 0: len 4; hex 80000002; asc ;;
36 1: len 6; hex 000000210520; asc ! ;;
37 2: len 7; hex 17000001c510f5; asc ;;
38 3: len 4; hex 80000009; asc ;;
39 4: len 1; hex 62; asc b;;

第9行和第10行：“空间ID”是表空间ID，“页面号”给出了记录锁在表空间中位于哪一页。“ n位”不是页面偏移量，而是锁定位图中的位数。页面偏移量是第10行上的“堆号”，

第11〜15行：以十六进制数字显示记录数据。字段0是群集索引（主键）。忽略最高位，值为3。字段1是上次修改该记录的交易的交易ID，十进制值为2164001，即交易（2）。字段2是回滚指针。从字段3开始是其余的行数据。字段3是整数列，值为8。字段4是带有字符’c’的字符串列。通过读取数据，我们确切地知道哪一行被锁定以及当前值是多少。

我们还可以从分析中学到什么？

由于大多数MySQL死锁发生在两个事务之间，因此我们可以基于该假设开始分析。在示例1中，trx（2）正在等待共享锁，因此trx（1）在表t2的主键记录上持有共享或互斥锁。假设col2是外键列，通过检查trx（1）的当前语句，我们知道它不需要相同的记录锁定，因此它必须是trx（1）中的某些先前语句需要S或X锁定（ s）在t2的PK记录上。Trx（1）在7秒内仅进行了4行更改。然后，您了解了trx（1）的一些特征：它进行了大量处理，但进行了一些更改；更改涉及表t1和t2，将单个记录插入到t2。此信息与其他数据结合可以帮助开发人员找到交易。

我们还能在哪里找到交易的先前报表？

以下是提取事务历史记录的辅助查询，其中<PROCESSID>是有问题的连接的ID。

SELECT * FROM performance_schema.events_statements_history 
WHERE thread_id = (SELECT THREAD_ID FROM performance_schema.threads 
WHERE PROCESSLIST_ID = <PROCESSID>) \G

您可以在此处找到events_statements_history表的更多详细信息。

除了应用程序日志和先前的SHOW ENGINE INNODB STATUS输出之外，您还可以利用binlog，slow日志和/或常规查询日志。对于binlog，如果binlog_format = statement，则每个binlog事件将具有thread_id。仅已提交的事务记录到binlog中，因此我们只能在binlog中查找Trx（2）。在示例1的情况下，我们知道何时发生死锁，并且知道Trx（2）在9秒钟前启动。我们可以在正确的binlog文件上运行mysqlbinlog，并查找thread_id = 155097580的语句。然后将这些语句与应用程序代码交叉引用进行确认始终是一件好事。

$ mysqlbinlog -vvv --start-datetime=“2014-10-13 6:06:12” --
stop-datatime=“2014-10-13 6:06:22” 
mysql-bin.000010 > binlog_1013_0606.out

使用Percona Server 5.5及更高版本，您可以设置log_slow_verbosity在慢日志中包括InnoDB事务ID。然后，如果long_query_time = 0，则可以捕获所有语句，包括回滚到慢日志文件中的语句。在常规查询日志中，包含线程ID，该线程ID可用于查找相关语句。

如何避免MySQL死锁

更改应用程序。在某些情况下，您可以通过将一个长事务分成较小的事务来大大减少死锁的发生，因此锁会更快地释放。在其他情况下，死锁上升的原因是两个事务以一个不同的顺序接触一个或多个表中的同一组数据。然后更改它们以相同的顺序访问数据，换句话说，将访问序列化。这样，当事务同时发生时，您将拥有锁等待而不是死锁。

更改表架构，例如删除外键约束以分离两个表，或添加索引以最小化扫描和锁定的行。

在间隙锁定的情况下，您可以将事务隔离级别更改为读取已提交的会话或事务以避免它。但是，会话或事务的二进制日志格式必须是ROW或MIXED。(RC+row避免间隙锁gap)

文章来自percona官网2014年，需要我们自行测试

本文说明，主要技术内容来自互联网技术大佬的分享，还有一些自我的加工(仅仅起到注释说明的作用)。如有相关疑问，请留言，将确认之后，执行侵权必删​