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?

Accepted Answer

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

How to list all locked rows of a table?

Solution

Problem