From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Anthony Berglas <anthony@berglas.org>
Cc: pgsql-docs@lists.postgresql.org
Subject: Re: Locking
Date: Sat, 16 Mar 2024 14:48:32 +0100
Message-ID: <5f459c76d3decb4e2e9e8b5a2a29daed0e99e0ce.camel@cybertec.at> (raw)
In-Reply-To: <CA+_PZMcrwfVVEa9cNUAbz77xEKqHazWs7GHByPpjGcbd5sObWQ@mail.gmail.com>
References: <171037536712.644.16792405319486797414@wrigleys.postgresql.org>
<7d0e70d3c0ccab7acd4b6f012f8c37a1618d5c3c.camel@cybertec.at>
<CA+_PZMcrwfVVEa9cNUAbz77xEKqHazWs7GHByPpjGcbd5sObWQ@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: laurenz.albe@cybertec.at, anthony@berglas.org, pgsql-docs@lists.postgresql.org
Subject: Re: Locking
In-Reply-To: <5f459c76d3decb4e2e9e8b5a2a29daed0e99e0ce.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox