ApsaraDB RDS:Resolve database failures caused by metadata locks

Background

MySQL 5.5 introduced the metadata lock (MDL) to ensure consistency between Data Definition Language (DDL) and Data Manipulation Language (DML) operations. However, blocking can occur in some scenarios, such as when an ALTER operation is performed during a DML operation, or when an ALTER operation is attempted while a long-running query is active.

Scenarios

• Create or drop an index.

• Modify a table structure.

• Perform table maintenance operations, such as optimize table or repair table.

• Drop a table.

• Acquire a table-level write lock.

Causes

• A long-running query is active on the table.

• A transaction that was started explicitly or implicitly was not committed or rolled back. For example, a query completes but its transaction remains open.

• A transaction that includes a failed query exists on the table.

Steps

1. Log on to an ApsaraDB RDS database using DMS.

2. In the SQL Console, run the show full processlist command to view the status of all database threads.

3. Check whether the State column contains a large number of Waiting for table metadata lock values. If Waiting for table metadata lock appears, blocking is occurring.

4. Find the session ID of the blocking session.

   1. For a session with the Waiting for table metadata lock status, check the Info column to identify the table it is trying to access, for example, sbtest2.

   2. Scan the Info column of other sessions to find a session that is operating on table sbtest2. Record the Id of that session.

      Note

      You need to find the session that is actively holding the lock on the table, not the sessions waiting for the lock. Sessions waiting for a lock show the Waiting for table metadata lock status. You can identify the blocking session by analyzing the State and Info columns.

      For example, for a session whose State is Waiting for table metadata lock, you can determine from the command in its Info column that this session needs to operate on the sbtest2 table. Among other sessions that also need to operate on the sbtest2 table, you can determine from the State column of the session with an Id of 267 that it is currently operating on the sbtest2 table and causing the block.

      Note

      The Sending data state is an example. The actual state of a blocking session may vary.

      You can also run the following query to find long-running transactions. If the user who initiated the blocking statement is different from the current user, you must log in as that user to terminate the session.

      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);

5. Run kill <session ID> (for example, kill 267) to terminate the session and release the metadata lock.

Recommendations

• Perform operations such as creating or dropping an index during off-peak hours.

• Enable autocommit.

• Set the lock_wait_timeout parameter to a lower value.

• Consider using an event to automatically terminate long-running transactions. For example, the following event terminates any transaction that has been running for more than 60 minutes.

  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;