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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介: RDS for MySQL 表上 Metadata lock 的产生和处理 1. Metadata lock wait 出现的场景 2. Metadata lock wait 的含义 3. 导致 Metadata lock wait 等待的活动事务 4. 解决方案

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

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

 

0人关注该文章


相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
1月前
|
关系型数据库 MySQL Java
MySQL数据锁:Record Lock,Gap Lock 和 Next-Key Lock
本文基于 MySQL 8.0.30 版本及 InnoDB 引擎,深入解析三种行锁机制:记录锁(Record Lock)、间隙锁(Gap Lock)和临键锁(Next-key Lock)。记录锁锁定索引记录,确保事务唯一修改;间隙锁锁定索引间的间隙,防止新记录插入;临键锁结合两者,锁定范围并记录自身,有效避免幻读现象。通过具体示例展示了不同锁的作用机制及其在并发控制中的应用。
107 2
|
5月前
|
存储 SQL 关系型数据库
【MySQL技术内幕】6.1-锁、lock和latch
【MySQL技术内幕】6.1-锁、lock和latch
79 0
|
SQL 关系型数据库 MySQL
Mysql Lock Wait
Mysql Lock Wait
183 0
|
关系型数据库 MySQL 数据库
MySQL报错:Lock wait timeout exceeded; try restarting transaction
MySQL报错:Lock wait timeout exceeded; try restarting transaction
599 0
|
SQL 关系型数据库 MySQL
MySQL报错:1205 Lock wait timeout exceeded; try restarting transaction处理
MySQL报错:1205 Lock wait timeout exceeded; try restarting transaction处理
310 0
|
SQL 关系型数据库 MySQL
【MySQL异常】MySQL事务锁问题----lock wait timeout exceeded; try restarting transaction
【MySQL异常】MySQL事务锁问题----lock wait timeout exceeded; try restarting transaction
317 0
|
关系型数据库 MySQL 索引
MySQL InnoDB中的锁-插入意向锁(Insert Intention Lock)
MySQL InnoDB 插入意向锁 Insert Intention Lock
3530 0
MySQL InnoDB中的锁-插入意向锁(Insert Intention Lock)
|
SQL 关系型数据库 MySQL
Mysql 异常:Lock wait timeout exceeded; try restarting transaction的解决办法
Mysql 异常:Lock wait timeout exceeded; try restarting transaction的解决办法
280 0
|
SQL 关系型数据库 MySQL
mysql Lock wait timeout exceeded; try restarting transaction解决方案
在测试程序时,打的断点怎么都跳不进去,console一直报 “Lock wait timeout exceeded; try restarting transaction”
166 0