This topic describes how to use Data Management (DMS) to release metadata locks that block subsequent operations on a table.

Background information

MySQL 5.5 or later uses metadata locking to ensure consistency between DDL and DML operations. However, metadata locks may block subsequent operations on tables, like ALTER operations that are performed while DML operations are in progress or while data queries spanning a long time range are being processed.


  • Create and delete indexes from a table.
  • Modify the structure of a table.
  • Manage a table, including optimize and repair operations.
  • Delete a table.
  • Obtain table-level write locks.


  • Requests for querying data spanning a long time range from a table are being processed.
  • Explicitly or implicitly started transactions are not committed or rolled back after errors occur. The transactions include the transactions for completing query operations.
  • Query transactions in a table failed.


  1. Log on to your RDS instance. For more information, see Use DMS to log on to an ApsaraDB RDS for MySQL instance.
  2. On the SQL Console tab, execute the SHOW FULL PROCESSLIST statement to view the status of all threads in the database.
  3. In the output, check whether Waiting for table metadata lock is displayed in the State column for a large number of rows. The Waiting for table metadata lock state indicates that a session is blocked.
  4. Identify the ID of the session that is blocked.
    1. View the Info column of the session that is in the Waiting for table metadata lock session state to identify the table of the session. Example: sbtest2.
    2. View the Info column of other sessions to identify sessions that perform operations on the sbtest2 table. Record the IDs of the sessions .
      Note 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.
      In the following example, some sessions are in the Waiting for table metadata lock state. If you observe the statement in the Info column, you can determine that these sessions are waiting to manage the sbtest2 table. If you observe the information in the State and Info columns of the session whose Id is 267, you can determine that the session is managing the sbtest2 table and is blocking subsequent operations.
      Note The Sending data state is provided only for reference.
      You can execute the following statements to query transactions that are pending for a long period of time. To terminate the sessions that hold locks, you must log on to the database by using the account that initiates the sessions.
      select concat('kill ',i.trx_mysql_thread_id,';') from information_schema.innodb_trx i,
               id, time
               time = (select 
                       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(),;
  5. Enter kill Id to terminate the session that is specified by the Id parameter. For example, run the kill 267 command. After the session is terminated, the metadata lock of the session is released.

What to do next

  • Manage tables during off-peak hours, including creating and deleting indexes.
  • Enable the autocommit mode.
  • Set the lock_wait_timeout parameter to a small value.
  • Use events to terminate transactions that are running for a long period of time. In the following example, transactions that are running for more than 60 minutes are terminated:
    create event my_long_running_trx_monitor
    on schedule every 60 minute
    starts '2015-09-15 11:00:00'
    on completion preserve enable do
      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;
        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;