How can I clear a transaction deadlock?

Question

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

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

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)

Answer 1

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

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.

Answer 2

Using 'show engine innodb status' I see that wordpress has two deadlocks... Suggestions on how to resolve this?

We were seeing Java hibernate issues causing stuck locks. We found the locks by combing trough the output from:

show engine innodb status;

This spits out a crap-ton of information. The relevant section is in the TRANSACTIONS section. In your output the relevant problem seems to be:

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:

kill 12505095

This worked on AWS MySQL RDS as well as local 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 sec messages.

Answer 3

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.