On Mon, Dec 23, 2024 at 3:17 PM David G. Johnston <david.g.johns...@gmail.com> wrote:
> The quoted section describes how two consecutive select queries will see the > same data. Your example shows how a single query behaves in isolation. The > “as the first query saw it” is fundamentally important since until it > successfully executes 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-time read. 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 documentation is 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 the reader. 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
