Bug #69835 deadlock detected on concurrent insert

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...

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?

They should lock nothing, but actually they both locked the rollbacked record successfully.... :-)

By the way , I am using REPEATABLE-READ;  the deadlock will not happen if using READ-COMMITTED

Rolling back occurs AFTER the deadlock, hence the other transaction continued it's operation with the lock granted. Rolled back transaction should be restarted.

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.