From 8afb34c037e0b4d5e1fd77d8f31394f479a5aab8 Mon Sep 17 00:00:00 2001 From: Thomas Munro Date: Thu, 11 Jun 2020 17:29:08 +1200 Subject: [PATCH v2] Doc: Add references for SI and SSI. Our documentation failed to point out that REPEATABLE READ is really SNAPSHOT ISOLATION, which might be important to some users. Point to the standard reference paper for this complicated topic. Likewise, add a reference to the VLDB paper about PostgreSQL SSI, which for those interested in reading about the differences between SSI and S2PL, and further papers referenced from there. While here, add a note about catalog access using a lower isolation level, per recent user complaint. Reported-by: Kyle Kingsbury Reviewed-by: Kyle Kingsbury Reviewed-by: Andres Freund Discussion: https://postgr.es/m/db7b729d-0226-d162-a126-8a8ab2dc4443%40jepsen.io Discussion: https://postgr.es/m/16454-9408996bb1750faf%40postgresql.org --- doc/src/sgml/biblio.sgml | 54 ++++++++++++++++++++++++++++++++++++++++ doc/src/sgml/mvcc.sgml | 32 ++++++++++++++++++++++++ 2 files changed, 86 insertions(+) diff --git a/doc/src/sgml/biblio.sgml b/doc/src/sgml/biblio.sgml index b40c06eb38..e1ea912106 100644 --- a/doc/src/sgml/biblio.sgml +++ b/doc/src/sgml/biblio.sgml @@ -230,6 +230,60 @@ ssimkovi@ag.or.at Proceedings and Articles + + <ulink url="https://arxiv.org/pdf/1208.4179">Serializable Snapshot Isolation in PostgreSQL</ulink> + + + Dan + Ports + + + Kevin + Grittner + + + + Proceedings of the VLDB Endowment (PVLDB) + + Vol. 5, No. 12, pp. 1850-1861 + 2012 + + + + <ulink url="https://www.microsoft.com/en-us/research/wp-content/uploads/2016/02/tr-95-51.pdf">A Critique of ANSI SQL Isolation Levels</ulink> + + + Hal + Berenson + + + Phil + Bernstein + + + Jim + Gray + + + Jim + Melton + + + Elizabeth + O'Neil + + + Patrick + O'Neil + + + MSR-TR-95-51 + + Microsoft Research + + June 1995 + + Partial indexing in POSTGRES: research project diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml index a826f2b4e4..e5e111690c 100644 --- a/doc/src/sgml/mvcc.sgml +++ b/doc/src/sgml/mvcc.sgml @@ -535,6 +535,19 @@ ERROR: could not serialize access due to concurrent update of explicit locks to block conflicting transactions. + + The Repeatable Read isolation level is implemented using a technique + known in academic database literature and in some other database products + as Snapshot Isolation. Differences in behavior + and performance may be observed when compared with systems using a more + traditional technique that permits less concurrency, and some other + systems may even offer Repeatable Read and Snapshot Isolation as distinct + isolation levels. The allowable phenomena that can be used to distinguish + the two techniques were not formalized by database researchers until after + the SQL Standard was developed, and are outside the scope of this manual. + For a full treatment, please see . + + Prior to PostgreSQL version 9.1, a request @@ -783,6 +796,15 @@ ERROR: could not serialize access due to read/write dependencies among transact + + + The Serializable isolation level is implemented using a technique + known in academic database literature as Serializable Snapshot Isolation, + which builds on Snapshot Isolation by adding checks for serialization + anomalies. Some differences in behavior and performance may be observed + when compared with systems using other techniques. Please see + for detailed information. + @@ -1726,6 +1748,16 @@ SELECT pg_advisory_lock(q.id) FROM see a transient state that is inconsistent with any serial execution of the transactions on the master. + + + Internal access to the system catalogs is not done using the isolation + level of the current transaction. This means that newly created + database objects such as tables are visible to concurrent Repeatable Read + and Serializable transactions, even though the rows they contain are not. + In contrast, queries that explicitly examine the system catalogs don't + see rows representing concurrently created database objects in the higher + isolation levels. + -- 2.20.1