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.
Show quoted text
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 lockedYou 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