Re: Locking

On Sat, 2024-03-16 at 10:52 +1000, Anthony Berglas wrote:
> The code is wrong by any normal definition.  We lose one of the updates.
> It is a very common error.  It is a very common way to write code,
> especially if using an ORM, which is very common.
>
> [example of a lost update]
>
> The problem is that this fundamental problem is lost in all the escoteric
> details of locking.
>
> Sure, a database expert that carefully studies the docs might figure it
> out if they did not already know it.  But the other 99.9% of users will
> just consider Postgresql to be buggy because balances do not add up.
>
> So I think something in the docs is very much necessary.

Yes, the "lost update" is a common and well-known transaction anomaly,
and every developer should know about it.

What you are looking for is a tutorial about database transactions.
There are fundamental differences between a tutorial and documentation.
A tutorial is an example-based introduction aimed at beginners, while
the documentation describes the behavior in greater detail, aiming for
rigorourness and completeness.

Now there is a tutorial inside the PostgreSQL documentation, and it even
has a chapter about transactions:
https://www.postgresql.org/docs/current/tutorial-transactions.html
It even talks some about transaction isolation, but doesn't go as far
as mentioning anomalies and the individual isolation levels.

Perhaps you feel inspired to write a patch for that page that demonstrates
the lost update and shows how to avoid it using the REPEATABLE READ
isolation level?

Yours,
Laurenz Albe