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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.073 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.