Friday, March 16, 2012

Curious Case in MYSQL : Lock wait timeout exceeded on INSERT

Sounds strange, that how can a insert be locked or timed out . I had a innodb table with very frequent inserts, updated and deletes. After every few minutes, one of the inserts got timed out (50 sec is default value for innodb_lock_wait_timeout). My understanding was that timeouts happen when some other thread/transaction holds a exclusive record lock (select .. from update) for a long time. So how can a non-existent new row be already locked. I do not have a proper answer to this.

What solved my problem was dropping index and foreign key mapping, which were luckily irrelevant. Random guess is that innodb locks a range of index on insert. If you have an answer do let me know !

No comments:

Post a Comment