ApsaraDB RDS:Resolve database failures caused by metadata locks

Contexto

O MySQL 5.5 implementou o bloqueio de metadados (MDL) para garantir a consistência entre as operações de Data Definition Language (DDL) e Data Manipulation Language (DML). No entanto, bloqueios podem ocorrer em alguns cenários, como ao executar uma operação ALTER durante uma operação DML ou ao tentar uma operação ALTER enquanto uma consulta de longa duração está ativa.

Cenários

• Crie ou remover um índice.

• Modifique a estrutura de uma tabela.

• Execute operações de manutenção de tabela, como optimize table ou repair table.

• Excluir uma tabela.

• Obter um bloqueio de escrita no nível da tabela.

Causas

• Uma consulta de longa duração está ativa na tabela.

• Uma transação iniciada de forma explícita ou implícita não foi confirmada nem desfeita. Por exemplo, a consulta é concluída, mas a transação permanece aberta.

• Há uma transação na tabela que inclui uma consulta com falha.

Etapas

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

2. No SQL Console, execute o comando show full processlist e visualize o status de todos os threads do banco de dados.

3. Verifique se a coluna State contém um grande número de valores Waiting for table metadata lock. Se o status Waiting for table metadata lock aparecer, indica a ocorrência de um bloqueio.

4. Encontre o ID da sessão que causa o bloqueio.

   1. Para uma sessão com o status Waiting for table metadata lock, verifique a coluna Info para identificar a tabela que a sessão tenta acessar, por exemplo, sbtest2.

   2. Examine a coluna Info de outras sessões para encontrar aquela que opera na tabela sbtest2. Anote o Id dessa sessão.

      Nota

      Identifique a sessão que mantém ativamente o bloqueio na tabela, e não as sessões que aguardam o bloqueio. As sessões que aguardam um bloqueio exibem o status Waiting for table metadata lock. Analise as colunas State e Info para identificar a sessão que causa o bloqueio.

      Por exemplo, para uma sessão cujo State é Waiting for table metadata lock, o comando na coluna Info indica que a sessão precisa operar na tabela sbtest2. Dentre as outras sessões que também precisam operar na tabela sbtest2, a coluna State da sessão com Id 267 mostra que ela opera atualmente na tabela sbtest2 e causa o bloqueio.

      Nota

      O status Sending data é apenas um exemplo. O status real de uma sessão de bloqueio pode variar.

      Execute também a seguinte consulta para encontrar transações de longa duração. Se o usuário que iniciou a instrução de bloqueio for diferente do atual, faça login como esse usuário para encerrar a sessão.

      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. Execute kill <ID da sessão> (por exemplo, kill 267) para encerrar a sessão e liberar o bloqueio de metadados.

Recomendações

• Execute operações como crie ou remover um índice fora dos horários de pico.

• Ative o autocommit.

• Defina o parâmetro lock_wait_timeout para um valor menor.

• Considere usar um evento para encerrar automaticamente transações de longa duração. Por exemplo, o evento a seguir encerra qualquer transação em execução há mais de 60 minutos.

  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;