Tom Lane对于locktype=transactionid的解释

Question:

Tony Day  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?
pg_locks
Explicit Locking
When and how does Postgres use “transactionid” locks