Geek Answers Handbook

What request causes Deadlock when trying to get a lock; try restarting the transaction

I can’t figure out which request causes Deadlock found when trying to get lock; try restarting transaction Deadlock found when trying to get lock; try restarting transaction . My shell for mysql has the following lines

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

where bug_log written to the file.

There are no Deadlock errors in the error log file, but /var/log/mysqld.log has several entries:

 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?

+4
source share
2 answers

An update with a WHERE clause that is not a unique column causes a deadlock if another transaction is waiting for the current transaction to complete. Here is 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 locked)

Session 1

 UPDATE test SET a=1 WHERE a>0;

In session 2, we get an error

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

If the update WHERE clause uses only the pk column, an error does not occur.

+7
source

I saw this in one or more of the following conditions:

  • Joining the same table multiple times in a query (SELF JOIN)
  • When using transactions that contain queries that manage the same table in several ways at the same time
  • When using transactions and using the same table as SELF JOIN or Sub-query

It is difficult to track, but the situation basically says that one request prevents the other from starting, which, in turn, prevents the execution of the first, etc.

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

+2
source

More articles:


All Articles