I am trying to emulate a pessimistic locking system you would find in an
old school database file system, for example cobol. Generally, when a
cobol program tries to read a record that is locked by somebody else,
the read fails and either a message is displayed by the user or a error
handling procedure is executed. I would like to emulate this behavior
for legacy code while using mvcc for newer procedures I write.
4 questions:
1. Can you query if a tuple is locked by another transaction (the
documentation unclearly suggests this can't be done via the pg_lock
view) before executing select for update...?
2. If so, is this reasonable efficient to do, i.e. straight join on
oid/xid?
3. If so, is this possible to query without a race condition regarding
the lock status?
4. If so, is this likely to be possible in future versions of postgres
without non-trivial changes?
In other words, if User B attempts to select for update a record that
user A has selected for update, it would be nice if User B's query would
fail with a NOTICE to act upon.
Thanks in advance,
Merlin