解决MDL锁导致无法操作数据库的问题

背景信息

MySQL 5.5版本开始，引入了MDL锁，用于解决或者保证DDL操作与DML操作之间的一致性，但是在部分场景下会出现阻塞，例如执行DML操作时执行ALTER操作、存在长时间查询时执行ALTER操作等。

出现场景

• 创建、删除索引。
• 修改表结构。
• 表维护操作（optimize table、repair table 等）。
• 删除表。
• 获取表级写锁 。

原因

• 当前有对表的长时间查询。
• 显示或者隐式开启事务后未提交或回滚，比如查询完成后未提交或者回滚。
• 表上有失败的查询事务。

操作步骤

1. 登陆数据库
2. 在SQL窗口执行show full processlist命令，查看数据库所有线程状态。
3. 查看State列是否存在大量Waiting for table metadata lock，出现Waiting for table metadata lock即表示出现阻塞。
4. 查找造成阻塞的会话ID。
   1. 查看状态为Waiting for table metadata lock会话的Info列，找到该会话操作的表，例如sbtest2。
   2. 查看其他会话的Info列，找到正在对表sbtest2进行操作的会话，记录会话Id。   说明 这里需要找到的是一直在占用操作该表的会话，而不是正在等待MDL锁（状态为Waiting for table metadata lock）解除的会话，注意区分。可以根据State列的状态和Info列的命令内容来进行分析判断。 例如，下图中State为Waiting for table metadata lock的会话，从其Info列的命令判断，此会话需要对表sbtest2进行操作；其他需要操作表sbtest2的会话中，从Id为267会话的State列状态可以判断，此会话正在对表sbtest2进行操作，造成了阻塞。   说明 本文以状态Sending data为例，请根据实际的会话状态进行判断。 您也可以用如下命令查询长时间未完成的事务，如果导致阻塞的语句的用户与当前登录用户不同，请使用导致阻塞的语句的用户登录来终止会话。  

      select concat('kill ',i.trx_mysql_thread_id,';') from information_schema.innodb_trx i,
        (select 
               id, time
           from
               information_schema.processlist
           where
               time = (select 
                       max(time)
                   from
                       information_schema.processlist
                   where
                       state = 'Waiting for table metadata lock'
                           and substring(info, 1, 5) in ('alter' , 'optim', 'repai', 'lock ', 'drop ', 'creat'))) p
        where timestampdiff(second, i.trx_started, now()) > p.time
        and i.trx_mysql_thread_id  not in (connection_id(),p.id);

5. 在命令行输入kill 会话ID，例如 kill 267，即可中断会话，解除MDL锁。

后续维护

• 在业务低峰期执行相关场景操作，例如创建索引、删除索引等。
• 开启事务自动提交autocommit。
• 设置参数lock_wait_timeout为较小值。
• 考虑使用事件来终止长时间运行的事务，比如下面的例子中会终止执行时间超过60分钟的事务。  

  create event my_long_running_trx_monitor
  on schedule every 60 minute
  starts '2015-09-15 11:00:00'
  on completion preserve enable do
  begin
    declare v_sql varchar(500);
    declare no_more_long_running_trx integer default 0; 
    declare c_tid cursor for
      select concat ('kill ',trx_mysql_thread_id,';') 
      from information_schema.innodb_trx 
      where timestampdiff(minute,trx_started,now()) >= 60;
    declare continue handler for not found
      set no_more_long_running_trx=1;
    open c_tid;
    repeat
      fetch c_tid into v_sql;
   set @v_sql=v_sql;
   prepare stmt from @v_sql;
   execute stmt;
   deallocate prepare stmt;
    until no_more_long_running_trx end repeat;
    close c_tid;
  end;