Re: Question about SELECT FOR UPDATE in transaction, isolation level

On Fri, Apr 06, 2001 at 10:56:43AM +0200, Al wrote:
> Hi all. I have a problem understanding/using PostgreSQL as a multiuser
> database.
> I have a simple table with a number of different IDs (int8) used for
> different activities.
> It should be possible for any number of simultaneous users to receive a
> unique ID for a particular activity and then update these IDs.
>
> I thought this would be the way to go:
>
> ---
> set transaction isolation level serializable;
>
> begin work;
> select val from ids where cntr='ct1' for update;
> update ids set val=val+(some integer value) where cntr='ct1';
> commit work;
> ---
>
> However, when I run everything except the commit on one terminal and
> then run up to and including the select on another terminal, I receive
> the OLD, non-updated value for val on that second terminal. In other
> words, the select is not blocked and the id is not unique.
> Not what I intended at all!
>
> What gives? Does anybody know how to do this in PostgreSQL?

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Note: Every other transaction will block until you do a commit or
rollback -- so be quick about it...

You might consider using a sequence if you can...

--
Eric G. Miller <egm2(at)jps(dot)net>