MySQL Bugs: #72011: Deadlock with 3 transactions (inserts)

Bug #72011	Deadlock with 3 transactions (inserts)
Submitted:	11 Mar 2014 16:06	Modified:	17 Mar 2014 21:33
Reporter:	Martin Arrieta (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.6	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	deadlock

Description:
In the documentation say that if you make the following:

CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

tx1:
START TRANSACTION;
INSERT INTO t1 VALUES(1);

tx2:
START TRANSACTION;
INSERT INTO t1 VALUES(1);

tx3:
START TRANSACTION;
INSERT INTO t1 VALUES(1);

tx1: ROLLBACK;

In the documentation say that this will generate a DEADLOCK for both transaction (tx2 and tx3) but this is not true, it will generate a deadlock only for tx3 and tx2 will succeed.

I think that is because after the rollback on tx1, tx2 gets the S lock and after check that there is no other row with (i = 1) it release the S lock and request the X lock for that row . When tx2 release the S lock, tx3 gets the S lock and request the X lock for that row, but tx2 is waiting for tx3 to release the S lock in order to get the X lock and then tx3 and that's why tx3 gets the deadlock.

This is the deadlock:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2014-03-11 12:50:35 1279f5000
*** (1) TRANSACTION:
TRANSACTION 2684, ACTIVE 16 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 136, OS thread handle 0x127b89000, query id 10935 localhost root update
insert into t1 values(1)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 14 page no 3 n bits 72 index `i` of table `test`.`t1` trx id 2684 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 2685, ACTIVE 9 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 137, OS thread handle 0x1279f5000, query id 10937 localhost root update
insert into t1 values(1)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 14 page no 3 n bits 72 index `i` of table `test`.`t1` trx id 2685 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 14 page no 3 n bits 72 index `i` of table `test`.`t1` trx id 2685 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

Regards,

Martin.

How to repeat:
CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

tx1:
START TRANSACTION;
INSERT INTO t1 VALUES(1);

tx2:
START TRANSACTION;
INSERT INTO t1 VALUES(1);

tx3:
START TRANSACTION;
INSERT INTO t1 VALUES(1);

tx1: ROLLBACK;

Suggested fix:
Change the documentation.

Sent too soon :) .

This is the URL: http://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html

Thank you for the report.

> it will generate a deadlock only for tx3 and tx2 will succeed.

What do you mean by "tx2 will succeed"? When deadlock happens one transaction succeed and one rolled back. This is expected behavior. There can not be deadlock for single transaction.

Sveta. 

"tx2 will succeed" = tx2 will be able to insert the row.

When tx1 is rolled back, tx2 will be able to insert the row and tx3 will generate the deadlock.

Regards, 

Martin

Thank you for the feedback.

Yes, this is expected behavior for the deadlock: one transaction is able to insert a row and finish successfully, second rolled back. This is not a bug.

Agree with you that is the expected behavior, but check this part of the documentation:

"The first operation by session 1 acquires an exclusive lock for the row. The operations by sessions 2 and 3 both result in a duplicate-key error and they both request a shared lock for the row. When session 1 rolls back, it releases its exclusive lock on the row and the queued shared lock requests for sessions 2 and 3 are granted. At this point, sessions 2 and 3 deadlock: Neither can acquire an exclusive lock for the row because of the shared lock held by the other."

"At this point, sessions 2 and 3 deadlock" is not correct. only session 3 get the deadlock.

Regards, 

Martin