Thread: process deadlocking on its own transactionid?
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.
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? (Yes, the process is doing a "select for update" on this context_objects table according to pg_stat_activity)
production=> select locktype, database, relname, relation, virtualxid, virtualtransaction, pid, mode, granted
from pg_locks left outer join pg_class on pg_class.oid = pg_locks.relation
where pid = 2288;
locktype | database | relname | relation | virtualxid | virtualtransaction | pid | mode | granted
---------------+----------+----------------------+----------+--------------+--------------------+------+-----------------+---------
relation | 41194 | context_objects_pkey | 95318843 | | 123/45694692 | 2288 | AccessShareLock | t
relation | 41194 | context_objects | 41553 | | 123/45694692 | 2288 | RowShareLock | t
virtualxid | | | | 123/45694692 | 123/45694692 | 2288 | ExclusiveLock | t
transactionid | | | | | 123/45694692 | 2288 | ShareLock | f
tuple | 41194 | context_objects | 41553 | | 123/45694692 | 2288 | ExclusiveLock | t
(5 rows)
```
Kevin M. Goess
Software Engineer
Berkeley Electronic Press
kgoess@bepress.com
510-665-1200 x179
www.bepress.com
bepress: sustainable scholarly publishing
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