Row Locking 
Author Message
 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.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.***.com/



Fri, 05 Nov 2004 11:48:29 GMT
 Row Locking

Quote:

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



Fri, 05 Nov 2004 22:26:54 GMT
 Row Locking
...

Quote:
> 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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster



Sat, 06 Nov 2004 02:02:44 GMT
 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.


Quote:

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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html



Sat, 06 Nov 2004 05:52:46 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Row locking versus page locking

2. SYBASE page locks outperform ORACLE row locks

3. Row-locking instead of page-locking ?

4. Row lock automagically being escalated to Table Locks

5. Table/Row locking and the SELECT for BROWSE statement

6. Row locking and serializability

7. row locks and updates causing deadlocks

8. Yes! A row locking success

9. Rows Locked

10. PROBLEM WITH ROW LOCKING

11. PROBLEM WITH ROW LOCKING

12. SQL2000: Exclusive row locking.


 
Powered by phpBB® Forum Software