Re: Common case not at all clear

Hello David,

You are talking about optimistic locking, commonly used for web
applications where there is no transaction kept open during user think
time. A COMMIT between the SELECT and the UPDATE.

This is also what was needed for traditional MySql running only in
AutoCommit mode. It requires no locking at all other than
atomic statements. And the end user has to re-enter the transaction if it
fails due to a conflict.

While processing something on the server however, it is nice to be able to
use a proper database like Postgresql that does have locking during a
transaction. Failures are more difficult to deal with on a server where
you cannot just throw failures back to a user, transactions are more
complex, and a short wait for a lock is generally not an issue (there
should always be a generous timeout).

(If you use SET quantity_on_hand = *quantity_on_hand - 50 *then you do not
even need the optimistic lock. But that is rarely done in practice using
an Object Relational Mapping library.)

Once upon a time, tools like Oracle Forms kept database locks during user
think time so locking strategies were very important. But client/server
web apps cannot work that way, so databases like MySql were useable. But
there is still some server processing to be done, so while locking is less
important, it is still worth doing properly. And more importantly it is
very important that people do not use a SELECT without a FOR UPDATE and
introduce subtle, unreproducible threading errors.

So please do have the update (or similar) inserted. If you wanted to also
talk about optimistic locking that would be fine, but probably not
necessary.

Thanks,

Anthony

P.S. Do you know if Postgresql Guarantees that all timestamps are
distinct, even if they occur within the same clock tick? (i.e. does it run
the clock forward). I have another reason to know that. Using clocks is
iffy for synchronization.

On Tue, Aug 3, 2021 at 1:26 AM David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> On Sun, Aug 1, 2021 at 11:35 PM Anthony Berglas <anthony(at)berglas(dot)org>
> wrote:
>
>> I have attached a proposed doc update that makes the problem clearer. I
>> think that this is important because if people do not understand it they
>> will write buggy code and then blame Postgresql for losing updates, which
>> is totally unacceptable. So please do action this. I have tested and
>> confirm that the behaviour is as I specify.
>>
>
> That really isn't a good solution though...a better one is to modify the
> update command to be:
>
> UPDATE products SET quantity_on_hand = qoh - 50 WHERE quantity_on_hand =
> qoh;
>
> Or, even better:
>
> SELECT ... last_updated INTO lastupdatetimestamp, ...;
>
> UPDATE products SET quantity_on_hand = qoh - 50 AND last_updated =
> lastupdatetimestamp;
>
> Then the application needs to simply check for a zero record update and,
> if that happens, decide how it wants to deal with the fact that the data
> has changed out from under it.
>
> This is superior to waiting an indeterminate amount of time holding a FOR
> UPDATE lock in an open transaction.
>
> I would still expand on the FOR UPDATE option as you suggest.
>
> This is still just discussion though, someone will need to convert this
> into a proper doc patch that can be built, added to the commitfest,
> reviewed, and ultimately committed. IMO it is not worth going to the
> trouble of making this all HTML-friendly as your patch did. Just stick to
> plain text discussion in the email body if you aren't going to write a
> patch in the sgml source language and present it as a diff.
>
> David J.
>
>