如何处理MySQL死锁

曾经终败给现在 2023-06-12 08:52 10阅读 0赞

个[死锁][Link 1]在MySQL发生在两个或多个事务相互持有和锁请求，创建依赖的循环。在交易系统中，死锁是生活中不可或缺的事实，并非完全可以避免的。InnoDB自动检测事务死锁，立即回滚事务并返回错误。它使用一个指标来选择最容易回滚的事务。尽管不必担心偶尔出现死锁，但频繁发生的事件需要引起注意。

在MySQL 5.6之前，只能使用SHOW ENGINE INNODB STATUS命令查看最新的死锁。但是，使用Percona Toolkit的[pt-deadlock-logger，][pt-deadlock-logger]您可以按给定的时间间隔从SHOW ENGINE INNODB STATUS中检索死锁信息，并将其保存到文件或表中以进行后期诊断。有关使用pt-deadlock-logger的更多信息，请参阅[本文][Link 2]。使用MySQL 5.6，您可以启用一个新变量[innodb\_print\_all\_deadlocks][innodb_print_all_deadlocks]，使InnoDB中的所有死锁都记录在mysqld错误日志中。

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

## 如何诊断MySQL死锁 ##

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

让我们看两个例子，看看**给出了什么信息**。范例1：

Vim

第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社区版本中，每个记录锁都有打印的记录内容：

Vim

<table style="margin-left:0px;"> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;"> 
 <div style="margin-left:0px;"> 
 <div style="margin-left:0px;"> 
 1 
 </div> 
 <div style="margin-left:0px;"> 
 2 
 </div> 
 <div style="margin-left:0px;"> 
 3 
 </div> 
 <div style="margin-left:0px;"> 
 4 
 </div> 
 <div style="margin-left:0px;"> 
 5 
 </div> 
 <div style="margin-left:0px;"> 
 6 
 </div> 
 <div style="margin-left:0px;"> 
 7 
 </div> 
 <div style="margin-left:0px;"> 
 8 
 </div> 
 <div style="margin-left:0px;"> 
 9 
 </div> 
 <div style="margin-left:0px;"> 
 10 
 </div> 
 <div style="margin-left:0px;"> 
 11 
 </div> 
 <div style="margin-left:0px;"> 
 12 
 </div> 
 <div style="margin-left:0px;"> 
 13 
 </div> 
 <div style="margin-left:0px;"> 
 14 
 </div> 
 <div style="margin-left:0px;"> 
 15 
 </div> 
 <div style="margin-left:0px;"> 
 16 
 </div> 
 <div style="margin-left:0px;"> 
 17 
 </div> 
 <div style="margin-left:0px;"> 
 18 
 </div> 
 <div style="margin-left:0px;"> 
 19 
 </div> 
 <div style="margin-left:0px;"> 
 20 
 </div> 
 <div style="margin-left:0px;"> 
 21 
 </div> 
 <div style="margin-left:0px;"> 
 22 
 </div> 
 <div style="margin-left:0px;"> 
 23 
 </div> 
 <div style="margin-left:0px;"> 
 24 
 </div> 
 <div style="margin-left:0px;"> 
 25 
 </div> 
 <div style="margin-left:0px;"> 
 26 
 </div> 
 <div style="margin-left:0px;"> 
 27 
 </div> 
 <div style="margin-left:0px;"> 
 28 
 </div> 
 <div style="margin-left:0px;"> 
 29 
 </div> 
 <div style="margin-left:0px;"> 
 30 
 </div> 
 <div style="margin-left:0px;"> 
 31 
 </div> 
 <div style="margin-left:0px;"> 
 32 
 </div> 
 <div style="margin-left:0px;"> 
 33 
 </div> 
 <div style="margin-left:0px;"> 
 34 
 </div> 
 <div style="margin-left:0px;"> 
 35 
 </div> 
 <div style="margin-left:0px;"> 
 36 
 </div> 
 <div style="margin-left:0px;"> 
 37 
 </div> 
 <div style="margin-left:0px;"> 
 38 
 </div> 
 <div style="margin-left:0px;"> 
 39 
 </div> 
 </div> </td> 
 <td style="vertical-align:top;"> 
 <div style="margin-left:0px;"> 
 <div style="margin-left:0px;"> 
 1 2014-10-11 10:41:12 7f6f912d7700 
 </div> 
 <div style="margin-left:0px;"> 
 2 *** (1) TRANSACTION: 
 </div> 
 <div style="margin-left:0px;"> 
 3 TRANSACTION 2164000, ACTIVE 27 sec starting index read 
 </div> 
 <div style="margin-left:0px;"> 
 4 mysql tables in use 1, locked 1 
 </div> 
 <div style="margin-left:0px;"> 
 5 LOCK WAIT 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1 
 </div> 
 <div style="margin-left:0px;"> 
 6 MySQL thread id 9, OS thread handle 0x7f6f91296700, query id 87 localhost ro ot updating 
 </div> 
 <div style="margin-left:0px;"> 
 7 update t1 set name = 'b' where id = 3 
 </div> 
 <div style="margin-left:0px;"> 
 8 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: 
 </div> 
 <div style="margin-left:0px;"> 
 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 
 </div> 
 <div style="margin-left:0px;"> 
 10 Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bit s 0 
 </div> 
 <div style="margin-left:0px;"> 
 11 0: len 4; hex 80000003; asc ;; 
 </div> 
 <div style="margin-left:0px;"> 
 12 1: len 6; hex 000000210521; asc ! !;; 
 </div> 
 <div style="margin-left:0px;"> 
 13 2: len 7; hex 180000122117cb; asc ! ;; 
 </div> 
 <div style="margin-left:0px;"> 
 14 3: len 4; hex 80000008; asc ;; 
 </div> 
 <div style="margin-left:0px;"> 
 15 4: len 1; hex 63; asc c;; 
 </div> 
 <div style="margin-left:0px;"> 
 16 
 </div> 
 <div style="margin-left:0px;"> 
 17 *** (2) TRANSACTION: 
 </div> 
 <div style="margin-left:0px;"> 
 18 TRANSACTION 2164001, ACTIVE 18 sec starting index read 
 </div> 
 <div style="margin-left:0px;"> 
 19 mysql tables in use 1, locked 1 
 </div> 
 <div style="margin-left:0px;"> 
 20 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1 
 </div> 
 <div style="margin-left:0px;"> 
 21 MySQL thread id 10, OS thread handle 0x7f6f912d7700, query id 88 localhost r oot updating 
 </div> 
 <div style="margin-left:0px;"> 
 22 update t1 set name = 'c' where id = 2 
 </div> 
 <div style="margin-left:0px;"> 
 23 *** (2) HOLDS THE LOCK(S): 
 </div> 
 <div style="margin-left:0px;"> 
 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 
 </div> 
 <div style="margin-left:0px;"> 
 25 Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bit s 0 
 </div> 
 <div style="margin-left:0px;"> 
 26 0: len 4; hex 80000003; asc ;; 
 </div> 
 <div style="margin-left:0px;"> 
 27 1: len 6; hex 000000210521; asc ! !;; 
 </div> 
 <div style="margin-left:0px;"> 
 28 2: len 7; hex 180000122117cb; asc ! ;; 
 </div> 
 <div style="margin-left:0px;"> 
 29 3: len 4; hex 80000008; asc ;; 
 </div> 
 <div style="margin-left:0px;"> 
 30 4: len 1; hex 63; asc c;; 
 </div> 
 <div style="margin-left:0px;"> 
 31 
 </div> 
 <div style="margin-left:0px;"> 
 32 *** (2) WAITING FOR THIS LOCK TO BE GRANTED: 
 </div> 
 <div style="margin-left:0px;"> 
 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 
 </div> 
 <div style="margin-left:0px;"> 
 34 Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bit s 0 
 </div> 
 <div style="margin-left:0px;"> 
 35 0: len 4; hex 80000002; asc ;; 
 </div> 
 <div style="margin-left:0px;"> 
 36 1: len 6; hex 000000210520; asc ! ;; 
 </div> 
 <div style="margin-left:0px;"> 
 37 2: len 7; hex 17000001c510f5; asc ;; 
 </div> 
 <div style="margin-left:0px;"> 
 38 3: len 4; hex 80000009; asc ;; 
 </div> 
 <div style="margin-left:0px;"> 
 39 4: len 1; hex 62; asc b;; 
 </div> 
 </div> </td> 
 </tr> 
 </tbody> 
</table>

第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。

Shell

您可以在[此处][Link 3]找到events\_statements\_history表的更多详细信息。

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

Shell

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

## 如何避免MySQL死锁 ##

了解死锁后，我们可以采取一些措施来消除死锁。

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

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

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

[Link 1]: https://dev.mysql.com/doc/refman/5.7/en/innodb-deadlocks.html
[pt-deadlock-logger]: https://www.percona.com/doc/percona-toolkit/LATEST/pt-deadlock-logger.html
[Link 2]: https://www.percona.com/doc/percona-toolkit/2.2/pt-deadlock-logger.html
[innodb_print_all_deadlocks]: https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_print_all_deadlocks
[Link 3]: https://dev.mysql.com/doc/refman/5.7/en/events-statements-history-table.html
[Percona Server]: https://www.percona.com/software/percona-server