Re: Row Locking

Ok, thanks Tom! It is now starting to make sense. So basicly,
LOCK TABLE table_name IN ROW EXCLUSIVE MODE
locks the entire table table_name and not any individual row.
It conflicts with
LOCK TABLE table_name IN ROW SHARE MODE
which does not conflict with other ROW SHARE locks.

Your explanation about the SELECT FOR UPDATE seems to answer Patrick's
question too. It basically acts like an UPDATE without an actual change in
the database meaning it grabs a ROW SHARE lock like the UPDATE does, performs
the SELECT like the SELECT does, and gives up the ROW SHARE lock like the
UPDATE does. This seems to indicate that Patrick needs to perform
LOCK TABLE locktable, other, tables IN ROW SHARE MODE
and then do a regular select.

Thanks for helping to straighten that out!
Alex.

On Monday 20 May 2002 10:24, you wrote:
> Alexander Klayman <aklayman(at)mindspring(dot)com> writes:
> > LOCK TABLE A IN ROW EXCLUSIVE MODE;
> > SELECT c FROM A WHERE id=1; # The row with d=1 is locked
>
> You seem to have a fundamental misconception about what sort of locking
> Postgres does.
>
> LOCK TABLE gets table-level locks of various kinds. The names for the
> lock types are fairly bizarre and unhelpful IMHO, but they are all
> *table* level, without exception; and there is no change in the behavior
> of other statements.
>
> The only sorts of row-level locks we use are those acquired by
> updating/deleting an existing row, or equivalently by SELECT FOR UPDATE
> (which doesn't change the row, but marks it as if it did). These
> locks do not prevent another transaction from reading the row with
> SELECT --- only from updating, deleting, or selecting it FOR UPDATE.
>
> All locks are held till transaction commit.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org