Message-Id: <9304211807.AA25102@postgres.Berkeley.EDU>
From: aoki@postgres.berkeley.edu (Paul M. Aoki)
Subject: Re: incrementing counter
To: postgres@postgres.berkeley.edu
Sender: pg_adm@postgres.berkeley.edu
In-Reply-To: Your message of Wed, 21 Apr 93 11:18:10 -0400 
	     <9304211517.AA24003@postgres.Berkeley.EDU> 
Date: Wed, 21 Apr 93 11:01:55 -0700
Sender: aoki@postgres.Berkeley.EDU

Nat Howard <nrh@uunet.uu.net> writes:,
> What I *really* want, though, is a way get atomically-incremented 
> values, so that neither transaction must time out:

this seems to work better:

begin
replace tt_number (tt_number = tt_number.tt_number + 1)
retrieve (tt_number.tt_number)
/* other stuff */
end

with the original solution, one xact will get a read lock,
after which one of two things can happen: either the second xact 
gets a read lock, or the first xact gets its write lock.  in the 
first case, neither xact can get a write lock (deadlock) so at 
least one xact must lose.  in the second, the first xact must 
commit before the second xact gets its read lock, and all is well.
(in the first case, there's no way postgres can decide not to grant
the second read lock because it can't read your mind and know
that you're going to upgrade to a write lock later in the xact.
putting the update up front is a way to avoid this problem.)

hmm, now that i think about it, my solution might just shorten
the window of vulnerability to something really small. (i'm not
sure that the write lock is obtained first in the replace statement.)
oh, well.

i'm on allergy drugs and sort of free-associating, so feel free to 
throw beer bottles from the stands, folks..
--
  Paul M. Aoki  |  CS Div., Dept. of EECS, UCB  |  aoki@postgres.Berkeley.EDU
                |  Berkeley, CA 94720           |  ...!uunet!ucbvax!aoki