Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id C523D475EE2 for ; Fri, 18 Oct 2002 04:19:57 -0400 (EDT) Received: from matrix.gatewaynet.com (unknown [217.19.69.50]) by postgresql.org (Postfix) with ESMTP id D0F4B475D84 for ; Fri, 18 Oct 2002 04:19:55 -0400 (EDT) Received: from localhost (achill@localhost) by matrix.gatewaynet.com (8.11.6/8.11.6) with ESMTP id g9I8Wp314923; Fri, 18 Oct 2002 11:32:51 +0300 Date: Fri, 18 Oct 2002 11:32:51 +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: Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200210/207 X-Sequence-Number: 9924 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