Re: Commit Sequence Numbers and Visibility

On Tue, 2026-06-02 at 16:33 -0700, Jeff Davis wrote:
> For instance, let's say that some sync transaction T1 that writes a
> commit record with LSN 122 but has not flushed yet. Then there's an
> async transaction T2 that writes a commit record with LSN 123, then
> finishes and releases locks. Then sync transaction T3 takes a
> snapshot, which must include T2 due to constraint (c), and therefore
> also includes T1 because the commit LSN is less than that of T2. If
> T3
> uses the snapshot right away, that would mean a sync transaction T3
> is
> reading the changes of another sync transaction T1 that hasn't
> flushed
> yet, which is broken.

This is the mixed-durability mode problem with CSNs. It's only a
problem if different transactions have different settings for
synchronous_commit. (Or, if you cancel a sync rep transaction, which
effectively changes its durability setting to 'local'.)

There are a few resolutions to this problem, each of which might be
useful in some workload. Those who are happy with the current semantics
would choose 1, while other options offer more strict CSN semantics but
have some cost.

1. (proposed default) Offer true CSN order for all transactions within
a durability level, but allow out-of-order visibility between
transactions of different durability levels. Only transactions that
haven't met their durability requirement would be excluded (much like
today) and later snapshots would always be a superset of prior
snapshots. For instance, T3's snapshot would see T2 but not T1, even
though T2's commit LSN is later. Even in mixed-durability mode, these
are better semantics than are offered today. No additional waits for
readers or writers.

2. Return potentially-unflushed data in mixed durability mode. Offers
true CSN semantics always; but a transaction's data may be returned to
the client even if it hasn't met the appropriate durability level. For
instance, T3 could return T1's data to the client before T1 is flushed.

3. Reader waits until all transactions in the snapshot have reached the
appropriate durability level. For instance, T3 would wait until T1 has
reached the appropriate durability level before using the snapshot.

4. Writer waits until all transactions with an earlier commit LSN have
reached the appropriate durability level before releasing locks. For
instance, T2 would not release locks until T1 has reached the
appropriate durability level, making the above scenario impossible.

These options are mostly orthogonal to the question of whether "the
appropriate durability level" means that of the writer, the reader, or
both. (Except #4, which only works with the writer's durability
setting.)

Each of these could be controlled with a GUC, and none have a major
impact on the design. (Except perhaps #3.)

Regards,
Jeff Davis