Deadlock on concurrent acquisition from multiple indexes

# MDEV-22698 Deadlock on concurrent acquisition from multiple indexes

--source include/have_innodb.inc

create table t1 (a int unique) engine innodb;

insert into t1 values (1);

connect (con1, localhost, root,, test);

set debug_sync= 'after_lock_clust_rec_read_check_and_lock wait_for clust_locked';

send delete from t1;

connection default;

set debug_sync= 'after_lock_clust_rec_read_check_and_lock signal clust_locked';

--error ER_LOCK_DEADLOCK

delete from t1 where a = 1;

show engine innodb status;

connection con1;

reap;

connection default;

disconnect con1;

drop table t1;

set debug_sync= reset;

*** (1) TRANSACTION:

TRANSACTION 1287, ACTIVE 0 sec starting index read

mysql tables in use 1, locked 1

LOCK WAIT 3 lock struct(s), heap size 1152, 2 row lock(s)

MySQL thread id 9, OS thread handle 140665229768448, query id 22 localhost root updating

delete from t1 where a = 1

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 4 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t1` trx id 1287 lock_mode X locks rec but not gap waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 32

 0: len 6; hex 000000000200; asc       ;;

 1: len 6; hex 000000000506; asc       ;;

 2: len 7; hex 04000001460110; asc     F  ;;

 3: len 4; hex 80000001; asc     ;;

*** (2) TRANSACTION:

TRANSACTION 1286, ACTIVE 0 sec updating or deleting

mysql tables in use 1, locked 1

3 lock struct(s), heap size 1152, 2 row lock(s), undo log entries 1

MySQL thread id 10, OS thread handle 140665229461248, query id 21 localhost root updating

delete from t1

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 4 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t1` trx id 1286 lock_mode X

Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 32

 0: len 6; hex 000000000200; asc       ;;

 1: len 6; hex 000000000506; asc       ;;

 2: len 7; hex 04000001460110; asc     F  ;;

 3: len 4; hex 80000001; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 4 page no 4 n bits 72 index a of table `test`.`t1` trx id 1286 lock_mode X locks rec but not gap waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

 0: len 4; hex 80000001; asc     ;;

 1: len 6; hex 000000000200; asc       ;;

*** WE ROLL BACK TRANSACTION (1)