PostgreSQL DBA(79) - Locks(pg_blocking_pids)

本节介绍了函数pg_blocking_pids,该函数用于获取哪些进程(输出参数)阻塞了某个进程(输入参数).

我们在执行某些操作时,console可能会挂起没有输出,这时候你没有办法判断是因为执行很慢还是因为被阻塞了,通过pg_blocking_pids可以判断是否存在阻塞.

----------- session 1
[local]:5432 pg12@testdb=# begin;
BEGIN
Time: 2.877 ms
[local]:5432 pg12@testdb=#* delete from tbl where id = 1;
DELETE 1
Time: 1.113 ms
----------- session 2
[local]:5432 pg12@testdb=# begin;
BEGIN
Time: 1.700 ms
[local]:5432 pg12@testdb=#* delete from tbl where id = 1;
----------- session 3
[local]:5432 pg12@testdb=# select * from pg_blocking_pids(1628);
 pg_blocking_pids 
------------------
 {1541}
(1 row)
Time: 1.838 ms
[local]:5432 pg12@testdb=#

通过查询可发现阻塞pid = 1628的进程1541.这时候可以查询pg_locks和进程活动信息获取更详细的信息

[local]:5432 pg12@testdb=# select pid,locktype,relation::regclass,mode,page,tuple,virtualxid,transactionid,virtualtransactio