Thread: Locking that will delayed a SELECT
Hi: Suppose I have a transaction (T1) which executes a complicated stored procedure. While T1 is executing, trasaction #2 (T2) begins to execute. T1 take more time to execute that T2 in such a way that T2 finished earlier than T1. The result is that t2 returns set of data before it can be modified by T1. Given the above scenario. Is there a way such that while T2 will only read that value updated by T1 (i.e. T2 must wait until T1 is finished) ? What locks should I used since a portion of T1 contains SELECT statements? Should I used the "SERIALIZABLE isolation". Thank you in advance. ludwig. __________________________________________________ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com
> Suppose I have a transaction (T1) which executes a > complicated stored procedure. While T1 is executing, > trasaction #2 (T2) begins to execute. > > T1 take more time to execute that T2 in such a way > that T2 finished earlier than T1. The result is that > t2 returns set of data before it can be modified by > T1. > > Given the above scenario. Is there a way such that > while T2 will only read that value updated by T1 (i.e. > T2 must wait until T1 is finished) ? What locks should > I used since a portion of T1 contains SELECT > statements? Should I used the "SERIALIZABLE > isolation". What's wrong about this question? I'm interested in an answer, too. Regards, Christoph
On Fri, 18 Oct 2002, Christoph Haller wrote: > > Suppose I have a transaction (T1) which executes a > > complicated stored procedure. While T1 is executing, > > trasaction #2 (T2) begins to execute. > > > > T1 take more time to execute that T2 in such a way > > that T2 finished earlier than T1. The result is that > > t2 returns set of data before it can be modified by > > T1. > > > > Given the above scenario. Is there a way such that > > while T2 will only read that value updated by T1 (i.e. > > T2 must wait until T1 is finished) ? What locks should > > I used since a portion of T1 contains SELECT > > statements? Should I used the "SERIALIZABLE > > isolation". > > What's wrong about this question? > I'm interested in an answer, too. > > Regards, Christoph Second small xaction T2's select statemenst will use values commited before these select statements started. That is, these queries will NOT see values updated by T1. The problem is solved a) Using SERIALIZABLE XACTION ISOLATION LEVEL b) in T2 using "select for update" instead of select. That way T2's queries will wait untill T1's statements commit or rollback. The SERIALIZABLE XACTION ISOLATION LEVEL scheme is heavier and thus maybe less efficient. See http://www.postgresql.org/idocs/index.php?mvcc.html ================================================================== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel: +30-10-8981112 fax: +30-10-8981877 email: achill@matrix.gatewaynet.com mantzios@softlab.ece.ntua.gr
On Fri, 18 Oct 2002, Achilleus Mantzios wrote: > Second small xaction T2's select statemenst will use values commited > before these select statements started. That is, these queries > will NOT see values updated by T1. > > The problem is solved > > a) Using SERIALIZABLE XACTION ISOLATION LEVEL > b) in T2 using "select for update" instead of select. That way T2's > queries will wait untill T1's statements commit or rollback. > > The SERIALIZABLE XACTION ISOLATION LEVEL scheme is heavier > and thus maybe less efficient. Also the serialization must be secured from the application side. In your case the program invoking T2 must be prepared to retry the transaction if T1 commits in the meantime. With SERIALIZABLE XACTION ISOLATION LEVEL T2's select statements will use values commited before T2 began (and not before these select statements began as in the READ COMMITED (default) XACTION ISOLATION LEVEL case) ================================================================== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel: +30-10-8981112 fax: +30-10-8981877 email: achill@matrix.gatewaynet.com mantzios@softlab.ece.ntua.gr
Achilleus Mantzios <achill@matrix.gatewaynet.com> writes: >> The problem is solved >> >> a) Using SERIALIZABLE XACTION ISOLATION LEVEL >> b) in T2 using "select for update" instead of select. That way T2's >> queries will wait untill T1's statements commit or rollback. ISTM that SERIALIZABLE mode will not solve this problem, since by definition you want T2 to see results committed after T2 has started. A simple answer is to have T1 grab an ACCESS EXCLUSIVE lock on some table to block T2's progress. If that locks out third-party transactions that you'd rather would go through, you can probably use a lesser form of lock --- but then both T1 and T2 will have to cooperate since each will need to explicitly take a lock. I gave a presentation at the O'Reilly conference this year that covered some of these issues. Looks like you can still get the slides from http://conferences.oreillynet.com/cs/os2002/view/e_sess/2681 regards, tom lane
--- Tom Lane <tgl@sss.pgh.pa.us> wrote: > Achilleus Mantzios <achill@matrix.gatewaynet.com> > writes: > >> The problem is solved > >> > >> a) Using SERIALIZABLE XACTION ISOLATION LEVEL > >> b) in T2 using "select for update" instead of > select. That way T2's > >> queries will wait untill T1's statements commit > or rollback. > > ISTM that SERIALIZABLE mode will not solve this > problem, since by > definition you want T2 to see results committed > after T2 has started. > > A simple answer is to have T1 grab an ACCESS > EXCLUSIVE lock on some > table to block T2's progress. If that locks out > third-party > transactions that you'd rather would go through, you > can probably use > a lesser form of lock --- but then both T1 and T2 > will have to cooperate > since each will need to explicitly take a lock. > If I will be using ACCESS EXCLUSIVE lock, should I should SELECT statement only in T1 instead SELECT...FOR UPDATE statement since SELECT...FOR UPDATE uses ROW SHARE MODE lock since the ACCESS EXCLUSIVE lock is in conflict with other lock mode (besides it is pointless to use other locks when using ACCESS EXCLUSIVE lock) ? *** For clarification *** In the SQL command reference of PostgreSQL: in SELECT statement section : "The FOR UPDATE clause allows the SELECT statement to perform exclusive locking of selected rows" in LOCK statement section : "ROW SHARE MODE Note: Automatically acquired by SELECT ... FOR UPDATE." - Isn't this two statements somewhat conflicting? Is the PostgreSQL meaning of SHARE lock and EXCLUSIVE LOCK similar to the definition of Elmasri/Navathe in the book "Fundamentals of Database Systems" where a SHARE lock is a "read lock", while an EXCLUSIVE lock is a "write lock"? Thank you in advance. ludwig. __________________________________________________ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com
Ludwig Lim <lud_nowhere_man@yahoo.com> writes: > *** For clarification *** > In the SQL command reference of PostgreSQL: > in SELECT statement section : > "The FOR UPDATE clause allows the SELECT > statement to perform exclusive locking of selected > rows" Hmm. That is a misstatement: FOR UPDATE only locks the selected row(s) against other updates (ie UPDATE, DELETE, SELECT FOR UPDATE), so it's not "exclusive" in the usual sense of the word: readers can still see the row. I'll fix that for 7.3, but meanwhile you might care to read the 7.3 development docs' discussion of concurrency, which is (IMHO anyway) more accurate than what was there before: http://developer.postgresql.org/docs/postgres/mvcc.html Note in particular that table-level locks and row-level locks are two independent features. Updates acquire an appropriate table-level lock and then acquire row locks on the rows they are updating. regards, tom lane
--- Tom Lane <tgl@sss.pgh.pa.us> wrote: > A simple answer is to have T1 grab an ACCESS > EXCLUSIVE lock on some > table to block T2's progress. If that locks out > third-party > transactions that you'd rather would go through, you > can probably use > a lesser form of lock --- but then both T1 and T2 > will have to cooperate > since each will need to explicitly take a lock. - Is there a possibility of having a lock that similar to a row level ACCESS EXCLUSIVE (i.e. ROW ACCESS EXCLUSIVE lock) in the future release of PostgreSQL? The ACCESS EXCLUSIVE lock also locks the rows not used in T1, making concurrent transactions almost impossible. regards, ludwig __________________________________________________ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com