开发者社区> 芷沁> 正文

Generation and Handling of Metadata Locks on RDS for MySQL Tables

简介: 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.

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

相关文章
etcdserver: mvcc: database space exceeded
etcdserver: mvcc: database space exceeded
97 0
Troubleshooting SQL Server RESOURCE_SEMAPHORE Waittype Memory Issues
原文:Troubleshooting SQL Server RESOURCE_SEMAPHORE Waittype Memory Issues    前言: 本文是对博客https://www.mssqltips.com/sqlservertip/2827/troubleshooting-sql-server-resourcesemaphore-waittype-memory-issues/的翻译,本文基本直译,部分地方读起来有点不自然。
975 0
Best Practices for RDS MySQL Storage Optimization
This article talks about spatial optimization of ApsaraDB for RDS MySQL. We will discuss the various objects which comprise the RDS Console with an understanding of the composition of objects.
1466 0
How to Synchronize RDS Data to a Local User-Defined Database
Here we will discuss how to synchronize RDS data to a local database. We will also explore the critical steps involving the data synchronization from RDS to a local database.
1048 0
Why You Should Use HybridDB for MySQL for Online and Offline Data Separation
HybridDB for MySQL helps you separate online and offline data in a precise, economical, and secure way.
2202 0
RDS SQL Server– Best Practices of Execution Plan Cache for Missing Indexes
Execution plan cache is a significant part of SQL Server memory management. It can reveal to you how the execution of a query will occur, or how query execution took place.
2543 0
关于mysql中information_schema.tables
项目中出现这样一个SQL语句,现记录如下: @Select("select table_name tableName, engine, table_comment tableComment, create_time createTime from information_schema.
1552 0
+关注
芷沁
https://www.alibabacloud.com/blog/
文章
问答
视频
文章排行榜
最热
最新
相关电子书
更多
Bringing MySQL Compatibility to HBase using Database Virtualization
立即下载
MySQL Replication: Latest Developments
立即下载
PostgresChina2018_杨杰_PostgreSQL-Flashback_Query实现与介绍
立即下载