ApsaraDB RDS:Resolve database failures caused by metadata locks

Contexto

O MySQL 5.5 introduziu o bloqueio de metadados (MDL) para garantir a consistência entre 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 executar uma operação ALTER enquanto uma consulta de longa duração está ativa.

Cenários

• Crie ou remova um índice.

• Modifique a estrutura de uma tabela.

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

• Remover uma tabela.

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

Causas

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

• Uma transação iniciada explícita ou implicitamente não teve commit ou rollback. Por exemplo, a consulta foi concluída, mas a transação permanece aberta.

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

Etapas

1. Faça login em um banco de dados ApsaraDB RDS usando o DMS.

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

3. Verifique se a coluna State contém muitos valores Waiting for table metadata lock. Se Waiting for table metadata lock aparecer, há um bloqueio em andamento.

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

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

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

      Nota

      Encontre a sessão que detém ativamente o bloqueio na tabela, e não as sessões que aguardam o bloqueio. As sessões em espera exibem o status Waiting for table metadata lock. Analise as colunas State e Info para identificar a sessão bloqueadora.

      Por exemplo, se uma sessão tem o State definido como Waiting for table metadata lock, o comando na coluna Info indica que essa sessão precisa operar na tabela sbtest2. Entre as demais sessões que também precisam operar na tabela sbtest2, observe a coluna State da sessão com Id igual a 267. Isso mostra que ela opera atualmente na tabela sbtest2 e causa o bloqueio.

      Nota

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

      Também é possível executar a seguinte consulta para localizar transações de longa duração. Se o usuário que iniciou a instrução bloqueadora for diferente do usuário 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 criação ou remoção de índices fora dos horários de pico.

• Ative o autocommit.

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

• Considere usar um evento para encerrar automaticamente transações de longa duração. Por exemplo, o evento abaixo 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;