Thread: Locking confusion

My apologies for what may be an obvious answer to others but the documentation
(for v8.1) leaves me concerned (enough not to trust "expected behaviour" of
some commands).

Essentially I was thinking of using PERFORM (SELECT) ... FOR UPDATE within
PL/pgSQL - all indications are that this should be sufficient to prevent other
concurrent transactions from modifying selected rows until the current
transaction completes:

  "This prevents them from being modified or deleted by other transactions
  until the current transaction ends. That is, other transactions that
  attempt UPDATE, DELETE, or SELECT FOR UPDATE of these rows will be blocked
  until the current transaction ends."

See:

http://www.postgresql.org/docs/8.1/interactive/sql-select.html#SQL-FOR-UPDATE-SHARE

Okay so far - until you read this:

  "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) [...]

  "ROW EXCLUSIVE
    Conflicts with the SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS
    EXCLUSIVE lock modes.
    The commands UPDATE, DELETE, and INSERT acquire this lock mode on the
    target table [...]"

See:

http://www.postgresql.org/docs/8.1/interactive/explicit-locking.html#LOCKING-TABLES

The thing that worries me is that according to the documentation, "ROW SHARE"
and "ROW EXCLUSIVE" lock modes don't conflict which means (logical
progression, not considering actual behaviour) an UPDATE, DELETE or INSERT
could change data in an underlying table, despite another transaction already
having executed "SELECT ... FOR UPDATE".

Is the explicit locking documentation page just missing something or have I
completely misunderstood either the behaviour of "SELECT ... FOR UPDATE" or
the interaction of the various locking modes?

The workaround for the moment is just to explicitly LOCK the table but I'd
rather remove explicit locking statements if possible.

Thanks..

David J N Begley <d.begley@uws.edu.au> writes:
> My apologies for what may be an obvious answer to others but the documentation
> (for v8.1) leaves me concerned (enough not to trust "expected behaviour" of
> some commands).

You're confusing table-level locks with row-level locks.  They are
entirely independent beasts.  The names of the table-level lock modes
are rather unfortunately chosen, I think.

            regards, tom lane

On Tue, 2 May 2006, Tom Lane wrote:

> David J N Begley <d.begley@uws.edu.au> writes:
> > My apologies for what may be an obvious answer to others but the documentation
> > (for v8.1) leaves me concerned (enough not to trust "expected behaviour" of
> > some commands).
>
> You're confusing table-level locks with row-level locks.  They are

D'oh!  Comes from trying to read/understand this stuff without enough sleep.
Thanks for the reminder.