Re: 13.2.1. Read Committed Isolation Level

[Laurenz Albe]

On Sun, 2024-07-14 at 06:17 +, 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

Re: 13.2.1. Read Committed Isolation Level

[David G. Johnston]

On Tue, Jul 16, 2024 at 7:06 AM PG Doc comments form 
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.

Re: 13.2.1. Read Committed Isolation Level

[Василий Лебедев]

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 ? 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 :

> On Tue, Jul 16, 2024 at 7:06 AM PG Doc comments form <
> nore...@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.
>
>