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.