Thread: Lock contention, docs vs. reality

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

Roland Turner <raz.cbfgterfdy.bet@raz.cx> writes:
> 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.

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

A moment's examination of the source code (see the conflict tables in
src/backend/storage/lmgr/lock.c) shows that the documentation claim is
correct.  Please provide your experiment to the contrary.

            regards, tom lane

On Sun, 22 Apr 2007, Roland Turner wrote:

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

The list in question revolves around table-level locks. Those statements
mentioned also take out locks on affected rows. You should be able select
for update one row while updating a different row in the same table.