How can I identify what is blocking a query on a DB instance that is running Amazon RDS PostgreSQL or Aurora PostgreSQL?

Resolution

Uncommitted transactions can cause new queries to be blocked, to sleep, and to fail when they exceed the lock wait timeout or the statement timeout. To resolve this issue, identify and stop the transaction that blocks the query.

1.    Run the following query against the pg_stat_activity view to identify the current state of the blocked transaction:

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

Note: Replace TABLE NAME with your table name or condition.

If the value of the wait_event_type column is Lock, then the query is blocked by other transactions or queries. If the wait_event_type column is any other value, then there's a performance bottleneck with resources such as CPU, storage, or network capacity. To resolve performance bottlenecks, tune the performance of your database. For example, you can add indexes, rewrite queries, or run vacuum and analyze commands. For more information, see Best practices for working with PostgreSQL.

If you turned on Performance Insights, view the DB load that's grouped by wait event, hosts, SQL queries, or users to identify blocked transactions. For more information, see Monitoring DB load with Performance Insights on Amazon RDS.

2.    If the value of the wait_event_type column is Lock, then run the following command to identify the cause of the blocked transaction:

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.    Review the columns that have a blocking prefix. In the following example table, you can see that the blocked transaction runs on the 27.0.3.146 host and uses psql. Use blocking_user, blocking_user_addr, and blocking_client_port to help identify which sessions block transactions.

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;

Important: Before you terminate transactions, evaluate the potential effect that each transaction has on the state of your database and your application.

4.    Run the following query to stop the transactions:

SELECT pg_terminate_backend(PID);

Note: Replace PID with blocking_pid of the process that you identified in the preceding step.

Related information

PostgreSQL documentation for Viewing locks

PostgreSQL documentation for Server signaling functions

PostgreSQL documentation for wait_event description

PostgreSQL Wiki for Lock monitoring

Amazon Aurora PostgreSQL wait events