
> 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. 

Without "for update" I see a tuple in session2 from before session1 began.
After both complete, the net result is correct (id is incremented by 2).
This is very interesting, unfortunately I must leave Internet access until 
monday since my daughter called me home, and mail is so dead slow,
that I did not even receive my last mails yet :-(

Andreas

Fri, 30 Mar 2001 18:04:08 +0200
Zeugswetter Andreas SB  <ZeugswetterA@wien.spardat.at>
AW: Re: [SQL] possible row locking bug in 7.0.3 & 7.1