尝试获取锁时发现哪个查询导致死锁；尝试重新启动事务

Question

我无法弄清楚哪个查询导致尝试获取锁定时发现死锁；尝试重新启动事务。 我的 mysql 包装器具有以下几行

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

，其中 bug_log 写入文件。

bug日志文件没有死锁错误，但/var/log/mysqld.log有多个记录：

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

我如何追踪它？

Answer 1

如果另一个事务等待当前事务完成，则使用 WHERE 子句而不是通过唯一列进行更新将导致死锁。下面是一个快速测试：

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

会话 1

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

会话 2

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

（会话 2 现已被阻止）

会话 1

UPDATE test SET a=1 WHERE a>0;

在会话 2 中，我们收到错误

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

如果在更新的 WHERE 子句中我们仅使用 pk 列，则不会发生错误。

Answer 2

我发现这种情况发生在以下一种或多种情况下：

1. 在一个查询中多次加入同一个表 (SELF JOIN)
2. 当使用包含以多种方式同时操作同一个表的查询的事务
3. 时 当使用事务并使用与自连接或子查询相同的表

可能很难追踪，但情况基本上是说一个查询正在阻止另一个查询运行，这反过来又阻止第一个查询完成等...

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