Use DMS to release metadata locks

Background information

MySQL 5.5 or later uses metadata locking to ensure consistency between Data Definition Language (DDL) and Data Manipulation Language (DML) operations. However, metadata locks may block subsequent operations on tables, for example, if you perform ALTER operations while DML operations are in progress, or if you perform ALTER operations while queries for data 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, repair, and other operations.
• Delete a table.
• Acquire 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, nor are they rolled back after errors occur. Such transactions include those for completing query operations.
• Some query transactions failed in a table.

Procedure

1. Use DMS to log on to the target database of your ApsaraDB for RDS instance.
   Note In the Network address:Port field, you must enter the Internal Endpoint and Internal Port of your ApsaraDB for RDS instance. Then, you can log on to the ApsaraDB for RDS console and navigate to the Basic Information page to obtain the internal endpoint and internal port.
2. In the top navigation bar of the DMS home page, choose SQL Operations > SQL Window.
3. Enter show full processlist; and click the execute button to view the status of all threads. If a large number of threads whose State reads as Waiting for table metadata lock are found, operations on the target database are blocked. In the Info column, obtain the tables where operations are to be performed. Then, find the sessions that solicit the operations, and note down the Id of the sessions.
   Note

   • You must find the sessions holding locks on tables but not those waiting to release locks. You can distinguish between the two types of sessions based on the State and Info columns.
   • You can run the following command to query transactions that have been pending for a long time. If the account blocking operations is not the one you are using, log on to the target database by using this account, and then terminate 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'))) p
       where timestampdiff(second, i.trx_started, now()) > p.time
       and i.trx_mysql_thread_id  not in (connection_id(),p.id);

4. Enter kill Id and click the execute button to terminate the session specified by the Id parameter. An example command is as follows: kill 267. After the session is terminated, its metadata lock is released.

What to do next

• Manage tables during off-peak hours, including creating and deleting indexes.
• Enable autocommit mode.
• Set the lock_wait_timeout parameter to a small value.
• Use events to terminate transactions that have been running for a long time. In the following example, transactions that have been 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;