Hallo Habr! Mit diesem Artikel beginne ich eine Reihe von Reihen (oder eine Reihe von Mengen? - Mit einem Wort, die Idee ist grandios) über die interne Struktur von PostgreSQL.

Das Material basiert auf Schulungen (auf Russisch) zur Verwaltung, die Pavel Pluzanov und ich erstellen. Nicht jeder sieht sich gerne Videos an (das tue ich definitiv nicht), und das Lesen von Folien ist selbst mit Kommentaren überhaupt nicht gut.

Leider ist der einzige Kurs, der derzeit auf Englisch verfügbar ist, die zweitägige Einführung in PostgreSQL 11 .

Natürlich stimmen die Artikel nicht genau mit dem Inhalt der Kurse überein. Ich werde nur darüber sprechen, wie alles organisiert ist, wobei die Verwaltung selbst weggelassen wird, aber ich werde versuchen, dies detaillierter und gründlicher zu tun. Und ich glaube, dass dieses Wissen für einen Anwendungsentwickler genauso nützlich ist wie für einen Administrator.

Ich werde mich an diejenigen wenden, die bereits Erfahrung mit PostgreSQL haben und zumindest allgemein verstehen, was was ist. Der Text wird für Anfänger zu schwierig sein. Zum Beispiel werde ich kein Wort darüber sagen, wie man PostgreSQL installiert und psql ausführt.

Das fragliche Zeug variiert nicht viel von Version zu Version, aber ich werde das aktuelle 11. Vanille-PostgreSQL verwenden.

Die erste Serie befasst sich mit Problemen im Zusammenhang mit Isolation und Multiversion-Parallelität. Der Plan der Serie lautet wie folgt:

1. Isolation im Sinne von Standard und PostgreSQL (dieser Artikel).
2. Gabeln, Dateien, Seiten - was auf physischer Ebene passiert.
3. Zeilenversionen, virtuelle Transaktionen und Subtransaktionen.
4. Datenschnappschüsse und die Sichtbarkeit von Zeilenversionen; der Ereignishorizont.
5. In-Page-Vakuum und HOT-Updates .
6. Normales Vakuum .
7. Autovakuum .
8. Umgehung und Einfrieren der Transaktions-ID .

Auf geht's!

Und bevor wir anfangen, möchte ich Elena Indrupskaya für die Übersetzung der Artikel ins Englische danken.

Was ist Isolation und warum ist sie wichtig?

Wahrscheinlich ist sich zumindest jeder der Existenz von Transaktionen bewusst, ist auf die Abkürzung ACID gestoßen und hat von Isolationsstufen gehört. Wir sind jedoch immer noch der Meinung, dass dies die Theorie betrifft, was 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 zufrieden sind, wenn eine Anwendung falsche Daten aus der Datenbank erhält oder wenn die Anwendung falsche Daten in die Datenbank schreibt.

Aber was sind "richtige" Daten? Es ist bekannt, dass Integritätsbeschränkungen wie NOT NULL oder UNIQUE auf Datenbankebene erstellt werden können. Wenn die Daten immer Integritätsbeschränkungen erfüllen (und dies ist so, da das DBMS dies garantiert), sind sie ganzheitlich.

Sind richtig und ganzheitlich die gleichen Dinge? Nicht genau. Auf Datenbankebene können nicht alle Einschränkungen angegeben werden. Einige der Einschränkungen sind beispielsweise zu kompliziert und decken mehrere Tabellen gleichzeitig ab. Und selbst wenn eine Einschränkung im Allgemeinen in der Datenbank hätte definiert werden können, aber aus irgendeinem Grund nicht, bedeutet dies nicht, dass die Einschränkung verletzt werden kann.

Korrektheit ist also stärker als Integrität , aber wir wissen nicht genau, was dies bedeutet. Wir müssen nur zugeben, dass der „Goldstandard“ der Korrektheit eine Anwendung ist, die, wie wir gerne glauben würden, richtig geschrieben ist und niemals falsch läuft. In jedem Fall, wenn eine Anwendung nicht die Integrität, sondern die Richtigkeit verletzt, weiß das DBMS nichts davon und fängt die Anwendung nicht auf frischer Tat ab.

Weiterhin werden wir den Begriff Konsistenz verwenden , um die Korrektheit zu bezeichnen.

Nehmen wir jedoch an, dass eine Anwendung nur korrekte Folgen von Operatoren ausführt. Welche Rolle spielt DBMS, wenn die Anwendung korrekt ist?

Erstens stellt sich heraus, dass eine korrekte Folge von Operatoren die Datenkonsistenz vorübergehend beeinträchtigen kann, und seltsamerweise ist dies normal. Ein abgedroschenes, aber klares 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 den Konten (diese Regel ist in SQL als Integritätsbedingung nur schwer anzugeben, 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 und der zweite erhöht das Guthaben auf dem anderen. Die erste Operation unterbricht die Datenkonsistenz, während die zweite die Datenkonsistenz wiederherstellt.

Eine gute Übung besteht darin, die obige Regel auf der Ebene der Integritätsbeschränkungen zu implementieren.

Was ist, wenn die erste Operation ausgeführt wird und die zweite nicht? In der Tat, ohne viel Aufhebens: Während der zweiten Operation kann es zu einem Stromausfall, einem Serverabsturz und einer Division durch Null kommen - was auch immer. Es ist klar, dass die Konsistenz gebrochen wird, und dies kann nicht zugelassen werden. Im Allgemeinen ist es möglich, solche Probleme auf Anwendungsebene zu lösen, jedoch auf Kosten enormer Anstrengungen. Zum Glück ist dies jedoch nicht erforderlich: Dies erfolgt durch das DBMS. Dazu muss das DBMS jedoch wissen, dass die beiden Operationen ein unteilbares Ganzes sind. Das heißt, eine Transaktion .

Es stellt sich als interessant heraus: Da das DBMS weiß, dass Vorgänge eine Transaktion bilden, trägt es zur Aufrechterhaltung der Konsistenz bei, indem sichergestellt wird, dass die Transaktionen atomar sind, und dies ohne Kenntnis spezifischer Konsistenzregeln.

Aber es gibt noch einen zweiten, subtileren Punkt. Sobald mehrere gleichzeitige Transaktionen im System angezeigt werden, die separat absolut korrekt sind, funktionieren sie möglicherweise nicht richtig zusammen. Dies liegt daran, dass die Reihenfolge der Vorgänge verwechselt ist: Sie können nicht davon ausgehen, dass alle Vorgänge einer Transaktion zuerst und dann alle Vorgänge der anderen Transaktion ausgeführt werden.

Ein Hinweis zur Gleichzeitigkeit. In der Tat können Transaktionen gleichzeitig auf einem System mit einem Mehrkernprozessor, einem Festplattenarray usw. ausgeführt werden. Die gleiche Überlegung gilt jedoch für einen Server, der Befehle nacheinander in einem Time-Sharing-Modus ausführt: Während bestimmter Taktzyklen wird eine Transaktion ausgeführt und während der nächsten bestimmten Zyklen die andere. Manchmal wird der Begriff gleichzeitige Ausführung für eine Verallgemeinerung verwendet.

Situationen, in denen korrekte Transaktionen falsch zusammenarbeiten, werden als Anomalien der gleichzeitigen Ausführung bezeichnet.

Ein einfaches Beispiel: Wenn eine Anwendung korrekte Daten aus der Datenbank abrufen möchte, darf sie zumindest keine Änderungen anderer nicht festgeschriebener 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 Dirty Read bezeichnet .

Es gibt andere, komplexere Anomalien, auf die wir später noch eingehen werden.

Es ist sicherlich unmöglich, eine gleichzeitige Ausführung zu vermeiden. Ansonsten, von welcher Art von Leistung können wir sprechen? Sie können aber auch 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 - voneinander isoliert . In der Realität kann das DBMS Operationen durcheinander bringen, aber sicherstellen, dass das Ergebnis einer gleichzeitigen Ausführung mit dem Ergebnis einiger möglicher sequentieller Ausführungen übereinstimmt. Und das beseitigt mögliche Anomalien.

Also kamen wir zu der Definition:

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

Diese Definition vereint die ersten drei Buchstaben des Akronyms ACID. Sie sind so eng miteinander verbunden, dass es keinen Sinn macht, einen ohne den anderen zu betrachten. Tatsächlich ist es auch schwierig, den Buchstaben D (Haltbarkeit) abzunehmen. Wenn ein System abstürzt, werden immer noch Änderungen an nicht festgeschriebenen Transaktionen vorgenommen, mit denen Sie etwas tun müssen, um die Datenkonsistenz wiederherzustellen.

Alles wäre in Ordnung gewesen, aber die Implementierung einer vollständigen Isolierung ist eine technisch schwierige Aufgabe, die eine Reduzierung des Systemdurchsatzes zur Folge hat. Daher wird in der Praxis sehr oft (nicht immer, aber fast immer) die geschwächte Isolation verwendet, wodurch einige, aber nicht alle Anomalien verhindert werden. Dies bedeutet, dass ein Teil der Arbeit zur Sicherstellung der Datenkorrektheit 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 was nicht und wie man unter solchen Bedingungen korrekten Code schreibt.

Isolationsstufen und Anomalien im SQL-Standard

Der SQL-Standard hat lange Zeit vier Isolationsstufen beschrieben. Diese Ebenen werden definiert, indem Anomalien aufgelistet werden, die zulässig oder nicht zulässig sind, wenn Transaktionen gleichzeitig auf dieser Ebene ausgeführt werden. Um über diese Ebenen zu sprechen, ist es daher notwendig, die Anomalien kennenzulernen.

Ich betone, dass es sich in diesem Teil um den Standard handelt, dh um eine Theorie, auf der die Praxis maßgeblich basiert, von der sie aber gleichzeitig erheblich abweicht. Daher sind alle Beispiele hier spekulativ. Sie werden die gleichen Operationen auf Kundenkonten anwenden: Dies ist ziemlich demonstrativ, hat jedoch zugegebenermaßen nichts mit der Organisation der Bankoperationen in der Realität zu tun.

Verlustaktualisierung

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

Beispielsweise erhöhen zwei Transaktionen den Betrag auf demselben Konto um 100 ₽ (₽ ist das Währungszeichen für den russischen Rubel). Die erste Transaktion liest den aktuellen Wert (~ 1000) und die zweite Transaktion liest den gleichen Wert. Die erste Transaktion erhöht den Betrag (dies ergibt 001100) und schreibt diesen Wert. Die zweite Transaktion verhält sich genauso: Sie erhält den gleichen Wert von 001100 und schreibt diesen Wert. Infolgedessen verlor der Kunde 100..

Der Standard erlaubt keine verlorenen Updates auf irgendeiner Isolationsstufe.

Dirty Read und Read Uncommitted

Eine schmutzige Lektüre ist das, was wir bereits kennengelernt haben. Diese Anomalie tritt auf, wenn eine Transaktion Änderungen liest, die noch nicht von einer anderen Transaktion festgeschrieben wurden.

Beispielsweise überträgt die erste Transaktion das gesamte Geld vom Konto des Kunden auf ein anderes Konto, schreibt die Änderung jedoch nicht fest. Eine andere Transaktion liest den Kontostand, um ₽0 zu erhalten, und weigert sich, dem Kunden Bargeld abzuheben, obwohl die erste Transaktion ihre Änderungen abbricht und zurücksetzt, sodass der Wert 0 nie in der Datenbank vorhanden war.

Der Standard erlaubt Dirty Reads auf der Ebene Read Uncommitted.

Nicht wiederholbares Lesen und Lesen festgeschrieben

Eine nicht wiederholbare Leseanomalie tritt auf, wenn eine Transaktion dieselbe Zeile zweimal liest und 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 eine Konsistenzregel negative Beträge auf Kundenkonten verbieten . Die erste Transaktion wird den Betrag auf dem Konto um 100 ₽ reduzieren. Es prüft den aktuellen Wert, erhält 1000 und entscheidet, dass die Abnahme möglich ist. Gleichzeitig reduziert die zweite Transaktion den Betrag auf dem Konto auf Null und schreibt die Änderungen fest. Wenn die erste Transaktion nun den Betrag erneut prüft, erhält sie ₽0 (aber sie hat bereits beschlossen, den Wert zu reduzieren, und das Konto schreibt rote Zahlen).

Der Standard erlaubt nicht wiederholbare Lesevorgänge auf den Ebenen Read Uncommitted und Read Committed. Read Committed erlaubt jedoch keine Dirty Reads.

Phantom Read und Repeatable Read

Ein Phantom- Lesevorgang tritt auf, wenn eine Transaktion einen Satz von Zeilen mit derselben Bedingung zweimal liest und zwischen den Lesevorgängen die zweite Transaktion Zeilen hinzufügt, die diese Bedingung erfüllen (und die Änderungen festschreibt). Dann erhält die erste Transaktion einen anderen Satz von Zeilen.

Lassen Sie beispielsweise eine Konsistenzregel verhindern, dass ein Kunde mehr als 3 Konten hat . Die erste Transaktion eröffnet ein neues Konto, überprüft die aktuelle Anzahl der Konten (z. B. 2) und entscheidet, dass eine Eröffnung möglich ist. Gleichzeitig eröffnet die zweite Transaktion ein neues Konto für den Kunden und schreibt die Änderungen fest. Wenn die erste Transaktion die Nummer erneut überprüft, erhält sie 3 (es wird jedoch bereits ein anderes Konto eröffnet, und der Kunde scheint 4 davon zu haben).

Der Standard erlaubt Phantom-Lesevorgänge auf den Ebenen Read Uncommitted, Read Committed und Repeatable Read. Nicht wiederholbares Lesen ist jedoch auf der Ebene Wiederholbares Lesen nicht zulässig.

Das Fehlen von Anomalien und serialisierbar

Der Standard definiert eine weitere Ebene - Serializable -, die keine Anomalien zulässt. Dies ist nicht dasselbe, um verlorene Updates und schmutzige, nicht wiederholbare oder Phantom-Lesevorgänge zu verbieten.

Die Sache ist, dass es viel mehr bekannte Anomalien gibt als im Standard aufgeführt und auch eine unbekannte Anzahl von noch unbekannten.

Die serialisierbare Ebene muss absolut alle Anomalien verhindern. Dies bedeutet, dass ein Anwendungsentwickler auf dieser Ebene nicht über die gleichzeitige Ausführung nachdenken muss. Wenn Transaktionen eine korrekte Reihenfolge von Operatoren ausführen, die separat arbeiten, sind die Daten auch dann konsistent, wenn diese Transaktionen gleichzeitig ausgeführt werden.

Übersichtstabelle

Jetzt können wir eine bekannte Tabelle bereitstellen. Hier wird jedoch der Klarheit halber die letzte Spalte hinzugefügt, die im Standard fehlt.

Warum genau diese Anomalien?

Warum listet der Standard nur einige der vielen möglichen Anomalien auf und warum sind sie genau diese?

Niemand scheint es sicher zu wissen. Aber hier ist die Praxis offensichtlich der Theorie voraus, so dass es möglich ist, dass zu dieser Zeit (nach dem SQL: 92-Standard) nicht nur an andere Anomalien gedacht wurde.

Außerdem wurde angenommen, dass die Isolation auf Schlössern aufgebaut sein muss. Die Idee hinter dem weit verbreiteten Two-Phase Locking-Protokoll (2PL) ist, dass eine Transaktion während der Ausführung die Zeilen sperrt, mit denen sie arbeitet, und die Sperren nach Abschluss aufhebt. Je mehr Sperren eine Transaktion erwirbt, desto besser ist sie von anderen Transaktionen isoliert. Die Leistung des Systems leidet jedoch auch stärker, da Transaktionen nicht zusammenarbeiten, sondern für dieselben Zeilen in die Warteschlange gestellt werden.

Meiner Meinung nach ist es nur die Anzahl der erforderlichen Sperren, die den Unterschied zwischen den Isolationsstufen des Standards erklärt.

Wenn eine Transaktion die zu ändernden Zeilen vom Aktualisieren, aber nicht vom Lesen sperrt, erhalten wir die Stufe "Nicht festgeschrieben lesen": Verlorene Änderungen sind nicht zulässig, aber nicht festgeschriebene Daten können gelesen werden.

Wenn eine Transaktion die zu ändernden Zeilen sowohl beim Lesen als auch beim Aktualisieren sperrt, erhalten wir die Stufe "Festgeschrieben": Sie können keine nicht festgeschriebenen Daten lesen, aber Sie können einen anderen Wert (nicht wiederholbares Lesen) erhalten, wenn Sie erneut auf die Zeile zugreifen.

Wenn eine Transaktion die zu lesenden und zu ändernden Zeilen sowie das Lesen und Aktualisieren sperrt, erhalten wir die Stufe Wiederholbares Lesen: Beim erneuten Lesen der Zeile wird derselbe Wert zurückgegeben.

Bei Serializable gibt es jedoch ein Problem: Sie können eine noch nicht vorhandene Zeile nicht sperren. Daher ist ein Phantom-Lesevorgang weiterhin möglich: Eine andere Transaktion kann eine Zeile hinzufügen (aber nicht löschen), die die Bedingungen einer zuvor ausgeführten Abfrage erfüllt, und diese Zeile wird in die Neuauswahl einbezogen.

Um die Ebene Serializable zu implementieren, reichen normale Sperren daher nicht aus. Sie müssen eher Bedingungen (Prädikate) als Zeilen sperren. Daher wurden solche Sperren als Prädikat bezeichnet . Sie wurden 1976 vorgeschlagen, aber ihre praktische Anwendbarkeit wird durch relativ einfache Bedingungen eingeschränkt, für die klar ist, wie zwei verschiedene Prädikate verbunden werden sollen. Soweit ich weiß, wurden solche Sperren bisher in keinem System implementiert.

Isolationsstufen in PostgreSQL

Im Laufe der Zeit wurden sperrbasierte Protokolle des Transaktionsmanagements durch das Snapshot Isolation Protocol (SI) ersetzt. Die Idee ist, dass jede Transaktion zu einem bestimmten Zeitpunkt mit einem konsistenten Snapshot der Daten arbeitet und nur die Änderungen in den Snapshot übernommen werden, die vor ihrer Erstellung festgeschrieben wurden.

Diese Isolierung verhindert automatisch fehlerhafte Lesevorgänge. Formal können Sie in PostgreSQL die Stufe Read Uncommitted angeben, diese funktioniert jedoch genauso wie Read Committed. Daher werden wir weiter überhaupt nicht über die Stufe "Uncommitted lesen" sprechen.

PostgreSQL implementiert eine Multiversionsvariante dieses Protokolls. Die Idee der Multiversion-Parallelität besteht darin, dass mehrere Versionen derselben Zeile in einem DBMS nebeneinander existieren können. Auf diese Weise können Sie mit vorhandenen Versionen einen Snapshot der Daten erstellen und ein Minimum an Sperren verwenden. Tatsächlich werden nur nachfolgende Änderungen an derselben Zeile gesperrt. Alle anderen Vorgänge werden gleichzeitig ausgeführt: Schreibtransaktionen sperren niemals schreibgeschützte Transaktionen, und schreibgeschützte Transaktionen sperren niemals etwas.

Durch die Verwendung von Datenschnappschüssen ist die Isolation in PostgreSQL strenger als vom Standard gefordert: Die Stufe Wiederholbares Lesen erlaubt nicht nur nicht wiederholbare Lesevorgänge, sondern auch Phantomlesevorgänge (obwohl sie keine vollständige Isolation bietet). Und das ohne Effizienzverlust.

In den nächsten Artikeln werden wir darüber sprechen, wie Multiversion-Parallelität „unter der Haube“ implementiert wird, und jetzt werden wir jede der drei Ebenen mit dem Auge eines Benutzers im Detail betrachten (wie Sie wissen, verbirgt sich das Interessanteste hinter „anderen Anomalien“ ”). Dazu erstellen wir eine Kontenliste. Alice und Bob haben jeweils 1000 Euro, aber Bob hat zwei eröffnete 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

Das Fehlen von Dirty Read

Es ist einfach sicherzustellen, dass schmutzige Daten nicht gelesen werden können. Wir starten die Transaktion. Standardmäßig wird die Isolationsstufe Read Committed verwendet:

 => BEGIN; => SHOW transaction_isolation; 

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

Genauer gesagt wird die Standardstufe durch den Parameter festgelegt, der bei Bedarf geändert werden kann:

 => SHOW default_transaction_isolation; 

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

Bei einer offenen Transaktion ziehen wir also Geld vom Konto ab, übernehmen jedoch keine Änderungen. 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. Zur Unterscheidung zwischen den Transaktionen werden Befehle der zweiten Transaktion eingerückt und mit einem Balken markiert.

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

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

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

Wie erwartet werden bei der anderen Transaktion keine nicht festgeschriebenen Änderungen angezeigt, da Dirty Reads nicht zulässig sind.

Nicht wiederholbares Lesen

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

 => COMMIT; 

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

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

 | => COMMIT; 

Die Abfrage erhält bereits neue Daten - und dies ist die nicht wiederholbare Leseanomalie, die auf der Ebene Read Committed zulässig ist.

Praktische Schlussfolgerung : In einer Transaktion können Sie keine Entscheidungen treffen, die auf Daten basieren, die von einem vorherigen Operator gelesen wurden, da sich die Dinge zwischen der Ausführung der Operatoren ändern können. Hier ist ein Beispiel, dessen Variationen im Anwendungscode so häufig auftreten, dass es als klassisches Antimuster betrachtet wird:

  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 auf irgendeine Weise ändern, sodass eine solche „Überprüfung“ vor nichts schützt. Es ist zweckmäßig, sich vorzustellen, dass zwischen den Betreibern einer Transaktion alle anderen Betreiber anderer Transaktionen beispielsweise wie folgt „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 durch die Neuanordnung der Operatoren alles verdorben werden kann, ist der Code falsch geschrieben. Und täuschen Sie sich nicht, dass ein solcher Zufall nicht passieren wird - das wird es mit Sicherheit.

Aber wie schreibt man Code richtig? Die Optionen sind in der Regel wie folgt:

• Code nicht schreiben.
  Das ist kein Scherz. In diesem Fall wird das Überprüfen beispielsweise leicht zu einer Integritätsbeschränkung:
  ALTER TABLE accounts ADD CHECK amount >= 0;
  Jetzt sind keine Überprüfungen erforderlich: Führen Sie einfach den Vorgang aus und behandeln Sie gegebenenfalls die Ausnahme, die auftritt, wenn eine Integritätsverletzung versucht wird.
  
• So verwenden Sie eine einzelne SQL-Anweisung
  Konsistenzprobleme treten auf, da im Zeitintervall zwischen Operatoren eine andere Transaktion abgeschlossen werden kann, wodurch die sichtbaren Daten geändert werden. Und wenn es einen Operator gibt, gibt es keine Zeitintervalle.
  PostgreSQL verfügt über genügend Techniken, um komplexe Probleme mit einer SQL-Anweisung zu lösen. Beachten Sie 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 von „insert, aber wenn die Zeile bereits vorhanden ist, implementiert. update ”in einer Anweisung.
  
• Benutzerdefinierte Schlösser.
  Der letzte Ausweg besteht darin, manuell eine exklusive Sperre für alle erforderlichen Zeilen (SELECT FOR UPDATE) oder sogar für die gesamte Tabelle (LOCK TABLE) festzulegen. Dies funktioniert immer, macht jedoch die Vorteile der Multiversions-Parallelität zunichte: Einige Vorgänge werden nacheinander anstelle der gleichzeitigen Ausführung ausgeführt.
  

Inkonsistentes Lesen

Bevor Sie zur nächsten Stufe der Isolation übergehen, müssen Sie zugeben, dass dies nicht alles so einfach ist, wie es sich anhört. Die Implementierung von PostgreSQL ist so, dass andere, weniger bekannte Anomalien berücksichtigt werden, die nicht durch den Standard geregelt werden.

Nehmen wir an, dass die erste Transaktion den Geldtransfer von einem Bob-Konto auf das andere gestartet hat:

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

Gleichzeitig zählt eine andere Transaktion den Kontostand von Bob und die Berechnung wird in einer Schleife über alle Konten von Bob durchgeführt. 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 den neuen Wert):

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

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

 | => COMMIT; 

Daher erhielt die zweite Transaktion insgesamt 1100 GBP, dh falsche Daten. Und dies ist eine inkonsistente Leseanomalie.

Wie vermeide ich eine solche Anomalie, während ich auf der Stufe "Read Committed" bleibe? Verwenden Sie natürlich einen Operator. Zum Beispiel:

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

Bis hierher habe ich behauptet, dass sich die Sichtbarkeit von Daten nur zwischen Operatoren ändern kann, aber ist das so offensichtlich? Und wenn die Abfrage lange dauert, kann sie einen Teil der Daten in einem Zustand und einen Teil in einem anderen sehen?

Lassen Sie uns überprüfen. Eine bequeme Möglichkeit, dies zu tun, besteht darin, eine erzwungene Verzögerung in den Operator einzufügen, indem die Funktion pg_sleep aufgerufen wird. Sein Parameter gibt die Verzögerungszeit in Sekunden an.

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

Während dieser Operator ausgeführt wird, überweisen wir das Geld in einer anderen Transaktion 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, den sie zum Zeitpunkt des Starts der Ausführung des Bedieners hatten. Dies ist zweifellos richtig.

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

Aber so einfach ist es auch hier nicht. Mit PostgreSQL können Sie Funktionen definieren, und Funktionen haben das Konzept einer Volatilitätskategorie . Wenn eine VOLATILE-Funktion in einer Abfrage aufgerufen wird und eine andere Abfrage in dieser Funktion ausgeführt wird, werden in der Abfrage innerhalb der Funktion Daten angezeigt, die nicht mit den Daten in der Hauptabfrage ü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 dieser Effekt nur auf der Isolationsstufe Read Committed und nur mit den VOLATILE-Funktionen möglich ist. Das Problem ist, dass standardmäßig genau diese Isolationsstufe und diese Volatilitätskategorie verwendet werden. Fallen Sie nicht in die Falle!

Inkonsistentes Lesen im Austausch für verlorene Änderungen

Wir können während eines Updates auch einen inkonsistenten Lesevorgang innerhalb eines einzelnen Operators erhalten, wenn auch auf etwas unerwartete Weise.

Mal sehen, was passiert, wenn zwei Transaktionen versuchen, dieselbe Zeile zu ändern. Jetzt hat Bob 1000 Euro 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 bei einer anderen Transaktion Zinsen auf alle Kundenkonten mit einem Gesamtguthaben von mindestens 1.000 GBP an:

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

Die Ausführung des UPDATE-Operators besteht aus zwei Teilen. Zunächst wird tatsächlich SELECT ausgeführt, wodurch die zu aktualisierenden Zeilen ausgewählt werden, die die entsprechende Bedingung erfüllen. Da die Änderung in der ersten Transaktion nicht festgeschrieben wird, kann die zweite Transaktion sie nicht sehen, und die Änderung wirkt sich nicht auf die Auswahl der Zeilen für die Zinsabgrenzung aus. Nun, dann erfüllen Bobs Konten die Bedingung und sobald das Update ausgeführt wird, sollte sich sein Kontostand um ₽10 erhöhen.

In der zweiten Phase der Ausführung werden die ausgewählten Zeilen einzeln aktualisiert. Hier wird die zweite Transaktion zum "Hängen" gezwungen, da die Zeile mit 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) 

Einerseits sollte der UPDATE-Befehl die Änderungen der zweiten Transaktion nicht sehen. Andererseits sollte es die in der zweiten Transaktion festgeschriebenen Änderungen nicht verlieren.

Sobald die Sperre aufgehoben ist, liest UPDATE die Zeile, die aktualisiert werden soll, erneut (jedoch nur diese). Infolgedessen sammelte Bob 9., Basierend auf dem Betrag von 900.. Aber wenn Bob 900 Euro gehabt hätte, wären seine Konten überhaupt nicht in der Auswahl gewesen.

Die Transaktion erhält also falsche Daten: Einige der Zeilen sind zu einem bestimmten Zeitpunkt sichtbar, andere zu einem anderen. Anstelle eines verlorenen Updates erhalten wir erneut die Anomalie des inkonsistenten Lesens .

Aufmerksame Leser bemerken, dass Sie mit Hilfe der Anwendung ein verlorenes Update erhalten können, selbst auf der Ebene von Read Committed. Zum Beispiel:

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

Die Datenbank ist nicht schuld: Sie erhält zwei SQL-Anweisungen und weiß nichts darüber, dass der Wert von x + 100 in irgendeiner Weise mit dem Kontobetrag zusammenhängt. Vermeiden Sie es, Code auf diese Weise zu schreiben.

Wiederholbares Lesen

Das Fehlen von nicht wiederholbaren und Phantom-Lesevorgängen

Der Name der Isolationsstufe setzt voraus, dass das Lesen wiederholbar ist. Lassen Sie es uns überprüfen und gleichzeitig sicherstellen, dass keine Phantom-Lesevorgänge vorhanden sind. Zu diesem Zweck setzen wir in der ersten Transaktion die Konten von Bob auf den vorherigen Status zurück und erstellen 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, indem wir sie im Befehl BEGIN angeben (die Ebene der ersten Transaktion ist unwesentlich).

 | => 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 Abfrage 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; 

Die zweite Transaktion sieht immer noch genau die gleichen Daten wie zu Beginn: Es sind keine Änderungen an vorhandenen oder neuen Zeilen sichtbar.

Auf dieser Ebene können Sie vermeiden, sich über Dinge Gedanken zu machen, die sich zwischen zwei Operatoren ändern können.

Serialisierungsfehler im Austausch für verlorene Änderungen

Wir haben bereits erwähnt, dass beim Aktualisieren derselben Zeile auf Read-Committed-Ebene durch zwei Transaktionen eine Anomalie des inkonsistenten Lesens auftreten kann. Dies liegt daran, dass die wartende Transaktion die gesperrte Zeile erneut liest und sie daher nicht zum gleichen Zeitpunkt wie die anderen Zeilen sieht.

Auf der Ebene "Wiederholbares Lesen" ist diese Anomalie nicht zulässig. In diesem Fall kann jedoch nichts unternommen werden. Die Transaktion wird daher mit einem Serialisierungsfehler beendet. Überprüfen wir dies, indem wir dasselbe Szenario mit Zinsabgrenzung 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;<span/> | => UPDATE accounts SET amount = amount * 1.01<span/> | WHERE client IN (<span/> | SELECT client<span/> | FROM accounts<span/> | GROUP BY client<span/> | HAVING sum(amount) >= 1000<span/> | );<span/> 

 => COMMIT; 

 | ERROR: could not serialize access due to concurrent update 

 | => ROLLBACK; 

Die 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 Wettbewerbsänderung einer Zeile auf, selbst wenn die Spalten unseres Anliegens nicht tatsächlich geändert wurden.

Praktische Schlussfolgerung : Wenn Ihre Anwendung die Isolationsstufe Wiederholbares Lesen für Schreibtransaktionen verwendet, muss sie bereit sein, Transaktionen zu wiederholen, die mit einem Serialisierungsfehler beendet wurden. Bei schreibgeschützten Transaktionen ist dieses Ergebnis nicht möglich.

Inkonsistentes Schreiben

In PostgreSQL werden auf der Isolationsstufe "Wiederholbares Lesen" alle im Standard beschriebenen Anomalien verhindert. Aber nicht alle Anomalien im Allgemeinen. Es stellt sich heraus, dass genau zwei Anomalien noch möglich sind. (Dies gilt nicht nur für PostgreSQL, sondern auch für andere Implementierungen von Snapshot Isolation.)

Die erste dieser Anomalien ist ein inkonsistentes Schreiben .

Es gilt die folgende Konsistenzregel: Negative Beträge auf Kundenkonten sind 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 GBP reduziert werden kann.

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

Und die zweite Transaktion kommt zu dem gleichen Ergebnis. Aber es reduziert ein anderes Konto:

 | => 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 in die roten Zahlen zu bringen, obwohl jede Transaktion alleine korrekt funktioniert.

Schreibgeschützte Transaktionsanomalie

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 sie nur liest.

Aber lassen Sie uns zuerst den Status von Bobs Konten wiederherstellen:

 => 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 fallen Zinsen auf den auf allen Bob-Konten verfügbaren Betrag an. 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 Bob-Konto ab und schreibt seine Änderungen fest:

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

Wenn die erste Transaktion zu diesem Zeitpunkt festgeschrieben wird, tritt keine Anomalie auf: Wir können davon ausgehen, dass die erste Transaktion zuerst und dann die zweite ausgeführt wurde (aber nicht umgekehrt, da bei der ersten Transaktion zuvor der Status des Kontos mit der ID = 3 angezeigt wurde Konto wurde durch die zweite Transaktion geändert).

Stellen Sie sich jedoch vor, dass zu diesem Zeitpunkt die dritte (schreibgeschützte) Transaktion beginnt, 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 die dritte Transaktion die Änderungen der zweiten Transaktion (die bereits festgeschrieben wurde) sehen, jedoch nicht der ersten Transaktion (die noch nicht festgeschrieben worden war). Andererseits haben wir oben bereits festgestellt, dass die zweite Transaktion nach der ersten als gestartet betrachtet werden sollte. Welchen Status die dritte Transaktion sieht, ist inkonsistent - dies ist nur die Anomalie einer schreibgeschützten Transaktion. 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

Die Stufe Serializable verhindert alle möglichen Anomalien. Tatsächlich basiert Serializable auf der Snapshot-Isolation. Die Anomalien, die beim wiederholbaren Lesen nicht auftreten (z. B. ein fehlerhafter, nicht wiederholbarer oder Phantom-Lesevorgang), treten auch auf der Ebene der Serialisierbarkeit nicht auf. Und die auftretenden Anomalien (ein inkonsistentes Schreiben und eine schreibgeschützte Transaktionsanomalie) werden erkannt und die Transaktion wird abgebrochen - ein bekannter Serialisierungsfehler tritt auf: Der Zugriff konnte nicht serialisiert werden .

Inkonsistentes Schreiben

Um dies zu veranschaulichen, wiederholen wir das Szenario mit einer inkonsistenten Schreibanomalie:

 => 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. 

Genau wie auf der Ebene "Wiederholbares Lesen" muss eine Anwendung, die die Isolationsstufe "Serialisierbar" verwendet, Transaktionen wiederholen, die mit einem Serialisierungsfehler beendet wurden, wie die Fehlermeldung uns auffordert.

Die Programmierung wird einfacher, aber der Preis dafür ist die erzwungene Beendigung eines Teils der Transaktionen und die Notwendigkeit, diese zu wiederholen. Die Frage ist natürlich, wie groß dieser Anteil ist. Wenn nur die Transaktionen beendet worden wären, die sich nicht mit anderen Transaktionen überschneiden, wäre es schön gewesen. Eine solche Implementierung wäre jedoch zwangsläufig ressourcenintensiv und ineffizient, da Sie die Vorgänge in jeder Zeile verfolgen müssten.

Tatsächlich ist die Implementierung von PostgreSQL so, dass sie falsche Negative zulässt: Einige absolut normale Transaktionen, die nur "unglücklich" sind, werden ebenfalls abgebrochen. Wie wir später sehen werden, hängt dies von vielen Faktoren ab, wie der Verfügbarkeit geeigneter Indizes oder der verfügbaren RAM-Größe. Darüber hinaus gibt es einige andere (ziemlich schwerwiegende) Implementierungsbeschränkungen, z. B. funktionieren Abfragen auf der Ebene der Serialisierbarkeit nicht für Replikate und sie verwenden keine parallelen Ausführungspläne. Obwohl die Arbeiten zur Verbesserung der Implementierung fortgesetzt werden, machen die bestehenden Einschränkungen diese Isolationsstufe weniger attraktiv.

Parallele Pläne werden bereits in PostgreSQL 12 ( Patch ) angezeigt . Abfragen zu Replikaten können in PostgreSQL 13 ( einem weiteren Patch ) ausgeführt werden.

Schreibgeschützte Transaktionsanomalie

Damit eine schreibgeschützte Transaktion nicht zu einer Anomalie führt und nicht darunter leidet, bietet PostgreSQL eine interessante Technik: Eine solche Transaktion kann gesperrt werden, bis ihre Ausführung sicher ist. Dies ist der einzige Fall, in dem ein SELECT-Operator durch Zeilenaktualisierungen gesperrt werden kann. So sieht das 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 als READ ONLY und DEFERRABLE deklariert:

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

Beim Versuch, die Abfrage auszuführen, wird die Transaktion gesperrt, da dies sonst zu einer Anomalie führen würde.

 => COMMIT; 

Und erst nachdem die erste Transaktion festgeschrieben wurde, setzt die dritte die Ausführung fort:

 | 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 mischen, aber dann verhält sich Serializable wie "Repeatable Read" ohne Warnungen. Wir werden später diskutieren, warum dies passiert, wenn wir über die Implementierung sprechen.

Wenn Sie sich also für die Verwendung von Serializble entscheiden, ist es am besten, die Standardstufe global festzulegen (obwohl dies Sie natürlich nicht daran hindert, explizit eine falsche Stufe anzugeben):

 ALTER SYSTEM SET default_transaction_isolation = 'serializable'; 

Eine genauere Darstellung der Probleme im Zusammenhang mit Transaktionen, Konsistenz und Anomalien finden Sie im Buch- und Vorlesungskurs von Boris Novikov „Grundlagen der Datenbanktechnologien“ (nur in Russion verfügbar).

Welche Isolationsstufe soll verwendet werden?

Die Read Committed-Isolationsstufe wird in PostgreSQL standardmäßig verwendet, und es ist wahrscheinlich, dass diese Stufe in der überwiegenden Mehrheit der Anwendungen verwendet wird. Diese Standardeinstellung ist praktisch, da auf dieser Ebene ein Transaktionsabbruch nur im Fehlerfall möglich ist, jedoch nicht, um Inkonsistenzen zu vermeiden. Mit anderen Worten, ein Serialisierungsfehler kann nicht auftreten.

Die andere Seite der Medaille ist eine große Anzahl möglicher Anomalien, die oben ausführlich diskutiert wurden. Der Softwareentwickler muss sie immer im Auge behalten und Code schreiben, damit sie nicht angezeigt werden. Wenn Sie die erforderlichen Aktionen nicht in einer einzelnen SQL-Anweisung codieren können, müssen Sie auf explizite Sperren zurückgreifen. Am problematischsten ist, dass es schwierig ist, Code auf Fehler zu testen, die mit dem Abrufen inkonsistenter Daten verbunden sind. Die Fehler selbst können auf unvorhersehbare und nicht reproduzierbare Weise auftreten und sind daher schwer zu beheben.

Die Isolationsstufe "Wiederholbares Lesen" beseitigt einige der Inkonsistenzprobleme, aber leider nicht alle. Daher müssen Sie sich nicht nur an die verbleibenden Anomalien erinnern, sondern auch die Anwendung so ändern, dass Serialisierungsfehler korrekt behandelt werden. Es ist sicherlich 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 können Sie sich auf der Ebene Serializable keine Gedanken über Inkonsistenzen machen, was die Codierung erheblich erleichtert. Das einzige, was von der Anwendung benötigt wird, ist die Möglichkeit, jede Transaktion zu wiederholen, wenn ein Serialisierungsfehler auftritt. Der Anteil abgebrochener Transaktionen, der zusätzliche Overhead und die Unfähigkeit, Abfragen zu parallelisieren, können den Systemdurchsatz jedoch erheblich reduzieren. Beachten Sie außerdem, dass die serialisierbare Ebene nicht auf Replikate anwendbar ist und nicht mit anderen Isolationsstufen gemischt werden kann.

Lesen Sie weiter .