Re: Repeatable Read Isolation Level "transaction start time"

On 2024-09-25 13:53:30 -0700, Christophe Pettus wrote:
> > On Sep 25, 2024, at 13:49, Greg Sabino Mullane <htamfids(at)gmail(dot)com> wrote:
> > BEGIN ISOLATION MODE REPEATABLE READ SNAPSHOT NOW;
>
> This might well be a failure of imagination on my part, but when would
> it pragmatically matter that the snapshot is taken at the first
> statement as opposed to at BEGIN?

It may make a difference if you're comparing timestamps.

For example, if you're using isolation level REPEATABLE READ and
(mistakenly) assume that the snapshot is taken at BEGIN, you would
expect any transaction_timestamp() written by a different transaction
and readable by this transaction to be earlier than the
transaction_timestamp() of this transaction.

But that's wrong because the other transaction could have happened
entirely in the time between your BEGIN and the statement which actually
triggers the snapshot.

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"