Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group