如何快速找到阻塞源头？

快速解决问题永远是第一位的，一旦出现长时间的 metadata lock，尤其是在访问频繁的业务表上产生，通常会导致表无法访问，读写全被阻塞，此时找到阻塞源头是第一位的。这里最重要的表就是前面提到过的performance_schema.metadata_locks 表。

metadata_locks 是 5.7 中被引入，记录了 metadata lock 的相关信息，包括持有对象、类型、状态等信息。但 5.7 默认设置是关闭的（8.0 默认打开），需要通过下面命令打开设置：

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'WHERE NAME = 'wait/lock/metadata/sql/mdl';

如果要永久生效，需要在配置文件中加入如下内容：

[mysqld]
performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'

 

单纯查询这个表无法得出具体的阻塞关系，也无法得知什么语句造成的阻塞，这里要关联另外两个表 performance_schema.thread 和performance_schema.events_statements_history,thread 表可以将线程 id 和 show processlist 中 id 关联，events_statements_history 表可以得到事务的历史 sql，关联后的完整 sql 如下：

SELECT
    locked_schema,
    locked_table,
    locked_type,
    waiting_processlist_id,
    waiting_age,
    waiting_query,
    waiting_state,
    blocking_processlist_id,
    blocking_age,
    substring_index(sql_text,"transaction_begin;" ,-1) AS blocking_query,
    sql_kill_blocking_connection
FROM
    (
        SELECT
            b.OWNER_THREAD_ID AS granted_thread_id,
            a.OBJECT_SCHEMA AS locked_schema,
            a.OBJECT_NAME AS locked_table,
            "Metadata Lock" AS locked_type,
            c.PROCESSLIST_ID AS waiting_processlist_id,
            c.PROCESSLIST_TIME AS waiting_age,
            c.PROCESSLIST_INFO AS waiting_query,
            c.PROCESSLIST_STATE AS waiting_state,
            d.PROCESSLIST_ID AS blocking_processlist_id,
            d.PROCESSLIST_TIME AS blocking_age,
            d.PROCESSLIST_INFO AS blocking_query,
            concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
        FROM
            performance_schema.metadata_locks a
        JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA
        AND a.OBJECT_NAME = b.OBJECT_NAME
        AND a.lock_status = 'PENDING'
        AND b.lock_status = 'GRANTED'
        AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
        AND a.lock_type = 'EXCLUSIVE'
        JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID
        JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID
    ) t1,
    (
        SELECT
            thread_id,
            group_concat(   CASE WHEN EVENT_NAME = 'statement/sql/begin' THEN "transaction_begin" ELSE sql_text END ORDER BY event_id SEPARATOR ";" ) AS sql_text
        FROM
           performance_schema.events_statements_history
        GROUP BY thread_id
    ) t2
WHERE
    t1.granted_thread_id = t2.thread_id \G

 

对于前面的例子执行此 sql，得到一个清晰的阻塞关系：

               locked_schema: db1
                locked_table: t1
                 locked_type: Metadata Lock
      waiting_processlist_id: 28
                 waiting_age: 227
               waiting_query: alter table t1 add cl3 int
               waiting_state: Waiting for table metadata lock
     blocking_processlist_id: 27
                blocking_age: 252
              blocking_query: select * from t1
sql_kill_blocking_connection: KILL 27
1 row in set, 1 warning (0.00 sec)

 

根据显示结果，processlist_id 为 27 的线程阻塞了 28 的线程，我们需要 kill 27 即可解锁。

 

实际上，MySQL 也提供了一个类似的视图来解决 metadata lock 问题，视图名称为 sys.schema_table_lock_waits，但此视图查询结果有 bug，不是很准确，建议大家还是参考上面 sql。

小结

生产环境大多是 dml 操作，metadata 读锁之间不会产生锁等待，而目前 MySQL 的 ddl 操作大多可以 online 执行，因此即使有写锁，也会很快降级为读锁，所以 ddl 执行期间阻塞 dml 的几率也很小。最容易出现的情况是由于有未完成的事务，导致 ddl metadata 写锁无法加上，只能在锁队列等待，而一旦进入锁队列，写锁又会阻塞其他的读锁，导致数据库连接快速增长，直至消耗殆尽，最终业务受到影响。

 

为了尽可能避免类似问题，下面是几个小建议：

  • 生产环境的任何大表或频繁操作的小表，ddl 都要非常慎重，最好在业务低峰期执行。
  • 设计上要尽可能避免大事务，大事务不仅仅会带来各种锁问题，还好引起复制延迟 / 回滚空间爆满等各类问题。
  • 要及时提交事务，经常发现客户端设置了事务手工提交，但 sql 执行后忘记点击提交按钮，导致事务长时间无法提交。建议监控实例中的长事务，避免由于各种原因导致事务没有及时提交。