Skip to content

schema_table_lock_waits,x$schema_table_lock_waits

Jump to bottom
xiaoboluo768 edited this page Jun 9, 2020 · 2 revisions

  • 查看当前链接线程的MDL锁等待信息，显示哪些会话被MDL锁阻塞，是谁阻塞了这些会话，数据来源：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';
    • 该视图在MySQL 5.7.9中新增

  • schema_table_lock_waits和x$schema_table_lock_waits视图字段含义如下：

    • object_schema：包含发生MDL锁等待的数据库对象的schema名称
    • OBJECT_NAME：MDL锁等待监控对象的名称
    • waiting_thread_id：正在等待MDL锁的线程ID
    • waiting_pid：正在等待MDL锁的进程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锁的语句影响的数据行数（该字段来自events_statement_current表，该表中记录的是语句事件，如果语句是多表联结查询，则该语句可能已经执行了一部分DML语句，所以哪怕该语句当前被其他线程阻塞了，被阻塞线程的这个字段也可能出现大于0的值）
    • waiting_query_rows_examined：正在等待MDL锁的语句从存储引擎读取的数据行数（同理，该字段来自events_statement_current表）
    • blocking_thread_id：持有MDL锁的线程ID
    • blocking_pid：持有MDL锁的进程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锁对应会话的语句

  • 视图定义语句

# schema_table_lock_waits
CREATE OR REPLACE
  ALGORITHM = TEMPTABLE
  DEFINER = 'root'@'localhost'
  SQL SECURITY INVOKER
VIEW schema_table_lock_waits (
  object_schema,  object_name,  waiting_thread_id,  waiting_pid,  waiting_account,  waiting_lock_type,  waiting_lock_duration,  waiting_query,  waiting_query_secs,  waiting_query_rows_affected,
  waiting_query_rows_examined,  blocking_thread_id,  blocking_pid,  blocking_account,  blocking_lock_type,  blocking_lock_duration,  sql_kill_blocking_query,  sql_kill_blocking_connection
) AS
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';

# x$schema_table_lock_waits
CREATE OR REPLACE
  ALGORITHM = TEMPTABLE
  DEFINER = 'root'@'localhost'
  SQL SECURITY INVOKER
VIEW x$schema_table_lock_waits (
  object_schema,  object_name,  waiting_thread_id,  waiting_pid,  waiting_account,  waiting_lock_type,  waiting_lock_duration,  waiting_query,  waiting_query_secs,  waiting_query_rows_affected,  
  waiting_query_rows_examined,  blocking_thread_id,  blocking_pid,  blocking_account,  blocking_lock_type,  blocking_lock_duration,  sql_kill_blocking_query,  sql_kill_blocking_connection
) AS
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,
      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';
  • 视图查询信息示例
# waiting_*开头的字段是当前正在等待资源的线程相关信息，而blocking_*开头的字段是阻塞了waiting_pid线程的那个线程相关信息，所以如果要保证waiting_pid线程正常执行，
# 一般情况下通过sql_kill_blocking_query字段的kill语句杀掉这个查询即可，当然，如果这个查询当前处于空闲状态而且是非自动提交的事务，可能需要使用sql_kill_blocking_connection中的kill语句直接杀掉这个连接。
# 另外，如果大量后续的查询被waiting_pid线程阻塞（例如这个线程正在操作DDL语句，而这个DDL语句又在等待之前会话中的MDL锁，那么可能导致后续大量查询被这个DDL语句阻塞），
# 这个时候可能需要kill掉这个waiting_pid线程（正在等待锁即被阻塞的线程可以使用 KILL QUERY 1175形式的语句只杀掉这个查询，不必要杀掉这个连接）
# x$schema_table_lock_waits与schema_table_lock_waits视图定义语句在当前5.7.18版本中相同，所以查询结果是一样的形态
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: 58
waiting_query_rows_affected: 0
waiting_query_rows_examined: 0
          blocking_thread_id: 49
                blocking_pid: 7
            blocking_account: admin@localhost
          blocking_lock_type: SHARED_WRITE
      blocking_lock_duration: TRANSACTION
    sql_kill_blocking_query: KILL QUERY 7
sql_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: 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)

上一篇： schema_redundant_indexes视图 |

下一篇： schema_table_statistics,x$schema_table_statistics视图

  • 验证、测试、整理：罗小波
  • QQ：309969177
  • 提示：本系列文章的主体结构遵循Oracle MySQL 官方 5.7 手册中，关于information_schema、mysql schema、performance_schema、sys schema的章节结构体系，并额外添加了一些验证、测试数据。鉴于本人精力和能力有限，难免出现一些纰漏，欢迎大家踊跃指正！
Clone this wiki locally