Generation and Handling of Metadata Locks on RDS for MySQL Tables

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.

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.

● 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.

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.

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.