文档中心 > 云数据库 RDS 版

云数据库 RDS 版

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

更新时间： 访问次数：1522

问题描述

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

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

问题原因

常见的问题原因如下：

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

解决方案

请参考以下步骤解决问题：

  1. 通过DMS登录RDS数据库。
    说明 ： 网络地址和端口需要输入对应RDS实例的内网地址及内网端口，可在RDS管理控制台中的RDS实例基本信息页面查看。
  2. 在首页上方单击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版

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

    FAQ

    关于此文档暂时还没有FAQ
    有用(0) 我要提问
    返回
    顶部
    阿里巴巴集团 | 淘宝网 | 天猫 | 聚划算 | 全球速卖通 | 阿里巴巴国际交易市场 | 1688 | 阿里妈妈 | 阿里旅行·去啊 | 阿里云计算 | YunOS | 阿里通信 | 万网 | 高德 | 优视 | 友盟 | 酷盘 | 虾米 | 天天动听 | 来往 | 钉钉 | 11Main | 支付宝
    关于淘宝 合作伙伴 营销中心 联系客服 开放平台 诚征英才 联系我们 网站地图 法律声明 © 2016 Taobao.com 版权所有
    网络文化经营许可证：文网文[2010]040号 | 增值电信业务经营许可证：浙B2-20080224-1 | 信息网络传播视听节目许可证：1109364号