Re: process deadlocking on its own transactionid?

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