Generation and Handling of Metadata Locks on RDS for MySQL Tables

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 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.

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
4月前
|
存储 关系型数据库 MySQL
MySQL数据库进阶第三篇(MySQL性能优化)
MySQL数据库进阶第三篇(MySQL性能优化)
|
24天前
|
存储 SQL 关系型数据库
一篇文章搞懂MySQL的分库分表,从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案
MySQL如何进行分库分表、数据迁移?从相关概念、使用场景、拆分方式、分表字段选择、数据一致性校验等角度阐述MySQL数据库的分库分表方案。
104 14
一篇文章搞懂MySQL的分库分表,从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案
|
3月前
|
关系型数据库 MySQL Serverless
函数计算产品使用问题之调用RDS MySQL的步骤是怎样的
函数计算产品作为一种事件驱动的全托管计算服务,让用户能够专注于业务逻辑的编写,而无需关心底层服务器的管理与运维。你可以有效地利用函数计算产品来支撑各类应用场景,从简单的数据处理到复杂的业务逻辑,实现快速、高效、低成本的云上部署与运维。以下是一些关于使用函数计算产品的合集和要点,帮助你更好地理解和应用这一服务。
|
2月前
|
SQL 关系型数据库 MySQL
MySql⭐一、配置MySql数据库,并创建一个表单
MySql⭐一、配置MySql数据库,并创建一个表单
|
3月前
|
开发框架 前端开发 关系型数据库
ABP框架使用Mysql数据库,以及基于SQLServer创建Mysql数据库的架构和数据
ABP框架使用Mysql数据库,以及基于SQLServer创建Mysql数据库的架构和数据
|
4月前
|
关系型数据库 MySQL 数据库
【MySQL】MySQL数据库的delete from table和truncate table之间的区别
【MySQL】MySQL数据库的delete from table和truncate table之间的区别
309 1
|
4月前
|
存储 关系型数据库 MySQL
MySQL数据库进阶第一篇(存储引擎与Linux系统上安装MySQL数据库)
MySQL数据库进阶第一篇(存储引擎与Linux系统上安装MySQL数据库)
|
3月前
|
关系型数据库 数据库 RDS
利用DTS将自建mysql5.7版本数据库迁移至对应rds报错
利用DTS将自建mysql5.7版本数据库迁移至对应rds报错
141 0
|
3月前
|
SQL 关系型数据库 MySQL
Navicate,数据库,Mysql,改表,4月29日Finished - Unsuccessfully,导出数据不妨,右键,备份一下Mysql数据库的内容,你想导入和导出数据不如,用查询的方式去做
Navicate,数据库,Mysql,改表,4月29日Finished - Unsuccessfully,导出数据不妨,右键,备份一下Mysql数据库的内容,你想导入和导出数据不如,用查询的方式去做
|
4月前
|
存储 安全 关系型数据库

热门文章

最新文章

推荐镜像

更多