From: | Forest Wilkinson <fspam(at)home(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-sql(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: possible row locking bug in 7.0.3 & 7.1 |
Date: | 2001-03-28 06:08:00 |
Message-ID: | 01032722080000.08138@bartok |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-sql |
On Tuesday 27 March 2001 15:14, Tom Lane wrote:
> Forest Wilkinson <fspam(at)home(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-03-28 06:08:45 | Re: Re: Call for platforms |
Previous Message | Tatsuo Ishii | 2001-03-28 05:53:35 | Re: Re: Call for platforms |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-03-28 06:11:08 | Re: possible row locking bug in 7.0.3 & 7.1 |
Previous Message | Tom Lane | 2001-03-28 00:30:16 | Re: [HACKERS] Re: possible row locking bug in 7.0.3 & 7.1 |