如何清除交易僵局? [英] How can I clear a transaction deadlock?

问题描述

使用'show engine innodb status'，我看到wordpress有两个死锁.我想清除这些错误，但是我没有看到任何一个有效的cmd进程(例如，某些杀死"并希望强制回滚的cmd).

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).

我可以看到线程ID，查询ID等，但是我无法用来停止任何一项工作.

I can see thread ids, query ids, etc but nothing that I can use to stop either job.

有关如何解决此问题的建议?

Suggestions on how to resolve this?

这是状态的(相关?)部分:

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)

推荐答案

给出一些"innodb status"输出，如下所示:

Given some 'innodb status' output like this:

---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.

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.

这篇关于如何清除交易僵局?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！