Bug #22876 Four-way deadlock
Submitted: 1 Oct 2006 20:10 Modified: 6 Mar 2010 19:32
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.1.12-beta-debug OS:Linux (SUSE 10.0 / 64-bit)
Assigned to: Jon Olav Hauglid CPU Architecture:Any
Tags: falcon

[1 Oct 2006 20:10] Peter Gulutzan
Description:
I have four connections with four interleaved transactions, T1 + T2 + T3 + T4.

After six INSERT and DROP instructions, all the transactions hang,
waiting for each other. I interpret this as an exotic deadlock.

How to repeat:
/* I have to start up each connection before starting transactions. */

On T1, say:
set @@autocommit=0;

On T2, say:
set @@autocommit=0;

On T3, say:
set @@autocommit=0;

On T4, say:
set @@autocommit=0;

On T1, say:
create table tz (s1 int unique) engine=innodb;
insert into tz values (1);

On T2, say:
insert into tz values (2);
insert into tz values (1);        /* hangs */

On T3, say:
drop table tz;                    /* hangs */

On T4, say:
drop table tz;                    /* hangs */

On T1, say:
insert into tz values (2);        /* hangs */

Observe that T1 + T2 + T3 + T4 are all hanging
[2 Oct 2006 12:27] Heikki Tuuri
Peter,

the reason is that the second inserting transaction will wait for a row lock on the duplicate key record: a duplicate key error must be locked with an S-lock on the duplicate row to ensure that execution is serializable.

I am reluctant to fix this for InnoDB (if at all possible inside InnoDB code), as the lock wait timeout will normally resolve the hang in 50 seconds.

Regards,

Heikki
[2 Oct 2006 14:10] Peter Gulutzan
Heikki,

You are right to be reluctant. I believe this bug should stay in category
"MySQL Server".My use of the engine=innodb clause in the example was not
meant to suggest that I think this should be of any concern for InnoDB
people.
[10 Oct 2006 12:00] MySQL Verification Team
Thank you for the bug report. Verified as described.
[2 Jul 2009 9:49] Konstantin Osipov
Fixed with the fix for Bug#989. 

Now you don't get a hang in connection #1, but you get:
mysql> insert into tz values (2);        /* hangs */
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Once you commit the DML transactions DDL transactions can proceed.

Jon Olav, please add a test case and close the bug.
[7 Jul 2009 10:28] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/78107

2824 Jon Olav Hauglid	2009-07-07
      Bug #22876 Four-way deadlock
      
      This bug was fixed as a part of Bug#989 
      "If DROP TABLE while there's an active transaction, wrong binlog order"
      A statement which would have caused circular wait will now 
      be aborted with ER_LOCK_DEADLOCK.
      
      Test case based on bug description added to innodb-lock.test.
      Note that innodb_lock_wait_timeout is set to 5 mins to 
      prevent race conditions in the test.
[8 Jul 2009 12:13] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/78198

2825 Jon Olav Hauglid	2009-07-08
      Bug #22876 Four-way deadlock
      
      This bug was fixed as a part of Bug#989 
      "If DROP TABLE while there's an active transaction, wrong binlog order"
      A statement which would have caused circular wait will now 
      be aborted with ER_LOCK_DEADLOCK.
      
      Test case based on bug description added to innodb_mysql_lock.test.
      Note that innodb_lock_wait_timeout is set to 5 mins to 
      prevent race conditions in the test.
[23 Jul 2009 9:13] Konstantin Osipov
Test case is approved to go into Azalea.
[23 Jul 2009 10:13] Jon Olav Hauglid
Pushed to azalea-bugfixing.
[4 Aug 2009 19:50] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090804194615-h40sa098mx4z49qg) (version source revid:iggy@mysql.com-20090731204544-7nio1afvg0dmzs7g) (merge vers: 5.4.4-alpha) (pib:11)
[6 Aug 2009 20:26] Paul Dubois
Noted in 5.4.4 changelog.

A statement that caused a circular wait among statements did not 
return a deadlock error. Now the server detects deadlock and returns
ER_LOCK_DEADLOCK.
[8 Dec 2009 13:28] Jon Olav Hauglid
Pushed to mysql-next-4284 (5.6.0-beta).
[16 Feb 2010 16:50] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100216101445-2ofzkh48aq2e0e8o) (version source revid:kostja@sun.com-20091211154405-c9yhiewr9o5d20rq) (merge vers: 6.0.14-alpha) (pib:16)
[16 Feb 2010 17:00] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100216101208-33qkfwdr0tep3pf2) (version source revid:kostja@sun.com-20091208135954-h6ipvx9mpzmpuipk) (pib:16)
[17 Feb 2010 0:31] Paul Dubois
Noted in 6.0.14 changelog.

Setting report to Need Merge pending push of Celosia into release tree.
[6 Mar 2010 10:54] Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20100216221947-luyhph0txl2c5tc8) (merge vers: 5.5.99-m3) (pib:16)
[6 Mar 2010 19:32] Paul Dubois
Noted in 5.5.3 changelog.