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
