在上一篇《等待事件统计视图 | 全方位认识 sys 系统库》中，我们介绍了sys 系统库中的等待事件统计视图，本期的内容先给大家介绍会话信息和锁等待信息查询视图，通过这些视图我们可以清晰地知道每个会话正在做什么事情，是否存在锁等待。下面请跟随我们一起开始 sys 系统库的系统学习之旅吧～
PS：由于本文中所提及的视图功能的特殊性（DBA日常工作中除了排查慢SQL之外，另外一个可能需要占用大量精力的地方可能就是锁问题分析），所以下文中会列出相应视图中的select语句文本，以便大家更直观地学习它们。
01
innodb_lock_waits,x$innodb_lock_waits
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）
视图查询语句文本
# 不带x$前缀的视图的查询语句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_connectionFROM 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_idORDER BY r.trx_wait_started;# x$innodb_lock_waits：在8.0之前的版本，两者无区别
下面我们看看使用该视图查询返回的结果
# 不带x$前缀的视图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:12waiting_trx_rows_locked: 1waiting_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:25blocking_trx_rows_locked: 1blocking_trx_rows_modified: 0sql_kill_blocking_query: KILL QUERY 6sql_kill_blocking_connection: KILL 61 row in set, 3 warnings (0.00 sec)
视图字段含义如下：
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中新增
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/8.0/en/data-locks-table.html
https://dev.mysql.com/doc/refman/8.0/en/data-lock-waits-table.html
02
processlist,x$processlist
包含所有前台和后台线程的processlist信息，默认按照进程等待时间和最近一个语句执行完成的时间降序排序。数据来源：performance_schema的threads、events_waits_current、events_statements_current、events_stages_current 、events_transactions_current 、session_connect_attrs表和 sys 系统库的 x$memory_by_thread_by_current_bytess视图
这些视图列出了进程相关的较为详细的信息，比SHOW PROCESSLIST语句和INFORMATION_SCHEMA PROCESSLIST表更完整，且对该视图的查询是非阻塞的（因为不是从information_schema.processlist表中获取数据的，对processlist表查询是阻塞的）
视图查询语句文本
# 不带x$前缀的视图SELECT pps.thread_id AS thd_id, pps.processlist_id AS conn_id, IF(pps.name = 'thread/sql/one_connection', CONCAT(pps.processlist_user, '@', pps.processlist_host), REPLACE(pps.name, 'thread/', '')) user, pps.processlist_db AS db, pps.processlist_command AS command, pps.processlist_state AS state, pps.processlist_time AS time, sys.format_statement(pps.processlist_info) AS current_statement, IF(esc.end_event_id IS NULL, sys.format_time(esc.timer_wait), NULL) AS statement_latency, IF(esc.end_event_id IS NULL, ROUND(100 * (estc.work_completed / estc.work_estimated), 2), NULL) AS progress, sys.format_time(esc.lock_time) AS lock_latency, esc.rows_examined AS rows_examined, esc.rows_sent AS rows_sent, esc.rows_affected AS rows_affected, esc.created_tmp_tables AS tmp_tables, esc.created_tmp_disk_tables AS tmp_disk_tables, IF(esc.no_good_index_used > 0 OR esc.no_index_used > 0, 'YES', 'NO') AS full_scan, IF(esc.end_event_id IS NOT NULL, sys.format_statement(esc.sql_text), NULL) AS last_statement, IF(esc.end_event_id IS NOT NULL, sys.format_time(esc.timer_wait), NULL) AS last_statement_latency, sys.format_bytes(mem.current_allocated) AS current_memory, ewc.event_name AS last_wait, IF(ewc.end_event_id IS NULL AND ewc.event_name IS NOT NULL, 'Still Waiting', sys.format_time(ewc.timer_wait)) last_wait_latency, ewc.source, sys.format_time(etc.timer_wait) AS trx_latency, etc.state AS trx_state, etc.autocommit AS trx_autocommit, conattr_pid.attr_value as pid, conattr_progname.attr_value as program_nameFROM performance_schema.threads AS ppsLEFT JOIN performance_schema.events_waits_current AS ewc USING (thread_id)LEFT JOIN performance_schema.events_stages_current AS estc USING (thread_id)LEFT JOIN performance_schema.events_statements_current AS esc USING (thread_id)LEFT JOIN performance_schema.events_transactions_current AS etc USING (thread_id)LEFT JOIN sys.x$memory_by_thread_by_current_bytes AS mem USING (thread_id)LEFT JOIN performance_schema.session_connect_attrs AS conattr_pidON conattr_pid.processlist_id=pps.processlist_id and conattr_pid.attr_name='_pid'LEFT JOIN performance_schema.session_connect_attrs AS conattr_prognameON conattr_progname.processlist_id=pps.processlist_id and conattr_progname.attr_name='program_name'ORDER BY pps.processlist_time DESC, last_wait_latency DESC;# 带x$前缀的视图查询语句与不带x$前缀的视图查询语句相比，只是少了单位格式化函数......
下面我们看看使用该视图查询返回的结果
# 不带x$前缀的视图admin@localhost : sys 04:27:20> select * from processlist where program_name='mysql' and trx_state is not null limit 1\G*************************** 1. row *************************** thd_id: 49 conn_id: 7 user: admin@localhost db: sbtest command: Sleep state: NULL time: 89current_statement: NULLstatement_latency: NULL progress: NULL lock_latency: 157.00 us rows_examined: 1000 rows_sent: 1000 rows_affected: 0 tmp_tables: 0 tmp_disk_tables: 0 full_scan: YES last_statement: select * from sbtest1 limit 1000last_statement_latency: 2.06 ms current_memory: 0 bytes last_wait: idlelast_wait_latency: Still Waiting source: socket_connection.cc:69 trx_latency: 1.49 ms trx_state: COMMITTED trx_autocommit: YES pid: 3927 program_name: mysql1 row in set (0.13 sec)# 带x$前缀的视图admin@localhost : sys 04:27:28> select * from x$processlist where program_name='mysql' and trx_state is not null limit 1\G;*************************** 1. row *************************** thd_id: 49 conn_id: 7 user: admin@localhost db: sbtest command: Sleep state: NULL time: 150current_statement: NULLstatement_latency: NULL progress: NULL lock_latency: 157000000 rows_examined: 1000 rows_sent: 1000 rows_affected: 0 tmp_tables: 0 tmp_disk_tables: 0 full_scan: YES last_statement: select * from sbtest1 limit 1000last_statement_latency: 2055762000 current_memory: 0 last_wait: idlelast_wait_latency: Still Waiting source: socket_connection.cc:69 trx_latency: 1490662000 trx_state: COMMITTED trx_autocommit: YES pid: 3927 program_name: mysql1 row in set (0.14 sec)
视图字段含义如下：
thd_id：内部threqd ID
conn_id：连接ID，即processlist id
user：对于前台线程，该字段值为account名称，对于后台线程，该字段值为后台线程名称
db：线程的默认数据库，如果没有默认数据库，则该字段值为NULL
command：对于前台线程，表示线程正在执行的客户端代码对应的command名称，如果会话处于空闲状态则该字段值为'Sleep ',对于后台超线程，该字段值为NULL
state：表示线程正在做什么：什么事件或状态，与information_schema.processlist表中的state字段值一样
time：表示线程处于当前状态已经持续了多长时间(秒)
current_statement：线程当前正在执行的语句，如果当前没有执行任何语句，该字段值为NULL
statement_latency：线程当前语句已经执行了多长时间，该字段在MySQL 5.7.9中新增
progress：在支持进度报告的阶段事件中统计的工作进度百分比。 该字段在MySQL 5.7.9中新增
lock_latency：当前语句的锁等待时间
rows_examined：当前语句从存储引擎检查的数据行数
rows_sent：当前语句返回给客户端的数据行数
rows_affected：受当前语句影响的数据行数（DML语句对数据执行变更才会影响行）
tmp_tables：当前语句创建的内部内存临时表的数量
tmp_disk_tables：当前语句创建的内部磁盘临时表的数量
full_scan：当前语句执行的全表扫描次数
last_statement：如果在performance_schema.threads表中没有找到正在执行的语句或正在等待执行的语句，那么在该字段可以显示线程执行的最后一个语句(在performance_schema.events_statements_current表中查找，该表中会为每一个线程保留最后一条语句执行的事件信息，其他current后缀的事件记录表也类似)
last_statement_latency：线程执行的最近一个语句执行了多长时间
current_memory：当前线程分配的字节数
last_wait：线程最近的等待事件名称
last_wait_latency：线程最近的等待事件的等待时间(执行时间)
source：线程最近的等待事件的instruments所在源文件和行号
trx_latency：线程当前正在执行的事务已经执行了多长时间，该列在MySQL 5.7.9中新增
trx_state：线程当前正在执行的事务的状态，该列在MySQL 5.7.9中新增
trx_autocommit：线程当前正在执行的事务的提交模式，有效值为：'ACTIVE','COMMITTED','ROLLED BACK'，该列在MySQL 5.7.9中新增
pid：客户端进程ID，该列在MySQL 5.7.9中新增
program_name：客户端程序名称，该列在MySQL 5.7.9中新增
03
session,x$session
查看当前用户会话的进程列表信息，与processlist&x$processlist视图类似，但是session视图过滤掉了后台线程，只显示前台(用户)线程相关的统计数据，数据来源：sys.processlist
该视图在MySQL 5.7.9中新增
视图查询语句
# 不带x$的视图查询语句## 只需要在processlist视图的查询语句上加上如下条件即可......[WHERE] conn_id IS NOT NULL AND command != 'Daemon';# 带x$前缀的视图查询语句与不带x$前缀的视图查询语句相比，只是少了单位格式化函数......
下面我们看看使用该视图查询返回的结果
# 不带x$前缀的视图admin@localhost : sys 12:44:22> select * from session where command='query' and conn_id!=connection_id()\G*************************** 1. row *************************** thd_id: 48 conn_id: 6 user: admin@localhost db: xiaoboluo command: Query state: Sending data time: 72current_statement: select * from test limit 1 for updatestatement_latency: 1.20 m progress: NULL lock_latency: 169.00 us rows_examined: 0 rows_sent: 0 rows_affected: 0 tmp_tables: 0 tmp_disk_tables: 0 full_scan: NO last_statement: NULLlast_statement_latency: NULL current_memory: 461 bytes last_wait: wait/io/table/sql/handlerlast_wait_latency: Still Waiting source: handler.cc:3185 trx_latency: NULL trx_state: NULL trx_autocommit: NULL pid: 3788 program_name: mysql1 row in set (0.15 sec)# 带x$前缀的视图admin@localhost : sys 12:45:09> select * from x$session where command='query' and conn_id!=connection_id()\G;*************************** 1. row *************************** thd_id: 48 conn_id: 6 user: admin@localhost db: xiaoboluo command: Query state: Sending data time: 91current_statement: select * from test limit 1 for updatestatement_latency: 91077336919000 progress: NULL lock_latency: 169000000 rows_examined: 0 rows_sent: 0 rows_affected: 0 tmp_tables: 0 tmp_disk_tables: 0 full_scan: NO last_statement: NULLlast_statement_latency: NULL current_memory: 461 last_wait: wait/io/table/sql/handlerlast_wait_latency: Still Waiting source: handler.cc:3185 trx_latency: NULL trx_state: NULL trx_autocommit: NULL pid: 3788 program_name: mysql1 row in set (0.13 sec)
视图字段含义
与processlist,x$processlist视图相同，但session视图排除了后台线程和Daemon线程，只查询用户连接线程的信息
04
schema_table_lock_waits,x$schema_table_lock_waits
查看当前链接线程的MDL锁等待信息，显示哪些会话被MDL锁阻塞，是谁阻塞了这些会话，数据来源：performance_schema下的threads、metadata_locks、events_statements_current表
MDL锁的instruments默认没有启用，要使用需要显式开启，如下： * 启用MDL锁的instruments：update setup_instruments set enabled='yes',timed='yes' where name='wait/lock/metadata/sql/mdl'; * 或者也可以使用sys 系统库下的辅助性视图操作：call sys.ps_setup_enable_instrument('wait/lock/metadata/sql/mdl');
该视图在MySQL 5.7.9中新增
视图定义语句文本
# 不带x$的视图查询语句SELECT g.object_schema AS object_schema, g.object_name AS object_name, pt.thread_id AS waiting_thread_id, pt.processlist_id AS waiting_pid, sys.ps_thread_account(p.owner_thread_id) AS waiting_account, p.lock_type AS waiting_lock_type, p.lock_duration AS waiting_lock_duration, sys.format_statement(pt.processlist_info) AS waiting_query, pt.processlist_time AS waiting_query_secs, ps.rows_affected AS waiting_query_rows_affected, ps.rows_examined AS waiting_query_rows_examined, gt.thread_id AS blocking_thread_id, gt.processlist_id AS blocking_pid, sys.ps_thread_account(g.owner_thread_id) AS blocking_account, g.lock_type AS blocking_lock_type, g.lock_duration AS blocking_lock_duration, CONCAT('KILL QUERY ', gt.processlist_id) AS sql_kill_blocking_query, CONCAT('KILL ', gt.processlist_id) AS sql_kill_blocking_connectionFROM performance_schema.metadata_locks gINNER JOIN performance_schema.metadata_locks pON g.object_type = p.object_typeAND g.object_schema = p.object_schemaAND g.object_name = p.object_nameAND g.lock_status = 'GRANTED'AND p.lock_status = 'PENDING'INNER JOIN performance_schema.threads gt ON g.owner_thread_id = gt.thread_idINNER JOIN performance_schema.threads pt ON p.owner_thread_id = pt.thread_idLEFT JOIN performance_schema.events_statements_current gs ON g.owner_thread_id = gs.thread_idLEFT JOIN performance_schema.events_statements_current ps ON p.owner_thread_id = ps.thread_idWHERE g.object_type = 'TABLE';# 带x$前缀的视图查询语句与不带x$前缀的视图查询语句相比，只是少了单位格式化函数......
下面我们看看使用该视图查询返回的结果
admin@localhost : sys 11:31:57> select * from schema_table_lock_waits\G;*************************** 1. row *************************** object_schema: xiaoboluo object_name: test waiting_thread_id: 1217 waiting_pid: 1175 waiting_account: admin@localhost waiting_lock_type: EXCLUSIVE waiting_lock_duration: TRANSACTION waiting_query: alter table test add index i_k(test) waiting_query_secs: 58waiting_query_rows_affected: 0waiting_query_rows_examined: 0 blocking_thread_id: 49 blocking_pid: 7 blocking_account: admin@localhost blocking_lock_type: SHARED_WRITE blocking_lock_duration: TRANSACTIONsql_kill_blocking_query: KILL QUERY 7sql_kill_blocking_connection: KILL 7*************************** 2. row *************************** object_schema: xiaoboluo object_name: test waiting_thread_id: 1217 waiting_pid: 1175 waiting_account: admin@localhost waiting_lock_type: EXCLUSIVE waiting_lock_duration: TRANSACTION waiting_query: alter table test add index i_k(test) waiting_query_secs: 58waiting_query_rows_affected: 0waiting_query_rows_examined: 0 blocking_thread_id: 1217 blocking_pid: 1175 blocking_account: admin@localhost blocking_lock_type: SHARED_UPGRADABLE blocking_lock_duration: TRANSACTIONsql_kill_blocking_query: KILL QUERY 1175sql_kill_blocking_connection: KILL 11752 rows in set (0.00 sec)
视图字段含义如下：
object_schema：发生MDL锁等待的schema名称
OBJECT_NAME：MDL锁等待监控对象的名称
waiting_thread_id：正在等待MDL锁的thread ID
waiting_pid：正在等待MDL锁的processlist ID
waiting_account：正在等待MDL锁的线程关联的account名称
waiting_lock_type：被阻塞的线程正在等待的MDL锁类型
waiting_lock_duration：该字段来自元数据锁子系统中的锁定时间。有效值为：STATEMENT、TRANSACTION、EXPLICIT，STATEMENT和TRANSACTION值分别表示在语句或事务结束时会释放的锁。 EXPLICIT值表示可以在语句或事务结束时被会保留，需要显式释放的锁，例如：使用FLUSH TABLES WITH READ LOCK获取的全局锁
waiting_query：正在等待MDL锁的线程对应的语句文本
waiting_query_secs：正在等待MDL锁的语句已经等待了多长时间（秒）
waiting_query_rows_affected：受正在等待MDL锁的语句影响的数据行数（该字段来自performance_schema.events_statement_current表，该表中记录的是语句事件，如果语句是多表联结查询，则该语句可能已经执行了一部分DML语句，所以哪怕该语句当前被其他线程阻塞了，被阻塞线程的这个字段也可能出现大于0的值）
waiting_query_rows_examined：正在等待MDL锁的语句从存储引擎检查的数据行数（同理，该字段来自performance_schema.events_statement_current表）
blocking_thread_id：持有MDL锁的thread ID
blocking_pid：持有MDL锁的processlist ID
blocking_account：持有MDL锁的线程关联的account名称
blocking_lock_type：持有MDL锁的锁类型
blocking_lock_duration：与waiting_lock_duration字段解释相同，只是该值与持有MDL锁的线程相关
sql_kill_blocking_query：生成的KILL掉持有MDL锁的查询的语句
sql_kill_blocking_connection：生成的KILL掉持有MDL锁对应会话的语句
本期内容就介绍到这里，本期内容参考链接如下：
https://dev.mysql.com/doc/refman/5.7/en/sys-schema-table-lock-waits.html
https://dev.mysql.com/doc/refman/5.7/en/sys-innodb-lock-waits.html
https://dev.mysql.com/doc/refman/5.7/en/sys-processlist.html
https://dev.mysql.com/doc/refman/5.7/en/sys-session.html
"翻过这座山，你就可以看到一片海！"。坚持阅读我们的"全方位认识 sys 系统库"系列文章分享，你就可以系统地学完它。 谢谢你的阅读，我们下期不见不散！
| 作者简介
罗小波·数据库技术专家
《千金良方——MySQL性能优化金字塔法则》、《数据生态：MySQL复制技术与生产实践》作者之一。熟悉MySQL体系结构，擅长数据库的整体调优，喜好专研开源技术，并热衷于开源技术的推广，在线上线下做过多次公开的数据库专题分享，发表过近100篇数据库相关的研究文章。
全文完。
Enjoy MySQL :)
叶老师的「MySQL核心优化」大课已升级到MySQL 8.0，扫码开启MySQL 8.0修行之旅吧
