我该如何确定是什么在阻止运行 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_user、blocking_user_addr 和 blocking_client_port 以帮助确定哪些会话正在阻止事务。
重要提示：在终止事务之前，评估每项事务可能对您的数据库和应用程序产生的影响。
4. 在对每项事务的可能影响进行评估后，通过运行以下查询停止事务：
SELECT pg_terminate_backend(PID);注意： 将 PID 替换成您在第 3 步中所确定进程的 blocking_pid。