PostgreSQL DBA(79) - Locks(pg_blocking_pids)

husthxd發表於2019-08-15

本節介紹了函式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/)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/6906/viewspace-2653765/，如需轉載，請註明出處，否則將追究法律責任。

相關文章

PostgreSQL DBA(158) - Locks(tuple locks)
2020-01-14
SQL
PostgreSQL DBA(85) - Locks(lightweight locks)
2019-08-23
SQL
PostgreSQL DBA(77) - Locks(Lock Monitoring)
2019-08-14
SQL
PostgreSQL DBA(81) - Locks(FOR UPDATE SKIP LOCKED)
2019-08-19
SQL
PostgreSQL DBA(76) - Locks(Advisory Locks)：What You Should Know
2019-08-13
SQL
PostgreSQL DBA(78) - Locks(When Postgres blocks 7 tips for dealing with locks)
2019-08-14
SQLBloC
PostgreSQL DBA(157) - pgAdmin（OOM & max_locks_per_transaction ）
2020-01-10
SQLOOM
PostgreSQL DBA(71) - Locks(Table-Level)：What You Should Know
2019-08-08
SQL
PostgreSQL DBA(74) - Locks(Row-Level)：What You Should Know
2019-08-12
SQL
PostgreSQL DBA(75) - Locks(locktype：transactionid)：What You Should Know
2019-08-13
SQL
PostgreSQL 原始碼解讀（238）- Locks(OOM & max_locks_per_transaction)
2020-01-14
SQL原始碼OOM
PostgreSQL 原始碼解讀（239）- Locks(OOM & max_locks_per_transaction#2)
2020-01-15
SQL原始碼OOM
PostgreSQL DBA(45) - Hypothetical Indexes in PostgreSQL
2019-06-27
SQLIndex
PostgreSQL 原始碼解讀（219）- Locks(Overview)
2019-08-28
SQL原始碼View
PostgreSQL DBA(35) - CTE
2019-06-03
SQL
PostgreSQL DBA(42) - locale
2019-06-25
SQL
PostgreSQL 原始碼解讀（221）- Locks(PROCLOCK Struct)
2019-09-03
SQL原始碼Struct
PostgreSQL DBA(133) - Extension(postgresql_anonymizer)
2019-11-19
SQL
PostgreSQL 原始碼解讀（220）- Locks(LOCK Struct)
2019-09-02
SQL原始碼Struct
PostgreSQL DBA(59) - Index(Bloom)
2019-07-18
SQLIndexOOM
PostgreSQL DBA(53) - Index(BRIN)
2019-07-11
SQLIndex
PostgreSQL DBA(58) - DBLink
2019-07-18
SQL
PostgreSQL DBA(48) - Index(GiST)
2019-07-04
SQLIndex
PostgreSQL DBA(51) - Index(GIN)
2019-07-09
SQLIndex
PostgreSQL DBA(52) - Index(RUM)
2019-07-10
SQLIndex
PostgreSQL DBA(47) - Index(Btree)
2019-07-02
SQLIndex
PostgreSQL DBA(43) - Index(Hash)
2019-06-26
SQLIndex
PostgreSQL DBA(191) - Collation
2022-04-30
SQL
PostgreSQL DBA(182) - HOT
2021-05-24
SQL
PostgreSQL DBA(185) - watch
2021-09-17
SQL
PostgreSQL DBA(181) - Using PostgreSQL as a Data Warehouse
2021-05-20
SQL
PostgreSQL 原始碼解讀（224）- Locks(The Deadlock Detection Algorithm)
2019-09-09
SQL原始碼Go
PostgreSQL DBA(175) - Cost EST（SeqScan）
2020-06-17
SQL
PostgreSQL DBA(150) - Extension(pgmetrics)
2019-12-19
SQL
PostgreSQL DBA(91) - PG upgrade
2019-09-12
SQL
PostgreSQL DBA(73) - dump函式
2019-08-12
SQL函式
PostgreSQL DBA(86) - Table Parameter(fillfactor)
2019-08-27
SQL
PostgreSQL DBA(87) - benchmarksql簡介
2019-08-28
SQL