1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
|
DESC sys.innodb_lock_waits;
Field Type Null Key Default Extra
wait_started datetime X X
wait_age time X X
wait_age_secs bigint X X
locked_table mediumtext X X
locked_table_schema varchar(64) X X
locked_table_name varchar(64) X X
locked_table_partition varchar(64) X X
locked_table_subpartition varchar(64) X X
locked_index varchar(64) X X
locked_type varchar(32) X X
waiting_trx_id bigint unsigned X X
waiting_trx_started datetime X X
waiting_trx_age time X X
waiting_trx_rows_locked bigint unsigned X X
waiting_trx_rows_modified bigint unsigned X X
waiting_pid bigint unsigned X X
waiting_query longtext X X
waiting_lock_id varchar(128) X X
waiting_lock_mode varchar(32) X X
blocking_trx_id bigint unsigned X X
blocking_pid bigint unsigned X X
blocking_query longtext X X
blocking_lock_id varchar(128) X X
blocking_lock_mode varchar(32) X X
blocking_trx_started datetime X X
blocking_trx_age time X X
blocking_trx_rows_locked bigint unsigned X X
blocking_trx_rows_modified bigint unsigned X X
sql_kill_blocking_query varchar(33) X X
sql_kill_blocking_connection varchar(27) X X
SELECT * FROM sys.innodb_lock_waits;
DESC sys.x$innodb_lock_waits;
Field Type Null Key Default Extra
wait_started datetime X X
wait_age time X X
wait_age_secs bigint X X
locked_table mediumtext X X
locked_table_schema varchar(64) X X
locked_table_name varchar(64) X X
locked_table_partition varchar(64) X X
locked_table_subpartition varchar(64) X X
locked_index varchar(64) X X
locked_type varchar(32) X X
waiting_trx_id bigint unsigned X X
waiting_trx_started datetime X X
waiting_trx_age time X X
waiting_trx_rows_locked bigint unsigned X X
waiting_trx_rows_modified bigint unsigned X X
waiting_pid bigint unsigned X X
waiting_query varchar(1024) X X
waiting_lock_id varchar(128) X X
waiting_lock_mode varchar(32) X X
blocking_trx_id bigint unsigned X X
blocking_pid bigint unsigned X X
blocking_query varchar(1024) X X
blocking_lock_id varchar(128) X X
blocking_lock_mode varchar(32) X X
blocking_trx_started datetime X X
blocking_trx_age time X X
blocking_trx_rows_locked bigint unsigned X X
blocking_trx_rows_modified bigint unsigned X X
sql_kill_blocking_query varchar(33) X X
sql_kill_blocking_connection varchar(27) X X
SELECT * FROM sys.x$innodb_lock_waits;
EXPLAIN SELECT * FROM sys.innodb_lock_waits;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 399996 100.00 NULL
2 DERIVED b NULL ALL NULL NULL NULL NULL NULL NULL Using temporary; Using filesort
2 DERIVED r NULL ALL NULL NULL NULL NULL NULL NULL Using join buffer (hash join)
2 DERIVED w NULL ALL PRIMARY,REQUESTING_ENGINE_LOCK_ID,BLOCKING_ENGINE_LOCK_ID NULL NULL NULL 99999 100.00 Using where; Using join buffer (hash join)
2 DERIVED bl NULL eq_ref PRIMARY PRIMARY 644 performance_schema.w.BLOCKING_ENGINE_LOCK_ID,performance_schema.w.ENGINE 1 100.00 NULL
2 DERIVED rl NULL eq_ref PRIMARY PRIMARY 644 performance_schema.w.REQUESTING_ENGINE_LOCK_ID,performance_schema.w.ENGINE 1 100.00 NULL
Warnings:
Note 1003 /* select#1 */ select `sys`.`innodb_lock_waits`.`wait_started` AS `wait_started`,`sys`.`innodb_lock_waits`.`wait_age` AS `wait_age`,`sys`.`innodb_lock_waits`.`wait_age_secs` AS `wait_age_secs`,`sys`.`innodb_lock_waits`.`locked_table` AS `locked_table`,`sys`.`innodb_lock_waits`.`locked_table_schema` AS `locked_table_schema`,`sys`.`innodb_lock_waits`.`locked_table_name` AS `locked_table_name`,`sys`.`innodb_lock_waits`.`locked_table_partition` AS `locked_table_partition`,`sys`.`innodb_lock_waits`.`locked_table_subpartition` AS `locked_table_subpartition`,`sys`.`innodb_lock_waits`.`locked_index` AS `locked_index`,`sys`.`innodb_lock_waits`.`locked_type` AS `locked_type`,`sys`.`innodb_lock_waits`.`waiting_trx_id` AS `waiting_trx_id`,`sys`.`innodb_lock_waits`.`waiting_trx_started` AS `waiting_trx_started`,`sys`.`innodb_lock_waits`.`waiting_trx_age` AS `waiting_trx_age`,`sys`.`innodb_lock_waits`.`waiting_trx_rows_locked` AS `waiting_trx_rows_locked`,`sys`.`innodb_lock_waits`.`waiting_trx_rows_modified` AS `waiting_trx_rows_modified`,`sys`.`innodb_lock_waits`.`waiting_pid` AS `waiting_pid`,`sys`.`innodb_lock_waits`.`waiting_query` AS `waiting_query`,`sys`.`innodb_lock_waits`.`waiting_lock_id` AS `waiting_lock_id`,`sys`.`innodb_lock_waits`.`waiting_lock_mode` AS `waiting_lock_mode`,`sys`.`innodb_lock_waits`.`blocking_trx_id` AS `blocking_trx_id`,`sys`.`innodb_lock_waits`.`blocking_pid` AS `blocking_pid`,`sys`.`innodb_lock_waits`.`blocking_query` AS `blocking_query`,`sys`.`innodb_lock_waits`.`blocking_lock_id` AS `blocking_lock_id`,`sys`.`innodb_lock_waits`.`blocking_lock_mode` AS `blocking_lock_mode`,`sys`.`innodb_lock_waits`.`blocking_trx_started` AS `blocking_trx_started`,`sys`.`innodb_lock_waits`.`blocking_trx_age` AS `blocking_trx_age`,`sys`.`innodb_lock_waits`.`blocking_trx_rows_locked` AS `blocking_trx_rows_locked`,`sys`.`innodb_lock_waits`.`blocking_trx_rows_modified` AS `blocking_trx_rows_modified`,`sys`.`innodb_lock_waits`.`sql_kill_blocking_query` AS `sql_kill_blocking_query`,`sys`.`innodb_lock_waits`.`sql_kill_blocking_connection` AS `sql_kill_blocking_connection` from `sys`.`innodb_lock_waits`
EXPLAIN SELECT * FROM sys.x$innodb_lock_waits;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 399996 100.00 NULL
2 DERIVED b NULL ALL NULL NULL NULL NULL NULL NULL Using temporary; Using filesort
2 DERIVED r NULL ALL NULL NULL NULL NULL NULL NULL Using join buffer (hash join)
2 DERIVED w NULL ALL PRIMARY,REQUESTING_ENGINE_LOCK_ID,BLOCKING_ENGINE_LOCK_ID NULL NULL NULL 99999 100.00 Using where; Using join buffer (hash join)
2 DERIVED bl NULL eq_ref PRIMARY PRIMARY 644 performance_schema.w.BLOCKING_ENGINE_LOCK_ID,performance_schema.w.ENGINE 1 100.00 NULL
2 DERIVED rl NULL eq_ref PRIMARY PRIMARY 644 performance_schema.w.REQUESTING_ENGINE_LOCK_ID,performance_schema.w.ENGINE 1 100.00 NULL
Warnings:
Note 1003 /* select#1 */ select `sys`.`x$innodb_lock_waits`.`wait_started` AS `wait_started`,`sys`.`x$innodb_lock_waits`.`wait_age` AS `wait_age`,`sys`.`x$innodb_lock_waits`.`wait_age_secs` AS `wait_age_secs`,`sys`.`x$innodb_lock_waits`.`locked_table` AS `locked_table`,`sys`.`x$innodb_lock_waits`.`locked_table_schema` AS `locked_table_schema`,`sys`.`x$innodb_lock_waits`.`locked_table_name` AS `locked_table_name`,`sys`.`x$innodb_lock_waits`.`locked_table_partition` AS `locked_table_partition`,`sys`.`x$innodb_lock_waits`.`locked_table_subpartition` AS `locked_table_subpartition`,`sys`.`x$innodb_lock_waits`.`locked_index` AS `locked_index`,`sys`.`x$innodb_lock_waits`.`locked_type` AS `locked_type`,`sys`.`x$innodb_lock_waits`.`waiting_trx_id` AS `waiting_trx_id`,`sys`.`x$innodb_lock_waits`.`waiting_trx_started` AS `waiting_trx_started`,`sys`.`x$innodb_lock_waits`.`waiting_trx_age` AS `waiting_trx_age`,`sys`.`x$innodb_lock_waits`.`waiting_trx_rows_locked` AS `waiting_trx_rows_locked`,`sys`.`x$innodb_lock_waits`.`waiting_trx_rows_modified` AS `waiting_trx_rows_modified`,`sys`.`x$innodb_lock_waits`.`waiting_pid` AS `waiting_pid`,`sys`.`x$innodb_lock_waits`.`waiting_query` AS `waiting_query`,`sys`.`x$innodb_lock_waits`.`waiting_lock_id` AS `waiting_lock_id`,`sys`.`x$innodb_lock_waits`.`waiting_lock_mode` AS `waiting_lock_mode`,`sys`.`x$innodb_lock_waits`.`blocking_trx_id` AS `blocking_trx_id`,`sys`.`x$innodb_lock_waits`.`blocking_pid` AS `blocking_pid`,`sys`.`x$innodb_lock_waits`.`blocking_query` AS `blocking_query`,`sys`.`x$innodb_lock_waits`.`blocking_lock_id` AS `blocking_lock_id`,`sys`.`x$innodb_lock_waits`.`blocking_lock_mode` AS `blocking_lock_mode`,`sys`.`x$innodb_lock_waits`.`blocking_trx_started` AS `blocking_trx_started`,`sys`.`x$innodb_lock_waits`.`blocking_trx_age` AS `blocking_trx_age`,`sys`.`x$innodb_lock_waits`.`blocking_trx_rows_locked` AS `blocking_trx_rows_locked`,`sys`.`x$innodb_lock_waits`.`blocking_trx_rows_modified` AS `blocking_trx_rows_modified`,`sys`.`x$innodb_lock_waits`.`sql_kill_blocking_query` AS `sql_kill_blocking_query`,`sys`.`x$innodb_lock_waits`.`sql_kill_blocking_connection` AS `sql_kill_blocking_connection` from `sys`.`x$innodb_lock_waits`
|
