Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 8551B476106 for ; Thu, 24 Oct 2002 00:10:27 -0400 (EDT) Received: from web80313.mail.yahoo.com (web80313.mail.yahoo.com [66.218.79.29]) by postgresql.org (Postfix) with SMTP id E7989475F5D for ; Thu, 24 Oct 2002 00:10:26 -0400 (EDT) Message-ID: <20021024041032.93394.qmail@web80313.mail.yahoo.com> Received: from [203.87.150.116] by web80313.mail.yahoo.com via HTTP; Wed, 23 Oct 2002 21:10:32 PDT Date: Wed, 23 Oct 2002 21:10:32 -0700 (PDT) From: Ludwig Lim Subject: Simulating a SELECT..FOR UPDATE to LOCK and SELECT statement To: PostgreSQL Mailing List MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200210/320 X-Sequence-Number: 10037 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