I've learned about this anomaly from these papers: https://www.cs.umb.edu/~poneil/ROAnom.pdf (original) https://johann.schleier-smith.com/blog/2016/01/06/analyzing-a-read-only-transaction-anomaly-under-snapshot-isolation.html (short and easy explanation)
Can somebody please explain why is this considered an anomaly? AFAIK, a read-only transaction should see committed changes at the time of its execution, so the result is correct.
Why is it considered an anomaly? The answer is quoted in your links (emphasis added):
Starting with [BBGMOO95], it was assumed that read-only transactions always execute serializably, without ever needing to wait or abort because of concurrent update transactions. This seemed self-evident because all reads take place at an instant of time, when all committed transactions have completed their writes and no writes of non-committed transactions are visible.
Aside from that surprise, the behaviour is as expected for snapshot isolation, as you say.
But it isn't really a surprise: Snapshot isolation is not serializable isolation (links to my articles, with a SQL Server focus).
Postgres blurred the boundaries with their implementation, Serializable Snapshot Isolation. They claim serializability, and that seems reasonable to me from the brief research I did on it. The second link in your question claims to have found a low error rate, but that likely arises from retrying failed transactions.
That snapshot is not serializable with writes is well-known, for example:
create table a ( x int );
create table b ( x int );
-- Session 1 (snapshot)
insert into a select count(*) from b
-- Session 2 (snapshot, concurrently)
insert into b select count(*) from a
Snapshot isolation allows both queries to return zero — a result that is clearly not possible in any serial schedule.
Yes it's arguable whether this is a deficiency in the isolation behavior, or simply a bug how the transactions are written. This is one of the patterns where SELECT ... FOR UPDATE or (UPDLOCK in SQL Server) is manditory.
In MVRC transactions must sometimes read the "current" version of a row, using a lock, instead of the "consistent" version provided by row versioning. This is generally necessary whenever the transaction is going to increment or decrement an existing value on the row.
In the example scenario if the first transaction (TXN2) read FOR UPDATE it would block second reader (TXN1) FOR UPDATE until it commits, and TXN3 would read the pre-TXN2 versions of both rows, avoiding the "anomaly".
