From: Ludwig Lim <lud_nowhere_man@yahoo.com>
To: PostgreSQL Mailing List <pgsql-sql@postgresql.org>
Subject: Simulating a SELECT..FOR UPDATE to LOCK and SELECT statement
Date: Wed, 23 Oct 2002 21:10:32 -0700 (PDT)
Message-ID: <20021024041032.93394.qmail@web80313.mail.yahoo.com> (raw)
Hi:
Is there a way to emulate a SELECT..FOR UPDATE to
series of LOCK/SELECT statement.
I tried the following statements using 2 psql
terminals.
T1 | T2
1) BEGIN; |
2) SELECT x | BEGIN;
FROM y |
WHERE y=1 |
FOR UPDATE; |
3) | SELECT x
| FROM y
| WHERE y=1
| FOR UPDATE;
4) COMMIT; |
5) | COMMIT;
At point #3 T2 will wait, however changing the WHERE
clause to other clause such as "WHERE y=2" will allow
T2 to proceed.
- I tried changing the SELECT..FOR UPDATE into LOCK
SHARE MODE followed by a SELECT (but w/o FOR UPDATE)
but it T2 is allowed to proceed even for the clause
"where y=1".
I am surprised because according to the docs
(version 7.2), it says:
ROW SHARE MODE
Note: Automatically acquired by SELECT ... FOR
UPDATE.
I'm assuming that the SELECT..FOR UPDATE performs
a lock in ROW SHARE MODE before the SELECT.
I also tried changing the lock mode into SHARE ROW
EXCLUSIVE MODE / EXCLUSIVE MODE but still T2 won't be
allowed to passed even the for the clause "where y=2".
Is there any to do it? I'm asking becuase our db
libaries (using libpq) always a cursor when generating
a SELECT statement thus I'm encourtering the following
error message when I use SELECT..FOR UPDATE:
Cursor must be READ ONLY.
DECLARE/UPDATE is not supported.
Another alternative would be studying libpq and
removing the cursors in a SELECT statement.
By the way is there any side effect / disadavtages
when I remove the "DELCARE CURSOR" statement and
change it to plain SELECT statememt in C++ codes?
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
Subject: Re: Simulating a SELECT..FOR UPDATE to LOCK and SELECT statement
In-Reply-To: <20021024041032.93394.qmail@web80313.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