AW: Re: [SQL] possible row locking bug in 7.0.3 & 7.1

From: Zeugswetter Andreas SB <ZeugswetterA(at)wien(dot)spardat(dot)at>
To: "'Forest Wilkinson'" <fspam(at)home(dot)com>, "'tgl(at)sss(dot)pgh(dot)pa(dot)us'" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "Mikheev, Vadim" <vmikheev(at)SECTORBASE(dot)COM>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: AW: Re: [SQL] possible row locking bug in 7.0.3 & 7.1
Date: 2001-03-30 15:42:08
Message-ID: 11C1E6749A55D411A9670001FA687963368267@sdexcsrv1.f000.d0188.sd.spardat.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


> 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';

Yes, better, but be sure, to only use this function from inside a transaction.
If you use it in autocommit mode (no begin work) you might in theory read a row,
that another session modified between the two lines.

> Or, would I still have to add FOR UPDATE to that final SELECT?

Now, this certainly looks very funny. You actually get reasonable results only
if you do include the "for update" with RC1 sources .

To the rest on the list:
Try the above example by adding a lock between the two lines:

create function nextid( varchar(32)) returns int8 as '
update idseq set id = id + 1 where name = $1::text;
select * from lock1;
select id from idseq where name = $1::text for update;
' language 'sql';

session1:
begin work;
lock table lock1 in access exclusive mode;
session 2:
not in txn: select nextid('one'); // this blocks
select nextid('one');
commit work;

And stare at the results you get with and without for update :-(
Something is definitely fishy with the visibility of SELECT here.

Andreas

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2001-03-30 15:48:54 Re: User administration tool
Previous Message Peter Eisentraut 2001-03-30 15:38:31 Re: testing last sanpshot in QNX platform