Обсуждение: process deadlocking on its own transactionid?

On Tue, Jul 23, 2013 at 12:54 PM, Kevin Goess <kgoess@bepress.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

Jeff Janes escribió:

> 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.

If you do CREATE INDEX CONCURRENTLY and it has to wait for other
processes to finish their current transactions (in order for them to
release their snapshots, which is what it needs to ensure the index can
be enabled), it will use the virtualxid.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Hi Kevin,

Il 23/07/2013 21:54, Kevin Goess ha scritto:
> 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.

You are seeing a 'blocked' (and not 'blocking') process, so you can
state about the expected lock mode of the blocked process but you can't
say anything about the applied lock mode. You have to compare two
(different) processes with same transaction id to compare blocked and
blocking processes.

Following  http://wiki.postgresql.org/wiki/Lock_Monitoring it is
possible to have a good locks monitoring. For instance, I create the
following query which lists all blocked processes, and respective
blocking processes with expected lock modes of blocking ones and blocked
ones.

SELECT
              bl.pid AS locked_pid, a.usename AS locked_user,
a.current_query AS locked_query, bl.virtualtransaction AS locked_vxid,
bl.transactionid AS locked_xid,
              kl.pid AS locking_pid, ka.usename AS locking_user,
ka.current_query AS locking_query, kl.virtualtransaction AS
locking_vxid, kl.transactionid AS locking_xid,
              bl.mode AS locked_expected_lock, kl.mode AS
locking_expected_lock
FROM
              pg_catalog.pg_locks bl JOIN pg_catalog.pg_stat_activity a
ON bl.pid = a.procpid
JOIN
              pg_catalog.pg_locks kl JOIN pg_catalog.pg_stat_activity ka
ON kl.pid = ka.procpid
ON
              bl.transactionid = kl.transactionid AND bl.pid != kl.pid
WHERE NOT
              bl.granted;

Hope it can help.

Giuseppe.

--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
giuseppe.broccolo@2ndQuadrant.it | www.2ndQuadrant.it