RDS for MySQL 表上 Metadata Lock 的产生和处理-阿里云开发者社区

开发者社区> 田杰> 正文

RDS for MySQL 表上 Metadata Lock 的产生和处理

简介: RDS for MySQL 表上 Metadata lock 的产生和处理 1. Metadata lock wait 出现的场景 2. Metadata lock wait 的含义 3. 导致 Metadata lock wait 等待的活动事务 4. 解决方案 5. 如何避免出现长时间 Metadata lock wait 导致表上相关查询阻塞,影响业务 1.
+关注继续查看

RDS for MySQL 表上 Metadata lock 的产生和处理

1. Metadata lock wait 出现的场景

2. Metadata lock wait 的含义

3. 导致 Metadata lock wait 等待的活动事务

4. 解决方案

5. 如何避免出现长时间 Metadata lock wait 导致表上相关查询阻塞,影响业务


1. Metadata lock wait 出现的场景

  • 创建、删除索引

  • 修改表结构

  • 表维护操作(optimize table、repair table 等)

  • 删除表

  • 获取表上表级写锁 (lock table tab_name write)

metadata_lock_02.png

注:

  • 支持事务的 InnoDB 引擎表和 不支持事务的 MyISAM 引擎表,都会出现 Metadata Lock Wait 等待现象。
  • 一旦出现 Metadata Lock Wait 等待现象,后续所有对该表的访问都会阻塞在该等待上,导致连接堆积,业务受影响。

 

2. Metadata lock wait 的含义

为了在并发环境下维护表元数据的数据一致性,在表上有活动事务(显式或隐式)的时候,不可以对元数据进行写入操作。因此 MySQL 引入了 metadata lock ,来保护表的元数据信息。

因此在对表进行上述操作时,如果表上有活动事务(未提交或回滚),请求写入的会话会等待在 Metadata lock wait 。

3. 导致 Metadata lock wait 等待的活动事务

  • 当前有对表的长时间查询

  • 显示或者隐式开启事务后未提交或回滚,比如查询完成后未提交或者回滚。

  • 表上有失败的查询事务

4. 解决方案

  • show processlist 查看会话有长时间未完成的查询,使用kill 命令终止该查询。

metadata_lock_03.png

metadata_lock_04.png

  • 查询 information_schema.innodb_trx 看到有长时间未完成的事务, 使用 kill 命令终止该查询。

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);

-- 请根据具体的情景修改查询语句
-- 如果导致阻塞的语句的用户与当前用户不同,请使用导致阻塞的语句的用户登录来终止会话

metadata_lock_05.png

  • 如果上面两个检查没有发现,或者事务过多,建议使用下面的查询将相关库上的会话终止


select 
    concat('kill ', p1.id, ';')
from
    information_schema.processlist p1,
    (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', 'trunc'))) p2
where
    p1.time >= p2.time
        and p1.command in ('Sleep' , 'Query')
        and p1.id not in (connection_id() , p2.id);

-- RDS for MySQL 5.5 语句请根据具体的 DDL 语句情况修改查询的条件;
-- 如果导致阻塞的语句的用户与当前用户不同,请使用导致阻塞的语句的用户登录来终止会话

 

5. 如何避免出现长时间 metadata lock wait 导致表上相关查询阻塞,影响业务

  • 在业务低峰期执行上述操作,比如创建删除索引。

  • 在到RDS的数据库连接建立后,设置会话变量 autocommit 为 1 或者 on,比如 set autocommit=1; 或 set autocommit=on; 。

  • 考虑使用事件来终止长时间运行的事务,比如下面的例子中会终止执行时间超过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;


    注:请根据您自身情况,自行修改运行间隔和事务执行时长。

  • 执行上述1中操作前,设置会话变量 lock_wait_timeout 为较小值,比如 set lock_wait_timeout=30; 命令可以设置 metadata lock wait 的最长时间为 30 秒;避免长时间等待元数据锁影响表上其他业务查询。

 metadata_lock_08.png

 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Nacos 发布 1.0.0 GA 版本,可大规模投入到生产环境
经过 3 个 RC 版本的社区体验之后,Nacos 正式发布 1.0.0 GA 版本,在架构、功能和 API 设计上进行了全方位的重构和升级。
3060 0
jquery formatCurrency货币格式化处理
jquery formatCurrency是一个对货币格式进行格式化输入控制、显示的一个jquery插件,可以对文本框输入进行货币合法性验证,并且支持对文本输入字符串进行格式化显示。(国内的会计记账法是保留两位小数,整数位每3个千位使用,号隔开) 1.
779 0
C++实践参考——处理C++源代码的程序
【项目 - 处理C++源代码的程序】   在CodeBlocks等IDE中都提供了代码格式整理的功能。完成这种功能的程序,操作的数据是用C++写的源代码文件。C++源文件是一种文本文件,可以通过程序进行操作。集成开发环境(IDE)对对程序进行编译,操作的“数据”是源程序。编译中,要对源程序进行词法检查和语法检查,后续还要进行目标代码生成、代码优化等工作。相关的技术将在《编译原理》课中学习。这些
883 0
使用OpenApi弹性释放和设置云服务器ECS释放
云服务器ECS的一个重要特性就是按需创建资源。您可以在业务高峰期按需弹性的自定义规则进行资源创建,在完成业务计算的时候释放资源。本篇将提供几个Tips帮助您更加容易和自动化的完成云服务器的释放和弹性设置。
7776 0
DataXceiver error processing unknown operation src: /127.0.0.1:36479 dst: /127.0.0.1:50010处理
异常信息如下:  2015-12-09 17:39:20,310 ERROR datanode.DataNode (DataXceiver.java:run(278)) - hadoop07:50010:DataXceiver error processingunknown operation  src: /127.
1430 0
+关注
田杰
专注于数据库领域技术
21
文章
46
问答
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载