如何排查RDS for PostgreSQL查询被阻塞问题

尝试在RDS PostgreSQL实例上运行查询，发现查询语句长时间未返回结果，语句执行被阻塞。我该如何排查并解决这个问题？

通常情况下，查询被阻塞是由于其他未提交的事务所导致的。当锁等待超时后，查询语句将返回失败。大量的阻塞会导致应用与数据库性能下降，应该引起我们的足够重视。

1. 通过对pg_stat_activity [1]定位被阻塞的事务的当前状态

test=# SELECT * FROM pg_stat_activity WHERE wait_event_type ='Lock'
```
`
wait_event_type =Lock [2]意味着查询被其他事务阻塞。
### 2. 通过如下命令确定事务被阻塞的具体原因
```sql
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;
```

示例输出有如下内容：
```sql
blocked_pid                           | 3526
blocked_user                          | test
blocked_client_addr                   | 128.xx.xx.xx
blocked_client_hostname               |
blocked_client_port                   | 20245
blocked_application_name              | psql
blocked_wait_event_type               | Lock
blocked_wait_event                    | transactionid
blocked_statement                     | UPDATE test_table SET name = 'test' WHERE id = 1;
blocking_pid                          | 37140
blocking_user                         | test
blocking_user_addr                    | 128.xx.xx.xx
blocking_client_hostname              |
blocking_client_port                  | 242458
blocking_application_name             | psql
blocking_wait_event_type              | Client
blocking_wait_event                   | ClientRead
current_statement_in_blocking_process | UPDATE tset_table SET name = 'test' WHERE id = 1;
```

使用 blocking_user_addr,blocking_user 和 blocking_client_port 可以帮助您确定哪些会话正在阻塞事务。
### 3. 终止事务
在您和业务方充评估完影响之后，您可以使用如下语句来终止事务，您需要将下面语句中的PID已换为第二步中的blocking_pid。
```sql
SELECT pg_terminate_backend(PID);
```

# 参考文档
[1] [https://www.postgresql.org/docs/11/monitoring-stats.html](https://www.postgresql.org/docs/11/monitoring-stats.html)
[2] [https://www.postgresql.org/docs/11/monitoring-locks.html](https://www.postgresql.org/docs/11/monitoring-locks.html)
**如果您有其他问题，欢迎您联系火山引擎**[技术支持服务](https://console.volcengine.com/ticket/createTicketV2/)