Re: Lock wait timeout exceeded; try restarting transaction

[Harshdeep Khatri]

Let me share you more information.

# Time: 120212 21:43:30
# User@Host: saas[saas] @ localhost []
# Query_time: 50.510479 Lock_time: 0.000022 Rows_sent: 0 Rows_examined: 0
use saas_20110806;
SET timestamp=1329083010;
UPDATE cfidentity SET lastunkid='00000000000319' WHERE tablename = 'hktrail' AND hotel_code='77';
# Time: 120212 21:44:29
# User@Host: saas[saas] @ localhost []
# Query_time: 51.165999 Lock_time: 0.000026 Rows_sent: 0 Rows_examined: 0
SET timestamp=1329083069;
UPDATE cfidentity SET lastunkid='00000000000319' WHERE tablename = 'hktrail' AND hotel_code='77';
# Time: 120212 21:45:24
# User@Host: saas[saas] @ localhost []
# Query_time: 50.943563 Lock_time: 0.000039 Rows_sent: 0 Rows_examined: 0
SET timestamp=1329083124;
UPDATE cfidentity SET lastunkid='00000000000319' WHERE tablename = 'hktrail' AND hotel_code='77';
# Time: 120212 21:46:17
# User@Host: saas[saas] @ localhost []
# Query_time: 51.277622 Lock_time: 0.000050 Rows_sent: 0 Rows_examined: 0
SET timestamp=1329083177;
UPDATE cfidentity SET lastunkid='00000000000319' WHERE tablename = 'hktrail' AND hotel_code='77';
# Time: 120212 21:47:14
# User@Host: saas[saas] @ localhost []
# Query_time: 50.745176 Lock_time: 0.000059 Rows_sent: 0 Rows_examined: 0
SET timestamp=1329083234;
UPDATE cfidentity SET lastunkid='00000000000319' WHERE tablename = 'hktrail' AND hotel_code='77';
# Time: 120212 21:48:07
# User@Host: saas[saas] @ localhost []
# Query_time: 51.359062 Lock_time: 0.000064 Rows_sent: 0 Rows_examined: 0
SET timestamp=1329083287;
UPDATE cfidentity SET lastunkid='00000000000319' WHERE tablename = 'hktrail' AND hotel_code='77';
# Time: 120212 21:49:00
# User@Host: saas[saas] @ localhost []
# Query_time: 50.891924 Lock_time: 0.000051 Rows_sent: 0 Rows_examined: 0
SET timestamp=1329083340;
UPDATE cfidentity SET lastunkid='00000000000319' WHERE tablename = 'hktrail' AND hotel_code='77';


I have also on slow query log, nothing observe in it during this timing, I am wondring how suddenly all this started ? I am observing this query trying to run almost on every aprrox 50 seconds which is my default innodb_lock_wait_timeout value, do it set innodb_rollback_on_timeout variable to ON so this query will rollbacked and not try to write the data, because I don't know why when this error comes my cpu usage starts increasing and mysql reached to max connection limit as a last option I need to restart the mysql.

Hope this information helps.