Re: process deadlocking on its own transactionid?

On Tue, Jul 23, 2013 at 12:54 PM, Kevin Goess <kgoess(at)bepress(dot)com> wrote:
> We're seeing a problem with some of our processes hanging on locks. The
> select below makes it look like it's *waiting* for a ShareLock on
> transactionid, but it *has* an ExclusiveLock on the same value in
> virtualxid.

It has an ExclusiveLock on itself, but that is independent of the
ShareLock it is waiting for.

The transaction it is waiting for is in the transactionid column,
which is not in your select list. The virtualxid column seems pretty
useless to me, I don't really know why it is there. Also, since you
are filtering for only the blocked pid, you will not see the blocking
pid in your results, which is probably what you really want to see.

> That makes it look like the process has deadlocked on its own transactionid.
> Or are we reading the results of this query wrong, and this is expected
> behavior, and our problem lies elsewhere?

You are reading the results wrong, which is very easy to do.

For this type of lock, you need to join the table to itself on the
transactionid column.

http://wiki.postgresql.org/wiki/Lock_Monitoring

Cheers,

Jeff