本文簡單介紹了PostgreSQL Locks(locktype：transactionid)的基礎知識.
PostgreSQL系統表pg_locks中的locktype,有一種稱為transactionid的鎖型別,PG文件的說法是:
>Every transaction holds an exclusive lock on its virtual transaction ID for its entire duration. If a permanent ID is assigned to the transaction (which normally happens only if the transaction changes the state of the database), it also holds an exclusive lock on its permanent transaction ID until it ends. When one transaction finds it necessary to wait specifically for another transaction, it does so by attempting to acquire share lock on the other transaction ID (either virtual or permanent ID depending on the situation). That will succeed only when the other transaction terminates and releases its locks.

>In Postgres, every transaction takes an exclusive lock on its own transactionid when it starts.  Sometimes, when a transaction wants to wait for another transaction to complete, it'll try to take share lock on that other transaction's id.This will of course block until the exclusive lock goes away.

>Currently, the only case where anything will try to take a sharelock on transaction id is when it is blocking on a row-level lock as a result of trying to modify or delete or SELECT FOR UPDATE/FOR SHARE a row that the
other transaction already modified or deleted or selected FOR UPDATE/SHARE.  (Why this doesn't show up as a more obvious row-level lock in pg_locks is an interesting technical detail, but you probably don't care that much about that.)Given what you're showing in pg_stat_activity, the most likely bet is that the "idle in transaction" client is sitting on an uncommitted row modification.  You need to whack it upside the head and convince it to commit or abort its modifications a bit more promptly.  The dependency could be a bit indirect --- for instance, modifying a row that is linked by a foreign key dependency to the one the second transaction wants to change --- but it's a very general rule that sitting on uncommitted modifications for any length of time is Bad Behavior.

如下例所示,session 1執行update語句,session 2 update相同的rows,session 3查詢locktype為transactionid的資訊.
session 1
```
[local]:5432 pg12@testdb=# begin;
ere relation=295053;
BEGIN
Time: 1.430 ms
[local]:5432 pg12@testdb=#* -- SELECT * from t_lock where id < 10 FOR UPDATE;
[local]:5432 pg12@testdb=#* select pg_backend_pid();
 pg_backend_pid 
----------------
           2475
(1 row)

Time: 9.013 ms
```
session 2
```
[local]:5432 pg12@testdb=# ---- session 2
[local]:5432 pg12@testdb=# begin;
BEGIN
Time: 1.117 ms
[local]:5432 pg12@testdb=#* select pg_backend_pid();
 pg_backend_pid 
----------------
           2480
(1 row)

Time: 1.825 ms
[local]:5432 pg12@testdb=#* update t_lock set id = 3000 where id = 3;
-- 阻塞/掛起
```

Time: 1.243 ms
```
可以看到,程式2475中的事務669310和程式2480中的669312分別持有transactionid的ExclusiveLock,程式2480在等待事務ID=669310的lock(granted=f).
為什麼會等待669310的ShareLock呢?回過頭來檢視t_lock表的xmax資訊:
```
[local]:5432 pg12@testdb=# select xmin,xmax,ctid from t_lock where id = 3;
  xmin  |  xmax  |  ctid   
--------+--------+---------
 669246 | 669310 | (0,3)
 669247 | 669310 | (4,99)
 669248 | 669310 | (8,195)
 669252 | 669310 | (13,65)
(4 rows)

Time: 4.715 ms
```
可以看到 : 待更新的tuple.xmax = 669310.
回滾事務669310,再次檢視xmax:
```
[local]:5432 pg12@testdb=# select xmin,xmax,ctid from t_lock where id = 3;
  xmin  |  xmax  |  ctid   
--------+--------+---------
 669246 | 669312 | (0,3)
 669247 | 669312 | (4,99)
 669248 | 669312 | (8,195)
 669252 | 669312 | (13,65)
(4 rows)

Time: 1.182 ms

Time: 5.434 ms
```
xmax被更新為669312.

**參考資料**
[pg_locks](https://www.postgresql.org/docs/12/view-pg-locks.html)
[PostgreSQL locking, Part 1: Row Locks
](https://www.percona.com/blog/2018/10/16/postgresql-locking-part-1-row-locks/)

PostgreSQL DBA(75) - Locks(locktype：transactionid)：What You Should Know

相關文章