Ben Chobot <bench@xxxxxxxxxxxxxxx> writes: > We recently had an issue where a misbehaving application was running a long transaction that modified a bunch of rows, and this was holding up other transactions that wanted to do similar modifications. No surprising there. But what I'm unclear of is how this was showing up in pg_locks. The blocked transactions were all waiting on the transactionid of the long-running transaction, not any particular relation or tuple. Why doesn't pg_locks show the actual blockage? We don't try to record individual tuple locks in pg_locks (or more accurately, in the shared-memory data structure that pg_locks presents a view of), because it wouldn't be hard at all for applications to blow out the limited amount of space in shared memory if we did. Instead, this type of case is represented as you see, with the waiter(s) blocked on the XID of the transaction that's modified and not yet committed the row. The actual holder of the row lock is indicated in the tuple's on-disk state. In recent PG versions you can find out which tuple is at stake. I don't remember the exact details offhand, but there should be at least one waiting transaction (*not* the successful updater) that is holding a pg_locks tuple lock on the tuple in question. This will be released as soon as it's successfully locked the tuple on-disk. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
