Lock contention, docs vs. reality

From: Roland Turner <raz(dot)cbfgterfdy(dot)bet(at)raz(dot)cx>
To: pgsql-general(at)postgresql(dot)org
Subject: Lock contention, docs vs. reality
Date: 2007-04-22 14:42:01
Message-ID: 1177252921.4124.7.camel@neb
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michael Schmidt 2007-04-22 15:37:03 Help with pgFoundry
Previous Message A. Kretschmer 2007-04-22 14:36:00 Re: understanding output from Explain