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

Resolução

As consultas são bloqueadas porque uma transação no InnoDB está aguardando que outra transação libere um bloqueio. Transações não confirmadas também podem bloquear consultas. Essas transações podem aparecer como NULL.

Usar a tabela INNODB\ _TRX para identificar transações não confirmadas

Para identificar a consulta ou sessão que está bloqueando a consulta, conclua as seguintes etapas:

1. Para visualizar as transações em execução no momento, execute a seguinte consulta na tabela INNODB\ _TRX:

   select * from information_schema.innodb_trx\G

2. Para ver as transações que estão aguardando e as transações que as estão bloqueando, execute uma das seguintes consultas com base na sua versão do MySQL.
   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;
   

   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 só pode continuar depois que a outra transação for confirmada ou revertida. Quando a sessão que emitiu a consulta está inativa, o valor NULL é registrado na consulta bloqueada. Nesse caso, use a consulta anterior para encontrar o PROCESSLIST_ID de blocking_thread.

3. Para o MySQL 5.7 ou posterior, execute a seguinte consulta para determinar o PROCESSLIST_ID da transação de bloqueio pelo THREAD_ID de bloqueio substituto:

   SELECT PROCESSLIST_ID FROM performance_schema.threads where THREAD_ID = blocking;

4. Execute a seguinte consulta na tabela events_statements_current com o THREAD_ID para determinar a última consulta que o thread executou:

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

   Observação: substitua THREAD\ _ID pelo valor que você recebeu na etapa 3.

5. Execute o comando a seguir para interromper a transação:

   CALL mysql.rds_kill(PROCESSLIST_ID);

   Observação: interromper ou reverter uma transação de longa duração é demorado e exige muita E/S.

Identificar as transações XA de bloqueio

Se blocking_thread for 0 e CALL mysql.rds_kill(PROCESSLIST_ID); não funcionar, uma transação XA poderá bloquear você.

Para identificar a transação XA que está causando o bloqueio, execute o seguinte comando:

XA RECOVER;

O comando anterior retorna uma saída para transações XA que estão no estado Preparado. Para interromper o bloqueio, use a coluna de dados para reverter ou confirmar a transação:

XA RECOVER;

+----------+--------------+--------------+--------------------+
| formatID | gtrid_length | bqual_length | data               |
+----------+--------------+--------------+--------------------+
|   1      |           10 |            0 | RePostTest         |
+----------+--------------+--------------+--------------------+
1 row in set (0.00 sec)

XA COMMIT 'RePostTest';
XA ROLLBACK 'RePostTest';

Se você receber o erro a seguir ao realizar a confirmação ou a reversão, converta o XID em hexadecimal e reverta a transação:

“ERRO 1397 (XAE04): XAER_NOTA: XID desconhecido”

Para reverter a transação, execute o comando a seguir para recuperar gtrid_length e bqual_length para extrair as partes necessárias do campo de dados:

mysql> xa recover convert xid;
+----------+--------------+--------------+----------------------------------------+
| formatID | gtrid_length | bqual_length | data                                   |
+----------+--------------+--------------+----------------------------------------+
|   1      |           10 |            0 | 0x5265506F737454657374                 |
+----------+--------------+--------------+----------------------------------------+
1 row in set (0.00 sec)

mysql> XA ROLLBACK X'5265506F737454657374';
Query OK, 0 rows affected (0.01 sec)

Para mais informações sobre como converter valores XID, consulte as instruções de SQL de transação XA no site do MySQL.

Informações relacionadas

Encerrar uma sessão ou consulta no RDS para MySQL

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

A tabela INFORMATION_SCHEMA_INNODB_TRX no site do MySQL

Identificar transações de bloqueio no site do MySQL

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