From: Tom Lane <tgl@sss.pgh.pa.us>
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>
Cc: Christoph Haller <ch@rodos.fzk.de>
Cc: lud_nowhere_man@yahoo.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Locking that will delayed a SELECT
Date: Fri, 18 Oct 2002 09:57:58 -0400
Message-ID: <17217.1034949478@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.LNX.4.44.0210181128130.14163-100000@matrix.gatewaynet.com>
References: <Pine.LNX.4.44.0210181128130.14163-100000@matrix.gatewaynet.com>
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
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: tgl@sss.pgh.pa.us, achill@matrix.gatewaynet.com, ch@rodos.fzk.de, lud_nowhere_man@yahoo.com
Subject: Re: Locking that will delayed a SELECT
In-Reply-To: <17217.1034949478@sss.pgh.pa.us>
* 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