How to list all locked rows of a table?

Question

My application uses pessimistic locking. When a user opens the form for update a record, the application executes this query (table names are exemplary):

begin;
select * 
from master m
natural join detail d
where m.master_id = 123456
for update nowait;

The query locks one master row and several (to several dozen) detail rows. Transaction is open until a user confirms or cancels updates.

I need to know what rows (at least master rows) are locked. I have excavated the documentation and postgres wiki without success.

Is it possible to list all locked rows?

Answer 1

PostgreSQL 9.5 added a new option to FOR UPDATE that provides a straightforward way to do this.

SELECT master_id
FROM master
WHERE master_id NOT IN (
  SELECT master_id
  FROM master
  FOR UPDATE SKIP LOCKED);

This acquires locks on all the not-currently-locked rows, so think through whether that's a problem for you, especially if your table is large. If nothing else, you'll want to avoid doing this in an open transaction. If your table is huge you can apply additional WHERE conditions and step through it in chunks to avoid locking everything at once.

Answer 2

Is it possible? Probably yes, but it is the Greatest Mystery of Postgres. I think you would need to write your own extension for it (*).

However, there is an easy way to work around the problem. You can use very nice Postgres feature, advisory locks. Two arguments of the function pg_try_advisory_lock(key1 int, key2 int) you can interpret as: table oid (key1) and row id (key2). Then

select pg_try_advisory_lock(('master'::regclass)::integer, 123456)

locks row 123456 of table master, if it was not locked earlier. The function returns boolean.

After update the lock has to be freed:

select pg_advisory_unlock(('master'::regclass)::integer, 123456)

And the nicest thing, list of locked rows:

select classid::regclass, objid
from pg_locks
where locktype = 'advisory'

Advisory locks may be complementary to regular locks or you can use them independently. The second option is very temptive, as it can significantly simplify the code. But it should be applied with caution because you have to make sure that all updates (deletes) on the table in all applications are performed with this locking.

---

(*) Mr. Tatsuo Ishii did it (I did not know about it, have just found).