pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Forest Wilkinson <fspam@home.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Cc: pgsql-hackers@postgresql.org
Subject: Re: possible row locking bug in 7.0.3 & 7.1
Date: Tue, 27 Mar 2001 22:08:00 -0800
Message-ID: <01032722080000.08138@bartok> (raw)
In-Reply-To: <15828.985734842@sss.pgh.pa.us>
References: <01032713101600.07241@bartok>
	<15828.985734842@sss.pgh.pa.us>

On Tuesday 27 March 2001 15:14, Tom Lane wrote:
> Forest Wilkinson <fspam@home.com> 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



view thread (21+ messages)  latest in thread

Message-ID: <01032722080000.08138@bartok>
Permalink:  ../01032722080000.08138@bartok/
Also on:    postgresql.org/message-id/01032722080000.08138@bartok

 · 

reply

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: fspam@home.com, tgl@sss.pgh.pa.us, pgsql-hackers@postgresql.org
  Subject: Re: possible row locking bug in 7.0.3 & 7.1
  In-Reply-To: <01032722080000.08138@bartok>

* 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