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

> 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