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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,高可用系列 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人关注该文章


相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
目录
相关文章
|
关系型数据库 MySQL Java
MySQL数据锁:Record Lock,Gap Lock 和 Next-Key Lock
本文基于 MySQL 8.0.30 版本及 InnoDB 引擎,深入解析三种行锁机制:记录锁(Record Lock)、间隙锁(Gap Lock)和临键锁(Next-key Lock)。记录锁锁定索引记录,确保事务唯一修改;间隙锁锁定索引间的间隙,防止新记录插入;临键锁结合两者,锁定范围并记录自身,有效避免幻读现象。通过具体示例展示了不同锁的作用机制及其在并发控制中的应用。
1146 2
|
SQL 关系型数据库 MySQL
Mysql Lock Wait
Mysql Lock Wait
267 0
|
存储 SQL 关系型数据库
【MySQL技术内幕】6.1-锁、lock和latch
【MySQL技术内幕】6.1-锁、lock和latch
271 0
|
关系型数据库 MySQL 索引
MySQL InnoDB中的锁-插入意向锁(Insert Intention Lock)
MySQL InnoDB 插入意向锁 Insert Intention Lock
4108 0
MySQL InnoDB中的锁-插入意向锁(Insert Intention Lock)
|
关系型数据库 MySQL 数据库
MySQL报错:Lock wait timeout exceeded; try restarting transaction
MySQL报错:Lock wait timeout exceeded; try restarting transaction
1421 0
|
SQL 关系型数据库 MySQL
MySQL报错:1205 Lock wait timeout exceeded; try restarting transaction处理
MySQL报错:1205 Lock wait timeout exceeded; try restarting transaction处理
879 0
|
SQL 关系型数据库 MySQL
【MySQL异常】MySQL事务锁问题----lock wait timeout exceeded; try restarting transaction
【MySQL异常】MySQL事务锁问题----lock wait timeout exceeded; try restarting transaction
1109 0
|
SQL 关系型数据库 MySQL
Mysql 异常:Lock wait timeout exceeded; try restarting transaction的解决办法
Mysql 异常:Lock wait timeout exceeded; try restarting transaction的解决办法
840 0
|
SQL 关系型数据库 MySQL
mysql Lock wait timeout exceeded; try restarting transaction解决方案
在测试程序时,打的断点怎么都跳不进去,console一直报 “Lock wait timeout exceeded; try restarting transaction”
338 0

推荐镜像

更多