Re: Common case not at all clear

My point is that while I can follow the academic style discussion, most of
my colleagues could not. They just need to have a clear idea of how to
handle the common case, which is to use a database using some programming
language.

Early on, for Read Committed, it should discuss and ideally provide an
example of Select For Update, followed by an Update, together with a
discussion of why the "For Update" is important. Indeed essential. That
is totally unclear unless you already understand it.

Yes, it does mention an Update statement, but that is a less common
approach. It needs to mention how and why to use Select For Update clearly
and early. Without being tangled up in all the other more
esoteric considerations.

>> Select balance into :bal ...where key =123; ...
>
> Update set balance = :bal+100 where key = 100
>>
>
> That isn't SQL, or any syntax that PostgreSQL supports that I know of.
>

OK, so I have omitted the table name which is not important. And the :bal
is a traditional notation used in APIs, no ":" for postgresql triggers
etc. But the meaning should be clear. Retrieve a value and update it in a
seperate statement.

>
>> The discussion of read committed for Updates is misleading, I am pretty
>> sure
>> it will fail if the select is in a different statement, a common case.
>>
>
> I don't believe it is possible for it to fail - or serializable is going
> to actually result in errors.
>

"Fail" meaning roll back.

>
> Is that how PostgreSql works? Is that the generally recommended pattern?
>> Impossible to tell from the docs as written.
>
>
> That part of the issue with the documentation, they tend to simply say how
> things work and let the user decide. Recommendations are uncommon.
>

Well, in this case they are rather important.

>
>
>> MVCC really relies on Select
>> For Update to work for transactions, I think.
>>
>
> IIUC it is basically the difference between optimistic and pessimistic
> concurrency. You get to choose which cost/benefit package you want.
>
> My impression is that if you are getting that deep into the bowels of
> concurrency you should learn and use the serializable isolation level to
> ensure a consistent linear flow without having to really deal with manual
> locking directly.
>

I am more interested in people that do not go into the depths getting the
really simple things right. Not really possible from these docs.

For me, the only way to understand these docs is to do lots of little
experiments.

Incidentally, I think that in the default mode (Read Committed?), Oracle
gives you the pre transaction snapshot values for a Select but the
currently committed values for Select For Update. Those semantics seem to
work pretty well in practice.

From my understanding and experiments those semantics cannot be achieved
with Postgresql.

Anthony

>
> David J.
>
>