(sorry to the CCs for sending this twice) This example has been pointed out as confusing in multiple places, e.g.:
* https://postgrespro.com/list/thread-id/1845118 * https://dba.stackexchange.com/a/43951 * https://stackoverflow.com/a/50733640 ("this paragraph is dark") As mentioned in those links, it seems to attempt to summarize the wiki's example at https://wiki.postgresql.org/wiki/SSI#Deposit_Report . I'm not sure if/how to link to the wiki from the docs, so here is my humble attempt to summarize it concisely and (hopefully) clearly. Alternatively, this example could be removed in favor of a reference to the Serializable section and its example.
From 13007ea36fa44bd80e595fb89deddfb6184e96d1 Mon Sep 17 00:00:00 2001 From: Will Mortensen <w...@extrahop.com> Date: Wed, 1 Jun 2022 23:14:19 -0700 Subject: [PATCH 4/6] doc: clarify example of serialization anomaly This example has been pointed out as confusing in multiple places, e.g.: * https://postgrespro.com/list/thread-id/1845118 * https://dba.stackexchange.com/a/43951 * https://stackoverflow.com/a/50733640 ("this paragraph is dark") As mentioned in those links, it seems to attempt to summarize the wiki's example at https://wiki.postgresql.org/wiki/SSI#Deposit_Report . I'm not sure if/how to link to the wiki from the docs, so try again to summarize it concisely and (hopefully) clearly. Alternatively, this example could be removed in favor of a reference to the Serializable section and its example. --- doc/src/sgml/mvcc.sgml | 33 +++++++++++++++++++++++---------- 1 file changed, 23 insertions(+), 10 deletions(-) diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml index 1382504fa9..029c25232c 100644 --- a/doc/src/sgml/mvcc.sgml +++ b/doc/src/sgml/mvcc.sgml @@ -571,16 +571,29 @@ ERROR: could not serialize access due to concurrent update <para> The Repeatable Read mode provides a rigorous guarantee that each - transaction sees a completely stable view of the database. However, - this view will not necessarily always be consistent with some serial - (one at a time) execution of concurrent transactions of the same level. - For example, even a read-only transaction at this level may see a - control record updated to show that a batch has been completed but - <emphasis>not</emphasis> see one of the detail records which is logically - part of the batch because it read an earlier revision of the control - record. Attempts to enforce business rules by transactions running at - this isolation level are not likely to work correctly without careful use - of explicit locks to block conflicting transactions. + transaction sees a completely stable view of the database. However, even + for a read-only transaction, this view will not necessarily always be + consistent with some serial (one at a time) execution of concurrent + transactions of the same level. Therefore, attempts to enforce business + rules by transactions running at this isolation level are not likely to work + correctly without careful use of explicit locks to block conflicting + transactions. + </para> + + <para> + For example, imagine a system where records are organized by a "batch ID" + column, and a transaction T1 "closes" a batch by updating a separate control + record to indicate that new records should use a new batch ID. Meanwhile, + another transaction T2 starts before T1 commits, reads the old version of + the control record, inserts records with the old batch ID, and commits after + T1 commits. Finally, a read-only transaction T3 starts after T1 commits but + before T2 commits, and sees the control record with the new batch ID + indicating that the previous batch was closed, but can't see T2's records. + This is a serialization anomaly among the three transactions. Similarly, a + serialization anomaly would arise between just T1 and T2 if T1 attempted to + compute an aggregation over all the records in the previous batch. These + anomalies are not detected at the Repeatable Read level, but would be + detected at the Serializable level. </para> <para> -- 2.25.1
