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