问题描述

MySQL从5.5版本开始引入了MDL锁,用于解决DDL操作与DML操作的一致性,但是在如下部分场景下会出现阻塞,例如执行DML操作时会执行ALTER操作,存在长时间查询执行ALTER操作等。异常情况下的元数据锁MDL(Metadata Lock)会阻塞后续对表的操作,本文主要介绍如何通过DMS工具解决该问题。

  • 修改表结构。
  • 表维护操作(optimize table、repair table 等)。
  • 删除表。
  • 获取表级写锁 。

 

问题原因

问题原因如下。

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

 

解决方案

  1. 使用DMS登录RDS数据库
    提示网络地址和端口需要输入对应RDS实例的内网地址及内网端口,可在RDS管理控制台中的RDS实例基本信息页面查看。
  2. 首页上方选择 SQL操作 > SQL窗口
  3. 在命令行中执行如下SQL语句,查看所有线程状态。
    show full processlist;
    系统显示类似如下,在 State 列存在大量 Waiting for table metadata lock 信息, 即表示出现阻塞。在对应的 Info 列查看是对哪个表进行的操作,找到正在对该表进行操作的会话,查看并记录对应的会话ID。

    提示:
    • 这里需要找到的是一直在占用该表的会话,而不是正在等待MDL锁解除的会话,注意区分。可以根据State列的状态和Info列的命令内容来进行分析判断。
    • 您也可以执行如下SQL语句,查询长时间未完成的事务。如果导致阻塞语句的用户与当前用户不同,请使用执行SQL语句导致阻塞的用户登录来终止会话。
      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);
  4. 执行如下SQL语句,中断正在进行的会话解除MDL锁。
    kill [$ID]
    注:[$ID]为上一步获取的ID。
    系统显示类似如下。
  5. 后续维护操作。
    • 在业务低峰期执行相关场景操作,例如创建索引、删除索引等。
    • 开启事务自动提交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

 

适用于

  • 云数据库 RDS MySQL 版

 

如果您的问题仍未解决,您可以在阿里云社区免费咨询,或提交工单联系阿里云技术支持。