Обсуждение: Locking

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/16/mvcc-intro.html
Description:

There really needs to be an explicit warning that the following is invalid
in normal read committed mode:-

select foo into f from bar where id=1;
f = f + 123;
update bar set foo = f where id =1;
commit;

This is a very common and serious mistake and extremely difficult to
understand from the current documentation.

On Thu, 2024-03-14 at 00:16 +0000, PG Doc comments form wrote:
> There really needs to be an explicit warning that the following is invalid
> in normal read committed mode:-
>
> select foo into f from bar where id=1;
> f = f + 123;
> update bar set foo = f where id =1;
> commit;
>
> This is a very common and serious mistake and extremely difficult to
> understand from the current documentation.

There is nothing invalid in the code sample you are showing.

If you are talking about lost updates, that is described on
https://www.postgresql.org/docs/current/transaction-iso.html#XACT-READ-COMMITTED

  UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same
  as SELECT in terms of searching for target rows: they will only find target rows
  that were committed as of the command start time. However, such a target row
  might have already been updated (or deleted or locked) by another concurrent
  transaction by the time it is found. In this case, the would-be updater will
  wait for the first updating transaction to commit or roll back (if it is still
  in progress). If the first updater rolls back, then its effects are negated and
  the second updater can proceed with updating the originally found row. If the
  first updater commits, the second updater will ignore the row if the first
  updater deleted it, otherwise it will attempt to apply its operation to the
  updated version of the row. The search condition of the command (the WHERE
  clause) is re-evaluated to see if the updated version of the row still matches
  the search condition. If so, the second updater proceeds with its operation
  using the updated version of the row. In the case of SELECT FOR UPDATE and
  SELECT FOR SHARE, this means it is the updated version of the row that is locked
  and returned to the client.

Yours,
Laurenz Albe

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

On Sun, 2024-03-17 at 15:11 +1000, Anthony Berglas wrote:
> I would write something iff there is interest in posting it.

I cannot promise that the patch would be accepted; I personally think
that briefly mentioning transaction anomalies and isolation levels
would be ok.

> Probably an overview in the Introduction to Locking section.

I don't know exactly which section you mean, but it should definitely
be part of the tutorial, in chapter 3.4.

Appendix J contains some information about building the documentation
from source.  The patch should be against the current development
version.

Yours,
Laurenz Albe