Received: from femail1.sdc1.sfba.home.com (femail1.sdc1.sfba.home.com [24.0.95.81]) by mail.postgresql.org (8.11.3/8.11.1) with ESMTP id f2S682w71577; Wed, 28 Mar 2001 01:08:02 -0500 (EST) (envelope-from fspam@home.com) Received: from bartok ([24.9.226.149]) by femail1.sdc1.sfba.home.com (InterMail vM.4.01.03.20 201-229-121-120-20010223) with SMTP id <20010328060731.ETIP2627.femail1.sdc1.sfba.home.com@bartok>; Tue, 27 Mar 2001 22:07:31 -0800 Content-Type: text/plain; charset="iso-8859-1" From: Forest Wilkinson To: Tom Lane Subject: Re: possible row locking bug in 7.0.3 & 7.1 Date: Tue, 27 Mar 2001 22:08:00 -0800 X-Mailer: KMail [version 1.2] Cc: pgsql-sql@postgresql.org, pgsql-hackers@postgresql.org References: <01032713101600.07241@bartok> <15828.985734842@sss.pgh.pa.us> In-Reply-To: <15828.985734842@sss.pgh.pa.us> MIME-Version: 1.0 Message-Id: <01032722080000.08138@bartok> Content-Transfer-Encoding: 8bit X-Archive-Number: 200103/569 X-Sequence-Number: 2239 On Tuesday 27 March 2001 15:14, Tom Lane wrote: > Forest Wilkinson writes: > > session1<< create function nextid( varchar(32)) returns int8 as ' > > session1<< select * from idseq where name = $1::text for update; > > session1<< update idseq set id = id + 1 where name = $1::text; > > session1<< select id from idseq where name = $1::text; > > session1<< ' language 'sql'; > > [ doesn't work as expected in parallel transactions ] [snip] > The workaround for Forest is to make the final SELECT be a SELECT FOR > UPDATE, so that it's playing by the same rules as the earlier commands. > But I wonder whether we ought to rethink the MVCC rules so that that's > not necessary. I have no idea how we might change the rules though. > If nothing else, we should document this issue better: SELECT and SELECT > FOR UPDATE have different visibility rules, so you probably don't want > to intermix them. My, that's ugly. (But thanks for the workaround.) If I remember correctly, UPDATE establishes a lock on the affected rows, which will block another UPDATE on the same rows for the duration of the transaction. If that's true, shouldn't I be able to achieve my desired behavior by removing the initial as follows: create function nextid( varchar(32)) returns int8 as ' update idseq set id = id + 1 where name = $1::text; select id from idseq where name = $1::text; ' language 'sql'; Or, would I still have to add FOR UPDATE to that final SELECT? Forest