public inbox for [email protected]
help / color / mirror / Atom feedRe: Repeatable Read Isolation Level "transaction start time"
4+ messages / 2 participants
[nested] [flat]
* Re: Repeatable Read Isolation Level "transaction start time"
@ 2024-09-24 12:59 Ron Johnson <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Ron Johnson @ 2024-09-24 12:59 UTC (permalink / raw)
To: [email protected] <[email protected]>
On Tue, Sep 24, 2024 at 8:29 AM David G. Johnston <
[email protected]> wrote:
> On Monday, September 23, 2024, Wizard Brony <[email protected]> wrote:
>
>>
>> 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?
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> crustacean!
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Repeatable Read Isolation Level "transaction start time"
@ 2024-09-24 16:06 Adrian Klaver <[email protected]>
parent: Ron Johnson <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Adrian Klaver @ 2024-09-24 16:06 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; [email protected] <[email protected]>
On 9/24/24 05:59, Ron Johnson wrote:
> On Tue, Sep 24, 2024 at 8:29 AM David G. Johnston
> <[email protected] <mailto:[email protected]>> wrote:
>
> On Monday, September 23, 2024, Wizard Brony <[email protected]
> <mailto:[email protected]>> 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
[email protected]
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Repeatable Read Isolation Level "transaction start time"
@ 2024-09-24 16:12 Ron Johnson <[email protected]>
parent: Adrian Klaver <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Ron Johnson @ 2024-09-24 16:12 UTC (permalink / raw)
To: [email protected] <[email protected]>
On Tue, Sep 24, 2024 at 12:06 PM Adrian Klaver <[email protected]>
wrote:
> On 9/24/24 05:59, Ron Johnson wrote:
> > On Tue, Sep 24, 2024 at 8:29 AM David G. Johnston
> > <[email protected] <mailto:[email protected]>> wrote:
> >
> > On Monday, September 23, 2024, Wizard Brony <[email protected]
> > <mailto:[email protected]>> 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
>
Makes sense. Never would have occurred to me to try and change the
isolation level using a second SET TRANSACTION statement, though.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> crustacean!
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Repeatable Read Isolation Level "transaction start time"
@ 2024-09-24 16:16 Adrian Klaver <[email protected]>
parent: Ron Johnson <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Adrian Klaver @ 2024-09-24 16:16 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; [email protected] <[email protected]>
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
[email protected]
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-09-24 16:16 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-09-24 12:59 Re: Repeatable Read Isolation Level "transaction start time" Ron Johnson <[email protected]>
2024-09-24 16:06 ` Adrian Klaver <[email protected]>
2024-09-24 16:12 ` Ron Johnson <[email protected]>
2024-09-24 16:16 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox