Re: process deadlocking on its own transactionid?

From: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
To: Kevin Goess <kgoess(at)bepress(dot)com>
Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: process deadlocking on its own transactionid?
Date: 2013-07-23 21:36:51
Message-ID: CAMkU=1y0th7v_ExyuujejKDeehV3deYJLJ0CLqx422GKJbUQrg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alvaro Herrera 2013-07-23 21:41:20 Re: process deadlocking on its own transactionid?
Previous Message Jeff Janes 2013-07-23 21:02:53 Re: maintenance_work_mem and CREATE INDEX time