From: Ludwig Lim <lud_nowhere_man@yahoo.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: PostgreSQL Mailing List <pgsql-sql@postgresql.org>
Subject: Re: Locking that will delayed a SELECT
Date: Fri, 18 Oct 2002 08:02:34 -0700 (PDT)
Message-ID: <20021018150234.23205.qmail@web80305.mail.yahoo.com> (raw)
In-Reply-To: <17217.1034949478@sss.pgh.pa.us>
--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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.
>
If I will be using ACCESS EXCLUSIVE lock, should I
should SELECT statement only in T1 instead
SELECT...FOR UPDATE statement since SELECT...FOR
UPDATE uses ROW SHARE MODE lock since the ACCESS
EXCLUSIVE lock is in conflict with other lock mode
(besides it is pointless to use other locks when using
ACCESS EXCLUSIVE lock) ?
*** For clarification ***
In the SQL command reference of PostgreSQL:
in SELECT statement section :
"The FOR UPDATE clause allows the SELECT
statement to perform exclusive locking of selected
rows"
in LOCK statement section :
"ROW SHARE MODE
Note: Automatically acquired by SELECT ... FOR
UPDATE."
- Isn't this two statements somewhat conflicting? Is
the PostgreSQL meaning of SHARE lock and EXCLUSIVE
LOCK similar to the definition of Elmasri/Navathe in
the book "Fundamentals of Database Systems" where a
SHARE lock is a "read lock", while an EXCLUSIVE lock
is a "write lock"?
Thank you in advance.
ludwig.
__________________________________________________
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com
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: lud_nowhere_man@yahoo.com, tgl@sss.pgh.pa.us
Subject: Re: Locking that will delayed a SELECT
In-Reply-To: <20021018150234.23205.qmail@web80305.mail.yahoo.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