Clear transactional deadlock?

Question

Using the showon innodb status. I see that wordpress has two deadlocks. I would like to clear them, but I don't see an active process for any of these cmds (IE is killing something and hopefully forces a rollback).

I can see thread IDs, request IDs, etc., but nothing that I can use to stop work.

Suggestions on how to resolve this?

EDIT: Here is the (relevant?) Part 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 that some of the status data for "innodb" is as follows:

 ---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 want to do

 KILL QUERY 771 

to kill one of the two queries that have come to a standstill. This will kill the request, but leave the connection open. if you want to kill the connection, you would do KILL 197 .

Answer 2

I know this is old, but usually when you see something like this because a dead end has occurred, and the application that launched the dead end has long passed - the victim of the dead end received a warning and either failed, or registered errors or retries, and in any case, moved on to other productive things. Usually you do not need to do anything other than look at the cause of the deadlock and try to avoid future deadlocks if you are writing software. If you just use software (for example, Wordpress, if you do not work in Wordpress), you can report a block as a possible error.

Answer 3

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

I thought that I would provide additional information that would help us solve a similar problem. We have seen Java hibernation issues causing lockable locks. We detected locks by combing the output through:

 show engine innodb status; 

It spills out all the information. The corresponding section is in the TRANSACTIONS section. The issue is important in your release:

 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 # lock struct(s) , which pointed to a stuck lock. To kill it, you need to execute using the specified "thread id #" - in this case:

 kill 12505095 

This worked on AWS MySQL RDS, as well as on local MySQL.

---

In our section "OPERATIONS" 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 10177 1.3.5.7 mpsp cleaning up 

We are looking for messages 2 lock struct(s) and ACTIVE 21 sec .