Thread: 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