On Mon, Dec 23, 2024 at 3:17 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
> The quoted section describes how two consecutive select queries will see the same data. Your example shows how a
singlequery behaves in isolation. The “as the first query saw it” is fundamentally important since until it
successfullyexecutes there are no locks being held restricting the changing of non-data structural aspects of the
database. In short, the snapshot doesn’t include an object until it is requested. It’s a repeatable read, not a frozen
point-in-timeread. The performance implications for the later would be unacceptable.
>
> Thus, the behavior is expected and needed as-is; but I would say that the concurrency control chapter of the
documentationis one of the harder to actually learn and understand. It is a challenging topic, so I get why. In its
defense,the commentary surrounding the regarding control record and detail does try to make this distinction clear to
thereader. YMMV as to its effectiveness in this regard.
>
> David J.
>
Thank you for your comments.
OK, I agree that there are no contradictions from the point of view of
the source code. But essentially, snapshot is just a range of xids,
and we must not see changes of transactions that started after the
snapshot was taken.
As far as I understand, documentation says that repeatable read
transactions take a snapshot at first non-transaction-control
statement, and this snapshot remains relevant for all statements
within the transaction.
My example shows that the second session sees changes that have been
made by a transaction that started after snapshot creation (and it
sees them only because of cache optimization). It might be unexpected
behavior for users.
I also agree that taking it into account will reduce performance, but
maybe we can clarify this aspect in documentation (?)
--
Best regards,
Daniil Davydov
