¿Cómo puedo identificar qué bloquea una consulta en una instancia de base de datos que ejecuta Amazon RDS PostgreSQL o Aurora PostgreSQL?

Solución

Las transacciones no confirmadas pueden provocar que las consultas nuevas se bloqueen, queden suspendidas y fallen si superan el tiempo de espera del bloqueo o el tiempo de espera de la instrucción. Para resolver este problema, identifique y detenga la transacción que bloquea la consulta.

1.Ejecute la siguiente consulta en la vista pg_stat_activity para identificar el estado actual de la transacción bloqueada:

SELECT * FROM pg_stat_activity WHERE query iLIKE '%TABLE NAME%' ORDER BY state;

Nota: Sustituya TABLE NAME por el nombre o condición de la tabla.

Si el valor de la columna wait_event_type es Lock, otras transacciones o consultas están bloqueando la consulta. Si la columna wait_event_type tiene cualquier otro valor, hay un cuello de botella en el rendimiento con recursos como la capacidad de la CPU, el almacenamiento o la red. Para resolver los cuellos de botella de rendimiento, ajuste el rendimiento de la base de datos. Por ejemplo, puede añadir índices, reescribir consultas o ejecutar comandos vacuum y analyze. Para obtener más información, consulte Prácticas recomendadas para trabajar con PostgreSQL.

Si ha activado Performance Insights, consulte la carga de base de datos agrupada por eventos de espera, hosts, consultas SQL o usuarios para identificar las transacciones bloqueadas. Para obtener más información, consulte Monitoreo de la carga de base de datos con Performance Insights en Amazon RDS.

2.Si el valor de la columna wait_event_type es Lock, ejecute el siguiente comando para identificar la causa de la transacción bloqueada:

SELECT blocked_locks.pid     AS blocked_pid,
       blocked_activity.usename  AS blocked_user,
       blocked_activity.client_addr as blocked_client_addr,
       blocked_activity.client_hostname as blocked_client_hostname,
       blocked_activity.client_port as blocked_client_port,
       blocked_activity.application_name as blocked_application_name,
       blocked_activity.wait_event_type as blocked_wait_event_type,
       blocked_activity.wait_event as blocked_wait_event,
       blocked_activity.query    AS blocked_statement,
       blocking_locks.pid     AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocking_activity.client_addr as blocking_user_addr,
       blocking_activity.client_hostname as blocking_client_hostname,
       blocking_activity.client_port as blocking_client_port,
       blocking_activity.application_name as blocking_application_name,
       blocking_activity.wait_event_type as blocking_wait_event_type,
       blocking_activity.wait_event as blocking_wait_event,
       blocking_activity.query   AS current_statement_in_blocking_process
 FROM  pg_catalog.pg_locks         blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks         blocking_locks
        ON blocking_locks.locktype = blocked_locks.locktype
        AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
        AND blocking_locks.pid != blocked_locks.pid
    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
   WHERE NOT blocked_locks.granted ORDER BY blocked_activity.pid;

3.Revise las columnas que tienen un prefijo blocking. En la siguiente tabla de ejemplo, puede ver que la transacción bloqueada se ejecuta en el host 27.0.3.146 y usa psql. Utilice blocking_user, blocking_user_addr y blocking_client_port para ayudar a identificar qué sesiones bloquean las transacciones.

blocked_pid                           | 9069
blocked_user                          | master
blocked_client_addr                   | 27.0.3.146
blocked_client_hostname               |
blocked_client_port                   | 50035
blocked_application_name              | psql
blocked_wait_event_type               | Lock
blocked_wait_event                    | transactionid
blocked_statement                     | UPDATE test_tbl SET name = 'Jane Doe' WHERE id = 1;
blocking_pid                          | 8740
blocking_user                         | master
blocking_user_addr                    | 27.0.3.146
blocking_client_hostname              |
blocking_client_port                  | 26259
blocking_application_name             | psql
blocking_wait_event_type              | Client
blocking_wait_event                   | ClientRead
current_statement_in_blocking_process | UPDATE tset_tbl SET name = 'John Doe' WHERE id = 1;

Importante: Antes de finalizar las transacciones, evalúe el posible efecto que cada transacción tiene en el estado de la base de datos y aplicación.

4.Ejecute la siguiente consulta para detener las transacciones:

SELECT pg_terminate_backend(PID);

Nota: Sustituya PID por blocking_pid del proceso que identificó en el paso anterior.

Información relacionada

Documentación de PostgreSQL para ver bloqueos

Documentación de PostgreSQL para las funciones de señalización de servidores

Documentación de PostgreSQL para la descripción de wait_event

Wiki de PostgreSQL para el monitoreo de bloqueos

Eventos de espera de Amazon Aurora PostgreSQL