MySQL 5.7中如何定位DDL被阻塞的问题(3)

mysql> select * from sys.schema_table_lock_waits\G
*************************** 1. row ***************************
              object_schema: slowtech
                object_name: t1
          waiting_thread_id: 29
                waiting_pid: 4
            waiting_account: root@localhost
          waiting_lock_type: EXCLUSIVE
      waiting_lock_duration: TRANSACTION
              waiting_query: alter table slowtech.t1 add c1 int
          waiting_query_secs: 446
 waiting_query_rows_affected: 0
 waiting_query_rows_examined: 0
          blocking_thread_id: 27
                blocking_pid: 2
            blocking_account: root@localhost
          blocking_lock_type: SHARED_READ
      blocking_lock_duration: TRANSACTION
    sql_kill_blocking_query: KILL QUERY 2
sql_kill_blocking_connection: KILL 2
*************************** 2. row ***************************
              object_schema: slowtech
                object_name: t1
          waiting_thread_id: 29
                waiting_pid: 4
            waiting_account: root@localhost
          waiting_lock_type: EXCLUSIVE
      waiting_lock_duration: TRANSACTION
              waiting_query: alter table slowtech.t1 add c1 int
          waiting_query_secs: 446
 waiting_query_rows_affected: 0
 waiting_query_rows_examined: 0
          blocking_thread_id: 29
                blocking_pid: 4
            blocking_account: root@localhost
          blocking_lock_type: SHARED_UPGRADABLE
      blocking_lock_duration: TRANSACTION
    sql_kill_blocking_query: KILL QUERY 4
sql_kill_blocking_connection: KILL 4
2 rows in set (0.00 sec)

具体分析下官方的输出,

只有一个alter table操作,却产生了两条记录,而且两条记录的kill对象竟然还不一样,对表结构不熟悉及不仔细看记录内容的话,难免会kill错对象。

不仅如此,如果有N个查询被DDL操作堵塞,则会产生N*2条记录。在阻塞操作较多的情况下,这N*2条记录完全是个噪音。

而之前的SQL,无论有多少操作被阻塞,一个alter table操作,就只会输出一条记录。

如何查看阻塞会话已经执行过的操作

但上面这个SQL也有遗憾,其blocking_query为NULL,而在会话1中,其明明已经执行了三个SQL。

这个与performance_schema.threads(类似于show processlist)有关,其只会输出当前正在运行的SQL,对于已经执行过的,实际上是没办法看到。

但在线上,kill是一个需要谨慎的操作,毕竟你很难知道kill的是不是业务关键操作?又或者,是个批量update操作?那么,有没有办法抓到该事务之前的操作呢?

答案,有。

即Performance Schema中记录Statement Event(操作事件)的表,具体包括events_statements_current,events_statements_history,events_statements_history_long,prepared_statements_instances。

常用的是前面三个。

MySQL 5.7中如何定位DDL被阻塞的问题

三者的表结构完全一致,其中,events_statements_history又包含了events_statements_current的操作,所以我们这里会使用events_statements_history。

终极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

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/6786ee6723cc843ac60b64cff81b5ec6.html