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

Breve descripción

Las consultas bloqueadas pueden producirse porque una transacción en InnoDB está esperando a que otra transacción libere un bloqueo. Las consultas también se pueden bloquear debido a transacciones no confirmadas. Estas transacciones pueden aparecer como NULL. Siga estos pasos para identificar la consulta o la sesión que podría estar bloqueando su consulta.

Resolución

Identificar las transacciones no comprometidas

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.    Ejecute esta consulta para ver qué transacciones están en espera y qué transacciones las bloquean.

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;

Para 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 no puede continuar hasta que la otra transacción se confirme o anule.

Al identificar las transacciones bloqueadas, se indica un valor NULL para la consulta de bloqueo si la sesión que ha emitido la consulta ha quedado inactiva. En este caso, use la consulta del paso 2 para encontrar el identificador de la lista de procesos blocking_thread.

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

SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = blocking_thread;

4.    Use el THREAD_ID para consultar la tabla events_statements_current del esquema de rendimiento. Esto determina la última consulta ejecutada por el hilo.

Nota: Asegúrese de sustituir THREAD_ID por el valor devuelto en el paso 3.

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

5.    Tras identificar la sesión de bloqueo o el thread-ID, detenga la transacción ejecutando este procedimiento:

Nota: Antes de ejecutar este procedimiento, compruebe si necesita la transacción o si es seguro detenerla.

CALL mysql.rds_kill(thread-ID);

Nota: Detener o revertir una operación de ejecución prolongada puede llevar mucho tiempo y requerir un uso intensivo de las E/S.

Información relacionada

Finalización de una sesión o consulta

Opciones para las instancias de bases de datos MySQL

The INFORMATION_SCHEMA_INNODB_TRX table del sitio web de MySQL

Identifying blocking transactions del sitio web de MySQL

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