Обсуждение: understanding pg_locks
We recently had an issue where a misbehaving application was running a long transaction that modified a bunch of rows, andthis was holding up other transactions that wanted to do similar modifications. No surprising there. But what I'm unclearof is how this was showing up in pg_locks. The blocked transactions were all waiting on the transactionid of the long-runningtransaction, not any particular relation or tuple. Why doesn't pg_locks show the actual blockage? Isn't thatwhat 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'munclear of is how this was showing up in pg_locks. The blocked transactions were all waiting on the transactionid of thelong-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'munclear of is how this was showing up in pg_locks. The blocked transactions were all waiting on the transactionid of thelong-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?