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)

This is the last detected deadlock that was already dealt to by mysql, hence `*** WE ROLL BACK TRANSACTION (1)` — KCD, Dec 11 '13 at 21:53

score 31 · Accepted Answer · answered Mar 28 '11 at 18:08

31

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.

answered Mar 28 '11 at 18:08

Marc B

356,200
43
426
500

6

This should be `show engine innodb status` in newer versions of mysql, I'm on 5.6.10 and `show innodb status` is not a valid command. – Robin Clowers Aug 22 '13 at 22:56
4

I met similar situation. It's not about the `show` command syntax. Since `show processlist` doesn't show any connection relate to these queries, it means these deadlock transactions are retain in the level of the storage engine, not the level of the MySQL API. The only way I found to solve things like this is to restart the service of MySQL. – Sender Sep 05 '13 at 15:24
4

You should kill 'MySQL thread id' which is 197 in this example. – witkacy26 Oct 27 '15 at 15:14
6

This answer is just wrong. The deadlock detection is "instantaneous" and when it is logged, no more actions are needed. The section is only for informative purposes. – jkavalik Nov 05 '15 at 09:32
2

@jkavalik in some rare cases a thread holding a lock can hang indefinitely and require to be manually killed before the lock is freed again. It's *not* what usually happens in a deadlock, but I've had it happen in addition to the deadlock itself. – Mahn Jul 06 '21 at 12:35
1

@Mahn in deadlock resolution only one of the two transactions is killed, the other one continues and still holds its locks, so imho in the case you describe the transaction "won" the deadlock resolution but then hang on something else or for some other reason. (ofc there might be a bug there but this seems more probable) – jkavalik Jul 06 '21 at 14:12

Gray · Answer 2 · 2020-10-19T23:37:49.403

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.

score 8 · Answer 3 · answered Aug 29 '12 at 13:56

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.

How can I clear a transaction deadlock?

3 Answers3

Linked