Generation and Handling of Metadata Locks on RDS for MySQL Tables

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: MySQL uses metadata locking to manage concurrent access to database objects and to ensure data consistency.

Galera_to_be_phased_out_MySQL_Group_Replication_officially_launched

Introduction

ApsaraDB for RDS, also known as Alibaba Cloud ApsaraDB for RDS (Relational Database System) is an on-demand database service that frees you from the administrative task of managing a database. It is a ready-to-use service offered on MySQL, SQL Server, and PostgreSQL. RDS handles routine database tasks such as provisioning, patch up, backup, recovery, failure detection, and repair.

Metadata Lock on RDS for MySQL Tables

MySQL uses metadata locking to manage concurrent access to database objects and to ensure data consistency. Metadata locking applies not just to tables, but also to schemas and stored programs (procedures, functions, triggers, and scheduled events). However, it does involve certain overheads, which increase as query volume increases. Metadata contention increases as multiple queries attempt to access the same objects.

Metadata locking is not a replacement for the table definition cache, and its mutexes and locks differ from the LOCK_open mutex.

The following discussion provides some information on how metadata locking works.

1. What does metadata lock wait mean?

When there is an active transaction (explicit or implicit) on the table, MySQL does not allow writing of data to metadata. It does this to maintain metadata consistency in the table in a concurrent environment. As a solution, MySQL has introduced the metadata lock to protect the metadata information in a table.

In the case of an active transaction (uncommitted or rolled back) when you perform the operations mentioned above on the table, the session requesting data write will be held in the Metadata Lock Wait status.

2. When does metadata lock wait occur?

A metadata lock wait may occur under the following scenarios:

● When you create or delete an index;
● When you modify the table structure;
● When you perform table maintenance operations (optimize table or repair table among others);
● When you delete a table;
● When you try to get the table-level write lock on the table (lock table tab_name write).

The screenshot below should act as a reference.

1

Notes:

● Both InnoDB engine tables that support transactions and MyISAM engine tables that do not support transactions may experience metadata lock wait.
● Once a Metadata Lock Wait occurs, it will result in blocking of all the subsequent requests to access this table. Such a scenario will lead to congestion in connections as well as impact on business.

3. What active transactions will lead to metadata lock waits?

Ideally, various kinds of active transactions that may lead to metadata lock waits exist. It may occur in one of the following cases:

● There is a query on the table that has been present for a long time;
● Failure to commit or roll back a transaction once explicitly or implicitly opened. For example, failure to commit or roll back a transaction following the completion of a query;
● There is a failed query transaction on the table.

Resolving Metadata Locks

Since a metadata lock wait has already occurred, we need to follow the steps below to resolve it.

● First, you should use show processlist to check whether there exists any session that remains active for a long duration. In case such a session exists, you need to kill the query. Please refer to the screenshot below for your reference.

2

3

● Next, you need to query information_schema.innodb_trx to check if a transaction that has been there for a long time exists. In case there is, you need to kill the query. You can implement the following codes for the same effect.

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

● You would have to modify the query statement according to the specific situation.
● You have to check if the user causing the blocking is different from the current user. If that is the case, then log on as that user and terminate the session. Refer to the screenshot below:

4

5

● In case the findings mentioned above are not true in your case, or there are excessive transactions, we recommend that you use the following query to terminate the session on the associated database.

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

● For RDS for MySQL 5.5 statements, you need to modify the query conditions according to the specific DDL statements;
● As mentioned previously, if the user causing the blocking is different from the current user, you need to log on as that user and terminate the session.

6

Avoiding a long metadata lock wait that blocks related queries on the table or impacts business

To avoid a long metadata lock wait, you are required to perform the following steps.

● Perform the operations mentioned above in non-rush hours, such as while creating or deleting an index.
● After establishing an RDS database connection, set the session variable autocommit to "1" or "on", such as set autocommit = 1; or set autocommit = on;.

Next, you need to consider using events to terminate long-running transactions.

The example below terminates transactions that have been running for more than 60 minutes.

create event my_long_running_trx_monitor on schedule every 60 minutes
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;

Note: You need to modify the run interval and transaction execution duration according to your specific scenario.

● Before you execute the operation in step 1 above, you would have to set the session variable lock_wait_timeout to a smaller value.
For example, the "set lock_wait_timeout = 30;" command sets the maximum duration of metadata lock wait to 30 seconds. Doing this avoids a long wait for the metadata lock to affect other business queries on the table.
The result should appear as shown in the screenshot below.

7

Conclusion:

MySQL utilizes metadata locking to manage concurrent access to database objects and to ensure data consistency. Metadata locking applies not just to tables, but also to schemas and stored programs. It occurs in several scenarios such as when you create or delete an index or when you perform table maintenance operations (optimize table or repair table among others). However, there may arise instances where you would want to avoid a long metadata lock wait, which can affect businesses.

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
2月前
|
负载均衡 算法 关系型数据库
大数据大厂之MySQL数据库课程设计:揭秘MySQL集群架构负载均衡核心算法:从理论到Java代码实战,让你的数据库性能飙升!
本文聚焦 MySQL 集群架构中的负载均衡算法,阐述其重要性。详细介绍轮询、加权轮询、最少连接、加权最少连接、随机、源地址哈希等常用算法,分析各自优缺点及适用场景。并提供 Java 语言代码实现示例,助力直观理解。文章结构清晰,语言通俗易懂,对理解和应用负载均衡算法具有实用价值和参考价值。
大数据大厂之MySQL数据库课程设计:揭秘MySQL集群架构负载均衡核心算法:从理论到Java代码实战,让你的数据库性能飙升!
|
2月前
|
存储 关系型数据库 MySQL
【免费动手教程上线】阿里云RDS MySQL推出大容量高性能存储:高性能本地盘(最高16TB存储空间)、高性能云盘(最高64TB存储空间)
阿里云RDS MySQL提供高性能本地盘与高性能云盘等存储方案,满足用户大容量、低延迟需求。高性能本地盘单盘最大16TB,IO延时微秒级;高性能云盘兼容ESSD特性,支持IO性能突发、BPE及16K原子写等能力。此外,阿里云还提供免费动手体验教程,帮助用户直观感受云数据库 RDS 存储性能表现。
|
3月前
|
负载均衡 算法 关系型数据库
大数据新视界--大数据大厂之MySQL数据库课程设计:MySQL集群架构负载均衡故障排除与解决方案
本文深入探讨 MySQL 集群架构负载均衡的常见故障及排除方法。涵盖请求分配不均、节点无法响应、负载均衡器故障等现象,介绍多种负载均衡算法及故障排除步骤,包括检查负载均衡器状态、调整算法、诊断修复节点故障等。还阐述了预防措施与确保系统稳定性的方法,如定期监控维护、备份恢复策略、团队协作与知识管理等。为确保 MySQL 数据库系统高可用性提供全面指导。
|
3月前
|
SQL 关系型数据库 MySQL
大数据新视界--大数据大厂之MySQL数据库课程设计:MySQL 数据库 SQL 语句调优方法详解(2-1)
本文深入介绍 MySQL 数据库 SQL 语句调优方法。涵盖分析查询执行计划,如使用 EXPLAIN 命令及理解关键指标;优化查询语句结构,包括避免子查询、减少函数使用、合理用索引列及避免 “OR”。还介绍了索引类型知识,如 B 树索引、哈希索引等。结合与 MySQL 数据库课程设计相关文章,强调 SQL 语句调优重要性。为提升数据库性能提供实用方法,适合数据库管理员和开发人员。
|
3月前
|
关系型数据库 MySQL 大数据
大数据新视界--大数据大厂之MySQL 数据库课程设计:MySQL 数据库 SQL 语句调优的进阶策略与实际案例(2-2)
本文延续前篇,深入探讨 MySQL 数据库 SQL 语句调优进阶策略。包括优化索引使用,介绍多种索引类型及避免索引失效等;调整数据库参数,如缓冲池、连接数和日志参数;还有分区表、垂直拆分等其他优化方法。通过实际案例分析展示调优效果。回顾与数据库课程设计相关文章,强调全面认识 MySQL 数据库重要性。为读者提供综合调优指导,确保数据库高效运行。
|
5月前
|
关系型数据库 MySQL 数据库
RDS用多了,你还知道MySQL主从复制底层原理和实现方案吗?
随着数据量增长和业务扩展,单个数据库难以满足需求,需调整为集群模式以实现负载均衡和读写分离。MySQL主从复制是常见的高可用架构,通过binlog日志同步数据,确保主从数据一致性。本文详细介绍MySQL主从复制原理及配置步骤,包括一主二从集群的搭建过程,帮助读者实现稳定可靠的数据库高可用架构。
319 9
RDS用多了,你还知道MySQL主从复制底层原理和实现方案吗?
|
10月前
|
存储 SQL 关系型数据库
一篇文章搞懂MySQL的分库分表,从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案
MySQL如何进行分库分表、数据迁移?从相关概念、使用场景、拆分方式、分表字段选择、数据一致性校验等角度阐述MySQL数据库的分库分表方案。
1055 15
一篇文章搞懂MySQL的分库分表,从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案
|
8月前
|
关系型数据库 MySQL Linux
在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。
本文介绍了在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。同时,文章还对比了编译源码安装与使用 RPM 包安装的优缺点,帮助读者根据需求选择最合适的方法。通过具体案例,展示了编译源码安装的灵活性和定制性。
735 2
|
9月前
|
关系型数据库 MySQL Java
Django学习二:配置mysql,创建model实例,自动创建数据库表,对mysql数据库表已经创建好的进行直接操作和实验。
这篇文章是关于如何使用Django框架配置MySQL数据库,创建模型实例,并自动或手动创建数据库表,以及对这些表进行操作的详细教程。
302 0
Django学习二:配置mysql,创建model实例,自动创建数据库表,对mysql数据库表已经创建好的进行直接操作和实验。
|
9月前
|
Java 关系型数据库 MySQL
springboot学习四:springboot链接mysql数据库,使用JdbcTemplate 操作mysql
这篇文章是关于如何使用Spring Boot框架通过JdbcTemplate操作MySQL数据库的教程。
506 0
springboot学习四:springboot链接mysql数据库,使用JdbcTemplate 操作mysql

热门文章

最新文章

推荐镜像

更多