Use DMS to release metadata locks - - Alibaba Cloud Documentation Center

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.

Scenarios

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.

Causes

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.

Procedure

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, execute the SHOW FULL PROCESSLIST statement to view the status of all threads in the database.
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.
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,
  (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);
```
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
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;