Por que uma consulta a uma instância de banco de dados MySQL do Amazon RDS foi bloqueada quando não há outra sessão ativa?

Breve descrição

Consultas bloqueadas podem ocorrer porque uma transação no InnoDB está aguardando que outra transação libere um bloqueio. As consultas também podem ser bloqueadas devido a transações não confirmadas. Essas transações podem aparecer como NULL. Siga estas etapas para identificar a consulta ou sessão que pode estar bloqueando sua consulta.

Resolução

Identifique transações não comprometidas

1.    Visualize as transações em execução no momento executando esta consulta na tabela INNODB_TRX:

select * from information_schema.innodb_trx\G

2.    Execute essa consulta para ver quais transações estão aguardando e quais transações as estão bloqueando.

Para o MySQL 5.7 e versões anteriores:

SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_trx_id;

Para o MySQL 8.0:

SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM       performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_engine_transaction_id;

Observação: a transação bloqueada não pode continuar até que a outra transação seja confirmada ou revertida.

Ao identificar transações de bloqueio, um valor NULL é relatado para a consulta de bloqueio se a sessão que emitiu a consulta ficar ociosa. Nesse caso, use a consulta da Etapa 2 para encontrar o ID da lista de processos blocking_thread.

3.    Para o MySQL 5.7 ou posterior, execute esta consulta para determinar o THREAD_ID da transação de bloqueio substituindo o ID da lista de processos blocking_thread:

SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = blocking_thread;

4.    Use o THREAD_ID para consultar a tabela do esquema de desempenho events_statements_current. Isso determina a última consulta executada pelo thread.

Observação: certifique-se de substituir THREAD_ID pelo valor retornado na Etapa 3.

SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_current WHERE THREAD_ID = THREAD_ID;

5.    Depois de identificar a sessão de bloqueio ou o thread-ID, interrompa a transação executando este procedimento:

Observação: antes de executar esse procedimento, avalie se você precisa da transação ou se é seguro interrompê-la.

CALL mysql.rds_kill(thread-ID);

Observação: interromper ou reverter uma operação de longa duração pode ser demorado e consumir muita E/S.

Informações relacionadas

Encerrar uma sessão ou consulta

Opções para instâncias de banco de dados MySQL

A tabela INFORMATION_SCHEMA_INNODB_TRX no site do MySQL

Identificação de transações de bloqueio no site do MySQL

Tarefas comuns de DBA para instâncias de banco de dados MySQL