569 lines
29 KiB
Plaintext
569 lines
29 KiB
Plaintext
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;
|