Thread

1. Question about SELECT FOR UPDATE in transaction, isolation level

   Al <alexm%innocent.com@news.tht.net> — 2001-04-06T08:56:43Z

   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.
   
   

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

   Eric G. Miller <egm2@jps.net> — 2001-04-08T07:05:13Z

   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>
   
   

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

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-04-13T16:22:22Z

   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