Thread: 13.2.1. Read Committed Isolation Level
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/transaction-iso.html Description: I don't understend this text. 'INSERT with an ON CONFLICT DO UPDATE clause behaves similarly. In Read Committed mode, each row proposed for insertion will either insert or update. Unless there are unrelated errors, one of those two outcomes is guaranteed. If a conflict originates in another transaction whose effects are not yet visible to the INSERT, the UPDATE clause will affect that row, even though possibly no version of that row is conventionally visible to the command. INSERT with an ON CONFLICT DO NOTHING clause may have insertion not proceed for a row due to the outcome of another transaction whose effects are not visible to the INSERT snapshot. Again, this is only the case in Read Committed mode.' And specifically this part 'the UPDATE clause will affect that row, even though possibly no version of that row is conventionally visible to the command.' What does it mean that no version of that row is visible to the command? Is this visibility related to xmin xmax values? Or does it mean that the version of the row is not visible because it has not yet been commited by a parallel transaction? 'If a conflict originates in another transaction whose effects are not yet visible to the INSERT' - Does this mean that at the moment of starting the INSERT statement, the parallel transaction has not yet been committed (or even started after calling the INSERT statement), but at the time of performing the action with a row in the INSERT statement, transaction 2 has already committed its changes? Or does it mean that contrary to Read Committed Isolation Level, uncommitted changes from a parallel transaction can affect the execution of an INSERT command? 'INSERT with the ON CONFLICT DO NOTHING clause can result in the insertion of a row not continuing due to the result of another transaction, whose effects are not visible in the instantaneous snapshot of INSERT' - same question. Could you please describe this behavior in more detail?
On Sun, 2024-07-14 at 06:17 +0000, PG Doc comments form wrote: > Page: https://www.postgresql.org/docs/16/transaction-iso.html > Description: > > I don't understend this text. > > [five paragraphs from the documentation] > > Could you please describe this behavior in more detail? It is difficult to help you if you are that unspecific about what exactly you fail to understand. Sure, there are some complicated concepts involved. If you can't understand *anything* about that text, perhaps you should start reading the whole chapter about concurrency. Yours, Laurenz Albe
On Tue, Jul 16, 2024 at 7:06 AM PG Doc comments form <noreply@postgresql.org> wrote:
Or does it mean that contrary to Read
Committed Isolation Level, uncommitted changes from a parallel transaction
can affect the execution of an INSERT command?
This. Because you are keying off of an unique index that has independent isolation mechanics. Upon attempting to insert a row that will violate the unique constraint enforced by the index the system must wait to see whether the earlier transaction commits. If it doesn't, the insert proceeds. If it does, the conflict clause is evaluated - updating the now committed row (or just doing nothing if that option is specified.)
David J.
Thank you for the response. Can I clarify if I understood the essence correctly?
It turns out that despite the Read Committed isolation level, due to the presence of a unique index, which has an independent isolation mechanism, a transaction can "know" that a parallel transaction has performed an insert before it commits its changes. And it will wait for the completion or rollback of the parallel transaction, after which it will be clear whether it can insert the row or if it needs to perform an alternative operation (update or do nothing)? This is very interesting! I would like to see this description in the documentation.
Why does the documentation say <Again, this is only the case in Read Committed mode>? Is the behavior different in other isolation levels? What is it like? The documentation does not say anything about the behavior of INSERT in the Repeatable Read and Serializable isolation levels.
It turns out that despite the Read Committed isolation level, due to the presence of a unique index, which has an independent isolation mechanism, a transaction can "know" that a parallel transaction has performed an insert before it commits its changes. And it will wait for the completion or rollback of the parallel transaction, after which it will be clear whether it can insert the row or if it needs to perform an alternative operation (update or do nothing)? This is very interesting! I would like to see this description in the documentation.
Why does the documentation say <Again, this is only the case in Read Committed mode>? Is the behavior different in other isolation levels? What is it like? The documentation does not say anything about the behavior of INSERT in the Repeatable Read and Serializable isolation levels.
вт, 16 июл. 2024 г. в 20:38, David G. Johnston <david.g.johnston@gmail.com>:
On Tue, Jul 16, 2024 at 7:06 AM PG Doc comments form <noreply@postgresql.org> wrote:Or does it mean that contrary to Read
Committed Isolation Level, uncommitted changes from a parallel transaction
can affect the execution of an INSERT command?This. Because you are keying off of an unique index that has independent isolation mechanics. Upon attempting to insert a row that will violate the unique constraint enforced by the index the system must wait to see whether the earlier transaction commits. If it doesn't, the insert proceeds. If it does, the conflict clause is evaluated - updating the now committed row (or just doing nothing if that option is specified.)David J.