¿Por qué se ha bloqueado una consulta a mi instancia de base de datos de Amazon RDS para MySQL cuando no hay ninguna otra sesión activa?

Resolución

Las consultas bloqueadas pueden producirse porque una transacción en InnoDB está esperando a que otra transacción libere un bloqueo. Las transacciones no confirmadas también pueden bloquear las consultas. Estas transacciones pueden aparecer como NULL.

Uso de la tabla INNODB_TRX para identificar las transacciones no confirmadas

Para identificar la consulta o sesión que bloquea la consulta, siga estos pasos:

1. Para ver las transacciones que se están ejecutando actualmente, ejecute la siguiente consulta en la tabla INNODB_TRX:

   select * from information_schema.innodb_trx\G

2. Para ver las transacciones que están en espera y las transacciones que las bloquean, ejecute una de las siguientes consultas según su versión de MySQL.
   Para MySQL 5.7 y versiones 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;
   

   Nota: La transacción bloqueada solo puede continuar cuando la otra transacción se confirma o anula. Cuando la sesión que emitió la consulta está inactiva, se notifica un valor NULL para la consulta de bloqueo. En este caso, use la consulta anterior para encontrar el PROCESSLIST_ID de blocking_thread.

3. Para MySQL 5.7 o posterior, ejecute esta consulta para determinar el PROCESSLIST_ID de la transacción que causa el bloqueo sustituyendo el THREAD_ID que causa el bloqueo:

   SELECT PROCESSLIST_ID FROM performance_schema.threads where THREAD_ID = blocking;

4. Ejecute la siguiente consulta en la tabla events_statements_current con el THREAD_ID para determinar la última consulta que ejecutó el subproceso:

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

   Nota: Sustituya THREAD_ID por el valor que recibió en el paso 3.

5. Ejecute el siguiente comando para detener la transacción:

   CALL mysql.rds_kill(PROCESSLIST_ID);

   Nota: Detener o revertir una transacción de larga duración lleva mucho tiempo y requiere una gran cantidad de E/S.

Identificación de las transacciones de bloqueo de XA

Si blocking_thread es 0 y CALL mysql.rds_kill(PROCESSLIST_ID); no funciona, es posible que una transacción de XA cause un bloqueo.

Para identificar la transacción de XA que está causando el bloqueo, ejecute el siguiente comando:

XA RECOVER;

El comando anterior devuelve un resultado para las transacciones de XA que están en estado Preparado. Para detener el bloqueo, use la columna de datos para deshacer o confirmar la transacción:

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';

Si recibe el siguiente error al realizar la confirmación o la reversión, convierta el XID a hexadecimal y anule la transacción:

"ERROR 1397 (XAE04): XAER_NOTA: XID desconocido»

Para recuperar la transacción, ejecute el siguiente comando para recuperar gtrid_length y bqual_length para extraer las partes necesarias del campo de datos:

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 obtener más información sobre cómo convertir valores XID, consulte las instrucciones SQL de transacciones de XA en el sitio web de MySQL.

Información relacionada

Finalización de una sesión o consulta de RDS para MySQL

Opciones para las instancias de bases de datos MySQL

La tabla INFORMATION_SCHEMA_INNODB_TRX en el sitio web de MySQL

Identificación de las transacciones de bloqueo en el sitio web de MySQL

Tareas comunes de administración de bases de datos para las instancias de bases de datos MySQL