understanding pg_locks
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? Isn't that what MVCC is good for?
Ben Chobot <bench@silentmedia.com> 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
On May 21, 2011, at 8:53 AM, Tom Lane wrote:
Ben Chobot <bench@silentmedia.com> 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.
Ah, that makes sense. But then, when pg_locks does show specific tuple and relation locks, how does this differ from that?