Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 1718C475B8E for ; Fri, 18 Oct 2002 04:06:22 -0400 (EDT) Received: from matrix.gatewaynet.com (unknown [217.19.69.50]) by postgresql.org (Postfix) with ESMTP id 044A1475B8D for ; Fri, 18 Oct 2002 04:06:20 -0400 (EDT) Received: from localhost (achill@localhost) by matrix.gatewaynet.com (8.11.6/8.11.6) with ESMTP id g9I8JEd14515; Fri, 18 Oct 2002 11:19:14 +0300 Date: Fri, 18 Oct 2002 11:19:14 +0300 (EEST) From: Achilleus Mantzios To: Christoph Haller Cc: lud_nowhere_man@yahoo.com, Subject: Re: Locking that will delayed a SELECT In-Reply-To: <3DAFB935.5E6980B1@rodos.fzk.de> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200210/206 X-Sequence-Number: 9923 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