怎样才能确定是什么阻止对运行 Amazon RDS PostgreSQL 或 Aurora PostgreSQL 的数据库实例的查询？

解决方法

未提交的事务可能会导致新查询被阻止、进入休眠状态，并在超过锁定等待超时或语句超时时时失败。要解决这个问题，请确定并停止阻止查询的事务。

1.    根据 pg_stat_activity 视图运行以下查询，以确定被阻止事务的当前状态：

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

**注意：**将 TABLE NAME 替换为您的表名称或条件。

如果 wait_event_type 列的值为 Lock，则查询会被其他事务或查询阻止。如果 wait_event_type 列是任何其他值，则 CPU、存储或网络容量等资源会存在性能瓶颈。要解决性能瓶颈，请调整您的数据库的性能。例如，您可以添加索引、重写查询或运行 vacuum 和 analyze 命令。有关详细信息，请参阅使用 PostgreSQL 的最佳实践。

如果您启用了 Performance Insights，请查看按等待事件、主机、SQL 查询或用户分组的数据库负载，以确定被阻止的事务。有关详细信息，请参阅在 Amazon RDS 上使用 Performance Insights 监控数据库负载。

2.    如果 wait_event_type 列的值为 Lock，则运行以下命令，以确定事务被阻止的原因：

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.    查看带 blocking 前缀的列。在下面的示例表中，您可以看到被阻止事务在 27.0.3.146 主机上运行并使用 psql。使用 blocking_user、blocking_user_addr 和 blocking_client_port 帮助确定阻止事务的会话。

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;

**重要事项：**在终止事务之前，评估每个事务对数据库和应用程序状态的潜在影响。

4.    运行以下查询以停止事务：

SELECT pg_terminate_backend(PID);

**注意：**将 PID 替换为您在上一步中确定的进程的 blocking_pid。

相关信息

有关查看锁定的 PostgreSQL 文档

有关服务器信令函数的 PostgreSQL 文档

有关 wait_event 描述的 PostgreSQL 文档

有关锁定监控的 PostgreSQL Wiki

Amazon Aurora PostgreSQL 等待事件