如何解决事务死锁？

Question

使用 'show engine innodb status' 命令，我看到 WordPress 有两个死锁。我想清除这些死锁，但我没有看到任何一个命令的活动进程（即“杀死”这些命令并希望强制回滚）。

我可以看到线程 ID、查询 ID 等等，但没有任何东西可以用来停止任何一个任务。

如何解决这个问题？

编辑：这是状态的（相关？）部分：

------------------------
LATEST DETECTED DEADLOCK
------------------------
110327 10:54:14
*** (1) TRANSACTION:
TRANSACTION 9FBA099E, ACTIVE 0 sec, process no 14207, OS thread id 1228433728 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 12505112, query id 909492800 juno....edu 129....54 wordpress_user updating
DELETE FROM wp_options WHERE option_name = ''_site_transient_timeout_theme_roots''
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4951009 page no 4 n bits 384 index `option_name` of table `wordpress_work`.`wp_options` trx id 9FBA099E lock_mode X waiting
Record lock, heap no 309 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 30; hex 5f736974655f7472616e7369656e745f74696d656f75745f7468656d655f; asc _site_transient_timeout_theme_; (total 35 bytes);
1: len 8; hex 0000000000002b6d; asc       +m;;

*** (2) TRANSACTION:
TRANSACTION 9FBA0995, ACTIVE 0 sec, process no 14207, OS thread id 1230031168 starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1248, 2 row lock(s)
MySQL thread id 12505095, query id 909492789 juno....edu 129.....54 wordpress_user updating
DELETE FROM wp_options WHERE option_name = ''_site_transient_timeout_theme_roots''
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 4951009 page no 4 n bits 384 index `option_name` of table `wordpress_work`.`wp_options` trx id 9FBA0995 lock_mode X locks rec but not gap
Record lock, heap no 309 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 30; hex 5f736974655f7472616e7369656e745f74696d656f75745f7468656d655f; asc   _site_transient_timeout_theme_; (total 35 bytes);
 1: len 8; hex 0000000000002b6d; asc       +m;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4951009 page no 4 n bits 384 index `option_name` of table     `wordpress_work`.`wp_options` trx id 9FBA0995 lock_mode X waiting
Record lock, heap no 309 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 30; hex 5f736974655f7472616e7369656e745f74696d656f75745f7468656d655f; asc   _site_transient_timeout_theme_; (total 35 bytes);
1: len 8; hex 0000000000002b6d; asc       +m;;

*** WE ROLL BACK TRANSACTION (1)

- ethrbunny

---

但是该查询不会通过“show processlist”显示。 - ethrbunny

我认为你需要关闭连接。 - Joe Phillips

这是最后一个已经由MySQL处理过的检测到的死锁，因此*** WE ROLL BACK TRANSACTION (1)。 - KCD

Answer 1

假设有下面这样的'innodb status'输出:

---TRANSACTION 0 0, not started, process no 1024, OS thread id 140386055603968
MySQL thread id 197, query id 771 localhost marc
show innodb status

你需要做的是

KILL QUERY 771

要杀死死锁的两个查询中的一个，这将杀死该查询，但保留连接处于打开状态。如果你想要关闭连接，那么你需要执行KILL 197。

- Marc B

---

在新版本的 MySQL 中应该是 show engine innodb status，我使用的是 5.6.10 版本，show innodb status 不是一个有效的命令。 - Robin Clowers

我遇到了类似的情况。这与 show 命令语法无关。因为 show processlist 没有显示任何与这些查询相关的连接，这意味着这些死锁事务是在存储引擎层面而不是 MySQL API 层面保留下来的。我发现唯一解决这类问题的方法是重新启动 MySQL 服务。 - Sender

在这个例子中，你应该杀掉线程ID为197的MySQL线程。 - witkacy26

这个答案完全是错误的。死锁检测是“瞬间”的，一旦被记录下来，就不需要再采取任何行动。该部分仅用于信息目的。 - jkavalik

在一些罕见的情况下，持有锁的线程可能会无限期地挂起，并要求在释放锁之前手动杀死它。这不是死锁通常发生的情况，但我曾经遇到过这种情况，除了死锁本身。 - Mahn

@Mahn 在死锁解决中，只有两个事务中的一个被终止，另一个继续并仍然持有其锁定，因此在您描述的情况下，事务“赢得”了死锁解决，但随后卡在其他地方或由于其他原因。 （当然可能存在错误，但这似乎更有可能） - jkavalik

Answer 2

使用 'show engine innodb status' 命令，我看到 WordPress 有两个死锁...请问如何解决？

我们发现 Java Hibernate 问题导致了锁定。我们通过分析以下输出找到了这些锁定：

show engine innodb status;

这里会输出大量信息，与此相关的部分在TRANSACTIONS部分。根据你的输出，相关的问题似乎是:

3 lock struct(s), heap size 1248, 2 row lock(s)
MySQL thread id 12505095, query id 909492789 juno....edu 129.....54 

对我们来说，# lock struct(s) 表明锁定了。为了杀掉它，您需要使用指定的“线程ID＃”执行--在这种情况下：

kill 12505095

这个方案同样适用于AWS MySQL RDS以及本地MySQL。

在我们的TRANSACTIONS部分，我们还可以看到以下内容:

---TRANSACTION 644793773, ACTIVE 21 sec
2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 217, OS thread handle 0x2aef097700, query id 1177 1.3.5.7 mpsp cleaning up

我们寻找2个锁结构和活动21秒的消息。

- Gray

Answer 3

我知道这是旧的，但通常当你看到这样的情况时，这是因为发生了死锁，触发死锁的应用程序已经很久以前就移动走了 - 死锁的受害者收到了警告并且可能失败、记录错误或重试，无论哪种方式都已经转向其他有生产力的事情。如果你正在编写软件，通常不需要做任何事情，只需查找死锁的原因并尝试避免未来的死锁。如果你只是使用软件（例如如果你不在WordPress工作），你可以报告死锁可能是一个bug。

- Geoffrey Wiseman