MySQL 如何清除事务死锁？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5462249/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I clear a transaction deadlock?
提问by ethrbunny
Using 'show engine innodb status' I see that wordpress has two deadlocks. I'd like to clear these up but I don't see an active process for either of these cmds (IE something to 'kill' and hopefully force a rollback).
使用 'show engine innodb status' 我看到 wordpress 有两个死锁。我想清除这些，但我没有看到这些 cmd 中的任何一个的活动进程（IE 某些东西可以“杀死”并希望强制回滚）。
I can see thread ids, query ids, etc but nothing that I can use to stop either job.
我可以看到线程 id、查询 id 等，但我不能用来停止任何一项工作。
Suggestions on how to resolve this?
有关如何解决此问题的建议？
EDIT: Here's the (relevant?) portion of the status:
编辑：这是状态的（相关？）部分：
------------------------
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)
回答by Marc B
Given some 'innodb status' output like this:
给出一些像这样的“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
you'd want to do
你想做
KILL QUERY 771
to kill one of the two queries that are deadlocked. That'll kill the query, but leave the connection open. if you want to kill the connection, then you'd do KILL 197.
杀死死锁的两个查询之一。这将终止查询，但保持连接打开。如果你想终止连接，那么你会这样做KILL 197。
回答by Gray
Using 'show engine innodb status' I see that wordpress has two deadlocks... Suggestions on how to resolve this?
使用 'show engine innodb status' 我看到 wordpress 有两个死锁...关于如何解决这个问题的建议？
Thought I'd provide some more information which would have helped us solve a similar problem. We were seeing Java hibernate issues causing stuck locks. We found the locks by combing trough the output from:
以为我会提供更多信息来帮助我们解决类似的问题。我们看到 Java 休眠问题导致锁定锁定。我们通过梳理以下输出找到了锁：
show engine innodb status;
This spits out a crap-ton of information. The relevant section is in the TRANSACTIONSsection. In your output the relevant problem seems to be:
这会吐出大量信息。相关部分在该TRANSACTIONS部分。在您的输出中，相关问题似乎是：
3 lock struct(s), heap size 1248, 2 row lock(s)
MySQL thread id 12505095, query id 909492789 juno....edu 129.....54
For us it was the # lock struct(s)that indicated a stuck lock. To kill it you need to execute by using the "thread id #" specified -- in this case:
对我们来说，# lock struct(s)这表明锁被卡住了。要杀死它，您需要使用指定的“线程 ID #”来执行——在这种情况下：
kill 12505095
This worked on AWS MySQL RDS as well as local MySQL.
这适用于 AWS MySQL RDS 以及本地 MySQL。
In our TRANSACTIONS section we also see the following:
在我们的交易部分，我们还看到以下内容：
---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
We look for both the 2 lock struct(s)and the ACTIVE 21 secmessages.
我们同时寻找消息2 lock struct(s)和ACTIVE 21 sec消息。
回答by Geoffrey Wiseman
I know this is old, but typically when you see something like this it's because a deadlock occurred and the app that triggered the deadlock has long since moved on -- the victim of the deadlock got warned and either failed, or logged an error or retried, and either way has moved on to other productive things. You usually don't need to do anything other than look into the cause of the deadlock and try and avoid future deadlocks, if you're writing the software. If you're just using the software (e.g. Wordpress if you don't work at Wordpress), you could report the deadlock as a possible bug.
我知道这是旧的，但通常当你看到这样的事情时，这是因为发生了死锁，并且触发死锁的应用程序早已运行——死锁的受害者得到警告，要么失败，要么记录错误或重试，并且无论哪种方式都已转移到其他富有成效的事情上。如果您正在编写软件，除了查看死锁的原因并尝试避免未来的死锁之外，您通常不需要做任何事情。如果您只是使用该软件（例如，如果您不在 Wordpress 工作，则可以使用 Wordpress），您可以将死锁报告为可能的错误。
