Re: Controlling query timeout period for deadlocks and long running queries
Posted by: Mark Matthews
Date: February 23, 2006 06:23PM

David Wynter wrote:
> Realised that 1205 error code tells me a lock
> timeout has occured. Now need to work out how the
> period til timeout is controlled, any pointers?
> Also if possible to timeout a long running query.
> thx.
> David


You can control deadlock detection's timeout with 'innodb_lock_wait_timeout' on the server, see:

If you use Connector/J 5.0.0 (beta) or newer w/ MySQL-5.0.x, you can use Statement.setQueryTimeout() to set a timeout for how long the driver will allow a query to run. (prior to MySQL-5.0 there wasn't a way to kill just a query, only a connection).

Some folks in high-volume environments actually write "long query killers" that run around and kill connections that have queries that are obviously wedged (from looking at the output of "SHOW PROCESSLIST") and issuing a "KILL" on the connection. The idea is that long-running queries that are hogging resources and are orders of magnitude difference in processing time are usually a query that an end-user has given up on anyways.


Mark Matthews
Consulting Member Technical Staff - MySQL Enterprise Tools

Options: ReplyQuote

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.