pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Achilleus Mantzios <achill@matrix.gatewaynet.com>
To: Christoph Haller <ch@rodos.fzk.de>
Cc: lud_nowhere_man@yahoo.com, <pgsql-sql@postgresql.org>
Subject: Re: Locking that will delayed a SELECT
Date: Fri, 18 Oct 2002 11:32:51 +0300 (EEST)
Message-ID: <Pine.LNX.4.44.0210181128130.14163-100000@matrix.gatewaynet.com> (raw)
In-Reply-To: <Pine.LNX.4.44.0210181056460.14163-100000@matrix.gatewaynet.com>

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




view thread (8+ messages)  latest in thread

Message-ID: <Pine.LNX.4.44.0210181128130.14163-100000@matrix.gatewaynet.com>
Permalink:  ../Pine.LNX.4.44.0210181128130.14163-100000@matrix.gatewaynet.com/
Also on:    postgresql.org/message-id/Pine.LNX.4.44.0210181128130.14163-100000@matrix.gatewaynet.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: achill@matrix.gatewaynet.com, ch@rodos.fzk.de
  Subject: Re: Locking that will delayed a SELECT
  In-Reply-To: <Pine.LNX.4.44.0210181128130.14163-100000@matrix.gatewaynet.com>
  References: <Pine.LNX.4.44.0210181056460.14163-100000@matrix.gatewaynet.com> <Pine.LNX.4.44.0210181128130.14163-100000@matrix.gatewaynet.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox