Re: Repeatable Read Isolation Level "transaction start time"

On 2024-10-05 09:59:00 -0700, Adrian Klaver wrote:
> On 10/5/24 02:14, Peter J. Holzer wrote:
> > On 2024-09-25 18:09:44 -0400, Tom Lane wrote:
> > > "Peter J. Holzer" <hjp-pgsql(at)hjp(dot)at> writes:
> > > Admittedly, that would normally not be a very long interval if BEGIN
> > > did both things ... but on a busy system you could lose the CPU for
> > > awhile in between.
> >
> > Assuming that the system does have a global clock of sufficiently
> > fine resolution which returns strictly monotonically increasing
> > timestamps[1], I think the following is true:
> >
> > Every snapshot divides the set of transactions into two non-overlapping
> > subsets: Those which have committed early enough that their effects are
> > visible in the snapshot and those which haven't. Let's call the first set
> > the "earlier" transactions and the second the "later" transactions. Let's
> > call the current transaction c and any transaction in the earlier set e
> > (we ignore the later transactions for now).
> >
> > Performing a commit and taking a snapshot take some time, but there
> > should be a time t_C(e) in each commit and t_S(c) in the snapshot, such
> > that t_C(e) < t_S(c) for each "earlier" transaction.
>
> Assuming t_C is time of commit and t_S is time of snapshot, is the
> above not the crux of the matter? Namely when in the current
> transaction the snapshot is actually taken. That would determine what
> constitutes an earlier visible transaction relative to the current
> transaction. In other words I am not seeing how this changes anything?

The important part is in the last paragraph:

> > If we choose the transaction_timestamp to be >= t_S, then
> > transaction_timestamp(e) < t_C(e) < t_S(c) <= transaction_timestamp(c)
> > and therefore
> > transaction_timestamp(e) < transaction_timestamp(c)

In PostgreSQL, transaction_timestamp is taken during BEGIN (as Greg
noted). If it was instead taken at the end of the snapshot, it would be
guaranteed to be later than any transaction_timestamp of an earlier
transaction.

Again, I'm not arguing for such a change, but I'm wondering if recording
transaction_timestamp just after the snapshot might be a safe change or
whether that might break some assumption that programmers can currently
make.

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!"