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.
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.
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.
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.
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.
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.
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.
● 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:
● 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.
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.
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.
Key Management Services Android Demo using Alibaba Cloud KMS Service
2,605 posts | 747 followers
FollowApsaraDB - March 19, 2020
Morningking - September 26, 2023
ApsaraDB - October 21, 2020
ApsaraDB - July 29, 2022
Alibaba Cloud Indonesia - March 23, 2023
ApsaraDB - October 26, 2023
2,605 posts | 747 followers
Follow
AnalyticDB for MySQL
AnalyticDB for MySQL is a real-time data warehousing service that can process petabytes of data with high concurrency and low latency.
Learn More
Database Overview
ApsaraDB: Faster, Stronger, More Secure
Learn More
PolarDB for MySQL
Alibaba Cloud PolarDB for MySQL is a cloud-native relational database service 100% compatible with MySQL.
Learn More
ApsaraDB RDS for MySQL
An on-demand database hosting service for MySQL with automated monitoring, backup and disaster recovery capabilities
Learn MoreMore Posts by Alibaba Clouder
Raja_KT March 26, 2019 at 4:49 pm
Good for reference especially when we run many Hive applications .....