Обсуждение: Row Locking

I have a few basic question and either need an affirmation or correction.
Thank you very much for help in advance.

When using Row locks, when does the lock start, which rows does it lock and
when does it end?  I was unable to find this info in documentation (maybe
because it is supposed to be implicit).  I have the following idea:

BEGIN;
LOCK TABLE A IN ROW EXCLUSIVE MODE;
SELECT c FROM A WHERE id=1;   # The row with d=1 is locked
if c then
  DELETE FROM A WHEE id=1;
COMMIT;                            # The row (if remaining) is unlocked


Also, could I do something like this:

BEGIN;
LOCK TABLE A IN ROW EXCLUSIVE MODE;
cursor = SELECT c, id FROM A WHERE d=1;
while cursor.fetchone() # Next row is locked, previous row
  if c then                    #  (if remaining) is unlocked
    DELETE FROM A WHERE id=id;

COMMIT;

Thanks.

Alexander Klayman <aklayman@mindspring.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

On Mon, May 20, 2002 at 10:24:06AM -0400, Tom Lane wrote:
...
> 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.

I'm trying to use select for update with libpq++. Is this possible as each
Exec seems to have its own transaction. I would like to

begin;
select 1
  from locktable, other, tables
 where locktable.id="<<lock<<"
   and the_rest

db.ExecTuplesOk   <===== I think this terminates the "begin" => no more lock..

if db.Tuples != 1 throw exeception("someone else edited your row")

update tables set x=1,y=2
 where tables.id=locktable.tables
   and locktable.id="<<lock<<"
delete from locktable where id="<<lock<<"
end;              <===== I would like the transaction to end here!
db.ExecCommandOk


I think each Exec has its own transaction because

DEBUG:  StartTransactionCommand
DEBUG:  query: begin;select 1...
DEBUG:  ProcessUtility: begin;select 1...
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  ProcessQuery
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  query: update...;end;
DEBUG:  ProcessQuery
DEBUG:  ProcessQuery
DEBUG:  ProcessQuery
DEBUG:  ProcessUtility: update...;end;
... refint select 1 for updates...
DEBUG:  CommitTransactionCommand

or does "CommitTransactionCommand" not imply an "end;"?

Cheers,

Patrick

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@mindspring.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@postgresql.org