Re: Controlling query timeout period for deadlocks and long running queries

[Mark Matthews]

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

David,

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

http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html

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

Mark Matthews
Consulting Member Technical Staff - MySQL Enterprise Tools
Oracle
http://www.mysql.com/products/enterprise/monitor.html