Release metadata locks blocking table operations using DMS - ApsaraDB RDS

When a long-running transaction or an uncommitted session holds a metadata lock on a table, subsequent DDL operations — such as ALTER TABLE — queue up and are blocked until the lock is released. This guide shows how to identify the blocking session and terminate it using Data Management (DMS).

MySQL 5.5 introduced metadata locking (MDL) to maintain consistency between DDL and DML operations.

When metadata locks block operations

Metadata locks block these table-level DDL operations:

Creating or dropping indexes
Modifying table structure (ALTER TABLE)
Running OPTIMIZE TABLE or REPAIR TABLE
Dropping a table
Acquiring table-level write locks

Common causes

A session holds a metadata lock when any of the following is true:

A long-running query is still scanning data from the table.
A transaction — started explicitly with START TRANSACTION or implicitly — was not committed or rolled back after an error.
A failed query transaction left a lock unreleased on the table.

Prerequisites

Before you begin, ensure that you have:

Access to your ApsaraDB RDS for MySQL instance via DMS
Permission to run SHOW FULL PROCESSLIST and KILL statements

Identify and release the blocking session

Step 1: Check for blocked sessions

Log on to your RDS instance. For more information, see Use DMS to log on to an ApsaraDB RDS for MySQL instance.
On the SQL Console tab, run the following statement to view the status of all threads:
```
SHOW FULL PROCESSLIST
```
In the output, check whether Waiting for table metadata lock appears in the State column for one or more rows. Sessions in this state are waiting for a lock to be released — they are not the source of the block.

Step 2: Identify the session holding the lock

Identify the blocking session from the PROCESSLIST output:

Find rows where State shows Waiting for table metadata lock. Check the Info column to see which table is involved — for example, sbtest2.
Look for other sessions whose Info column shows an operation on sbtest2 but whose State column is not Waiting for table metadata lock. These sessions are holding the lock.

You must identify the sessions that always occupy the table instead of the sessions that are waiting for metadata locks to be released. The Waiting for table metadata lock state indicates that a session is waiting for metadata locks to be released. You can distinguish between the two types of sessions based on the State and Info columns. The Sending data state is shown for reference only.

In the example above, session 267 is holding the lock on sbtest2 while other sessions are waiting.

To find long-running transactions that are likely causing the block, run:

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', 'trunc'))) p
  where timestampdiff(second, i.trx_started, now()) > p.time
  and i.trx_mysql_thread_id  not in (connection_id(),p.id);

The query returns KILL statements for transactions that have been running longer than the oldest waiting DDL operation.

Step 3: Terminate the blocking session

To terminate the blocking session, run kill Id. For example, to terminate session 267:

kill 267

Prevent future metadata lock contention

To reduce the frequency of metadata lock contention:

Schedule DDL during off-peak hours. Index creation, deletion, and other schema changes are less likely to conflict when traffic is low.
Enable autocommit. With autocommit enabled, transactions are committed immediately after each statement, minimizing the window during which a lock is held.
Set a shorter `lock_wait_timeout`. A smaller value causes blocked DDL operations to fail fast rather than pile up.

Automatically terminate long-running transactions using a MySQL event. The following event checks every 60 minutes and kills any transaction that has been running for 60 minutes or more:

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