Question about SELECT FOR UPDATE in transaction, isolation level

From: Al <alexm%innocent(dot)com(at)news(dot)tht(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Question about SELECT FOR UPDATE in transaction, isolation level
Date: 2001-04-06 08:56:43
Message-ID: 9ak0cd$1prq$1@news.tht.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message dazo 2001-04-06 09:13:01 ERROR: cannot read block 1452 from data (HELP!!!! I'M DESPERATE!)
Previous Message Steven Vajdic 2001-04-06 08:50:07 Re: Is this a bug in 7.0.2?