My monitoring tool, Zenoss, reports the "LAST DETECTIVE DEADLOCK" event from the MySQL Innodb engine. When I run the showon innodb status \ G status, I get the following deadlock information:
------------------------
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 can’t kill the stream, since it no longer exists:
mysql> kill 4283;
ERROR 1094 (HY000): Unknown thread id: 4283
I am getting bored of getting this event at Zenoss and would love to clear the innodb engine of this anxiety. I got googled high and low for an answer and came up with zilch, nada, nothing. Any help is much appreciated :-)