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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
