show create table performance_schema.data_lock_waits;
Table	Create Table
data_lock_waits	CREATE TABLE `data_lock_waits` (
  `ENGINE` varchar(32) NOT NULL,
  `REQUESTING_ENGINE_LOCK_ID` varchar(128) NOT NULL,
  `REQUESTING_ENGINE_TRANSACTION_ID` bigint(20) unsigned DEFAULT NULL,
  `REQUESTING_THREAD_ID` bigint(20) unsigned DEFAULT NULL,
  `REQUESTING_EVENT_ID` bigint(20) unsigned DEFAULT NULL,
  `REQUESTING_OBJECT_INSTANCE_BEGIN` bigint(20) unsigned NOT NULL,
  `BLOCKING_ENGINE_LOCK_ID` varchar(128) NOT NULL,
  `BLOCKING_ENGINE_TRANSACTION_ID` bigint(20) unsigned DEFAULT NULL,
  `BLOCKING_THREAD_ID` bigint(20) unsigned DEFAULT NULL,
  `BLOCKING_EVENT_ID` bigint(20) unsigned DEFAULT NULL,
  `BLOCKING_OBJECT_INSTANCE_BEGIN` bigint(20) unsigned NOT NULL,
  KEY `REQUESTING_ENGINE_LOCK_ID` (`REQUESTING_ENGINE_LOCK_ID`,`ENGINE`),
  KEY `BLOCKING_ENGINE_LOCK_ID` (`BLOCKING_ENGINE_LOCK_ID`,`ENGINE`),
  KEY `REQUESTING_ENGINE_TRANSACTION_ID` (`REQUESTING_ENGINE_TRANSACTION_ID`,`ENGINE`),
  KEY `BLOCKING_ENGINE_TRANSACTION_ID` (`BLOCKING_ENGINE_TRANSACTION_ID`,`ENGINE`),
  KEY `REQUESTING_THREAD_ID` (`REQUESTING_THREAD_ID`,`REQUESTING_EVENT_ID`),
  KEY `BLOCKING_THREAD_ID` (`BLOCKING_THREAD_ID`,`BLOCKING_EVENT_ID`)
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
# Connection con1
create database explain_test_db;
create table explain_test_db.explain_test_table(a int, b int, PRIMARY KEY (a, b)) engine=XENGINE
PARTITION BY RANGE( a )
SUBPARTITION BY HASH( b ) (
PARTITION p0 VALUES LESS THAN (1990) (
SUBPARTITION s0,
SUBPARTITION s1
),
PARTITION p1 VALUES LESS THAN (2000) (
SUBPARTITION s2,
SUBPARTITION s3
),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s4,
SUBPARTITION s5
)
);
insert into explain_test_db.explain_test_table values (1, 100), (1995, 200);
start transaction;
select * from explain_test_db.explain_test_table where a <= 1990 for update;
a	b
1	100
# Connection con2
start transaction;
insert into explain_test_db.explain_test_table values (2, 150), (1998, 250);
# Connection default
# Connection con3
create table explain_test_db.noise_table(a int, b int, PRIMARY KEY (a, b)) engine=XENGINE;
insert into explain_test_db.noise_table
values (1, 100), (2, 200), (3, 300), (5, 500);
start transaction;
select * from explain_test_db.noise_table where a >= 2 for update;
a	b
2	200
3	300
5	500
# Connection con4
start transaction;
insert into explain_test_db.noise_table values (4, 400);
select count(*) >= 2 from performance_schema.data_lock_waits;
count(*) >= 2
1
select ENGINE, ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID, THREAD_ID, EVENT_ID
from performance_schema.data_locks
where OBJECT_NAME='explain_test_table' and LOCK_STATUS='WAITING'
  into @engine, @requesting_lock_id, @requesting_trx_id,
@requesting_thread_id, @requesting_event_id;
select
BLOCKING_ENGINE_LOCK_ID,
BLOCKING_ENGINE_TRANSACTION_ID,
BLOCKING_THREAD_ID,
BLOCKING_EVENT_ID
from performance_schema.data_lock_waits
where (upper(ENGINE) = upper(@engine))
and (REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id)
into @blocking_lock_id, @blocking_trx_id, @blocking_thread_id, @blocking_event_id;
select @engine,
@requesting_lock_id is null,
@requesting_trx_id is null,
@requesting_thread_id is null,
@requesting_event_id is null,
@blocking_lock_id is null,
@blocking_trx_id is null,
@blocking_thread_id is null,
@blocking_event_id is null;
@engine	@requesting_lock_id is null	@requesting_trx_id is null	@requesting_thread_id is null	@requesting_event_id is null	@blocking_lock_id is null	@blocking_trx_id is null	@blocking_thread_id is null	@blocking_event_id is null
INNODB	0	0	0	0	0	0	0	0

====================================================================
Testing index for columns REQUESTING_ENGINE_LOCK_ID, ENGINE
====================================================================
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits
where REQUESTING_ENGINE_LOCK_ID = "impossible";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	REQUESTING_ENGINE_LOCK_ID	REQUESTING_ENGINE_LOCK_ID	514	const	#	100.00	NULL
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits
where REQUESTING_ENGINE_LOCK_ID > "impossible";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ALL	REQUESTING_ENGINE_LOCK_ID	NULL	NULL	NULL	#	33.33	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits
where REQUESTING_ENGINE_LOCK_ID < "2";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ALL	REQUESTING_ENGINE_LOCK_ID	NULL	NULL	NULL	#	33.33	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits
where REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	REQUESTING_ENGINE_LOCK_ID	REQUESTING_ENGINE_LOCK_ID	514	const	#	100.00	NULL
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits
where REQUESTING_ENGINE_LOCK_ID = "impossible"
    and ENGINE = "impossible";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	REQUESTING_ENGINE_LOCK_ID	REQUESTING_ENGINE_LOCK_ID	644	const,const	#	100.00	NULL
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits
where REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id
and ENGINE = "impossible";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	REQUESTING_ENGINE_LOCK_ID	REQUESTING_ENGINE_LOCK_ID	644	const,const	#	100.00	NULL
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits
where REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id
and ENGINE > "impossible";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	REQUESTING_ENGINE_LOCK_ID	REQUESTING_ENGINE_LOCK_ID	514	const	#	33.33	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits
where REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id
and ENGINE < "2";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	REQUESTING_ENGINE_LOCK_ID	REQUESTING_ENGINE_LOCK_ID	514	const	#	33.33	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits
where REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id
and ENGINE = @engine;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	REQUESTING_ENGINE_LOCK_ID	REQUESTING_ENGINE_LOCK_ID	644	const,const	#	100.00	NULL
############# Explain End #########################################
flush status;
select COUNT(*)
from performance_schema.data_lock_waits
where REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id
and ENGINE = @engine;
COUNT(*)
#
OK: handler_read_key incremented
select BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id
from performance_schema.data_lock_waits
where (ENGINE = @engine)
and (REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id);
BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id
1

====================================================================
Testing index for columns BLOCKING_ENGINE_LOCK_ID, ENGINE
====================================================================
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits
where BLOCKING_ENGINE_LOCK_ID = "impossible";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	BLOCKING_ENGINE_LOCK_ID	BLOCKING_ENGINE_LOCK_ID	514	const	#	100.00	NULL
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits
where BLOCKING_ENGINE_LOCK_ID > "impossible";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ALL	BLOCKING_ENGINE_LOCK_ID	NULL	NULL	NULL	#	33.33	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits
where BLOCKING_ENGINE_LOCK_ID < "2";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ALL	BLOCKING_ENGINE_LOCK_ID	NULL	NULL	NULL	#	33.33	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits
where BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	BLOCKING_ENGINE_LOCK_ID	BLOCKING_ENGINE_LOCK_ID	514	const	#	100.00	NULL
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits
where BLOCKING_ENGINE_LOCK_ID = "impossible"
    and ENGINE = "impossible";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	BLOCKING_ENGINE_LOCK_ID	BLOCKING_ENGINE_LOCK_ID	644	const,const	#	100.00	NULL
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits
where BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id
and ENGINE = "impossible";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	BLOCKING_ENGINE_LOCK_ID	BLOCKING_ENGINE_LOCK_ID	644	const,const	#	100.00	NULL
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits
where BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id
and ENGINE > "impossible";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	BLOCKING_ENGINE_LOCK_ID	BLOCKING_ENGINE_LOCK_ID	514	const	#	33.33	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits
where BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id
and ENGINE < "2";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	BLOCKING_ENGINE_LOCK_ID	BLOCKING_ENGINE_LOCK_ID	514	const	#	33.33	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits
where BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id
and ENGINE = @engine;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	BLOCKING_ENGINE_LOCK_ID	BLOCKING_ENGINE_LOCK_ID	644	const,const	#	100.00	NULL
############# Explain End #########################################
flush status;
select COUNT(*)
from performance_schema.data_lock_waits
where BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id
and ENGINE = @engine;
COUNT(*)
#
OK: handler_read_key incremented
select REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id
from performance_schema.data_lock_waits
where (ENGINE = @engine)
and (BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id);
REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id
1

====================================================================
Testing index for columns REQUESTING_ENGINE_TRANSACTION_ID, ENGINE
====================================================================
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits
where REQUESTING_ENGINE_TRANSACTION_ID = "impossible";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	REQUESTING_ENGINE_TRANSACTION_ID	REQUESTING_ENGINE_TRANSACTION_ID	9	const	#	100.00	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits
where REQUESTING_ENGINE_TRANSACTION_ID > "impossible";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ALL	REQUESTING_ENGINE_TRANSACTION_ID	NULL	NULL	NULL	#	33.33	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits
where REQUESTING_ENGINE_TRANSACTION_ID < "2";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ALL	REQUESTING_ENGINE_TRANSACTION_ID	NULL	NULL	NULL	#	33.33	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits
where REQUESTING_ENGINE_TRANSACTION_ID = @requesting_trx_id;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	REQUESTING_ENGINE_TRANSACTION_ID	REQUESTING_ENGINE_TRANSACTION_ID	9	const	#	100.00	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits
where REQUESTING_ENGINE_TRANSACTION_ID = "impossible"
    and ENGINE = "impossible";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	REQUESTING_ENGINE_TRANSACTION_ID	REQUESTING_ENGINE_TRANSACTION_ID	139	const,const	#	100.00	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits
where REQUESTING_ENGINE_TRANSACTION_ID = @requesting_trx_id
and ENGINE = "impossible";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	REQUESTING_ENGINE_TRANSACTION_ID	REQUESTING_ENGINE_TRANSACTION_ID	139	const,const	#	100.00	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits
where REQUESTING_ENGINE_TRANSACTION_ID = @requesting_trx_id
and ENGINE > "impossible";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	REQUESTING_ENGINE_TRANSACTION_ID	REQUESTING_ENGINE_TRANSACTION_ID	9	const	#	33.33	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits
where REQUESTING_ENGINE_TRANSACTION_ID = @requesting_trx_id
and ENGINE < "2";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	REQUESTING_ENGINE_TRANSACTION_ID	REQUESTING_ENGINE_TRANSACTION_ID	9	const	#	33.33	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits
where REQUESTING_ENGINE_TRANSACTION_ID = @requesting_trx_id
and ENGINE = @engine;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	REQUESTING_ENGINE_TRANSACTION_ID	REQUESTING_ENGINE_TRANSACTION_ID	139	const,const	#	100.00	Using where
############# Explain End #########################################
flush status;
select COUNT(*)
from performance_schema.data_lock_waits
where REQUESTING_ENGINE_TRANSACTION_ID = @requesting_trx_id
and ENGINE = @engine;
COUNT(*)
#
OK: handler_read_key incremented
select BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id
from performance_schema.data_lock_waits
where (ENGINE = @engine)
and (REQUESTING_ENGINE_TRANSACTION_ID = @requesting_trx_id);
BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id
1

====================================================================
Testing index for columns BLOCKING_ENGINE_TRANSACTION_ID, ENGINE
====================================================================
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits
where BLOCKING_ENGINE_TRANSACTION_ID = "impossible";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	BLOCKING_ENGINE_TRANSACTION_ID	BLOCKING_ENGINE_TRANSACTION_ID	9	const	#	100.00	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits
where BLOCKING_ENGINE_TRANSACTION_ID > "impossible";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ALL	BLOCKING_ENGINE_TRANSACTION_ID	NULL	NULL	NULL	#	33.33	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits
where BLOCKING_ENGINE_TRANSACTION_ID < "2";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ALL	BLOCKING_ENGINE_TRANSACTION_ID	NULL	NULL	NULL	#	33.33	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits
where BLOCKING_ENGINE_TRANSACTION_ID = @blocking_trx_id;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	BLOCKING_ENGINE_TRANSACTION_ID	BLOCKING_ENGINE_TRANSACTION_ID	9	const	#	100.00	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits
where BLOCKING_ENGINE_TRANSACTION_ID = "impossible"
    and ENGINE = "impossible";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	BLOCKING_ENGINE_TRANSACTION_ID	BLOCKING_ENGINE_TRANSACTION_ID	139	const,const	#	100.00	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits
where BLOCKING_ENGINE_TRANSACTION_ID = @blocking_trx_id
and ENGINE = "impossible";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	BLOCKING_ENGINE_TRANSACTION_ID	BLOCKING_ENGINE_TRANSACTION_ID	139	const,const	#	100.00	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits
where BLOCKING_ENGINE_TRANSACTION_ID = @blocking_trx_id
and ENGINE > "impossible";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	BLOCKING_ENGINE_TRANSACTION_ID	BLOCKING_ENGINE_TRANSACTION_ID	9	const	#	33.33	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits
where BLOCKING_ENGINE_TRANSACTION_ID = @blocking_trx_id
and ENGINE < "2";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	BLOCKING_ENGINE_TRANSACTION_ID	BLOCKING_ENGINE_TRANSACTION_ID	9	const	#	33.33	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits
where BLOCKING_ENGINE_TRANSACTION_ID = @blocking_trx_id
and ENGINE = @engine;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	BLOCKING_ENGINE_TRANSACTION_ID	BLOCKING_ENGINE_TRANSACTION_ID	139	const,const	#	100.00	Using where
############# Explain End #########################################
flush status;
select COUNT(*)
from performance_schema.data_lock_waits
where BLOCKING_ENGINE_TRANSACTION_ID = @blocking_trx_id
and ENGINE = @engine;
COUNT(*)
#
OK: handler_read_key incremented
select REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id
from performance_schema.data_lock_waits
where (ENGINE = @engine)
and (BLOCKING_ENGINE_TRANSACTION_ID = @blocking_trx_id);
REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id
1

====================================================================
Testing index for columns REQUESTING_THREAD_ID, REQUESTING_EVENT_ID
====================================================================
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits force index(REQUESTING_THREAD_ID)
where REQUESTING_THREAD_ID = "impossible";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	REQUESTING_THREAD_ID	REQUESTING_THREAD_ID	9	const	#	100.00	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits force index(REQUESTING_THREAD_ID)
where REQUESTING_THREAD_ID > "impossible";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ALL	REQUESTING_THREAD_ID	NULL	NULL	NULL	#	33.33	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits force index(REQUESTING_THREAD_ID)
where REQUESTING_THREAD_ID < "2";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ALL	REQUESTING_THREAD_ID	NULL	NULL	NULL	#	33.33	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits force index(REQUESTING_THREAD_ID)
where REQUESTING_THREAD_ID = @requesting_thread_id;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	REQUESTING_THREAD_ID	REQUESTING_THREAD_ID	9	const	#	100.00	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits force index(REQUESTING_THREAD_ID)
where REQUESTING_THREAD_ID = "impossible"
    and REQUESTING_EVENT_ID = "impossible";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	REQUESTING_THREAD_ID	REQUESTING_THREAD_ID	18	const,const	#	100.00	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits force index(REQUESTING_THREAD_ID)
where REQUESTING_THREAD_ID = @requesting_thread_id
and REQUESTING_EVENT_ID = "impossible";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	REQUESTING_THREAD_ID	REQUESTING_THREAD_ID	18	const,const	#	100.00	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits force index(REQUESTING_THREAD_ID)
where REQUESTING_THREAD_ID = @requesting_thread_id
and REQUESTING_EVENT_ID > "impossible";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	REQUESTING_THREAD_ID	REQUESTING_THREAD_ID	9	const	#	33.33	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits force index(REQUESTING_THREAD_ID)
where REQUESTING_THREAD_ID = @requesting_thread_id
and REQUESTING_EVENT_ID < "2";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	REQUESTING_THREAD_ID	REQUESTING_THREAD_ID	9	const	#	33.33	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits force index(REQUESTING_THREAD_ID)
where REQUESTING_THREAD_ID = @requesting_thread_id
and REQUESTING_EVENT_ID = @requesting_event_id;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	REQUESTING_THREAD_ID	REQUESTING_THREAD_ID	18	const,const	#	100.00	Using where
############# Explain End #########################################
flush status;
select COUNT(*)
from performance_schema.data_lock_waits force index(REQUESTING_THREAD_ID)
where REQUESTING_THREAD_ID = @requesting_thread_id
and REQUESTING_EVENT_ID = @requesting_event_id;
COUNT(*)
#
OK: handler_read_key incremented
select BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id
from performance_schema.data_lock_waits
where (REQUESTING_THREAD_ID = @requesting_thread_id)
and (REQUESTING_EVENT_ID = @requesting_event_id);
BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id
1

====================================================================
Testing index for columns BLOCKING_THREAD_ID, BLOCKING_EVENT_ID
====================================================================
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits force index(BLOCKING_THREAD_ID)
where BLOCKING_THREAD_ID = "impossible";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	BLOCKING_THREAD_ID	BLOCKING_THREAD_ID	9	const	#	100.00	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits force index(BLOCKING_THREAD_ID)
where BLOCKING_THREAD_ID > "impossible";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ALL	BLOCKING_THREAD_ID	NULL	NULL	NULL	#	33.33	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits force index(BLOCKING_THREAD_ID)
where BLOCKING_THREAD_ID < "2";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ALL	BLOCKING_THREAD_ID	NULL	NULL	NULL	#	33.33	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits force index(BLOCKING_THREAD_ID)
where BLOCKING_THREAD_ID = @blocking_thread_id;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	BLOCKING_THREAD_ID	BLOCKING_THREAD_ID	9	const	#	100.00	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits force index(BLOCKING_THREAD_ID)
where BLOCKING_THREAD_ID = "impossible"
    and BLOCKING_EVENT_ID = "impossible";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	BLOCKING_THREAD_ID	BLOCKING_THREAD_ID	18	const,const	#	100.00	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits force index(BLOCKING_THREAD_ID)
where BLOCKING_THREAD_ID = @blocking_thread_id
and BLOCKING_EVENT_ID = "impossible";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	BLOCKING_THREAD_ID	BLOCKING_THREAD_ID	18	const,const	#	100.00	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits force index(BLOCKING_THREAD_ID)
where BLOCKING_THREAD_ID = @blocking_thread_id
and BLOCKING_EVENT_ID > "impossible";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	BLOCKING_THREAD_ID	BLOCKING_THREAD_ID	9	const	#	33.33	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits force index(BLOCKING_THREAD_ID)
where BLOCKING_THREAD_ID = @blocking_thread_id
and BLOCKING_EVENT_ID < "2";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	BLOCKING_THREAD_ID	BLOCKING_THREAD_ID	9	const	#	33.33	Using where
############ Explain for Query ####################################
explain select COUNT(*)
from performance_schema.data_lock_waits force index(BLOCKING_THREAD_ID)
where BLOCKING_THREAD_ID = @blocking_thread_id
and BLOCKING_EVENT_ID = @blocking_event_id;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	data_lock_waits	NULL	ref	BLOCKING_THREAD_ID	BLOCKING_THREAD_ID	18	const,const	#	100.00	Using where
############# Explain End #########################################
flush status;
select COUNT(*)
from performance_schema.data_lock_waits force index(BLOCKING_THREAD_ID)
where BLOCKING_THREAD_ID = @blocking_thread_id
and BLOCKING_EVENT_ID = @blocking_event_id;
COUNT(*)
#
OK: handler_read_key incremented
select REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id
from performance_schema.data_lock_waits
where (BLOCKING_THREAD_ID = @blocking_thread_id)
and (BLOCKING_EVENT_ID = @blocking_event_id);
REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id
1
# Connection con1
commit;
# Connection con2
commit;
# Connection con3
commit;
# Connection con4
commit;
# Connection default
drop table explain_test_db.explain_test_table;
drop database explain_test_db;