MySQL Bugs: #22876: Four-way deadlock

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

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

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

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.

Thank you for the bug report. Verified as described.

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.

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.

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.

Test case is approved to go into Azalea.

Pushed to azalea-bugfixing.

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)

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.

Pushed to mysql-next-4284 (5.6.0-beta).

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)

Pushed into mysql-next-mr (revid:alik@sun.com-20100216101208-33qkfwdr0tep3pf2) (version source revid:kostja@sun.com-20091208135954-h6ipvx9mpzmpuipk) (pib:16)

Noted in 6.0.14 changelog.

Setting report to Need Merge pending push of Celosia into release tree.

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)

Noted in 5.5.3 changelog.