我该如何确定是什么在阻止运行 Amazon RDS PostgreSQL 或 Aurora PostgreSQL 的数据库实例上的查询?

上次更新时间:2019 年 11 月 7 日

我尝试在运行 Amazon Relational Database Service (Amazon RDS) PostgreSQL 或 Amazon 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 和分析。有关更多信息,请见 PostgreSQL 使用最佳实践

如果已在您的数据库实例上启用性能详情,您还可以通过查看按等待事件、主机、SQL 查询或用户分组的数据库负载来确定被阻止的事务。有关更多信息,请参阅使用 Amazon RDS 性能详情

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.    检查具有阻止前缀的列。在由此查询生成的下列示例表中,您可以查看在 27.0.3.146 主机上运行,而且使用 psql 的被阻止事务:

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;

提示:使用 blocking_userblocking_user_addrblocking_client_port 以帮助确定哪些会话正在阻止事务。

重要提示:在终止事务之前,评估每项事务可能对您的数据库和应用程序产生的影响。

4.    在对每项事务的可能影响进行评估后,通过运行以下查询停止事务:

SELECT pg_terminate_backend(PID);

注意: PID 替换成您在第 3 步中所确定进程的 blocking_pid


查看锁的 PostgreSQL 文档

服务器信令函数的 PostgreSQL 文档

wait_event 描述的 PostgreSQL 文档

锁监控的 PostgreSQL Wiki

Amazon Aurora PostgreSQL 事件

这篇文章对您有帮助吗?

我们可以改进什么?


需要更多帮助吗?