MySQL长事务导致的Table Metadata Lock

2019-05-18

前几日操作truncate table操作时，遇到一个问题，通过show processlist获取关键信息，通过此文章了解更多，故记录于此。

什么是Table Metadata Lock

在MySQL以前的版本中，存在这样一个Bug：

大致意思如下:

如果user1存在一个操作table的事务，而user2要删除这张表，当user1提交这个事务时，binlog表现出来的行为类似如下:

DROP TABLE t;
BEGIN;
INSERT INTO t ...;
COMMIT;

这样就导致这个问题出现了。

所以MySQL在后续版本中增加了Metadata Lock,只有在事务结束后才会释放Metadata lock，因此在事务提交或回滚前，是无法进行DDL(不仅仅是DDL哦)操作的。

MySQL官方文档地址:

http://dev.mysql.com/doc/refman/5.6/en/metadata-locking.html

如何解决

查询当前正在运行的事务（下面为拷贝过来的，仅作参考）

mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 7202
                 trx_state: RUNNING                 -- 事务状态，一般为RUNNING
               trx_started: 2016-07-20 23:02:53     -- 事务执行的起始时间，若时间较长，则要分析该事务是否合理
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 52402                   -- MySQL的线程ID，用于kill
                 trx_query: NULL                    -- 事务中的SQL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 360
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: READ COMMITTED
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)

查看运行的SQL语句

根据查询到的trx_started时间以及trx_mysql_thread_id到MySQL的general log中查找，当然前提是开启了general log的功能，对于这句话我有一个疑惑点，如果没有开启general log时那么无法查看对应操作的SQL语句了？？？

所以此处给出排查方式：

如果开启了general log，那么根据这个方式
给出两个SQL语句如下,参考文章:

-- 这条语句用于查看哪个线程被另外哪一个线程阻塞， 分别为 阻塞的线程ID、阻塞的SQL的语句、谁阻塞的、阻塞者的SQL语句
SELECT b.trx_mysql_thread_id             AS 'blocked_thread_id'
      ,b.trx_query                      AS 'blocked_sql_text'
      ,c.trx_mysql_thread_id             AS 'blocker_thread_id'
      ,c.trx_query                       AS 'blocker_sql_text'
      ,( Unix_timestamp() - Unix_timestamp(c.trx_started) )
                              AS 'blocked_time'
FROM   information_schema.innodb_lock_waits a
    INNER JOIN information_schema.innodb_trx b
         ON a.requesting_trx_id = b.trx_id
    INNER JOIN information_schema.innodb_trx c
         ON a.blocking_trx_id = c.trx_id
WHERE  ( Unix_timestamp() - Unix_timestamp(c.trx_started) ) > 4; -- 时长

-- 这条语句用于获取具体的阻塞语句

SELECT a.sql_text,
       c.id,
       d.trx_started
FROM   performance_schema.events_statements_current a
       join performance_schema.threads b
         ON a.thread_id = b.thread_id
       join information_schema.processlist c
         ON b.processlist_id = c.id
       join information_schema.innodb_trx d
         ON c.id = d.trx_mysql_thread_id
where c.id=17 -- 17 对应blocker_thread_id，
ORDER  BY d.trx_started\G;

但是方式2不一定能够打印出具体阻塞的语句，为什么,如果出现此类问题，可以先看下autocommit，哈哈…

如果还是无法定位SQL，那么还是暴力一点:

1	mysql> kill 52402;

如何复现这种情况（另外对于主主/主从复制也会出现），可参考文章