Tom Lane对于locktype=transactionid的解释 **Question:** ``` Tony Day <tonyd(at)panztel(dot)com> 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)