¿Cómo identifico el elemento que ha bloqueado una consulta en mi instancia de base de datos PostgreSQL o Aurora PostgreSQL de Amazon RDS?

Resolución

Las transacciones no confirmadas pueden bloquear las consultas nuevas, hacer que se suspendan o provocar que fallen cuando superan el tiempo de espera del bloqueo o el tiempo de espera de la declaración. Para resolver este problema, identifica y detén las transacciones sin confirmar.

Identificación de las transacciones bloqueadas

Para identificar las transacciones bloqueadas, activa Información de rendimiento y utiliza Database Insights. Usa el gráfico de carga de la base de datos para ver las dimensiones de carga de la base de datos para Waits, Host, SQL o User. Para obtener más información, consulte Supervisión de la carga de base de datos con Información de rendimiento en Amazon RDS.

Para identificar el estado actual de la transacción bloqueada, ejecuta la siguiente instrucción:

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

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

Si el valor de la columna wait_event_type no es Bloquear, quiere decir que 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, ajusta el rendimiento de la base de datos. Por ejemplo, puedes añadir índices, reescribir consultas o ejecutar comandos vacuum y analyze. Para obtener más información, consulta Prácticas recomendadas para trabajar con PostgreSQL.

Si el valor de la columna wait_event_type es Bloquear, otras transacciones o consultas bloquearán la consulta. Para identificar la causa de la transacción bloqueada, ejecuta la siguiente instrucción:

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;

Para identificar qué sesiones bloquean las transacciones, revisa blocking_user, blocking_user_addr y blocking_client_port. En el siguiente ejemplo de salida, el usuario principal usa psql para bloquear la transacción que se ejecuta en el host 27.0.3.146. El valor de blocking_pid es 8740.

blocked_pid                           | 9069blocked_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;

Detención de transacciones

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

Para detener las transacciones, ejecuta la siguiente instrucción:

SELECT pg_terminate_backend(PID);

**Nota:**Sustituye el valor PID por las transacciones blocking_pid.

Información relacionada

Eventos de espera de Amazon Aurora PostgreSQL