MySQL Innodb Deadlock - how to remove old event?

Question

My monitoring tool, Zenoss, is reporting a 'LATEST DETECTED DEADLOCK' event from a MySQL Innodb engine. When I run 'show engine innodb status\G' I get the following information about the deadlock:

------------------------
LATEST DETECTED DEADLOCK
------------------------
130819 14:01:12
*** (1) TRANSACTION:
TRANSACTION 0 108626388, ACTIVE 0 sec, process no 8726, OS thread id 47220783470912
starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1216, 2 row lock(s), undo log entries 1
MySQL thread id 4283, query id 21974219 10.92.210.108 ddrsrbe Updating
update ddrsproduction.requests set request_priority_grade_id=3, costs=0, data_version=1, date_completed='2013-08-19 13:59:39', date_end='2013-05-24 23:59:00', date_required_by='2013-08-26 13:57:31', date_start='2013-05-19 00:01:00', date_submitted='2013-08-19 13:57:31', designated_authority_id=84528, is_manual=1, missed_sla_reason=null, missed_sla_reason_ask_user=null, product_id=35, request_cost_status_id=0, request_delivery_method_id=0, request_legislation_id=0, request_method_id=0, request_reason_id=1, request_result_status_id=null, origin_id=0, request_status_id=2, request_type_id=1, result_row_count=2, results_last_downloaded=null, site_processed='KNOW', time_zone=null, urn='LBP/281/35/12 (CONS)', user_id=2357, vf_rep_id=8 where request_id=132536
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

You can see that the deadlock was in August last year, and I cannot kill the thread as it no longer exists:

mysql> kill 4283;
ERROR 1094 (HY000): Unknown thread id: 4283

I am getting bored receiving this event in to Zenoss, and would very much like to purge the innodb engine of this alarm. I have Googled high and low for an answer and have come up with zilch, nada, nothing. Any help hugely appreciated :-)

mysql> kill query 21974219; ERROR 1094 (HY000): Unknown thread id: 21974219 :( — machinist, Feb 05 '14 at 10:51
The same I'm afraid: mysql> kill query 47220783470912; ERROR 1094 (HY000): Unknown thread id: 47220783470912 — machinist, Feb 05 '14 at 10:58
What if you run the update statement in the lock? See if it creates a new lock and maybe you can kill that one — Mad Dog Tannen, Feb 05 '14 at 11:03
Hmmm, will have to check if I'm allowed to re-run that update command. In the meantime, is the suggestion here that this deadlock still exists? If it does, why on earth won't these kill commands work? — machinist, Feb 05 '14 at 12:19
I cannot imagine, but since its still in the engine innodb status its strange. — Mad Dog Tannen, Feb 05 '14 at 14:55

score 5 · Accepted Answer · answered Feb 06 '14 at 10:10

There is no way to clear the LATEST DETECTED DEADLOCK section in SHOW ENGINE INNODB STATUS unfortunately. The query has already been killed by the deadlock detector, so there's nothing you can KILL. The only way to reset that information is to restart the server.

No properly written software should continuously alert due to the presence of something in that output.

score 0 · Answer 2 · answered Feb 05 '14 at 13:55

0

Do a SHOW FULL PROCESSLIST to get the list of all connections and kill the connection performing the update, or all inactive/sleeping sessions using Kill command if no update shows up.

answered Feb 05 '14 at 13:55

Cedric Simon

4,571
4
40
52

Thanks for this @cedric-simon. The very top ID in the resulting list is for user _system user_ with a command of _Connect_ and a time value of _14690265_! State says **Waiting for master to send event**. Is that normal? – machinist Feb 06 '14 at 13:41
Looks like a replication status. Do you use replication? http://dev.mysql.com/doc/refman/5.0/en/replication-administration-status.html – Cedric Simon Feb 06 '14 at 13:48

MySQL Innodb Deadlock - how to remove old event?

2 Answers2