innodb_lock_waits,x$innodb_lock_waits - xiaoboluo768/mysql-system-schema GitHub Wiki
-
InnoDB当前锁等待信息，默认按照发生锁等待的开始时间升序排序--wait_started字段即innodb_trx表的trx_wait_started字段。数据来源：information_schema的innodb_trx、innodb_locks、innodb_lock_waits（注：在8.0及其之后的版本中，该视图的信息来源为information_schema的innodb_trx、performance_schema的data_locks和data_lock_waits）
-
innodb_lock_waits和x$innodb_lock_waits视图字段含义如下：
- wait_started：发生锁等待的开始时间
- wait_age：锁已经等待了多久，该值是一个时间格式值
- wait_age_secs：锁已经等待了几秒钟，该值是一个整型值，MySQL 5.7.9中新增
- locked_table：锁等待的表名称。此列值格式为：schema_name.table_name
- locked_index：锁等待的索引名称
- locked_type：锁等待的锁类型
- waiting_trx_id：锁等待的事务ID
- waiting_trx_started：发生锁等待的事务开始时间
- waiting_trx_age：发生锁等待的事务总的锁等待时间，该值是一个时间格式
- waiting_trx_rows_locked：发生锁等待的事务已经锁定的行数（如果是复杂事务会累计）
- waiting_trx_rows_modified：发生锁等待的事务已经修改的行数（如果是复杂事务会累计）
- waiting_pid：发生锁等待的事务的processlist_id
- waiting_query：发生锁等待的事务SQL语句文本
- waiting_lock_id：发生锁等待的锁ID
- waiting_lock_mode：发生锁等待的锁模式
- blocking_trx_id：阻塞锁等待的事务ID
- blocking_pid：阻塞锁等待的事务processlist_id
- blocking_query：阻塞锁等待的事务的SQL语句文本
- blocking_lock_id：阻塞锁等待的锁ID
- blocking_lock_mode：阻塞锁等待的锁模式
- blocking_trx_started：阻塞锁等待的事务的开始时间
- blocking_trx_age：阻塞锁等待的事务已执行了多长时间，该值为时间格式值
- blocking_trx_rows_locked：阻塞锁等待的事务的锁定行数
- blocking_trx_rows_modified：阻塞锁等待的事务需要修改的行数
- sql_kill_blocking_query：执行KILL语句来杀死阻塞锁等待的查询语句（而不是终止会话）。该列在MySQL 5.7.9中新增
- sql_kill_blocking_connection：执行KILL语句以终止阻塞锁等待的语句的会话。该列在MySQL 5.7.9中新增
-
视图定义语句
# innodb_lock_waits
CREATE OR REPLACE
ALGORITHM = TEMPTABLE
DEFINER = 'root'@'localhost'
SQL SECURITY INVOKER
VIEW innodb_lock_waits (
wait_started, wait_age, wait_age_secs, locked_table, locked_index, locked_type, waiting_trx_id, waiting_trx_started, waiting_trx_age,
waiting_trx_rows_locked, waiting_trx_rows_modified, waiting_pid, waiting_query, waiting_lock_id, waiting_lock_mode, blocking_trx_id,
blocking_pid, blocking_query, blocking_lock_id, blocking_lock_mode, blocking_trx_started, blocking_trx_age, blocking_trx_rows_locked,
blocking_trx_rows_modified, sql_kill_blocking_query, sql_kill_blocking_connection
) AS
SELECT r.trx_wait_started AS wait_started,
TIMEDIFF(NOW(), r.trx_wait_started) AS wait_age,
TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS wait_age_secs,
rl.lock_table AS locked_table,
rl.lock_index AS locked_index,
rl.lock_type AS locked_type,
r.trx_id AS waiting_trx_id,
r.trx_started as waiting_trx_started,
TIMEDIFF(NOW(), r.trx_started) AS waiting_trx_age,
r.trx_rows_locked AS waiting_trx_rows_locked,
r.trx_rows_modified AS waiting_trx_rows_modified,
r.trx_mysql_thread_id AS waiting_pid,
sys.format_statement(r.trx_query) AS waiting_query,
rl.lock_id AS waiting_lock_id,
rl.lock_mode AS waiting_lock_mode,
b.trx_id AS blocking_trx_id,
b.trx_mysql_thread_id AS blocking_pid,
sys.format_statement(b.trx_query) AS blocking_query,
bl.lock_id AS blocking_lock_id,
bl.lock_mode AS blocking_lock_mode,
b.trx_started AS blocking_trx_started,
TIMEDIFF(NOW(), b.trx_started) AS blocking_trx_age,
b.trx_rows_locked AS blocking_trx_rows_locked,
b.trx_rows_modified AS blocking_trx_rows_modified,
CONCAT('KILL QUERY ', b.trx_mysql_thread_id) AS sql_kill_blocking_query,
CONCAT('KILL ', b.trx_mysql_thread_id) AS sql_kill_blocking_connection
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
INNER JOIN information_schema.innodb_locks bl ON bl.lock_id = w.blocking_lock_id
INNER JOIN information_schema.innodb_locks rl ON rl.lock_id = w.requested_lock_id
ORDER BY r.trx_wait_started;
# x$innodb_lock_waits：在8.0之前的版本，两者无区别
- 视图查询信息示例
root@localhost : sys 12:41:45> select * from innodb_lock_waits\G;
*************************** 1. row ***************************
wait_started: 2017-09-07 00:42:32
wait_age: 00:00:12
wait_age_secs: 12
locked_table: `luoxiaobo`.`test`
locked_index: GEN_CLUST_INDEX
locked_type: RECORD
waiting_trx_id: 66823
waiting_trx_started: 2017-09-07 00:42:32
waiting_trx_age: 00:00:12
waiting_trx_rows_locked: 1
waiting_trx_rows_modified: 0
waiting_pid: 7
waiting_query: select * from test limit 1 for update
waiting_lock_id: 66823:106:3:2
waiting_lock_mode: X
blocking_trx_id: 66822
blocking_pid: 6
blocking_query: NULL
blocking_lock_id: 66822:106:3:2
blocking_lock_mode: X
blocking_trx_started: 2017-09-07 00:42:19
blocking_trx_age: 00:00:25
blocking_trx_rows_locked: 1
blocking_trx_rows_modified: 0
sql_kill_blocking_query: KILL QUERY 6
sql_kill_blocking_connection: KILL 6
1 row in set, 3 warnings (0.00 sec)
-
PS：8.0中废弃information_schema.innodb_locks和information_schema.innodb_lock_waits，迁移到performance_schema.data_locks和performance_schema.data_lock_waits，详见链接：
-
参考链接：https://dev.mysql.com/doc/refman/5.7/en/sys-innodb-lock-waits.html
上一篇： innodb_buffer_stats_by_table,x$innodb_buffer_stats_by_table视图 |