Обсуждение: Question about SELECT FOR UPDATE in transaction, isolation level

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?

Al.

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@jps.net>

Al <alexm%innocent.com@news.tht.net> writes:
> begin work;
> select val from ids where cntr='ct1' for update;
> update ids set val=val+(some integer value) where cntr='ct1';
> commit work;

Looks reasonable.

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

Hm, it works for me.  You sure you remembered to use select FOR UPDATE
in both transactions?

            regards, tom lane