"Lock wait timeout exceeded" time out on insert.

[Nic Whale]

I logged a topic a while back that was similar behaviour : http://forums.mysql.com/read.php?22,422297,422297 .

I thought some of these issues could be fixed by the introduction of "innodb_autoinc_lock_mode" in V5.1 (Although Rick James said suggested politely I was wrong!). So I have upgraded to "V5.1.52 on Linux" and now have configured this new property as

"innodb_autoinc_lock_mode=2"

However every couple of days or so I get the following error:

"SQLException: Lock wait timeout exceeded; try restarting transaction SQLState: 41000 VendorError: 1205"

While trying do a basic insert, such as:

INSERT INTO table1( id, lastupdated ,title, status, type, creationdate, notes ,history,userid ,operativeid ,type2 ,startdate ,enddate ,noattachmentrequired, field1, field2, field3, field4, field5, field6 ,approvaldate ,archivaldate, boolean1, boolean2, boolean3 ,opuseridedited,accuseridedited ) values (0,'2011-12-07 12:38:37',NULL,0,2,'2011-12-07 12:38:37',NULL,'07/12/2011 12:38:[e-mail]:Op Wizard <br>',97027,96915,0,NULL,NULL,-1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,-1,-1,-1,null,null);

This insert is not in a big transaction, but it is in a simple one. Also, there is not that much else happening on the database. While I cannot see exactly how many updates where happening around this time, I can see one insert was done about 3 mins prior to this error.

Any help or suggestions would be greatly appreciated.

Nick



Edited 1 time(s). Last edit at 12/11/2011 07:46AM by Nic Whale.