Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.3(EOL), 10.4(EOL)
Description
Reproduce
--source include/have_innodb.inc
|
|
create or replace table t1 (pk int primary key, x int) engine innodb;
|
start transaction;
|
insert into t1 values (3, 1);
|
connect (con1,localhost,root,,test);
|
send select * from t1 for update;
|
connection default;
|
--let $wait_condition= select count(*) from information_schema.innodb_lock_waits
|
--source include/wait_condition.inc
|
insert into t1 values (1, 1);
|
commit;
|
connection con1;
|
--error ER_LOCK_DEADLOCK
|
reap;
|
connection default;
|
show engine innodb status;
|
disconnect con1;
|
drop table t1;
|
Result
------------------------
|
LATEST DETECTED DEADLOCK
|
------------------------
|
2019-08-07 13:20:09 0x7f37e1cee700
|
*** (1) TRANSACTION:
|
TRANSACTION 37, ACTIVE 0 sec starting index read
|
mysql tables in use 1, locked 1
|
LOCK WAIT 2 lock struct(s), heap size 1160, 1 row lock(s)
|
MySQL thread id 10, OS thread handle 139877988054784, query id 24 localhost root Sending data
|
select * from t1 for update
|
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
|
RECORD LOCKS space id 5 page no 3 n bits 72 index PRIMARY of table `test`.`t1` trx id 37 lock_mode X waiting
|
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
|
0: len 4; hex 80000003; asc ;;
|
1: len 6; hex 000000000024; asc $;;
|
2: len 7; hex 830000013702c3; asc 7 ;;
|
3: len 4; hex 80000001; asc ;;
|
|
*** (2) TRANSACTION:
|
TRANSACTION 36, ACTIVE 0 sec inserting
|
mysql tables in use 1, locked 1
|
3 lock struct(s), heap size 1160, 2 row lock(s), undo log entries 1
|
MySQL thread id 9, OS thread handle 139877988361984, query id 26 localhost root Update
|
insert into t1 values (1, 1)
|
*** (2) HOLDS THE LOCK(S):
|
RECORD LOCKS space id 5 page no 3 n bits 72 index PRIMARY of table `test`.`t1` trx id 36 lock_mode X locks rec but not gap
|
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
|
0: len 4; hex 80000003; asc ;;
|
1: len 6; hex 000000000024; asc $;;
|
2: len 7; hex 830000013702c3; asc 7 ;;
|
3: len 4; hex 80000001; asc ;;
|
|
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
|
RECORD LOCKS space id 5 page no 3 n bits 72 index PRIMARY of table `test`.`t1` trx id 36 lock_mode X locks gap before rec insert intention waiting
|
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
|
0: len 4; hex 80000003; asc ;;
|
1: len 6; hex 000000000024; asc $;;
|
2: len 7; hex 830000013702c3; asc 7 ;;
|
3: len 4; hex 80000001; asc ;;
|
Cause
When implicit lock is converted to explicit it does not lock the gap: LOCK_REC | LOCK_X | LOCK_REC_NOT_GAP. On the other hand explicit insert intention does lock the gap: LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION. Explicit lock is skipped when there is no existing locks for the record.
Fix candidates
1. Implicit lock converted into insert intention: LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION. I don't see a reason why it is converted into different mode.
2. When inserting into a gap locked by the same transaction do not create explicit lock. We don't have to wait for locks in same transaction.
Attachments
Issue Links
- relates to
-
MDEV-18706 ER_LOCK_DEADLOCK on concurrent read and insert into already locked gap
- In Review