:Handle row lock waits and timeouts in RDS for MySQL

Descrição do problema

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Causas

Em condições normais, a sessão que mantém um bloqueio exclusivo de linha conclui sua transação rapidamente e libera o bloqueio por meio de um commit ou rollback. As sessões em espera adquirem o bloqueio antes do timeout e continuam a execução. Os timeouts de espera por bloqueio ocorrem quando uma sessão mantém um bloqueio de linha por mais tempo do que o valor configurado em innodb_lock_wait_timeout permite.

Padrões comuns:

• Conexão ociosa com transação aberta: a aplicação abriu uma transação e executou operações, mas nunca realizou commit nem rollback — mantendo o bloqueio ativo por tempo indeterminado.

• DML em lote de longa duração: um UPDATE ou DELETE de grande volume mantém bloqueios de linha por um período extenso enquanto processa muitas linhas.

• Sessão de aplicação interrompida: a sessão de banco de dados de uma aplicação cliente é interrompida sem que a instância detecte a desconexão, fazendo com que a transação e seus bloqueios não sejam liberados.

Solução

As etapas abaixo se aplicam somente quando uma espera por bloqueio de linha está ocorrendo ativamente. O valor padrão de innodb_lock_wait_timeout no RDS for MySQL é 50 segundos, o que torna difícil observar uma espera ativa. Para reproduzir o problema em um ambiente de testes, defina innodb_lock_wait_timeout com um valor elevado — não faça essa alteração em produção.

1. Log on to an RDS for MySQL instance using DMS.

2. Execute a seguinte consulta para identificar qual sessão está bloqueando qual, junto com a query bloqueada e há quanto tempo a sessão bloqueante está em execução:

   SELECT
     r.trx_mysql_thread_id    AS waiting_thread,
     r.trx_query              AS waiting_query,
     b.trx_mysql_thread_id    AS blocking_thread,
     b.trx_query              AS blocking_query,
     b.trx_started            AS blocking_started,
     TIMESTAMPDIFF(SECOND, b.trx_started, NOW()) AS blocking_duration_sec
   FROM information_schema.INNODB_LOCK_WAITS w
   JOIN information_schema.INNODB_TRX b ON w.blocking_trx_id  = b.trx_id
   JOIN information_schema.INNODB_TRX r ON w.requesting_trx_id = r.trx_id;

   O resultado exibe o id da thread e a query atual tanto da sessão em espera quanto da bloqueante. Use blocking_duration_sec para avaliar por quanto tempo a sessão bloqueante mantém o bloqueio.

   Para uma visão mais detalhada, consulte cada tabela individualmente:

   -- All active transactions
   SELECT * FROM information_schema.INNODB_TRX;
   
   -- Transactions waiting for locks
   SELECT * FROM information_schema.INNODB_LOCK_WAITS;
   
   -- Locks currently held
   SELECT * FROM information_schema.INNODB_LOCKS;

3. Uma sessão bloqueante é aquela que mantém um bloqueio impedindo operações de Linguagem de Manipulação de Dados (DML) em outras sessões. Se um rollback da transação for aceitável para a sessão bloqueante, encerre-a usando o id da thread obtido na Etapa 2:

   KILL <blocking_thread_id>;

   Essa ação realiza o rollback da transação aberta da sessão bloqueante e libera seus bloqueios, permitindo que as sessões em espera prossigam.

4. Se o problema persistir ou não for possível identificar a causa raiz, consulte Lock blocking para usar a página de estatísticas de bloqueio e localizar sessões bloqueantes de longa duração e visualize os detalhes de cada sessão.

Prevenir recorrência

Encerrar a sessão bloqueante é uma medida de emergência. Para evitar que os timeouts de espera por bloqueio se repitam, trate a causa subjacente:

• Realize commit ou rollback das transações rapidamente: mantenha as transações curtas. Evite deixar transações abertas enquanto aguarda lógica da aplicação ou entrada do usuário.

• Divida operações em lote grandes em partes menores: em vez de um único UPDATE ou DELETE de grande volume, processe as linhas em lotes menores e execute o commit após cada lote para liberar os bloqueios de forma incremental.

• Adicione índices adequados: varreduras de tabela em colunas sem índice adquirem mais bloqueios de linha do que o necessário. Adicione índices às colunas usadas nas cláusulas WHERE de instruções DML para reduzir o número de linhas bloqueadas.

• Detecte conexões ociosas na camada da aplicação: configure os timeouts do pool de conexões e ative o TCP keepalive para que o banco de dados detecte clientes desconectados e libere seus bloqueios prontamente.

Aplicável a

ApsaraDB RDS for MySQL