Come posso identificare cosa blocca una query su un'istanza database che esegue Amazon RDS PostgreSQL o Aurora PostgreSQL?

Risoluzione

Le transazioni non confermate possono causare il blocco, la sospensione e il fallimento delle nuove query quando superano il timeout di attesa del blocco o il timeout del rendiconto. Per risolvere questo problema, identifica e interrompi la transazione che blocca la richiesta.

1. Esegui la seguente query sulla vista pg_stat_activity per identificare lo stato corrente della transazione bloccata:

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

Nota: sostituisci TABLE NAME con il nome o la condizione della tabella.

Se il valore della colonna wait_event_type è Lock, la query viene bloccata da altre transazioni o query. Se la colonna wait_event_type è un altro valore, allora c'è un ostacolo alle prestazioni con risorse come CPU, storage o capacità di rete. Per risolvere i problemi di prestazioni, ottimizza le prestazioni del tuo database. Ad esempio, puoi aggiungere indici, riscrivere interrogazioni o eseguire comandi vacuum and analysis. Per ulteriori informazioni, consulta Best practice per lavorare con PostgreSQL.

Se hai attivato Performance Insights, visualizza il carico del database raggruppato per evento di attesa, host, query SQL o utenti per identificare le transazioni bloccate. Per ulteriori informazioni, consulta Monitoraggio del carico del database con Performance Insights su Amazon RDS.

2. Se il valore della colonna wait_event_type è Lock, esegui il comando seguente per identificare la causa della transazione bloccata:

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. Controlla le colonne che hanno un prefisso di blocco. Nella seguente tabella di esempio, puoi vedere che la transazione bloccata viene eseguita sull'host 27.0.3.146 e utilizza psql. Usa blocking_user, blocking_user_addr e blocking_client_port per identificare quali sessioni bloccano le transazioni.

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: prima di terminare le transazioni, valuta il potenziale effetto che ogni transazione ha sullo stato del tuo database e della tua applicazione.

4. Esegui la seguente query per interrompere le transazioni:

SELECT pg_terminate_backend(PID);

Nota: sostituisci PID con blocking_pid del processo che hai identificato nel passaggio precedente.

Informazioni correlate

Documentazione PostgreSQL per la Visualizzazione dei blocchi

Documentazione PostgreSQL per le funzioni di segnalazione del server

Documentazione PostgreSQL per la descrizione di wait_event

Wiki PostgreSQL per il monitoraggio di Lock

Eventi di attesa di Amazon Aurora PostgreSQL