问题描述
尝试在RDS PostgreSQL实例上运行查询,发现查询语句长时间未返回结果,语句执行被阻塞。我该如何排查并解决这个问题?
问题分析
通常情况下,查询被阻塞是由于其他未提交的事务所导致的。当锁等待超时后,查询语句将返回失败。大量的阻塞会导致应用与数据库性能下降,应该引起我们的足够重视。
解决方案
1. 通过对pg_stat_activity [1]定位被阻塞的事务的当前状态
test=# SELECT * FROM pg_stat_activity WHERE wait_event_type ='Lock'
wait_event_type =Lock [2]意味着查询被其他事务阻塞。
2. 通过如下命令确定事务被阻塞的具体原因
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;
示例输出有如下内容:
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。
SELECT pg_terminate_backend(PID);
参考文档
[1] https://www.postgresql.org/docs/11/monitoring-stats.html
[2] https://www.postgresql.org/docs/11/monitoring-locks.html
如果您有其他问题,欢迎您联系火山引擎技术支持服务