Hallo Habr! Mit diesem Artikel beginne ich eine Reihe von Schleifen (oder eine Reihe von Schleifen? Im Allgemeinen eine großartige Idee) über die interne Struktur von PostgreSQL.

Das Material basiert auf Verwaltungsschulungen, die wir mit Pavel pluzanov durchführen . Nicht jeder sieht sich gerne ein Video an (ich mag es definitiv nicht), aber das Lesen von Folien, auch mit Kommentaren, ist völlig "falsch".

Natürlich wiederholen die Artikel den Inhalt der Kurse nicht eins zu eins. Ich werde nur darüber sprechen, wie alles funktioniert, wobei die Verwaltung selbst weggelassen wird, aber ich werde versuchen, dies detaillierter und detaillierter zu tun. Und ich glaube, dass dieses Wissen für den Anwendungsentwickler nicht weniger nützlich ist als für den Administrator.

Ich werde mich auf diejenigen konzentrieren, die bereits Erfahrung mit PostgreSQL haben, und mir zumindest allgemein vorstellen, was passiert. Für Anfänger wird der Text etwas schwer sein. Zum Beispiel werde ich kein Wort darüber sagen, wie man PostgreSQL installiert und psql ausführt.

Dinge, die besprochen werden, ändern sich nicht viel von Version zu Version, aber ich werde das aktuelle 11. "Vanilla" PostgreSQL verwenden.

Der erste Zyklus ist Fragen im Zusammenhang mit Isolation und Multiversion gewidmet und hat folgenden Plan:

1. Isolation im Sinne von Standard und PostgreSQL (dieser Artikel);
2. Ebenen, Dateien, Seiten - was passiert auf physischer Ebene?
3. Zeilenversionen, virtuelle und verschachtelte Transaktionen ;
4. Datenschnappschüsse und Sichtbarkeit von Zeilenversionen, Ereignishorizont ;
5. In-Page-Reinigung und HOT-Updates ;
6. Normale Reinigung (Vakuum);
7. Automatische Reinigung (Autovakuum);
8. Überlauf und Einfrieren des Transaktionszählers .

Nun, lass uns gehen.

Was ist Isolierung und warum ist sie wichtig?

Wahrscheinlich weiß zumindest jeder über die Existenz von Transaktionen Bescheid, traf das Akronym ACID und hörte von Isolationsstufen. Man muss sich aber immer noch der Meinung stellen, dass dies eine Theorie ist, die in der Praxis nicht notwendig ist. Daher werde ich einige Zeit damit verbringen, zu erklären, warum dies wirklich wichtig ist.

Es ist unwahrscheinlich, dass Sie sich freuen werden, wenn die Anwendung falsche Daten aus der Datenbank empfängt oder wenn die Anwendung falsche Daten in die Datenbank schreibt.

Aber was sind "richtige" Daten? Es ist bekannt, dass Sie auf Datenbankebene Integritätsbeschränkungen erstellen können (z. B. NOT NULL oder UNIQUE). Wenn die Daten immer die Integritätsbedingungen erfüllen (und dies liegt daran, dass das DBMS dies garantiert), sind sie ganzheitlich.

Sind richtig und ganzheitlich - dasselbe? Nicht wirklich. Nicht alle Einschränkungen können auf Datenbankebene formuliert werden. Ein Teil der Einschränkungen ist zu kompliziert, z. B. werden mehrere Tabellen gleichzeitig abgedeckt. Und selbst wenn die Einschränkung im Prinzip in der Datenbank definiert werden könnte, aber aus irgendeinem Grund nicht, bedeutet dies nicht, dass sie verletzt werden kann.

Korrektheit ist also strenger als Integrität , aber wir wissen nicht genau, was es ist. Es bleibt abzuwarten, dass der Standard der Korrektheit eine Anwendung ist, die, wie wir glauben wollen, korrekt geschrieben ist und niemals falsch ist. In jedem Fall wird das DBMS nichts davon wissen und seine Hand nicht fangen, wenn die Anwendung nicht die Integrität, sondern die Richtigkeit verletzt.

Von nun an nennen wir Korrektheit den Begriff Konsistenz.

Nehmen wir jedoch an, dass die Anwendung nur die richtige Reihenfolge von Anweisungen ausführt. Welche Rolle spielt das DBMS dann, wenn die Anwendung korrekt ist?

Erstens stellt sich heraus, dass eine korrekte Folge von Anweisungen die Datenkonsistenz vorübergehend stören kann, und dies ist - seltsamerweise - normal. Ein abgedroschenes, aber verständliches Beispiel ist die Überweisung von Geldern von einem Konto auf ein anderes. Die Konsistenzregel mag folgendermaßen klingen: Eine Überweisung ändert niemals den Gesamtbetrag auf Konten (eine solche Regel ist als Integritätsbedingung in SQL nur schwer zu schreiben, daher existiert sie auf Anwendungsebene und ist für das DBMS unsichtbar). Eine Überweisung besteht aus zwei Vorgängen: Der erste reduziert das Guthaben auf einem Konto, der zweite erhöht sich auf einem anderen. Die erste Operation verletzt die Datenkonsistenz, die zweite - stellt wieder her.

Eine gute Übung besteht darin, die oben beschriebene Regel auf der Ebene der Integritätsbeschränkungen zu implementieren. Bist du schwach ©

Was ist, wenn die erste Operation abgeschlossen ist und die zweite nicht? Immerhin ist es einfach: Während der zweiten Operation kann Strom verloren gehen, der Server kann herunterfallen, es kann eine Division durch Null auftreten - aber Sie wissen es nie. Es ist klar, dass die Konsistenz verletzt wird, und dies sollte nicht erlaubt sein. Grundsätzlich ist es möglich, solche Situationen auf Anwendungsebene auf Kosten eines unglaublichen Aufwands zu lösen, aber zum Glück ist dies nicht erforderlich: Das DBMS kümmert sich darum. Dafür muss sie jedoch wissen, dass zwei Operationen ein unteilbares Ganzes bilden. Das ist eine Transaktion .

Es stellt sich als interessant heraus: Da das DBMS weiß, dass Operationen eine Transaktion darstellen, hilft es, die Konsistenz aufrechtzuerhalten, indem es die Atomizität von Transaktionen garantiert, ohne etwas über bestimmte Konsistenzregeln zu wissen.

Aber es gibt noch einen zweiten, subtileren Punkt. Sobald mehrere gleichzeitige Transaktionen im System erscheinen, die nacheinander absolut korrekt sind, können sie zusammen falsch funktionieren. Dies liegt an der Tatsache, dass die Reihenfolge der Operationen gemischt ist: Es kann nicht davon ausgegangen werden, dass alle Operationen einer Transaktion zuerst ausgeführt werden und erst dann alle Operationen einer anderen.

Ein Hinweis zur Gleichzeitigkeit. Gleichzeitig können Transaktionen auf einem System mit einem Mehrkernprozessor, einem Festplattenarray usw. ausgeführt werden. Für einen Server, der Befehle nacheinander im Time-Sharing-Modus ausführt, gelten jedoch dieselben Überlegungen: So viele Zyklen, eine Transaktion wird ausgeführt, so viele Zyklen sind unterschiedlich . Manchmal wird der Begriff Wettbewerbsausführung verwendet, um zusammenzufassen.

Situationen, in denen korrekte Transaktionen nicht korrekt zusammenarbeiten, werden als gleichzeitige Ausführungsanomalien bezeichnet.

Ein einfaches Beispiel: Wenn eine Anwendung die richtigen Daten aus der Datenbank abrufen möchte, sollte sie zumindest keine Änderungen bei anderen nicht festgeschriebenen Transaktionen sehen. Andernfalls können Sie nicht nur inkonsistente Daten abrufen, sondern auch etwas sehen, das noch nie in der Datenbank vorhanden war (wenn die Transaktion abgebrochen wird). Diese Anomalie wird als schmutziges Lesen bezeichnet .

Wenn es andere, komplexere Anomalien gibt, auf die wir etwas später eingehen werden.

Natürlich ist es unmöglich, die gleichzeitige Ausführung abzulehnen. Welche Art von Aufführung kann sonst diskutiert werden? Sie können jedoch nicht mit falschen Daten arbeiten.

Und wieder kommt das DBMS zur Rettung. Sie können Transaktionen wie nacheinander ausführen, als ob sie nacheinander ausgeführt würden. Mit anderen Worten, isoliert voneinander. In der Realität kann das DBMS Operationen gemischt ausführen, gleichzeitig aber sicherstellen, dass das Ergebnis der gleichzeitigen Ausführung mit dem Ergebnis einer der möglichen sequentiellen Ausführungen übereinstimmt. Und das beseitigt mögliche Anomalien.

Also kommen wir zur Definition:

Eine Transaktion ist der Satz von Operationen, die von einer Anwendung ausgeführt werden, die die Datenbank von einem korrekten Zustand in einen anderen korrekten Zustand überträgt (Konsistenz), vorausgesetzt, die Transaktion ist vollständig (Atomizität) und ohne Störung durch andere Transaktionen (Isolation).

Diese Definition kombiniert die ersten drei Buchstaben des Akronyms ACID. Sie sind so eng miteinander verwandt, dass es einfach keinen Sinn macht, eins ohne das andere zu betrachten. Tatsächlich ist es schwierig, den Buchstaben D (Haltbarkeit) abzureißen. Schließlich bleiben im Falle eines Systemabsturzes Änderungen an nicht festgeschriebenen Transaktionen darin, mit denen Sie etwas tun müssen, um die Datenkonsistenz wiederherzustellen.

Alles wäre in Ordnung, aber die Implementierung einer vollständigen Isolierung ist eine technisch schwierige Aufgabe, verbunden mit einer Verringerung des Systemdurchsatzes. Daher wird in der Praxis sehr oft (nicht immer, aber fast immer) eine geschwächte Isolierung angewendet, die einige, aber nicht alle Anomalien verhindert. Dies bedeutet, dass ein Teil der Arbeit zur Sicherstellung der Richtigkeit der Daten auf die Anwendung fällt. Aus diesem Grund ist es sehr wichtig zu verstehen, welche Isolationsstufe im System verwendet wird, welche Garantien es gibt und welche nicht und wie der richtige Code unter solchen Bedingungen geschrieben wird.

SQL-Isolationsstufen und Anomalien

Der SQL-Standard hat lange Zeit vier Isolationsstufen beschrieben. Diese Ebenen werden durch Auflisten der Anomalien ermittelt, die bei der Ausführung von Transaktionen auf dieser Ebene zulässig oder nicht zulässig sind. Um über diese Ebenen zu sprechen, müssen Sie sich daher mit den Anomalien vertraut machen.

Ich betone, dass wir in diesem Teil über den Standard sprechen, dh über eine bestimmte Theorie, auf die sich die Praxis stark stützt, die aber gleichzeitig im Widerspruch steht. Daher sind alle Beispiele hier spekulativ. Sie werden die gleichen Operationen auf Kundenkonten anwenden: Dies ist ziemlich offensichtlich, obwohl es zugegebenermaßen nichts damit zu tun hat, wie die Bankoperationen tatsächlich angeordnet sind.

Update verloren

Beginnen wir mit dem verlorenen Update . Diese Anomalie tritt auf, wenn zwei Transaktionen dieselbe Zeile in der Tabelle lesen, dann eine Transaktion diese Zeile aktualisiert und danach auch die zweite Transaktion dieselbe Zeile aktualisiert, ohne die durch die erste Transaktion vorgenommenen Änderungen zu berücksichtigen.

Beispielsweise erhöhen zwei Transaktionen den Betrag auf demselben Konto um 100 ₽. Die erste Transaktion liest den aktuellen Wert (1000 ₽), dann liest die zweite Transaktion den gleichen Wert. Die erste Transaktion erhöht den Betrag (es stellt sich heraus, 1100 ₽) und schreibt diesen Wert. Die zweite Transaktion macht das Gleiche - bekommt die gleichen 1.100 ₽ und schreibt sie. Infolgedessen verlor der Kunde 100 ₽.

Verlorene Updates sind vom Standard auf keiner Isolationsstufe zulässig.

Dirty Reading und Read Uncommitted

Mit schmutzigem Lesen haben wir uns oben schon getroffen. Diese Anomalie tritt auf, wenn eine Transaktion ausstehende Änderungen liest, die von einer anderen Transaktion vorgenommen wurden.

Bei der ersten Transaktion wird beispielsweise das gesamte Geld vom Konto des Kunden auf ein anderes Konto übertragen, die Änderung wird jedoch nicht erfasst. Eine andere Transaktion liest den Kontostatus, erhält 0 ₽ und weigert sich, Bargeld an den Kunden auszugeben - trotz der Tatsache, dass die erste Transaktion unterbrochen wird und ihre Änderungen abbricht, sodass der Wert 0 nie in der Datenbank vorhanden war.

Das fehlerhafte Lesen ist nach dem Standard auf der Ebene "Nicht festgeschrieben" zulässig.

Nicht wiederholtes Lesen und Lesen festgeschrieben

Die nicht wiederholte Leseanomalie tritt auf, wenn eine Transaktion dieselbe Zeile zweimal liest und in dem Intervall zwischen den Lesevorgängen die zweite Transaktion diese Zeile ändert (oder löscht) und die Änderungen festschreibt. Dann erhält die erste Transaktion unterschiedliche Ergebnisse.

Lassen Sie beispielsweise die Konsistenzregel negative Beträge in Kundenkonten verbieten . Bei der ersten Transaktion wird der Betrag auf dem Konto um 100 reduce reduziert. Sie überprüft den aktuellen Wert, erhält 1000 ₽ und entscheidet, dass eine Reduzierung möglich ist. Zu diesem Zeitpunkt reduziert die zweite Transaktion den Betrag auf dem Konto auf Null und zeichnet die Änderungen auf. Wenn nun die erste Transaktion den Betrag erneut prüfte, würde sie 0 ₽ erhalten (aber sie hatte bereits beschlossen, den Wert zu verringern, und das Konto geht auf Minus).

Nicht wiederholtes Lesen ist nach dem Standard auf den Ebenen Read Uncommitted und Read Committed zulässig. Aber schmutziges Lesen Read Committed erlaubt nicht.

Phantom Read und wiederholbares Lesen

Das Phantomlesen tritt auf, wenn eine Transaktion einen Satz von Zeilen unter derselben Bedingung zweimal liest, und in dem Intervall zwischen den Lesevorgängen fügt die zweite Transaktion Zeilen hinzu, die diese Bedingung erfüllen (und die Änderungen festschreiben). Dann erhält die erste Transaktion verschiedene Zeilensätze.

Angenommen, eine Konsistenzregel verhindert, dass ein Kunde mehr als drei Konten hat . Die erste Transaktion eröffnet ein neues Konto, überprüft die aktuelle Nummer (z. B. 2) und entscheidet, dass eine Eröffnung möglich ist. Zu diesem Zeitpunkt eröffnet die zweite Transaktion auch ein neues Konto für den Kunden und zeichnet die Änderungen auf. Wenn nun die erste Transaktion die Menge doppelt prüft, erhält sie 3 (aber sie eröffnet bereits ein anderes Konto und der Kunde hat 4 davon).

Das Lesen von Phantomen ist standardmäßig in den Stufen Read Uncommitted, Read Committed und Repeatable Read zulässig. Auf der Ebene Wiederholbares Lesen ist nicht wiederholtes Lesen jedoch nicht zulässig.

Mangel an Anomalien und serialisierbar

Der Standard definiert eine andere Ebene - serialisierbar - auf der keine Anomalien zulässig sind. Und dies ist keineswegs gleichbedeutend mit einem Verbot eines verlorenen Updates und einem schmutzigen, sich nicht wiederholenden und Phantom-Lesen.

Tatsache ist, dass es deutlich mehr bekannte Anomalien als die im Standard aufgeführten gibt und eine unbekannte Anzahl noch unbekannt ist.

Serialisierbar sollte generell alle Anomalien verhindern. Dies bedeutet, dass der Anwendungsentwickler auf dieser Ebene nicht daran denken muss, gleichzeitig ausgeführt zu werden. Wenn Transaktionen die richtigen Abfolgen von Anweisungen ausführen und alleine arbeiten, stimmen die Daten mit dem gleichzeitigen Betrieb dieser Transaktionen überein.

Zusammenfassungsschild

Jetzt können Sie jedem einen bekannten Tisch bringen. Aus Gründen der Übersichtlichkeit wird hier jedoch die letzte Spalte hinzugefügt, die nicht im Standard enthalten ist.

Warum genau diese Anomalien?

Warum sind nur einige der vielen möglichen Anomalien im Standard aufgeführt und warum sind dies?

Anscheinend scheint das niemand mit Sicherheit zu wissen. Aber die Praxis hier hat definitiv die Theorie überholt, so dass es möglich ist, dass wir dann nicht über andere Anomalien nachgedacht haben (Rede über den SQL-Standard: 92).

Außerdem wurde angenommen, dass die Isolierung auf Verriegelungen aufgebaut sein sollte. Die Idee des weit verbreiteten Zwei-Phasen-Blockierungsprotokolls (2PL) ist, dass die Transaktion während der Transaktion die Leitungen blockiert, mit denen sie arbeitet, und nach Abschluss die Sperren aufhebt. Je mehr Sperren eine Transaktion erfasst, desto besser ist sie von anderen Transaktionen isoliert. Die Leistung des Systems leidet jedoch noch mehr, da Transaktionen nicht mehr zusammenarbeiten, sondern für dieselben Zeilen ausgerichtet werden.

Es scheint mir, dass der Unterschied zwischen den Isolationsstufen des Standards genau durch die Anzahl der erforderlichen Sperren erklärt wird.

Wenn eine Transaktion das Ändern, aber nicht das Lesen geänderter Zeilen blockiert, erhalten wir die Stufe "Nicht festgeschrieben lesen": Verlorene Änderungen sind nicht zulässig, aber nicht festgeschriebene Daten können gelesen werden.

Wenn die Transaktion das Lesen und Ändern von veränderlichen Zeilen blockiert, erhalten wir die Stufe Read Committed: Sie können keine nicht festgeschriebenen Daten lesen, aber wenn Sie erneut auf die Zeile zugreifen, können Sie einen anderen Wert erhalten (nicht wiederholtes Lesen).

Wenn eine Transaktion sowohl lesbare als auch veränderbare Zeilen vom Lesen und Ändern blockiert, erhalten wir die Stufe Wiederholbares Lesen: Durch wiederholtes Lesen der Zeile wird der gleiche Wert erzeugt.

Bei Serializable gibt es jedoch ein Problem: Es ist nicht möglich, eine noch nicht vorhandene Zeile zu sperren. Aus diesem Grund bleibt die Möglichkeit des Phantomlesens bestehen: Eine andere Transaktion kann eine Zeile hinzufügen (aber nicht löschen), die unter die Bedingungen einer zuvor ausgeführten Abfrage fällt, und diese Zeile wird erneut abgerufen.

Daher reichen gewöhnliche Sperren nicht aus, um die Ebene der Serialisierbarkeit zu implementieren. Sie müssen nicht die Zeilen, sondern die Bedingungen (Prädikate) blockieren. Solche Sperren wurden Prädikate genannt . Sie wurden bereits 1976 vorgeschlagen, aber ihre praktische Anwendbarkeit wird durch relativ einfache Bedingungen eingeschränkt, für die klar ist, wie zwei verschiedene Prädikate kombiniert werden können. Soweit ich weiß, ist es in keinem System zur Implementierung solcher Sperren gekommen.

PostgreSQL-Isolationsstufen

Im Laufe der Zeit ersetzte Snapshot Isolation die blockierenden Transaktionsverwaltungsprotokolle. Seine Idee ist, dass jede Transaktion zu einem bestimmten Zeitpunkt mit einem konsistenten Snapshot der Daten arbeitet, bei dem nur die Änderungen fallen, die vor der Erstellung des Snapshots aufgezeichnet wurden.

Eine solche Isolierung ermöglicht nicht automatisch ein schmutziges Lesen. Formal können Sie in PostgreSQL die Stufe Read Uncommitted angeben, dies funktioniert jedoch genauso wie Read Committed. Daher werden wir nicht weiter über die Stufe "Nicht festgeschrieben lesen" sprechen.

PostgreSQL implementiert eine Mehrfachversion dieses Protokolls. Die Idee der Mehrfachversionierung besteht darin, dass mehrere Versionen derselben Zeichenfolge in einem DBMS gleichzeitig vorhanden sein können. Auf diese Weise können Sie mit den verfügbaren Versionen einen Snapshot der Daten erstellen und mit einem Minimum an Sperren auskommen. Tatsächlich werden nur wiederholte Änderungen an derselben Zeile blockiert. Alle anderen Vorgänge werden gleichzeitig ausgeführt: Das Schreiben von Transaktionen blockiert niemals das Lesen von Transaktionen, und das Lesen von Transaktionen blockiert niemals jemanden.

Bei Verwendung von Daten-Snapshots ist die Isolation in PostgreSQL strenger als es der Standard erfordert: Die Stufe "Wiederholbares Lesen" ermöglicht nicht nur nicht wiederholte, sondern auch Phantom-Lesevorgänge (obwohl sie keine vollständige Isolation bietet). Und das ohne Wirkungsverlust.

Wie Multi-Versioning „unter der Haube“ implementiert wird, werden wir in den folgenden Artikeln besprechen und nun jede der drei Ebenen mit den Augen des Benutzers detailliert betrachten (wie Sie wissen, verbirgt sich das Interessanteste hinter „anderen Anomalien“). Erstellen Sie dazu eine Kontenliste. Alice und Bob haben jeweils 1.000 US-Dollar, aber Bob hat zwei offene Konten:

=> CREATE TABLE accounts( id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, number text UNIQUE, client text, amount numeric ); => INSERT INTO accounts VALUES (1, '1001', 'alice', 1000.00), (2, '2001', 'bob', 100.00), (3, '2002', 'bob', 900.00); 

Lesen Sie verpflichtet

Mangel an schmutzigem Lesen

Es ist leicht zu überprüfen, ob verschmutzte Daten nicht gelesen werden können. Beginnen wir mit der Transaktion. Standardmäßig wird die Isolationsstufe Read Committed verwendet:

 => BEGIN; => SHOW transaction_isolation; 

  transaction_isolation ----------------------- read committed (1 row) 

Genauer gesagt wird der Standardpegel durch den Parameter festgelegt und kann bei Bedarf geändert werden:

 => SHOW default_transaction_isolation; 

  default_transaction_isolation ------------------------------- read committed (1 row) 

Bei einer offenen Transaktion ziehen wir also Geld vom Konto ab, erfassen die Änderungen jedoch nicht. Die Transaktion sieht ihre eigenen Änderungen:

 => UPDATE accounts SET amount = amount - 200 WHERE id = 1; => SELECT * FROM accounts WHERE client = 'alice'; 

  id | number | client | amount ----+--------+--------+-------- 1 | 1001 | alice | 800.00 (1 row) 

In der zweiten Sitzung starten wir eine weitere Transaktion mit derselben Read Committed-Ebene. Um zwischen verschiedenen Transaktionen zu unterscheiden, werden die Befehle der zweiten Transaktion eingerückt und durchgestrichen.

Um die obigen Befehle zu wiederholen (was nützlich ist), müssen Sie zwei Terminals öffnen und jeweils psql ausführen. Im ersten können Sie die Befehle einer Transaktion eingeben und im zweiten die Befehle einer anderen.

 | => BEGIN; | => SELECT * FROM accounts WHERE client = 'alice'; 

 | id | number | client | amount | ----+--------+--------+--------- | 1 | 1001 | alice | 1000.00 | (1 row) 

Wie erwartet werden bei einer anderen Transaktion keine nicht festgeschriebenen Änderungen angezeigt - Dirty Reading ist nicht zulässig.

Nicht wiederholtes Lesen

Lassen Sie nun die erste Transaktion die Änderungen festschreiben und die zweite dieselbe Anforderung erneut ausführen.

 => COMMIT; 

 | => SELECT * FROM accounts WHERE client = 'alice'; 

 | id | number | client | amount | ----+--------+--------+-------- | 1 | 1001 | alice | 800.00 | (1 row) 

 | => COMMIT; 

Die Anforderung empfängt bereits neue Daten - dies ist die Anomalie des nicht wiederholten Lesens , die auf der Ebene Read Committed zulässig ist.

Praktische Schlussfolgerung : In einer Transaktion ist es unmöglich, Entscheidungen auf der Grundlage der von der vorherigen Anweisung gelesenen Daten zu treffen, da sich zwischen den Ausführungszeiten der Anweisungen alles ändern kann. Hier ist ein Beispiel, dessen Variationen im Anwendungscode so häufig sind, dass es sich um ein klassisches Antimuster handelt:

  IF (SELECT amount FROM accounts WHERE id = 1) >= 1000 THEN UPDATE accounts SET amount = amount - 1000 WHERE id = 1; END IF; 

Während der Zeit zwischen Überprüfung und Aktualisierung können andere Transaktionen den Status des Kontos wie gewünscht ändern, sodass bei einer solchen „Überprüfung“ nichts gespeichert wird. Man kann sich leicht vorstellen, dass zwischen den Betreibern einer Transaktion alle anderen Betreiber anderer Transaktionen beispielsweise „keilen“ können:

  IF (SELECT amount FROM accounts WHERE id = 1) >= 1000 THEN ----- | UPDATE accounts SET amount = amount - 200 WHERE id = 1; | COMMIT; ----- UPDATE accounts SET amount = amount - 1000 WHERE id = 1; END IF; 

Wenn Sie beim Neuanordnen der Operatoren alles ruinieren können, ist der Code falsch geschrieben. Und täuschen Sie sich nicht, dass eine solche Kombination von Umständen nicht eintreten wird - es wird passieren.

Wie schreibe ich den Code richtig? Chancen beschränken sich in der Regel auf Folgendes:

• Schreiben Sie keinen Code.
  Das ist kein Scherz. In diesem Fall wird die Prüfung beispielsweise leicht zu einer Integritätsbeschränkung:
  ALTER TABLE accounts ADD CHECK amount >= 0;
  Jetzt sind keine Überprüfungen mehr erforderlich: Es reicht aus, die Aktion einfach auszuführen und gegebenenfalls die Ausnahme zu behandeln, die im Falle eines Versuchs, die Integrität zu verletzen, auftritt.
  
• Verwenden Sie eine einzelne SQL-Anweisung.
  Konsistenzprobleme entstehen aufgrund der Tatsache, dass im Intervall zwischen Operatoren eine andere Transaktion enden kann und sich die sichtbaren Daten ändern. Und wenn es nur einen Operator gibt, gibt es keine Lücken.
  PostgreSQL verfügt über genügend Tools, um komplexe Probleme mit einer einzigen SQL-Anweisung zu lösen. Wir beachten die allgemeinen Tabellenausdrücke (CTE), in denen Sie unter anderem die Anweisungen INSERT / UPDATE / DELETE sowie die Anweisung INSERT ON CONFLICT verwenden können, die die Logik "Einfügen und, wenn bereits eine Zeile vorhanden ist, aktualisieren" in einer Anweisung implementiert.
  
• Benutzersperren.
  Der letzte Ausweg besteht darin, manuell eine exklusive Sperre entweder für alle erforderlichen Zeilen (SELECT FOR UPDATE) oder für die gesamte Tabelle (LOCK TABLE) festzulegen. Dies funktioniert immer, negiert jedoch die Vorteile der Mehrfachversionierung: Anstatt gleichzeitig ausgeführt zu werden, wird ein Teil der Vorgänge nacheinander ausgeführt.
  

Inkonsistentes Lesen

Bevor man sich auf die nächste Stufe der Isolation begibt, muss man zugeben, dass nicht alles so einfach ist. Die Implementierung von PostgreSQL ist so, dass andere, weniger bekannte Anomalien berücksichtigt werden, die nicht durch den Standard geregelt sind.

Angenommen, mit der ersten Transaktion wurde Geld von einem Bob-Konto auf ein anderes übertragen:

 => BEGIN; => UPDATE accounts SET amount = amount - 100 WHERE id = 2; 

Zu diesem Zeitpunkt berechnet eine andere Transaktion den Saldo von Bob, wobei die Berechnung in einem Zyklus für alle Konten von Bob durchgeführt wird. Tatsächlich beginnt die Transaktion mit dem ersten Konto (und sieht offensichtlich den vorherigen Status):

 | => BEGIN; | => SELECT amount FROM accounts WHERE id = 2; 

 | amount | -------- | 100.00 | (1 row) 

Zu diesem Zeitpunkt wird die erste Transaktion erfolgreich abgeschlossen:

 => UPDATE accounts SET amount = amount + 100 WHERE id = 3; => COMMIT; 

Und der andere liest den Status des zweiten Kontos (und sieht bereits einen neuen Wert):

 | => SELECT amount FROM accounts WHERE id = 3; 

 | amount | --------- | 1000.00 | (1 row) 

 | => COMMIT; 

Somit erhielt die zweite Transaktion insgesamt 1100 ₽, dh falsche Daten. Dies ist eine Anomalie des inkonsistenten Lesens .

Wie kann man eine solche Anomalie vermeiden, indem man bei Read Committed bleibt? Verwenden Sie natürlich einen Operator. Zum Beispiel so:

  SELECT sum(amount) FROM accounts WHERE client = 'bob'; 

Bisher habe ich argumentiert, dass sich die Sichtbarkeit von Daten nur zwischen Betreibern ändern kann, aber ist das so offensichtlich? Und wenn die Anforderung für eine lange Zeit ausgeführt wird, kann sie einen Teil der Daten in einem Zustand und einen Teil in einem anderen sehen?

Schau es dir an. Eine bequeme Möglichkeit, dies zu tun, besteht darin, eine künstliche Verzögerung in den Operator einzufügen, indem die Funktion pg_sleep aufgerufen wird. Sein Parameter legt die Verzögerungszeit in Sekunden fest.

 => SELECT amount, pg_sleep(2) FROM accounts WHERE client = 'bob'; 

Während dieser Bauarbeiten überweisen wir bei einer anderen Transaktion Geld zurück:

 | => BEGIN; | => UPDATE accounts SET amount = amount + 100 WHERE id = 2; | => UPDATE accounts SET amount = amount - 100 WHERE id = 3; | => COMMIT; 

Das Ergebnis zeigt, dass der Bediener die Daten in dem Zustand sieht, in dem sie sich zum Zeitpunkt des Starts befanden. Das ist sicherlich richtig.

  amount | pg_sleep ---------+---------- 0.00 | 1000.00 | (2 rows) 

Aber hier ist es nicht so einfach. Mit PostgreSQL können Sie Funktionen definieren, während Funktionen das Konzept einer Variabilitätskategorie haben . Wenn eine flüchtige Funktion (mit der Kategorie VOLATILE) in einer Anforderung aufgerufen wird und eine andere Anforderung in dieser Funktion ausgeführt wird, werden bei dieser Anforderung innerhalb der Funktion Daten angezeigt, die nicht mit den Daten der Hauptanforderung übereinstimmen.

 => CREATE FUNCTION get_amount(id integer) RETURNS numeric AS $$ SELECT amount FROM accounts a WHERE a.id = get_amount.id; $$ VOLATILE LANGUAGE sql; 

 => SELECT get_amount(id), pg_sleep(2) FROM accounts WHERE client = 'bob'; 

 | => BEGIN; | => UPDATE accounts SET amount = amount + 100 WHERE id = 2; | => UPDATE accounts SET amount = amount - 100 WHERE id = 3; | => COMMIT; 

In diesem Fall erhalten wir falsche Daten - 100 ₽ gehen verloren:

  get_amount | pg_sleep ------------+---------- 100.00 | 800.00 | (2 rows) 

Ich betone, dass ein solcher Effekt nur auf der Isolationsstufe Read Committed und nur auf der Variabilitätskategorie VOLATILE möglich ist. Das Problem ist, dass diese Isolationsstufe und diese Kategorie von Variabilität standardmäßig verwendet werden, also muss ich zugeben - der Rechen liegt sehr gut. Nicht treten!

Inkonsistentes Lesen im Austausch für verlorene Änderungen

Ein inkonsistenter Messwert im Rahmen eines Operators kann - auf etwas unerwartete Weise - während eines Updates erhalten werden.

Mal sehen, was passiert, wenn Sie versuchen, dieselbe Zeile mit zwei Transaktionen zu ändern. Bob hat jetzt 1000 ₽ auf zwei Konten:

 => SELECT * FROM accounts WHERE client = 'bob'; 

  id | number | client | amount ----+--------+--------+-------- 2 | 2001 | bob | 200.00 3 | 2002 | bob | 800.00 (2 rows) 

Wir starten eine Transaktion, die Bobs Guthaben reduziert:

 => BEGIN; => UPDATE accounts SET amount = amount - 100 WHERE id = 3; 

Gleichzeitig fallen für eine andere Transaktion Zinsen auf alle Kundenkonten mit einem Gesamtguthaben von mindestens 1000 ₽ an:

 | => UPDATE accounts SET amount = amount * 1.01 | WHERE client IN ( | SELECT client | FROM accounts | GROUP BY client | HAVING sum(amount) >= 1000 | ); 

Das Ausführen einer UPDATE-Anweisung besteht aus zwei Teilen. Zunächst wird tatsächlich ein SELECT ausgeführt, das die Zeilen auswählt, die der Bedingung für die Aktualisierung entsprechen. Da die Änderung der ersten Transaktion nicht festgelegt ist, kann die zweite Transaktion sie nicht sehen und hat keinen Einfluss auf die Auswahl der Zeilen für die Zinsberechnung. Bobs Konten fallen also unter die Bedingung und nach Abschluss des Updates sollte sich sein Kontostand um 10 increase erhöhen.

Die zweite Ausführungsstufe - Die ausgewählten Zeilen werden nacheinander aktualisiert. Hier wird die zweite Transaktion zum "Einfrieren" gezwungen, da die Zeilen-ID = 3 bereits von der ersten Transaktion gesperrt ist.

In der Zwischenzeit werden bei der ersten Transaktion die Änderungen festgeschrieben:

 => COMMIT; 

Was wird das Ergebnis sein?

 => SELECT * FROM accounts WHERE client = 'bob'; 

  id | number | client | amount ----+--------+--------+---------- 2 | 2001 | bob | 202.0000 3 | 2002 | bob | 707.0000 (2 rows) 

Ja, einerseits sollte der Befehl UPDATE keine Änderungen in der zweiten Transaktion sehen. Andererseits sollten die in der zweiten Transaktion aufgezeichneten Änderungen nicht verloren gehen.

Nachdem die Sperre aufgehoben wurde, liest UPDATE die Zeile, die aktualisiert werden soll, erneut (aber nur eine!). Das Ergebnis ist, dass Bob 9 ₽ angesammelt hat, basierend auf dem Betrag von 900 ₽. Aber wenn Bob 900 had hätte, hätten seine Konten überhaupt nicht in die Stichprobe aufgenommen werden dürfen.

Die Transaktion empfängt also falsche Daten: Einige der Zeilen sind zu einem bestimmten Zeitpunkt sichtbar, andere zum anderen. Anstelle eines verlorenen Updates erhalten wir erneut eine Anomalie beim inkonsistenten Lesen .

Aufmerksame Leser stellen fest, dass Sie mit Hilfe der Anwendung auf der Ebene "Read Committed" ein verlorenes Update erhalten können. Zum Beispiel so:

  x := (SELECT amount FROM accounts WHERE id = 1); UPDATE accounts SET amount = x + 100 WHERE id = 1; 

Die Datenbank ist nicht schuld: Sie empfängt zwei SQL-Anweisungen und weiß nichts, dass der Wert von x + 100 in irgendeiner Weise mit accounts.amount zusammenhängt. Schreiben Sie keinen Code auf diese Weise.

Wiederholbares Lesen

Mangel an nicht wiederholenden und Phantomablesungen

Der Name der Isolationsstufe selbst zeigt an, dass der Messwert wiederholbar ist. Wir werden dies überprüfen und gleichzeitig davon überzeugt sein, dass keine Phantomwerte vorliegen. Setzen Sie dazu in der ersten Transaktion die Konten von Bob auf den vorherigen Status zurück und erstellen Sie ein neues Konto für Charlie:

 => BEGIN; => UPDATE accounts SET amount = 200.00 WHERE id = 2; => UPDATE accounts SET amount = 800.00 WHERE id = 3; => INSERT INTO accounts VALUES (4, '3001', 'charlie', 100.00); => SELECT * FROM accounts ORDER BY id; 

  id | number | client | amount ----+--------+---------+-------- 1 | 1001 | alice | 800.00 2 | 2001 | bob | 200.00 3 | 2002 | bob | 800.00 4 | 3001 | charlie | 100.00 (4 rows) 

In der zweiten Sitzung starten wir die Transaktion mit der Ebene Wiederholbares Lesen, die im Befehl BEGIN angegeben wird (die Ebene der ersten Transaktion ist nicht wichtig).

 | => BEGIN ISOLATION LEVEL REPEATABLE READ; | => SELECT * FROM accounts ORDER BY id; 

 | id | number | client | amount | ----+--------+--------+---------- | 1 | 1001 | alice | 800.00 | 2 | 2001 | bob | 202.0000 | 3 | 2002 | bob | 707.0000 | (3 rows) 

Jetzt schreibt die erste Transaktion die Änderungen fest und die zweite führt dieselbe Anforderung erneut aus.

 => COMMIT; 

 | => SELECT * FROM accounts ORDER BY id; 

 | id | number | client | amount | ----+--------+--------+---------- | 1 | 1001 | alice | 800.00 | 2 | 2001 | bob | 202.0000 | 3 | 2002 | bob | 707.0000 | (3 rows) 

 | => COMMIT; 

Bei der zweiten Transaktion werden weiterhin genau dieselben Daten wie zu Beginn angezeigt: Es sind weder Änderungen an vorhandenen noch neue Zeilen sichtbar.

Auf dieser Ebene müssen Sie sich keine Sorgen machen, dass sich zwischen den beiden Operatoren etwas ändert.

Serialisierungsfehler im Austausch für verlorene Änderungen

Wir haben oben gesagt, dass beim Aktualisieren derselben Zeile mit zwei Transaktionen auf der Ebene "Read Committed" eine Anomalie des inkonsistenten Lesens auftreten kann. Dies liegt an der Tatsache, dass die ausstehende Transaktion die gesperrte Zeile erneut liest und sie daher nicht zum gleichen Zeitpunkt wie die übrigen Zeilen sieht.

Auf der Ebene "Wiederholbares Lesen" ist eine solche Anomalie nicht zulässig. Wenn sie dennoch auftritt, kann nichts unternommen werden. Daher endet die Transaktion mit einem Serialisierungsfehler. Wir überprüfen dies, indem wir dasselbe Szenario mit Prozentsätzen wiederholen:

 => SELECT * FROM accounts WHERE client = 'bob'; 

  id | number | client | amount ----+--------+--------+-------- 2 | 2001 | bob | 200.00 3 | 2002 | bob | 800.00 (2 rows) 

 => BEGIN; => UPDATE accounts SET amount = amount - 100.00 WHERE id = 3; 

 | => BEGIN ISOLATION LEVEL REPEATABLE READ; | => UPDATE accounts SET amount = amount * 1.01 | WHERE client IN ( | SELECT client | FROM accounts | GROUP BY client | HAVING sum(amount) >= 1000 | ); 

 => COMMIT; 

 | ERROR: could not serialize access due to concurrent update 

 | => ROLLBACK; 

Daten blieben konsistent:

 => SELECT * FROM accounts WHERE client = 'bob'; 

  id | number | client | amount ----+--------+--------+-------- 2 | 2001 | bob | 200.00 3 | 2002 | bob | 700.00 (2 rows) 

Der gleiche Fehler tritt bei jeder anderen Änderung der Wettbewerbszeile auf, auch wenn sich die für uns interessanten Spalten nicht tatsächlich geändert haben.

Praktische Schlussfolgerung : Wenn die Anwendung die Isolationsstufe Wiederholbares Lesen zum Schreiben von Transaktionen verwendet, sollte sie bereit sein, Transaktionen zu wiederholen, die mit einem Serialisierungsfehler endeten. Bei schreibgeschützten Transaktionen ist ein solches Ergebnis nicht möglich.

Inkonsistenter Eintrag

In PostgreSQL werden auf der Isolationsstufe von Repeatable Read alle im Standard beschriebenen Anomalien verhindert. Aber überhaupt nicht. Es stellt sich heraus, dass genau zwei Anomalien möglich bleiben. (Dies gilt nicht nur für PostgreSQL, sondern auch für andere Snapshot-basierte Isolationsimplementierungen.)

Die erste dieser Anomalien ist eine inkonsistente Aufzeichnung .

Lassen Sie diese Konsistenzregel gelten: Negative Beträge sind auf den Konten des Kunden zulässig, wenn der Gesamtbetrag auf allen Konten dieses Kunden nicht negativ bleibt .

Die erste Transaktion erhält den Betrag auf Bobs Konten: 900 ₽.

 => BEGIN ISOLATION LEVEL REPEATABLE READ; => SELECT sum(amount) FROM accounts WHERE client = 'bob'; 

  sum -------- 900.00 (1 row) 

Die zweite Transaktion erhält den gleichen Betrag.

 | => BEGIN ISOLATION LEVEL REPEATABLE READ; | => SELECT sum(amount) FROM accounts WHERE client = 'bob'; 

 | sum | -------- | 900.00 | (1 row) 

Die erste Transaktion geht zu Recht davon aus, dass der Betrag eines der Konten um 600 reduced reduziert werden kann.

 => UPDATE accounts SET amount = amount - 600.00 WHERE id = 2; 

Und die zweite Transaktion kommt zu dem gleichen Ergebnis. Reduziert aber eine andere Punktzahl:

 | => UPDATE accounts SET amount = amount - 600.00 WHERE id = 3; | => COMMIT; 

 => COMMIT; => SELECT * FROM accounts WHERE client = 'bob'; 

  id | number | client | amount ----+--------+--------+--------- 2 | 2001 | bob | -400.00 3 | 2002 | bob | 100.00 (2 rows) 

Wir haben es geschafft, Bobs Saldo auf Minus zu bringen, obwohl jede der Transaktionen einzeln korrekt funktioniert.

Schreibgeschützte Anomalie

Dies ist die zweite und letzte der Anomalien, die auf der Ebene des wiederholbaren Lesens möglich sind. Um dies zu demonstrieren, benötigen Sie drei Transaktionen, von denen zwei die Daten ändern und die dritte - nur lesbar.

Stellen Sie jedoch zuerst den Kontostatus von Bob wieder her:

 => UPDATE accounts SET amount = 900.00 WHERE id = 2; => SELECT * FROM accounts WHERE client = 'bob'; 

  id | number | client | amount ----+--------+--------+-------- 3 | 2002 | bob | 100.00 2 | 2001 | bob | 900.00 (2 rows) 

Bei der ersten Transaktion werden Bob Zinsen für den Geldbetrag auf allen Konten berechnet. Die Zinsen werden einem seiner Konten gutgeschrieben:

 => BEGIN ISOLATION LEVEL REPEATABLE READ; -- 1 => UPDATE accounts SET amount = amount + ( SELECT sum(amount) FROM accounts WHERE client = 'bob' ) * 0.01 WHERE id = 2; 

Dann zieht eine andere Transaktion Geld von einem anderen Konto von Bob ab und erfasst seine Änderungen:

 | => BEGIN ISOLATION LEVEL REPEATABLE READ; -- 2 | => UPDATE accounts SET amount = amount - 100.00 WHERE id = 3; | => COMMIT; 

Wenn zu diesem Zeitpunkt die erste Transaktion festgeschrieben wird, liegt keine Anomalie vor: Wir können davon ausgehen, dass die erste Transaktion zuerst und dann die zweite abgeschlossen wurde (aber nicht umgekehrt, da bei der ersten Transaktion der Status der Konto-ID = 3 vor diesem Konto angezeigt wurde geändert durch die zweite Transaktion).

Angenommen, in diesem Moment beginnt die dritte (schreibgeschützte) Transaktion, die den Status eines Kontos liest, das von den ersten beiden Transaktionen nicht betroffen ist:

 | => BEGIN ISOLATION LEVEL REPEATABLE READ; -- 3 | => SELECT * FROM accounts WHERE client = 'alice'; 

 | id | number | client | amount | ----+--------+--------+-------- | 1 | 1001 | alice | 800.00 | (1 row) 

Und erst danach ist die erste Transaktion abgeschlossen:

 => COMMIT; 

Welchen Status sollte die dritte Transaktion jetzt sehen?

 | SELECT * FROM accounts WHERE client = 'bob'; 

Nach dem Start konnte bei der dritten Transaktion Änderungen in der zweiten Transaktion (die bereits festgeschrieben wurde) angezeigt werden, nicht jedoch in der ersten Transaktion (die noch nicht festgeschrieben wurde). Andererseits haben wir oben bereits festgestellt, dass die zweite Transaktion nach der ersten begonnen haben sollte. Welchen Status die dritte Transaktion auch sieht, sie ist inkonsistent - dies ist die Anomalie nur der Lesetransaktion. Auf der Ebene Wiederholbares Lesen ist jedoch Folgendes zulässig:

 | id | number | client | amount | ----+--------+--------+-------- | 2 | 2001 | bob | 900.00 | 3 | 2002 | bob | 0.00 | (2 rows) 

 | => COMMIT; 

Serialisierbar

Auf der Ebene Serializable werden alle möglichen Anomalien verhindert. Tatsächlich wird Serializable als Add-In für die Isolation basierend auf Datenschnappschüssen implementiert. Diejenigen Anomalien, die während des wiederholbaren Lesens nicht auftreten (z. B. schmutziges, nicht wiederholbares Phantom-Lesen), treten auf der Ebene von Serializable nicht auf. Und die auftretenden Anomalien (inkonsistente Aufzeichnung und Anomalie nur der Lesetransaktion) werden erkannt und die Transaktion wird abgebrochen - der bereits bekannte Serialisierungsfehler konnte den Zugriff nicht serialisieren.

Inkonsistenter Eintrag

Zur Veranschaulichung wiederholen wir das Szenario mit einer Anomalie inkonsistenter Aufzeichnung:

 => BEGIN ISOLATION LEVEL SERIALIZABLE; => SELECT sum(amount) FROM accounts WHERE client = 'bob'; 

  sum ---------- 910.0000 (1 row) 

 | => BEGIN ISOLATION LEVEL SERIALIZABLE; | => SELECT sum(amount) FROM accounts WHERE client = 'bob'; 

 | sum | ---------- | 910.0000 | (1 row) 

 => UPDATE accounts SET amount = amount - 600.00 WHERE id = 2; 

 | => UPDATE accounts SET amount = amount - 600.00 WHERE id = 3; | => COMMIT; 

 => COMMIT; 

 ERROR: could not serialize access due to read/write dependencies among transactions DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt. HINT: The transaction might succeed if retried. 

Neben der Ebene "Wiederholbares Lesen" muss eine Anwendung, die die Isolationsstufe "Serialisierbar" verwendet, Transaktionen wiederholen, die zu einem Serialisierungsfehler geführt haben, der uns auch durch den Hinweis in der Fehlermeldung gemeldet wird.

Wir erhalten die Einfachheit der Programmierung, aber der Preis dafür ist die erzwungene Aufschlüsselung eines bestimmten Anteils von Transaktionen und die Notwendigkeit, diese zu wiederholen. Die ganze Frage ist natürlich, wie groß dieser Anteil ist. Wenn nur die Transaktionen beendet würden, die sich in Daten wirklich inkompatibel mit anderen Transaktionen überschneiden, wäre alles schön. Eine solche Implementierung würde sich jedoch unweigerlich als ressourcenintensiv und ineffizient herausstellen, da sie den Betrieb mit jeder Zeile verfolgen müsste.

Tatsächlich ist die Implementierung von PostgreSQL so, dass falsch negative Auslöser zugelassen werden: Einige völlig normale Transaktionen, die einfach „kein Glück haben“, werden abgebrochen. Wie wir später sehen werden, hängt dies von vielen Gründen ab, beispielsweise von der Verfügbarkeit geeigneter Indizes oder der verfügbaren RAM-Menge. Darüber hinaus gibt es einige andere (ziemlich schwerwiegende) Implementierungsbeschränkungen, z. B. funktionieren Anforderungen auf serialisierbarer Ebene nicht für Replikate, parallele Ausführungspläne werden für sie nicht verwendet. Und obwohl die Arbeit zur Verbesserung der Umsetzung nicht aufhört, verringern die bestehenden Beschränkungen die Attraktivität dieser Isolationsstufe.

Parallele Pläne werden in PostgreSQL 12 ( Patch ) angezeigt . Und Anfragen zu Replikaten können in PostgreSQL 13 ( einem weiteren Patch ) verdient werden.

Schreibgeschützte Anomalie

Damit nur eine Lesetransaktion nicht zu einer Anomalie führen und nicht darunter leiden kann, bietet PostgreSQL einen interessanten Mechanismus: Eine solche Transaktion kann blockiert werden, bis ihre Ausführung sicher ist. Dies ist der einzige Fall, in dem eine SELECT-Anweisung durch Zeilenaktualisierungen blockiert werden kann. So sieht es aus:

 => UPDATE accounts SET amount = 900.00 WHERE id = 2; => UPDATE accounts SET amount = 100.00 WHERE id = 3; => SELECT * FROM accounts WHERE client = 'bob' ORDER BY id; 

  id | number | client | amount ----+--------+--------+-------- 2 | 2001 | bob | 900.00 3 | 2002 | bob | 100.00 (2 rows) 

 => BEGIN ISOLATION LEVEL SERIALIZABLE; -- 1 => UPDATE accounts SET amount = amount + ( SELECT sum(amount) FROM accounts WHERE client = 'bob' ) * 0.01 WHERE id = 2; 

 | => BEGIN ISOLATION LEVEL SERIALIZABLE; -- 2 | => UPDATE accounts SET amount = amount - 100.00 WHERE id = 3; | => COMMIT; 

Die dritte Transaktion wird ausdrücklich nur vom Leser deklariert (NUR LESEN) und zurückgestellt (DEFERRABLE):

 | => BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE; -- 3 | => SELECT * FROM accounts WHERE client = 'alice'; 

Wenn Sie versuchen, eine Anforderung auszuführen, wird die Transaktion blockiert, da ihre Ausführung sonst zu einer Anomalie führt.

 => COMMIT; 

Und erst nachdem die erste Transaktion festgeschrieben wurde, wird die dritte weiter ausgeführt:

 | id | number | client | amount | ----+--------+--------+-------- | 1 | 1001 | alice | 800.00 | (1 row) 

 | => SELECT * FROM accounts WHERE client = 'bob'; 

 | id | number | client | amount | ----+--------+--------+---------- | 2 | 2001 | bob | 910.0000 | 3 | 2002 | bob | 0.00 | (2 rows) 

 | => COMMIT; 

Ein weiterer wichtiger Hinweis: Wenn die serialisierbare Isolation verwendet wird, müssen alle Transaktionen in der Anwendung diese Ebene verwenden. Sie können Read Committed (oder Repeatable Read) -Transaktionen nicht mit Serializable mischen. Das heißt, Sie können etwas mischen, aber dann verhält sich Serializable wie ein wiederholbarer Lesevorgang ohne Vorwarnung.Warum dies geschieht, werden wir später betrachten, wenn wir über die Implementierung sprechen.

Wenn Sie sich also für Serializble entscheiden, ist es am besten, die Standardstufe global festzulegen (obwohl dies natürlich nicht die explizite Angabe der falschen Stufe verbietet):

  ALTER SYSTEM SET default_transaction_isolation = 'serializable'; 

Eine genauere Darstellung von Fragen im Zusammenhang mit Transaktionen, Konsistenz und Anomalien findet sich in Boris Asenovich Novikovs Buch- und Vorlesungskurs "Fundamentals of Database Technologies".

Welchen Isolationsgrad sollte ich verwenden?

Die Read Committed-Isolationsstufe wird in PostgreSQL standardmäßig verwendet, und es scheint, dass diese Stufe in der überwiegenden Mehrheit der Anwendungen verwendet wird. Es ist zweckmäßig, dass eine Transaktionsunterbrechung nur im Fehlerfall möglich ist, nicht aber um Inkonsistenzen zu vermeiden. Mit anderen Worten, ein Serialisierungsfehler kann nicht auftreten.

Die Kehrseite der Münze ist die große Anzahl möglicher Anomalien, die oben ausführlich erörtert wurden. Der Entwickler muss sie ständig im Auge behalten und Code so schreiben, dass ihr Auftreten verhindert wird. Wenn es nicht möglich ist, die erforderlichen Aktionen in einer einzelnen SQL-Anweisung zu formulieren, müssen Sie explizit Sperren setzen. Das Unangenehmste ist, dass der Code schwer auf Fehler beim Abrufen inkonsistenter Daten zu testen ist und die Fehler selbst auf unvorhersehbare und nicht reproduzierbare Weise auftreten können und daher schwer zu beheben sind.

Die Isolationsstufe von Repeatable Read beseitigt einige der Probleme der Inkonsistenz, aber leider nicht alle. Daher müssen Sie sich nicht nur die verbleibenden Anomalien merken, sondern auch die Anwendung so ändern, dass Serialisierungsfehler korrekt verarbeitet werden. Dies ist natürlich unpraktisch. Bei schreibgeschützten Transaktionen ergänzt diese Ebene jedoch perfekt Read Committed und ist beispielsweise sehr praktisch, um Berichte zu erstellen, die mehrere SQL-Abfragen verwenden.

Schließlich macht die serialisierbare Ebene die Notwendigkeit von Inkonsistenzen überflüssig und erleichtert das Schreiben von Code erheblich. Die Anwendung muss lediglich alle Transaktionen wiederholen können, wenn ein Serialisierungsfehler auftritt. Der Anteil unterbrochener Transaktionen, der zusätzliche Overhead und die Unfähigkeit, Anforderungen zu parallelisieren, können jedoch den Systemdurchsatz erheblich reduzieren. Beachten Sie außerdem, dass die serialisierbare Ebene nicht auf Replikate anwendbar ist und nicht mit anderen Isolationsstufen gemischt werden kann.

Fortsetzung folgt .