上QQ阅读APP看本书，新人免费读10天
设备和账号都新为新人
9.3 查看是否有MDL锁等待
通过schema_table_lock_waits视图可以查看当前链接线程的MDL锁等待信息，显示哪些会话被MDL锁阻塞，是谁阻塞了这些会话，数据来源：performance_schema下的threads、metadata_locks、events_statements_current表。该视图是MySQL 5.7.9中新增的。
下面是使用schema_table_lock_waits视图查询的结果集。
# 首先需要启用与MDL锁等待事件相关的instruments mysql> call sys.ps_setup_enable_instrument（'wait/lock/metadata/sql/mdl'）; +-----------------------+ | summary | +-----------------------+ | Enabled 1 instruments | +-----------------------+ 1 row in set（0.01 sec） Query OK, 0 rows affected（0.01 sec） # 然后 ，使用innodb_lock_waits视图进行查询（注意：请自行模拟一个事务不提交，另一个事务执行DDL操作的场景，即可查询到类似如下的MDL锁等待信息） mysql> select ＊ from schema_table_lock_waits\G ＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊ 1. row ＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊ object_schema: xiaoboluo # 发生MDL锁等待的schema名称 object_name: test # 正在等待MDL锁的表名称 waiting_thread_id: 1217 # 正在等待MDL锁的线程ID waiting_pid: 1175 # 正在等待MDL锁的processlist_id waiting_account: admin@localhost # 正在等待MDL锁的与线程关联的account名称 waiting_lock_type: EXCLUSIVE # 被阻塞的线程正在等待的MDL锁类型 waiting_lock_duration: TRANSACTION # 该字段来自元数据锁子系统中的锁定时间。有效值为：STATEMENT、TRANSACTION、EXPLICIT, STATEMENT和TRANSACTION值分别表示在语句或事务结束时会释放的锁。EXPLICIT值表示可以在语句或事务结束时会被保留，需要显式释放的锁，例如：使用FLUSH TABLES WITH READ LOCK获取的全局锁 waiting_query: alter table test add index i_k（test）# 正在等待 MDL 锁的线程对应的语句文本 waiting_query_secs: 58 # 正在等待MDL锁的语句已经等待了多长时间（秒） waiting_query_rows_affected: 0 # 受正在等待 MDL 锁的语句影响的数据行数（该字段来自performance_schema.events_statement_current 表，该表中记录的是语句事件，如果语句是多表联结查询语句，则该语句可能已经执行了一部分 DML 语句，所以即使该语句当前被其他线程阻塞了，被阻塞线程的这个字段也可能出现大于0的值） waiting_query_rows_examined: 0 # 正在等待 MDL 锁的语句从存储引擎检查的数据行数（同理，该字段来自performance_schema.events_statement_current表） blocking_thread_id: 49 # 持有MDL锁的线程ID blocking_pid: 7 # 持有MDL锁的processlist ID blocking_account: admin@localhost # 持有MDL锁的与线程关联的account名称 blocking_lock_type: SHARED_WRITE # 持有MDL锁的锁类型 blocking_lock_duration: TRANSACTION # 与 waiting_lock_duration 字段的解释相同，只是该值与持有MDL锁的线程相关 sql_kill_blocking_query: KILL QUERY 7 # 生成的KILL持有MDL锁的查询语句 sql_kill_blocking_connection: KILL 7 # 生成的KILL持有MDL锁的对应会话的语句 ＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊ 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: 58 waiting_query_rows_affected: 0 waiting_query_rows_examined: 0 blocking_thread_id: 1217 blocking_pid: 1175 blocking_account: admin@localhost blocking_lock_type: SHARED_UPGRADABLE blocking_lock_duration: TRANSACTION sql_kill_blocking_query: KILL QUERY 1175 sql_kill_blocking_connection: KILL 1175 2 rows in set（0.00 sec）
下面贴出视图查询语句文本。
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_connection
FROM performance_schema.metadata_locks g
INNER JOIN performance_schema.metadata_locks p
ON g.object_type = p.object_type
AND g.object_schema = p.object_schema
AND g.object_name = p.object_name
AND g.lock_status = 'GRANTED'
AND p.lock_status = 'PENDING'
INNER JOIN performance_schema.threads gt ON g.owner_thread_id = gt.thread_id
INNER JOIN performance_schema.threads pt ON p.owner_thread_id = pt.thread_id
LEFT JOIN performance_schema.events_statements_current gs ON g.owner_thread_id =gs.thread_id
LEFT JOIN performance_schema.events_statements_current ps ON p.owner_thread_id =ps.thread_id
WHERE g.object_type = 'TABLE';