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

------------------------

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