I'm working with 7.4, but the 8.2 docs[1] have the same apparent error:
ROW SHARE
Conflicts with the EXCLUSIVE and ACCESS EXCLUSIVE lock modes.
The SELECT FOR UPDATE and SELECT FOR SHARE commands acquire a
lock of this mode on the target table(s) (in addition to ACCESS
SHARE locks on any other tables that are referenced but not
selected FOR UPDATE/FOR SHARE).
If that conflict list were correct, then ROW SHARE wouldn't conflict
with itself, much less with ROW EXCLUSIVE (required to prevent
INSERT/UPDATE/DELETE); commonsense dictates that it should conflict with
both, and experiment demonstrates that it actually does so.
My actual problem (what I'm trying to solve) is deadlock prevention and
that, even in READ ONLY transactions, "SELECT FOR UPDATE" (generated by
an underlying O/R mapper, beyond my effective control) is actually
granted locks (and therefore READ ONLY transactions are contributing to
deadlocks!), at least until or unless an attempt is made by the read
only transaction to perform an actual UPDATE, at which time all locks
are lost, presumably because the transaction is now dead. In order to
make progress on deadlock prevention I need accurate information about
lock contention rules, which brings me to the apparent documentation
error above.
Can anyone either draw my attention to an error in my reasoning or
provide correct information about which lock types conflict with which?
Thanks.
- Raz
1:
http://www.postgresql.org/docs/8.2/static/explicit-locking.html#LOCKING-TABLES