1205 Lock Wait Timeout on heavy SELECT load?
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