Lock Wait Timeout Exceeded; Try Restarting Transaction – SQL Error 1205

SQL Commands on ASPIsFun

If you work with really large tables (or, I suppose, a really slow server), you are eventually going to risk hitting this error. Lock wait timeout exceeded; try restarting transaction. This is Error Code 1205.

What does this mean?

In essence, SQL is trying to do something and has locked a row or rows. Then thing are taking so long that it isn’t able to get those locks resolved before the timeout limit is reached.

This could be an actual problem with lots of users all interacting with rows and running into contention for them. However, for most of us, it’s usually a problem with a long query running that doesn’t finish before the time runs out. If you see that the timeout is happening at right around 50 seconds, it’s probably a time limit issue.

One setting which controls this is innodb_lock_wait_timeout

Run the command:

SET PERSIST innodb_lock_wait_timeout = 120;

That will give you more time to work with. Just remember that you should always make sure your code is as efficient as possible before resorting to extending time limits in your system.

Be the first to comment

Leave a Reply

Your email address will not be published.


This site uses Akismet to reduce spam. Learn how your comment data is processed.