Tom Lane對於locktype=transactionid的解釋

**Question:**
```
Tony Day <tonyd> writes:
> I have a process that is waiting for a lock and the locktype of the lock is
> "transactionid".
> Despite a fair bit of googling I have been unable to find more information
> on this type of lock.
```

**Answer:**
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.
在事務開始時，每個事務都持有自己transactionid的排他鎖，有些時候，某個事務會
等待其他事務結束，這時候該事務會嘗試獲取其他事務transactionid的sharelock。
當然，這時候已持有的排他鎖會阻塞sharelock的請求。

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.)
目前只有一種情況會嘗試在transactionid上請求sharelock：
某個事務希望更新、刪除或者SELECT FOR UPDATE/FOR SHARE某一個行，但改行已被其他
事務更新、刪除或SELECT FOR UPDATE/FOR SHARE
（為什麼不再pg_locks中顯示更清晰的行級鎖資訊？這是一個好問題，但其實你不需要關心：）

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.
                               regards, tom lane
如pg_stat_activity檢視所顯示的，我們可以打賭狀態為"idle in transaction"的事務
正處於修改了行但未提交的狀態,這時候需要迫切的讓該事務提交或者回滾,否則會阻塞其他
事務！

**參考資料**
[Re: What is locktype=transactionid?](https://www.postgresql.org/message-id/201.1274157039%40sss.pgh.pa.us)
[pg_locks](https://www.postgresql.org/docs/13/view-pg-locks.html)
[Explicit Locking](https://www.postgresql.org/docs/13/explicit-locking.html)
[When and how does Postgres use “transactionid” locks](https://stackoverflow.com/questions/62431485/when-and-how-does-postgres-use-transactionid-locks)</tonyd>

PostgreSQL DBA(180) - What is locktype=transactionid

相關文章