Обсуждение: Unclear\mistakable description of UPDATE behaviour in "13.2.1. Read Committed Isolation Level"
Unclear\mistakable description of UPDATE behaviour in "13.2.1. Read Committed Isolation Level"
От
PG Doc comments form
Дата:
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/13/transaction-iso.html Description: hello! documentation for "read committed" says that: "UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands ... ...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." but this behaviour is not reproduced for UPDATE .. WHERE .. update from second session don't takes into account updated data in row committed by first session. example: session1: begin; create table t ( x numeric, y numeric ); insert into t values ( -1, 11 ); insert into t values ( 1, 12 ); commit; -- so table is now visible for another transactions begin; update t set x=-x, y=y+100; -- here rows is locked by session1 until commit\rollback session 2 begin; update t set y=y+1000 where x<0; -- here session2 start waiting for session1 commit\rollback session1 commit; session2 -- successfully completed. NO ROWS AFFECTED! -- this means that update from session2 works definitely not like described in documentation ("The search condition of the command (the WHERE clause) is re-evaluated") I would like to see in documentation description of REAL behaviour of postgres for "second updater". Or, maybe, it's a bug.. but it's a principal part of isolation mechanism, so i think that it's just a documentation issue. i take example from here: https://franckpachot.medium.com/read-committed-anomalies-in-postgresql-f0d80330a0e0 it's reproduced on PostgreSQL 13.3 (Debian 13.3-1.pgdg100+1) many thanks!
Re: Unclear\mistakable description of UPDATE behaviour in "13.2.1. Read Committed Isolation Level"
От
"David G. Johnston"
Дата:
On Tue, Aug 17, 2021 at 3:56 PM PG Doc comments form <noreply@postgresql.org> wrote:
"UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands ...
...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."
described in documentation ("The search condition of the command (the WHERE
clause) is re-evaluated")
Maybe the nuance was lost and the docs could be improved, but it clearly says "...to see if the updated version of the rows STILL MATCHES the search condition" - i.e., it will happily skip a row it thought, before it started waiting, that it was going to have to update but it will not go looking for new rows that now may match the criteria. It also won't handle any inserts by the same reasoning. This is reinforced by the leading sentence:
"...they will only find target rows that were committed as of the command start time."
David J.
Hi, David!
Thanks for the explanation!
Now I clearly understand how it works.
I still think it would be great if this doc point was worded a little differently in new editions.
Thank you!
On Wed, 18 Aug 2021 at 02:03, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tue, Aug 17, 2021 at 3:56 PM PG Doc comments form <noreply@postgresql.org> wrote:
"UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands ...
...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."
described in documentation ("The search condition of the command (the WHERE
clause) is re-evaluated")Maybe the nuance was lost and the docs could be improved, but it clearly says "...to see if the updated version of the rows STILL MATCHES the search condition" - i.e., it will happily skip a row it thought, before it started waiting, that it was going to have to update but it will not go looking for new rows that now may match the criteria. It also won't handle any inserts by the same reasoning. This is reinforced by the leading sentence:"...they will only find target rows that were committed as of the command start time."David J.
with best regards,
radiodiversion
radiodiversion
Re: Unclear\mistakable description of UPDATE behaviour in "13.2.1. Read Committed Isolation Level"
От
"David G. Johnston"
Дата:
On Tue, Aug 17, 2021 at 4:17 PM radiodiversion <radiodiversion@gmail.com> wrote:
I still think it would be great if this doc point was worded a little differently in new editions.
Suggestions are welcome.
Without some idea of why you seemed to miss the two seemingly obvious references that I pointed out (including a fragment you quoted) it's hard to decide what might be an improvement. It's unappealing to consider rewriting the documentation based upon the experiences of a sample size of one without a clear rationale.
David J.
David, I have some suggestions.
but first i want to show how the current version of doc may look like for new readers. and why.
I am sure that it looks pretty logical if you KNOW how it works, but it is not so clear if you WANT to learn that from documentation.
explanation:
first sentence: "they will only find target rows that were committed as of the command start time."
how a naive user (me) read that: "okay. at the start of command we find only committed rows. LETS SEE WHAT HAPPENS NEXT!".
second sentence: "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."how it looks: "The command will be RE-EVALUATED for all rows in the current snapshot (i.e. against new state of db after first updater commits) to RE-CHECK the WHERE condition. AT THE SAME TIME (AS AN EFFECT) we see if the updated version of the row still matches the search condition."
For me RE-evaluating is an ACTION and "to see..that still matches" is ONE of the accompanying options.
As if I said: "I mowed all the grass to avoid seeing the dandelions again."
suggestion:
change sentence: "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."with sentence: "The search condition of the command (the
WHERE clause) is re-evaluated only for updated versions of rows to see if they still match the search condition. Note that this is different from re-executing the command."I tried to explain as best I could.
Of course, I dare not insist on anything.
Of course, I dare not insist on anything.
thanks!
On Wed, 18 Aug 2021 at 03:47, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tue, Aug 17, 2021 at 4:17 PM radiodiversion <radiodiversion@gmail.com> wrote:I still think it would be great if this doc point was worded a little differently in new editions.Suggestions are welcome.Without some idea of why you seemed to miss the two seemingly obvious references that I pointed out (including a fragment you quoted) it's hard to decide what might be an improvement. It's unappealing to consider rewriting the documentation based upon the experiences of a sample size of one without a clear rationale.David J.
with best regards,
radiodiversion
radiodiversion
Re: Unclear\mistakable description of UPDATE behaviour in "13.2.1. Read Committed Isolation Level"
От
"David G. Johnston"
Дата:
On Tue, Aug 17, 2021 at 11:18 PM radiodiversion <radiodiversion@gmail.com> wrote:
change sentence: "The search condition of the command (theWHEREclause) is re-evaluated to see if the updated version of the row still matches the search condition."
with sentence: "The search condition of the command (theWHEREclause) is re-evaluated only for updated versions of rows to see if they still match the search condition. Note that this is different from re-executing the command."
I'm indifferent about adding the additional note about this not being a new command start: while the whole of the paragraph makes it clear that once the command begins it waits and then continues the added note isn't going to make things worse.
I do not believe the "only for updated versions of rows" is an improvement - the immediately preceding sentence already says exactly that. I'd rather use fewer words in this already quite long paragraph.
David J.