上QQ阅读APP看本书，新人免费读10天
设备和账号都新为新人
9.2 查看是否有事务锁等待
通过innodb_lock_waits视图可以查看InnoDB当前事务锁等待信息，默认按照发生锁等待的开始时间升序排列——wait_started字段即innodb_trx表的trx_wait_started字段。数据来源：information_schema下的innodb_trx、innodb_locks、innodb_lock_waits表（注：在MySQL 8.0及之后的版本中，该视图的信息来源为information_schema下的innodb_trx表、performance_schema下的data_locks表和data_lock_waits表）。
下面是使用innodb_lock_waits视图查询的结果集。
mysql> 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 # 锁已经等待了几秒钟，该值是一个整型值。该字段是MySQL 5.7.9中新增的 locked_table: `luoxiaobo`.`test` # 锁等待的表名称。此字段值格式为：schema_name.table_name locked_index: GEN_CLUST_INDEX # 锁等待的索引名称 locked_type: RECORD # 锁等待的锁类型 waiting_trx_id: 66823 # 锁等待的事务ID 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 # 发生锁等待的事务的processlist_id waiting_query: select ＊ from test limit 1 for update # 发生锁等待的事务的SQL语句文本 waiting_lock_id: 66823:106:3:2 # 发生锁等待的锁ID waiting_lock_mode: X # 发生锁等待的锁模式 blocking_trx_id: 66822 # 持有锁的事务ID blocking_pid: 6 # 持有锁的事务的processlist_id blocking_query: NULL # 持有锁的事务的SQL语句文本 blocking_lock_id: 66822:106:3:2 # 持有锁的锁ID 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 # 执行KILL语句来杀死持有锁的查询语句（而不是终止 会话）。该字段是MySQL 5.7.9中新增的 sql_kill_blocking_connection: KILL 6 # 执行 KILL 语句以终止持有锁的语句的会话。该字段是 MySQL 5.7.9中新增的 1 row in set, 3 warnings（0.00 sec）
下面贴出视图查询语句文本。
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;