本节介绍了函数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,virtualtransaction,granted,fastpath from pg_locks where relation='tbl'::regclass; -[ RECORD 1 ]------+-------------------- pid | 1628 locktype | relation relation | tbl mode | RowExclusiveLock page | tuple | virtualxid | transactionid | virtualtransaction | 5/27 granted | t fastpath | t -[ RECORD 2 ]------+-------------------- pid | 1541 locktype | relation relation | tbl mode | RowExclusiveLock page | tuple | virtualxid | transactionid | virtualtransaction | 3/123 granted | t fastpath | t -[ RECORD 3 ]------+-------------------- pid | 1628 locktype | tuple relation | tbl mode | AccessExclusiveLock page | 0 tuple | 1 virtualxid | transactionid | virtualtransaction | 5/27 granted | t fastpath | f Time: 4.863 ms ``` 进程活动信息 ``` [local]:5432 pg12@testdb=# select * from pg_stat_activity where pid = 1541; -[ RECORD 1 ]----+------------------------------ datid | 16384 datname | testdb pid | 1541 usesysid | 10 usename | pg12 application_name | psql client_addr | client_hostname | client_port | -1 backend_start | 2019-08-14 10:46:54.422873+08 xact_start | 2019-08-14 12:06:14.357368+08 query_start | 2019-08-14 12:06:16.982161+08 state_change | 2019-08-14 12:06:16.983058+08 wait_event_type | Client wait_event | ClientRead state | idle in transaction backend_xid | 625 backend_xmin | query | delete from tbl where id = 1; backend_type | client backend Time: 8.979 ms ``` **参考资料** [How to check the lock level taken by operations in PostgreSQL](https://www.2ndquadrant.com/en/blog/how-to-check-the-lock-level-taken-by-operations-in-postgresql/)