Finding open locks on Postgresql

February 26, 2004 12:09 PM

This is mostly for my benefit, since I'm debugging nasty locking problems at the moment, but here's the query you run to list open locks on Postgresql, joined to tell you the name of the table the lock is against.

select pg_class.relname,pg_locks.* from pg_class,pg_locks where pg_class.relfilenode=pg_locks.relation;

This gives you something like the following (some rows elided):

relnamerelationdatabasetransactionpidmodegranted
pg_class125983813.27461AccessShareLockt
pg_locks1675783813.27461AccessShareLockt
os_user_group9373083813.27471AccessExclusiveLockt
os_user9374683813.27471AccessExclusiveLockf
os_user9374683813.27423AccessShareLockt

As you can see here, process 27423 has a shared lock on the os_user table, probably due to some kind of read. Then process 27471 has asked for an exclusive lock, and since the former process is still holding the shared lock, that lock hasn't yet been granted.

The fact that this is causing a deadlock at a higher level, and 27471 will just wait indefinitely for a lock that isn't going to arrive isn't really the database's fault :)

Previously: A Little Harsh?

Next: On Brand Loyalty