This topic describes how to use Data Management Service (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 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, chooseSQL 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 Ids 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;