Como identificar o que está bloqueando uma consulta em uma instância de banco de dados que executa o Amazon RDS PostgreSQL ou o Aurora PostgreSQL?

Resolução

Transações não confirmadas podem fazer com que novas consultas sejam bloqueadas, suspensas e falhem quando excederem o tempo limite de espera do bloqueio ou o tempo limite da instrução. Para resolver esse problema, identifique e interrompa a transação que bloqueia a consulta.

1.    Execute a consulta a seguir na visualização pg_stat_activity para identificar o estado atual da transação bloqueada:

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

Observação: Substitua NOME DA TABELA com o nome da sua tabela ou condição.

Se o valor da coluna wait_event_type for Bloquear, a consulta será bloqueada por outras transações ou consultas. Se a coluna wait_event_type tiver qualquer outro valor, haverá um gargalo de desempenho com recursos como CPU, armazenamento ou capacidade de rede. Para resolver gargalos de desempenho, ajuste o desempenho do seu banco de dados. Por exemplo, você pode adicionar índices, reescrever consultas ou executar comandos vacuum e analyze. Para obter mais informações, consulte Práticas recomendadas para trabalhar com o PostgreSQL.

Se você ativou o Performance Insights, visualize a carga do banco de dados agrupada por evento de espera, hosts, consultas SQL ou usuários para identificar transações bloqueadas. Para obter mais informações, consulte Monitorar a carga de banco de dados com o Performance Insights no Amazon RDS.

2.    Se o valor da coluna wait_event_type for Bloquear, execute o comando a seguir para identificar a causa da transação 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.    Analise as colunas que têm um prefixo de bloqueio. Na tabela de exemplo a seguir, você pode ver que a transação bloqueada é executada no host 27.0.3.146 e usa psql. Use blocking_user, blocking_user_addr, e blocking_client_port para ajudar a identificar quais sessões bloqueiam transações.

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 encerrar as transações, avalie o efeito potencial de cada transação sobre o estado do seu banco de dados e do seu aplicativo.

4.    Execute a consulta a seguir para interromper as transações:

SELECT pg_terminate_backend(PID);

Observação: substitua o PID pelo blocking_pid do processo identificado na etapa anterior.

Informações relacionadas

Documentação do PostgreSQL para visualização de bloqueios

Documentação do PostgreSQL para funções de sinalização do servidor

Documentação do PostgreSQL para descrição de wait_event

Wiki do PostgreSQL para monitoramento de bloqueio

Eventos de espera do Amazon Aurora PostgreSQL