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
