Lock wait timeout exceeded; try restarting transaction

So last friday I came up with this nasty message… Believe me, it was a very troublesome issue to solve. We have MySQL running Innodb for a couple of tables. We wanted to modify 3 tables and we decided to create a transaction if inserts and updates went ok then commit if not rollback.

I did it and tested it on my dev env (my lap hehehehe) and it all worked fine, speed was ok and everything worked as wanted. So I commited it to my svn server and uploaded the code to the production server. 5 min. before I leave the office my boss decided to test the new version…. good and bad idea, good because he got the nasty error, bad because of the time.

Anyway, we saw the error. Initially I thought it was because of a web service (no, I didn’t realized it was a database issue), then after discarding lots of probable errors I realize it was a transaction error. After hours looking and reading google search results I learned the following: When doing a transaction tables involved receive a lock, the lock is released once the transaction ends. If a table is being updated within the transaction and you try to select data from it then you’ll be trying to access a locked table, since it is locked you can’t read from it and since you can’t read from it you can’t finish the transaction so the waiting for the lock to release takes for ever and the connection throws the error:

“Lock wait timeout exceeded; try restarting the transaction”

How did I solved it?

After trying docens of solutions without success I finally realized that I had to take the reading statements off the transaction.

It worked! And because I forget things very fast I decided to put it here.

Hope it helps someone else out there.

Tags: ,

Comments are closed.