Which query is causing Deadlock found when trying to get lock; try restarting transaction

Question

I cannot figure out which Query is causing Deadlock found when trying to get lock; try restarting transaction. My wrapper for mysql has the following lines

if (mysql_errno($this->conn) == 1213) {
  $this->bug_log(0,"Deadlock. SQL:".$this->sql);
}

where bug_log writes to a file.

The bug log file has no Deadlock errors, but /var/log/mysqld.log has multiple records:

111016  3:00:02 [ERROR] /usr/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
111016  3:00:02 [ERROR] /usr/libexec/mysqld: Sort aborted
111016  3:00:02 [ERROR] /usr/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
111016  3:00:02 [ERROR] /usr/libexec/mysqld: Sort aborted
111016  3:00:02 [ERROR] /usr/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
111016  3:00:02 [ERROR] /usr/libexec/mysqld: Sort aborted

How can i track it down?

Answer 1

An update with WHERE clause which is not by unique column will cause deadlock if another transaction waits for the current transaction to complete. Here's a quick test:

CREATE TABLE test (pk int PRIMARY KEY, a int);
INSERT INTO test VALUES (0, 0);
INSERT INTO test VALUES (1, 0);

Session 1

BEGIN;
SELECT a FROM test WHERE pk=0 FOR UPDATE;

Session 2

BEGIN;
SELECT a FROM test WHERE pk=0 FOR UPDATE;

(Session 2 is now blocked)

Session 1

UPDATE test SET a=1 WHERE a>0;

In session 2 we receive an error

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

If in the WHERE clause of the update we use the pk column only, the error does not occur.

Answer 2

I've seen this occur on one or more of the following conditions:

1. Joining on the same table multiple times in a query (SELF JOIN)
2. When using transactions that contain queries that manipulate the same table in multiple ways concurrently
3. When using transactions and using the same table as a SELF JOIN or a Sub-query

It can be difficult to track down but the situation is basically saying one query is preventing another from running which in turn prevents the first from finishing etc...

http://en.wikipedia.org/wiki/Deadlock