MySQL Forums :: InnoDB :: 1205 Lock Wait Timeout on heavy SELECT load?


Advanced Search

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? 345 Paul Chambre 12/20/2016 12:24PM
Re: 1205 Lock Wait Timeout on heavy SELECT load? 185 harry sheng 12/20/2016 12:53PM
Re: 1205 Lock Wait Timeout on heavy SELECT load? 243 Paul Chambre 12/22/2016 07:40AM
Re: 1205 Lock Wait Timeout on heavy SELECT load? 168 Peter Brawley 12/22/2016 11:45AM
Re: 1205 Lock Wait Timeout on heavy SELECT load? 174 Paul Chambre 01/12/2017 09:32AM
Re: 1205 Lock Wait Timeout on heavy SELECT load? 122 Peter Brawley 01/12/2017 12:27PM
Re: 1205 Lock Wait Timeout on heavy SELECT load? 112 Paul Chambre 02/06/2017 12:13PM
Re: 1205 Lock Wait Timeout on heavy SELECT load? 58 Paul Chambre 02/21/2017 09:23AM


Sorry, only registered users may post in this forum.

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.