MySQL Forums
Forum List  »  InnoDB

1205 Lock Wait Timeout on heavy SELECT load?
Posted by: Paul Chambre
Date: December 20, 2016 12:24PM

Hi folks,

We're running a very small database (a few hundred rows) on Community Edition 5.6.21 (clustered). It stores access rights information for our application. The application itself is entirely separate from MySQL.

Normally, there's very little load on the MySQL DB, however, in the mornings, we have a bunch of scheduled jobs (maybe a hundred concurrent connections) that start up and need to check permissions. Recently, we have seen some of these queries (single SELECT statements, with a couple of sub-queries), fail with 1205 Lock Wait Timeout errors. The query in question normally takes about 1/10th of a second to run, when there's no other load on the server.

Looking through MySQL documentation, and forum threads, it seems that people have only seen this error when running DML, not when running SELECT statements. As far as we know, there is no concurrent DML running against these tables around the time of the errors (in general, DML to these tables is rare, and limited to single row modifications).

Has anyone seen this error as a symptom of query congestion/load? Any suggestions on how to troubleshoot it, or fix it? I'm thinking of changing the Transaction Isolation Level for the query to Read Committed... any thoughts on that? BTW, the server still has the default 50 second timeout set.

Thanks,
Paul

Options: ReplyQuote


Subject
Views
Written By
Posted
1205 Lock Wait Timeout on heavy SELECT load?
2514
December 20, 2016 12:24PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.