From 13007ea36fa44bd80e595fb89deddfb6184e96d1 Mon Sep 17 00:00:00 2001 From: Will Mortensen 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 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 - not 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. + + + + 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. -- 2.25.1