From: Laurenz Albe <laurenz.albe@cybertec.at>
To: anthony@berglas.org
To: pgsql-docs@lists.postgresql.org
Subject: Re: Locking
Date: Fri, 15 Mar 2024 08:29:19 +0100
Message-ID: <7d0e70d3c0ccab7acd4b6f012f8c37a1618d5c3c.camel@cybertec.at> (raw)
In-Reply-To: <171037536712.644.16792405319486797414@wrigleys.postgresql.org>
References: <171037536712.644.16792405319486797414@wrigleys.postgresql.org>
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
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: <7d0e70d3c0ccab7acd4b6f012f8c37a1618d5c3c.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