Subject: Re: incrementing counter

From: Nat Howard <[email-masked]>
Date: Wed, 21 Apr 93 15:34:53 -0400

Paul Aoki writes:

>Nat Howard <[email-masked]> 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

Interestingly, this works faster, but seems just as capable of causing
the "append" problem mentioned in my last note.

Try:

Q1				Q2

begin	
				begin

replace
retrieve
end
				replace
				retrieve
				end

You wind up with:

* retrieve(tt_number.oid, tt_number.all)\g

Query sent to backend is "retrieve(tt_number.oid, tt_number.all)"
-----------------------------
| oid         | tt_number   |
-----------------------------
| 151194      | 2           |
-----------------------------
| 151194      | 2           |
-----------------------------

The weird thing is that when Q2 asks the question, before the
second "end", it looks like this:

-----------------------------
| oid         | tt_number   |
-----------------------------
| 151194      | 1           |
-----------------------------
| 151194      | 2           |
-----------------------------

I admit bafflement (but plead ignorance -- this is my first
serious dip into postgres).

Is this really that hard?

---

From: [email-masked] (Jean Anderson)
Date: Thu, 22 Apr 93 08:56:47 -0700

Paul M. Aoki <[email-masked]>:
> as someone kindly pointed out in private mail, sql allows one to
> perform retrievals "for update," which seems reasonable.

Oracle allows a "select ... for update" which prohibits reads on the data.
Sybase's "select .... holdlock" does not prohibit reads; the strategy for 
blocking reads is to do an update that puts exclusive locks on the data.
Sybase System 10, coming out this summer, will be adding native cursors
that do "select ... for update".

Gerry Stringer <[email-masked]> writes:
> IMHO doing this sort of code requires too much knowledge and fudging.
> Perhaps an answer is to have an optional exclusive read lock (like a
> write lock) or manual control of locking?

I agree, but I hope folks realize that "knowledge and fudging" is required
for commercial databases; i.e., this is not a Postgres problem, it is a
concurrency management challenge.

Even when the fudging is reduced, it doesn't eliminate the need to know.
Oracle's "select ... for update" and other commands that let you manually
control locking are excellent capabilities -- and dangerous in the hands
of novice sql-ers, guaranteeing continued stressful employment for the 
DBA's who have to troubleshoot and make a complicated system work reliably.
To their credit, Oracle also provides a lock monitor which makes sleuthing
down lock problems easier.

 -jean