Thread: Repeatable Read Isolation Level "transaction start time"

https://www.postgresql.org/docs/16/transaction-iso.html#XACT-REPEATABLE-READ

The PostgreSQL documentation for the Repeatable Read Isolation Level states the following:

“UPDATE, DELETE, MERGE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of
searchingfor target rows: they will only find target rows that were committed as of the transaction start time.” 

What is defined as the "transaction start time?" When I first read the statement, I interpreted it as the start of the
transaction:

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

But in my testing, I find that according to that statement, the transaction start time is actually "the start of the
firstnon-transaction-control statement in the transaction" (as mentioned earlier in the section). Is my conclusion
correct,or am I misunderstanding the documentation? 

https://www.postgresql.org/docs/16/transaction-iso.html#XACT-REPEATABLE-READ

The PostgreSQL documentation for the Repeatable Read Isolation Level states the following:

“UPDATE, DELETE, MERGE, 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 transaction start time.”

What is defined as the "transaction start time?" When I first read the statement, I interpreted it as the start of the transaction:

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

But in my testing, I find that according to that statement, the transaction start time is actually "the start of the first non-transaction-control statement in the transaction" (as mentioned earlier in the section). Is my conclusion correct, or am I misunderstanding the documentation?

Wizard Brony <wizardbrony@gmail.com> writes:
> But in my testing, I find that according to that statement, the transaction start time is actually "the start of the
firstnon-transaction-control statement in the transaction" (as mentioned earlier in the section). Is my conclusion
correct,or am I misunderstanding the documentation? 

It's even looser than that, really: it's the first statement that
requires an MVCC snapshot.  From memory, LOCK TABLE is an important
exception --- you can acquire table locks before pinning down
a snapshot, and this is important in some scenarios.

            regards, tom lane

On 9/24/24 05:59, Ron Johnson wrote:
> On Tue, Sep 24, 2024 at 8:29 AM David G. Johnston 
> <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
> 
>     On Monday, September 23, 2024, Wizard Brony <wizardbrony@gmail.com
>     <mailto:wizardbrony@gmail.com>> wrote:
> 
>         https://www.postgresql.org/docs/16/transaction-iso.html#XACT-REPEATABLE-READ
<https://www.postgresql.org/docs/16/transaction-iso.html#XACT-REPEATABLE-READ>
> 
>         The PostgreSQL documentation for the Repeatable Read Isolation
>         Level states the following:
> 
>         “UPDATE, DELETE, MERGE, 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 transaction start time.”
> 
>         What is defined as the "transaction start time?" When I first
>         read the statement, I interpreted it as the start of the
>         transaction:
> 
>         BEGIN;
>         SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
> 
>         But in my testing, I find that according to that statement, the
>         transaction start time is actually "the start of the first
>         non-transaction-control statement in the transaction" (as
>         mentioned earlier in the section). Is my conclusion correct, or
>         am I misunderstanding the documentation?
> 
> 
>     Probably, since indeed the transaction cannot start at begin because
>     once it does start it cannot be modified.
> 
> Huh?

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

I read it as the transaction does not start at BEGIN because if it did 
you could not SET TRANSACTION to change it's characteristics.

The docs go into more detail:

https://www.postgresql.org/docs/current/sql-set-transaction.html

The transaction isolation level cannot be changed after the first query 
or data-modification statement (SELECT, INSERT, DELETE, UPDATE, MERGE, 
FETCH, or COPY) of a transaction has been executed.


So:

begin ;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET
select * from csv_test ;
[...]
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
ERROR:  SET TRANSACTION ISOLATION LEVEL must be called before any query


> 
> -- 
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> crustacean!

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 9/24/24 09:12, Ron Johnson wrote:

> Makes sense.  Never would have occurred to me to try and change the 
> isolation level using a second SET TRANSACTION statement, though.

 From the docs:

https://www.postgresql.org/docs/current/sql-set-transaction.html

The SET TRANSACTION command sets the characteristics of the current 
transaction. It has no effect on any subsequent transactions. SET 
SESSION CHARACTERISTICS sets the default transaction characteristics for 
subsequent transactions of a session. These defaults can be overridden 
by SET TRANSACTION for an individual transaction.


> 
> -- 
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> crustacean!

-- 
Adrian Klaver
adrian.klaver@aklaver.com