Bug #69835 deadlock detected on concurrent insert
Submitted: 25 Jul 2013 2:41 Modified: 22 Aug 2013 2:52
Reporter: zhai weixiang (OCA) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.6.12 OS:Any
Assigned to: CPU Architecture:Any

[25 Jul 2013 2:41] zhai weixiang
Description:

This bug is similar to bug#43210, but a little different...

steps to repeat:

mysql> create table t1 (a int primary key ,b int); insert into t1 values (1,2),(2,3),(3,4),(11,22);

Session 1:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 where a = 5 for update;
Empty set (0.00 sec)

mysql> insert into t1  values (5,5);
Query OK, 1 row affected (0.00 sec) 

Session 2:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 where a = 5 for update;(block)

Session 3:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 where a = 5 for update; (block)

Session 1 and Session2  are both waiting for  x lock on the same record..

mysql> select INNODB_LOCK_WAITS.blocking_trx_id, INNODB_LOCKS.*  from INNODB_LOCK_WAITS left join INNODB_LOCKS on (INNODB_LOCK_WAITS.requested_lock_id = INNODB_LOCKS.lock_id);
+-----------------+-------------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
| blocking_trx_id | lock_id           | lock_trx_id | lock_mode | lock_type | lock_table  | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-----------------+-------------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
| 44303459        | 44303460:3660:3:6 | 44303460    | X         | RECORD    | `test`.`t1` | PRIMARY    |       3660 |         3 |        6 | 5         |
| 44303446        | 44303460:3660:3:6 | 44303460    | X         | RECORD    | `test`.`t1` | PRIMARY    |       3660 |         3 |        6 | 5         |
| 44303446        | 44303459:3660:3:6 | 44303459    | X         | RECORD    | `test`.`t1` | PRIMARY    |       3660 |         3 |        6 | 5         |
+-----------------+-------------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
3 rows in set (0.00 sec)

Session 1
Rollback;

After Session 1  was rollback , Session 2 and Session 3 both got the x lock on the same record. 

Is this behavior reasonable?  In my opinion, only one thread should get x lock because it's exclusive.

Session 3:
mysql>  insert into t1  values (5,5);

Session 2:
mysql>  insert into t1  values (5,5);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

And the deadlock information:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2013-07-25 10:09:55 7fc8a1a36700
*** (1) TRANSACTION:
TRANSACTION 44303428, ACTIVE 220 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1248, 2 row lock(s)
MySQL thread id 2, OS thread handle 0x7fc8a18c2700, query id 200 localhost root update
insert into t1 values (5,5)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3660 page no 3 n bits 72 index `PRIMARY` of table `test`.`t1` trx id 44303428 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 8000000b; asc     ;;
 1: len 6; hex 000002a4042a; asc      *;;
 2: len 7; hex a6000004cf0137; asc       7;;
 3: len 4; hex 80000016; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 44303429, ACTIVE 177 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 2 row lock(s)
MySQL thread id 1, OS thread handle 0x7fc8a1a36700, query id 201 localhost root update
insert into t1  values (5,5)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3660 page no 3 n bits 72 index `PRIMARY` of table `test`.`t1` trx id 44303429 lock_mode X locks gap before rec
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 8000000b; asc     ;;
 1: len 6; hex 000002a4042a; asc      *;;
 2: len 7; hex a6000004cf0137; asc       7;;
 3: len 4; hex 80000016; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3660 page no 3 n bits 72 index `PRIMARY` of table `test`.`t1` trx id 44303429 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 8000000b; asc     ;;
 1: len 6; hex 000002a4042a; asc      *;;
 2: len 7; hex a6000004cf0137; asc       7;;
 3: len 4; hex 80000016; asc     ;;

*** WE ROLL BACK TRANSACTION (2)

How to repeat:
described  above 

Suggested fix:
I don't know...
[25 Jul 2013 7:32] Hartmut Holzgraefe
With transaction 1 rolled back the a=5 record ceases to exist, so both selects actually return an empty result set and actually lock nothing?
[25 Jul 2013 9:04] zhai weixiang
They should lock nothing, but actually they both locked the rollbacked record successfully.... :-)
[25 Jul 2013 9:29] zhai weixiang
By the way , I am using REPEATABLE-READ;  the deadlock will not happen if using READ-COMMITTED
[21 Aug 2013 13:39] MySQL Verification Team
Rolling back occurs AFTER the deadlock, hence the other transaction continued it's operation with the lock granted. Rolled back transaction should be restarted.
[22 Aug 2013 2:52] zhai weixiang
Hi, Sinisa
thanks for your reply.

while inserting records with same primary key (or unique key ) concurrently,  We really expect a duplicate key error rather than rollback because  the cost of ROLLBACK  the WHOLE transaction is too expensive.