Обсуждение: 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