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:19:14 +0300 (EEST)
Message-ID: <Pine.LNX.4.44.0210181056460.14163-100000@matrix.gatewaynet.com> (raw)
In-Reply-To: <3DAFB935.5E6980B1@rodos.fzk.de>
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
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 using the --to and --cc options:
reply via email
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.0210181056460.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