Geekly articles weekly

Sperren in PostgreSQL: 3. Sperrt andere Objekte

Wir haben bereits über einige Sperren auf Objektebene gesprochen (insbesondere über Sperren für Beziehungen) sowie über Sperren auf Zeilenebene , ihre Beziehung zu Objektsperren und die Warteschlange, was nicht immer ehrlich ist.

Heute haben wir ein Durcheinander. Beginnen wir mit Deadlocks (eigentlich wollte ich das letzte Mal darüber sprechen, aber dieser Artikel stellte sich als unangemessen lang heraus), dann werden wir die verbleibenden Objektsperren durchgehen und abschließend über Prädikatsperren sprechen.

Deadlocks


Bei Verwendung von Sperren ist eine Deadlock- (oder Deadlock- ) Situation möglich. Es tritt auf, wenn eine Transaktion versucht, eine Ressource zu erfassen, die bereits von einer anderen Transaktion erfasst wurde, während eine andere Transaktion versucht, eine Ressource zu erfassen, die von der ersten Transaktion erfasst wurde. Dies ist in der linken Abbildung unten dargestellt: Durchgezogene Pfeile zeigen erfasste Ressourcen an, gestrichelte Pfeile zeigen Versuche an, eine bereits belegte Ressource zu erfassen.

Es ist praktisch, einen Deadlock zu visualisieren, indem Sie ein Diagramm der Erwartungen erstellen. Dazu entfernen wir bestimmte Ressourcen und belassen nur Transaktionen, wobei wir notieren, welche Transaktion wartet. Wenn das Diagramm eine Kontur hat (von oben können Sie es durch die Pfeile erreichen), ist dies ein Deadlock.



Natürlich ist ein Deadlock nicht nur für zwei Transaktionen möglich, sondern auch für eine größere Anzahl.

Wenn ein Deadlock auftritt, können die daran beteiligten Transaktionen nichts dagegen tun - sie warten auf unbestimmte Zeit. Daher verfolgen alle DBMS und auch PostgreSQL automatisch Deadlocks.

Das Überprüfen erfordert jedoch bestimmte Anstrengungen, die ich nicht unternehmen möchte, wenn eine neue Sperre angefordert wird (schließlich sind Deadlocks ziemlich selten). Wenn der Prozess versucht, die Sperre zu erfassen, und dies nicht kann, tritt er in die Warteschlange ein und schläft ein, startet den Timer jedoch auf den im Parameter deadlock_timeout angegebenen Wert (standardmäßig 1 Sekunde). Wenn die Ressource früher freigegeben wurde, haben wir bei der Überprüfung gespart. Wenn das Warten nach deadlock_timeout fortgesetzt wird, wird der Wartevorgang aktiviert und eine Überprüfung eingeleitet.

Wenn die Prüfung (die darin besteht, ein Diagramm der Erwartungen zu erstellen und darin nach Konturen zu suchen) keine Deadlocks ergab, schläft der Prozess weiter - jetzt bereits bis zum bitteren Ende.

Zu Beginn der Kommentare wurde mir zu Recht vorgeworfen, nichts über den Parameter lock_timeout gesagt zu haben , der auf einen Operator einwirkt und eine unbegrenzt lange Wartezeit vermeidet: Wenn die Sperre nicht in der angegebenen Zeit abgerufen werden konnte, endet die Anweisung mit dem Fehler lock_not_available. Es sollte nicht mit dem Parameter statement_timeout verwechselt werden, der die Gesamtausführungszeit der Anweisung begrenzt, unabhängig davon, ob sie eine Sperre erwartet oder nur den Job ausführt.

Wenn ein Deadlock erkannt wird, wird eine der Transaktionen (in den meisten Fällen die, die die Prüfung initiiert hat) zwangsweise beendet. In diesem Fall werden die von ihm erfassten Sperren freigegeben und die verbleibenden Transaktionen können weiterhin ausgeführt werden.

Deadlocks bedeuten normalerweise, dass die Anwendung nicht richtig gestaltet ist. Es gibt zwei Möglichkeiten, solche Situationen zu erkennen: Erstens werden Nachrichten im Serverprotokoll angezeigt, und zweitens erhöht sich der Wert von pg_stat_database.deadlocks.

Deadlock-Beispiel


Eine häufige Ursache für Deadlocks ist die unterschiedliche Reihenfolge, in der Zeilen in Tabellen gesperrt werden.
Ein einfaches Beispiel. Die erste Transaktion beabsichtigt, 100 Rubel vom ersten Konto auf das zweite Konto zu übertragen. Dazu reduziert sie zunächst die erste Zählung:

=> BEGIN; => UPDATE accounts SET amount = amount - 100.00 WHERE acc_no = 1; 
 UPDATE 1

Gleichzeitig beabsichtigt die zweite Transaktion, 10 Rubel vom zweiten Konto auf das erste Konto zu übertragen. Sie beginnt mit der Reduzierung der zweiten Anzahl:

 | => BEGIN; | => UPDATE accounts SET amount = amount - 10.00 WHERE acc_no = 2; 
 | UPDATE 1

Jetzt versucht die erste Transaktion, das zweite Konto zu erhöhen, stellt jedoch fest, dass die Zeile gesperrt ist.

 => UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 2;

Dann versucht die zweite Transaktion, das erste Konto zu erhöhen, wird aber ebenfalls gesperrt.

 | => UPDATE accounts SET amount = amount + 10.00 WHERE acc_no = 1;

Es gibt eine zyklische Erwartung, die niemals von alleine enden wird. Nach einer Sekunde initiiert die erste Transaktion, die keinen Zugriff auf die Ressource hat, eine Deadlock-Prüfung und bricht den Server ab.

 ERROR: deadlock detected DETAIL: Process 16477 waits for ShareLock on transaction 530695; blocked by process 16513. Process 16513 waits for ShareLock on transaction 530694; blocked by process 16477. HINT: See server log for query details. CONTEXT: while updating tuple (0,2) in relation "accounts"

Jetzt kann die zweite Transaktion fortgesetzt werden.

 | UPDATE 1 
 | => ROLLBACK; 

 => ROLLBACK;

Die richtige Methode zum Ausführen solcher Vorgänge besteht darin, Ressourcen in derselben Reihenfolge zu blockieren. In diesem Fall können Sie beispielsweise Konten in aufsteigender Reihenfolge ihrer Nummern sperren.

Deadlock für zwei UPDATE-Befehle


Manchmal kann es zu einem Stillstand kommen, bei dem es anscheinend nicht so sein sollte. Zum Beispiel ist es bequem und vertraut, SQL-Befehle als atomar zu betrachten, aber UPDATE zu verwenden - dieser Befehl blockiert Zeilen, wenn sie aktualisiert werden. Dies geschieht nicht sofort. Wenn ein Befehl die Zeilen in einer Reihenfolge und der andere in einer anderen Reihenfolge aktualisiert, sind sie möglicherweise blockiert.

Es ist unwahrscheinlich, dass es zu einer solchen Situation kommt, aber es kann sich trotzdem erfüllen. Für die Wiedergabe erstellen wir einen Index für die Betragsspalte, der in absteigender Reihenfolge des Betrags erstellt wird:

 => CREATE INDEX ON accounts(amount DESC);

Um Zeit zu haben, um zu sehen, was passiert, schreiben wir eine Funktion, die den übertragenen Wert für eine Sekunde langsam, langsam erhöht:

 => CREATE FUNCTION inc_slow(n numeric) RETURNS numeric AS $$ SELECT pg_sleep(1); SELECT n + 100.00; $$ LANGUAGE SQL;

Wir brauchen auch die pgrowlocks-Erweiterung.

 => CREATE EXTENSION pgrowlocks;

Der erste UPDATE-Befehl aktualisiert die gesamte Tabelle. Der Ausführungsplan ist offensichtlich - ein sequentieller Scan:

 | => EXPLAIN (costs off) | UPDATE accounts SET amount = inc_slow(amount); 
 | QUERY PLAN | ---------------------------- | Update on accounts | -> Seq Scan on accounts | (2 rows)

Da die Versionen der Zeilen auf der Seite unserer Tabelle in aufsteigender Reihenfolge der Summe sind (genau so, wie wir sie hinzugefügt haben), werden sie in derselben Reihenfolge aktualisiert. Wir starten das Update, um zu funktionieren.

 | => UPDATE accounts SET amount = inc_slow(amount);

In der Zwischenzeit werden wir in einer anderen Sitzung die Verwendung des sequentiellen Scannens verbieten:

 || => SET enable_seqscan = off;

In diesem Fall entscheidet sich der Scheduler, den Index-Scan für die folgende UPDATE-Anweisung zu verwenden:

 || => EXPLAIN (costs off) || UPDATE accounts SET amount = inc_slow(amount) WHERE amount > 100.00; 
 || QUERY PLAN || -------------------------------------------------------- || Update on accounts || -> Index Scan using accounts_amount_idx on accounts || Index Cond: (amount > 100.00) || (3 rows)

Die zweite und dritte Zeile fallen unter die Bedingung, und da der Index in absteigender Reihenfolge erstellt wird, werden die Zeilen in umgekehrter Reihenfolge aktualisiert.

Wir starten das nächste Update.

 || => UPDATE accounts SET amount = inc_slow(amount) WHERE amount > 100.00;

Ein kurzer Blick auf die tabellarische Seite zeigt, dass es dem ersten Operator bereits gelungen ist, die erste Zeile (0,1) und die zweite - die letzte (0,3) zu aktualisieren:

 => SELECT * FROM pgrowlocks('accounts') \gx 
 -[ RECORD 1 ]----------------- locked_row | (0,1) locker | 530699 <-  multi | f xids | {530699} modes | {"No Key Update"} pids | {16513} -[ RECORD 2 ]----------------- locked_row | (0,3) locker | 530700 <-  multi | f xids | {530700} modes | {"No Key Update"} pids | {16549}

Eine weitere Sekunde vergeht. Der erste Operator hat die zweite Zeile aktualisiert, und der zweite möchte dies tun, kann dies jedoch nicht.

 => SELECT * FROM pgrowlocks('accounts') \gx 
 -[ RECORD 1 ]----------------- locked_row | (0,1) locker | 530699 <-  multi | f xids | {530699} modes | {"No Key Update"} pids | {16513} -[ RECORD 2 ]----------------- locked_row | (0,2) locker | 530699 <-    multi | f xids | {530699} modes | {"No Key Update"} pids | {16513} -[ RECORD 3 ]----------------- locked_row | (0,3) locker | 530700 <-  multi | f xids | {530700} modes | {"No Key Update"} pids | {16549}

Jetzt möchte die erste Anweisung die letzte Zeile der Tabelle aktualisieren, ist aber bereits von der zweiten gesperrt. Hier ist die Sackgasse.

Eine der Transaktionen wird abgebrochen:

 || ERROR: deadlock detected || DETAIL: Process 16549 waits for ShareLock on transaction 530699; blocked by process 16513. || Process 16513 waits for ShareLock on transaction 530700; blocked by process 16549. || HINT: See server log for query details. || CONTEXT: while updating tuple (0,2) in relation "accounts"

Und der andere vervollständigt die Ausführung:

 | UPDATE 3

Interessante Details zum Erkennen und Verhindern von Deadlocks finden Sie im README Lock Manager .

Hier dreht sich alles um Deadlocks, und wir fahren mit den verbleibenden Objektsperren fort.



Nicht-Beziehungssperren


Wenn Sie eine Ressource sperren möchten, die für das Verständnis von PostgreSQL keine Beziehung darstellt , werden Objektsperren verwendet. Eine solche Ressource kann fast alles sein: Tabellenbereiche, Abonnements, Schemata, Rollen, aufgezählte Datentypen ... Grob gesagt alles, was im Systemkatalog enthalten ist.

Schauen wir uns ein einfaches Beispiel an. Wir starten die Transaktion und erstellen eine Tabelle darin:

 => BEGIN; => CREATE TABLE example(n integer);

Nun wollen wir sehen, welche Objektsperren in pg_locks angezeigt wurden:

 => SELECT database, (SELECT datname FROM pg_database WHERE oid = l.database) AS dbname, classid, (SELECT relname FROM pg_class WHERE oid = l.classid) AS classname, objid, mode, granted FROM pg_locks l WHERE l.locktype = 'object' AND l.pid = pg_backend_pid(); 
  database | dbname | classid | classname | objid | mode | granted ----------+--------+---------+--------------+-------+-----------------+--------- 0 | | 1260 | pg_authid | 16384 | AccessShareLock | t 16386 | test | 2615 | pg_namespace | 2200 | AccessShareLock | t (2 rows)

Um zu verstehen, was genau hier blockiert ist, müssen Sie sich drei Felder ansehen: Datenbank, Klasse und Objekt. Beginnen wir mit der ersten Zeile.

Datenbank ist die OID der Datenbank, zu der die gesperrte Ressource gehört. In unserem Fall gibt es in dieser Spalte Null. Dies bedeutet, dass es sich um ein globales Objekt handelt, das keiner bestimmten Basis angehört.

Classid enthält die OID von pg_class, die dem Namen der Systemkatalogtabelle entspricht, die den Ressourcentyp bestimmt. In unserem Fall ist pg_authid, dh die Rolle ist die Ressource (Benutzer).

Objid enthält die OID aus der Systemkatalogtabelle, die uns die Klassen-ID angegeben hat.

 => SELECT rolname FROM pg_authid WHERE oid = 16384; 
  rolname --------- student (1 row)

Somit ist die Studentenrolle blockiert, von der aus wir arbeiten.

Nun beschäftigen wir uns mit der zweiten Zeile. Die Datenbank wird angezeigt, und dies ist die Testdatenbank, mit der wir verbunden sind.

Classid zeigt auf die Tabelle pg_namespace, die die Schemas enthält.

 => SELECT nspname FROM pg_namespace WHERE oid = 2200; 
  nspname --------- public (1 row)

Somit ist das öffentliche Schema blockiert.

Wir haben also gesehen, dass beim Erstellen eines Objekts die Eigentümerrolle und das Schema, in dem das Objekt erstellt wird, blockiert sind (im freigegebenen Modus). Was logisch ist: Andernfalls könnte jemand die Rolle oder das Schema entfernen, während die Transaktion noch nicht abgeschlossen ist.

 => ROLLBACK;

Beziehungserweiterungssperre


Wenn die Anzahl der Zeilen in einer Beziehung (dh in einer Tabelle, einem Index oder einer materialisierten Ansicht) zunimmt, kann PostgreSQL den freien Speicherplatz auf den vorhandenen Seiten zum Einfügen verwenden, aber natürlich müssen Sie irgendwann neue Seiten hinzufügen. Physikalisch werden sie am Ende der entsprechenden Datei hinzugefügt. Dies wird als Erweiterung der Beziehung verstanden .

Um zu verhindern, dass zwei Prozesse gleichzeitig Seiten hinzufügen, wird dieser Prozess durch eine spezielle Sperre vom Typ verlängern geschützt. Dieselbe Sperre wird beim Bereinigen von Indizes verwendet, damit andere Prozesse beim Scannen keine Seiten hinzufügen können.

Natürlich wird diese Sperre aufgehoben, ohne auf das Ende der Transaktion zu warten.

Bisher wurden Tabellen jeweils nur um eine Seite erweitert. Dies verursachte Probleme, wenn mehrere Prozesse gleichzeitig Zeilen einfügten. Daher wurden in PostgreSQL 9.6 mehrere Seiten gleichzeitig zu den Tabellen hinzugefügt (proportional zur Anzahl der Prozesse, die auf das Sperren warten, jedoch nicht mehr als 512).

Seitensperre


In nur einem Fall wird eine Sperre auf Seitenebene angewendet (mit Ausnahme der Prädikatsperren, die später erläutert werden).

Mit GIN-Indizes können Sie die Suche in zusammengesetzten Werten beschleunigen, z. B. in Wörtern in Textdokumenten (oder in Elementen in Arrays). In erster Näherung können solche Indizes als regulärer B-Baum dargestellt werden, in dem nicht die Dokumente selbst gespeichert sind, sondern einzelne Wörter dieser Dokumente. Daher muss beim Hinzufügen eines neuen Dokuments der Index ziemlich stark neu erstellt werden, wobei jedes im Dokument enthaltene Wort eingefügt wird.

Um die Leistung zu verbessern, verfügen GIN-Indizes über eine Funktion zum verzögerten Einfügen, die durch die Speicheroption fastupdate aktiviert wird. Neue Wörter werden zuerst schnell zur ungeordneten ausstehenden Liste hinzugefügt, und nach einiger Zeit wird alles, was sich angesammelt hat, in die Hauptindexstruktur verschoben. Einsparungen sind auf die Tatsache zurückzuführen, dass verschiedene Dokumente wahrscheinlich doppelte Wörter enthalten.

Um zu verhindern, dass mehrere Prozesse gleichzeitig von der Warteliste in den Hauptindex verschoben werden, wird die Index-Metaseite für die Dauer der Übertragung im exklusiven Modus blockiert. Dies beeinträchtigt die Verwendung des Index im normalen Modus nicht.

Hinweisschlösser


Im Gegensatz zu anderen Sperren (z. B. Beziehungssperren) werden Beratungssperren niemals automatisch festgelegt, sondern vom Anwendungsentwickler verwaltet. Sie sind beispielsweise dann bequem zu verwenden, wenn eine Anwendung für einen bestimmten Zweck eine Blockierungslogik benötigt, die nicht in die Standardlogik gewöhnlicher Sperren passt.

Angenommen, wir haben eine bedingte Ressource, die keinem Datenbankobjekt entspricht (das wir mit Befehlen wie SELECT FOR oder LOCK TABLE blockieren könnten). Sie müssen eine numerische Kennung dafür erstellen. Wenn die Ressource einen eindeutigen Namen hat, können Sie einfach einen Hash-Code daraus entnehmen:

 => SELECT hashtext('1'); 
  hashtext ----------- 243773337 (1 row)

So erfassen wir das Schloss:

 => BEGIN; => SELECT pg_advisory_lock(hashtext('1'));

Wie üblich sind Sperrinformationen in pg_locks verfügbar:

 => SELECT locktype, objid, mode, granted FROM pg_locks WHERE locktype = 'advisory' AND pid = pg_backend_pid(); 
  locktype | objid | mode | granted ----------+-----------+---------------+--------- advisory | 243773337 | ExclusiveLock | t (1 row)

Damit eine Sperre tatsächlich funktioniert, müssen auch andere Prozesse eine Sperre erhalten, bevor sie auf die Ressource zugreifen können. Die Einhaltung dieser Regel sollte natürlich durch den Antrag sichergestellt werden.

Im obigen Beispiel ist die Sperre bis zum Ende der Sitzung gültig und nicht wie üblich bis zur Transaktion.

 => COMMIT; => SELECT locktype, objid, mode, granted FROM pg_locks WHERE locktype = 'advisory' AND pid = pg_backend_pid(); 
  locktype | objid | mode | granted ----------+-----------+---------------+--------- advisory | 243773337 | ExclusiveLock | t (1 row)

Es muss ausdrücklich freigegeben werden:

 => SELECT pg_advisory_unlock(hashtext('1'));

Es gibt eine Vielzahl von Funktionen für die Arbeit mit Hinweisschlössern für alle Gelegenheiten:

  • pg_advisory_lock_shared behandelt eine gemeinsam genutzte Sperre.
  • pg_advisory_xact_lock (und pg_advisory_xact_lock_shared) erhalten eine Sperre bis zum Ende der Transaktion.
  • pg_try_advisory_lock (sowie pg_try_advisory_xact_lock und pg_try_advisory_xact_lock_shared) erwartet keine Sperre, gibt jedoch einen falschen Wert zurück, wenn die Sperre nicht sofort abgerufen werden konnte.

Die Try-Funktionen bieten zusätzlich zu den in einem vorherigen Artikel aufgeführten eine weitere Möglichkeit, nicht auf eine Sperre zu warten.

Prädikatsperren


Der Begriff Prädikatsperre tauchte vor langer Zeit bei den ersten Versuchen auf, eine vollständige Isolation basierend auf Sperren in frühen DBMS zu implementieren (die Ebene ist serialisierbar, obwohl der SQL-Standard zu diesem Zeitpunkt noch nicht existierte). Das Problem, das dann auftrat, war, dass selbst das Blockieren aller gelesenen und geänderten Zeilen keine vollständige Isolation bietet: In der Tabelle werden möglicherweise neue Zeilen angezeigt, die unter denselben Auswahlbedingungen liegen, was zu Phantomen führt (siehe Artikel zur Isolierung ). .

Die Idee von Prädikatsperren bestand darin, Prädikate und keine Zeilen zu blockieren. Wenn beim Ausführen einer Abfrage mit der Bedingung a > 10 das Prädikat a > 10 blockiert wird, werden der Tabelle keine neuen Zeilen hinzugefügt, die unter die Bedingung fallen, und Phantome werden vermieden. Das Problem ist, dass dies im allgemeinen Fall eine rechenintensive Aufgabe ist; In der Praxis kann es nur für Prädikate gelöst werden, die eine sehr einfache Form haben.

In PostgreSQL wird die serialisierbare Schicht zusätzlich zur vorhandenen Snapshot-basierten Isolation anders implementiert. Der Begriff Prädikatsperre bleibt erhalten, aber seine Bedeutung hat sich radikal geändert. Tatsächlich blockieren solche „Sperren“ nichts, sondern werden verwendet, um Datenabhängigkeiten zwischen Transaktionen zu verfolgen.

Es ist erwiesen, dass eine auf Bildern basierende Isolation eine Anomalie inkonsistenter Aufzeichnung und eine Anomalie nur einer Lesetransaktion zulässt, aber keine anderen Anomalien möglich sind. Um zu verstehen, dass es sich um eine der beiden aufgeführten Anomalien handelt, können wir die Abhängigkeiten zwischen Transaktionen analysieren und bestimmte Muster darin finden.

Wir interessieren uns für zwei Arten von Abhängigkeiten:

  • Eine Transaktion liest eine Zeile, die dann von einer anderen Transaktion geändert wird (RW-Abhängigkeit).
  • Eine Transaktion ändert die Zeile, die eine andere Transaktion dann liest (WR-Abhängigkeit).

WR-Abhängigkeiten können mit vorhandenen herkömmlichen Sperren verfolgt werden, RW-Abhängigkeiten müssen jedoch nur zusätzlich verfolgt werden.

Ich wiederhole noch einmal: Trotz des Namens blockieren Prädikatsperren nichts. Wenn eine Transaktion festgeschrieben wird, wird stattdessen eine Prüfung durchgeführt. Wenn eine „schlechte“ Folge von Abhängigkeiten festgestellt wird, die auf eine Anomalie hinweisen kann, wird die Transaktion unterbrochen.

Mal sehen, wie die Installation von Prädikatsperren erfolgt. Erstellen Sie dazu eine Tabelle mit einer ausreichend großen Anzahl von Zeilen und einem Index.

 => CREATE TABLE pred(n integer); => INSERT INTO pred(n) SELECT gn FROM generate_series(1,10000) g(n); => CREATE INDEX ON pred(n) WITH (fillfactor = 10); => ANALYZE pred;

Wenn die Abfrage durch sequentielles Scannen der gesamten Tabelle ausgeführt wird, wird die Prädikatsperre für die gesamte Tabelle festgelegt (auch wenn nicht alle Zeilen unter die Filterbedingungen fallen).

 | => SELECT pg_backend_pid(); 
 | pg_backend_pid | ---------------- | 12763 | (1 row) 

 | => BEGIN ISOLATION LEVEL SERIALIZABLE; | => EXPLAIN (analyze, costs off) | SELECT * FROM pred WHERE n > 100; 
 | QUERY PLAN | ---------------------------------------------------------------- | Seq Scan on pred (actual time=0.047..12.709 rows=9900 loops=1) | Filter: (n > 100) | Rows Removed by Filter: 100 | Planning Time: 0.190 ms | Execution Time: 15.244 ms | (5 rows)

Alle Prädikatsperren werden immer in einem speziellen SIReadLock-Modus (Serializable Isolation Read) erfasst:

 => SELECT locktype, relation::regclass, page, tuple FROM pg_locks WHERE mode = 'SIReadLock' AND pid = 12763; 
  locktype | relation | page | tuple ----------+----------+------+------- relation | pred | | (1 row) 

 | => ROLLBACK;

Wenn die Abfrage jedoch mithilfe der Indexprüfung ausgeführt wird, ändert sich die Situation zum Besseren. Wenn wir über den B-Baum sprechen, reicht es aus, die Sperre für die Zeilen der Lesetabelle und für die Blattseiten des Index zu setzen. Dadurch blockieren wir nicht nur bestimmte Werte, sondern auch den gesamten gelesenen Bereich.

 | => BEGIN ISOLATION LEVEL SERIALIZABLE; | => EXPLAIN (analyze, costs off) | SELECT * FROM pred WHERE n BETWEEN 1000 AND 1001; 
 | QUERY PLAN | ------------------------------------------------------------------------------------ | Index Only Scan using pred_n_idx on pred (actual time=0.122..0.131 rows=2 loops=1) | Index Cond: ((n >= 1000) AND (n <= 1001)) | Heap Fetches: 2 | Planning Time: 0.096 ms | Execution Time: 0.153 ms | (5 rows) 

 => SELECT locktype, relation::regclass, page, tuple FROM pg_locks WHERE mode = 'SIReadLock' AND pid = 12763; 
  locktype | relation | page | tuple ----------+------------+------+------- tuple | pred | 3 | 236 tuple | pred | 3 | 235 page | pred_n_idx | 22 | (3 rows)

Möglicherweise stellen Sie mehrere Schwierigkeiten fest.

Zunächst wird für jede gelesene Version der Zeile eine separate Sperre erstellt. Möglicherweise gibt es jedoch viele solcher Versionen. Die Gesamtzahl der Prädikatsperren im System wird durch das Produkt der Parameterwerte max_pred_locks_per_transaction × max_connections begrenzt (die Standardwerte sind 64 bzw. 100). Der Speicher für solche Sperren wird beim Serverstart zugewiesen. Der Versuch, diese Anzahl zu überschreiten, führt zu Fehlern.

Daher wird für Prädikatsperren (und nur für sie!) Eine Stufenerhöhung verwendet. Vor PostgreSQL 10 gab es Einschränkungen, die fest im Code verankert waren, und beginnend damit können Sie die Parameter steuern, indem Sie den Pegel erhöhen. Wenn die Anzahl der Zeilenversionssperren pro Zeile größer als max_pred_locks_per_page ist , werden diese Sperren durch eine Sperre auf Seitenebene ersetzt. Hier ist ein Beispiel:

 => SHOW max_pred_locks_per_page; 
  max_pred_locks_per_page ------------------------- 2 (1 row) 

 | => EXPLAIN (analyze, costs off) | SELECT * FROM pred WHERE n BETWEEN 1000 AND 1002; 
 | QUERY PLAN | ------------------------------------------------------------------------------------ | Index Only Scan using pred_n_idx on pred (actual time=0.019..0.039 rows=3 loops=1) | Index Cond: ((n >= 1000) AND (n <= 1002)) | Heap Fetches: 3 | Planning Time: 0.069 ms | Execution Time: 0.057 ms | (5 rows)

Anstelle von drei Tupel-Sperren sehen wir einen Seitentyp:

 => SELECT locktype, relation::regclass, page, tuple FROM pg_locks WHERE mode = 'SIReadLock' AND pid = 12763; 
  locktype | relation | page | tuple ----------+------------+------+------- page | pred | 3 | page | pred_n_idx | 22 | (2 rows)

Wenn die Anzahl der einer einzelnen Beziehung zugeordneten Seitensperren max_pred_locks_per_relation überschreitet, werden diese Sperren durch eine Sperre auf Beziehungsebene ersetzt.

Es gibt keine anderen Ebenen: Prädikatsperren werden nur für Relationen, Seiten oder Zeilenversionen und immer im SIReadLock-Modus erfasst.

Natürlich führt eine Erhöhung der Sperrenstufe zwangsläufig dazu, dass eine größere Anzahl von Transaktionen fälschlicherweise zu einem Serialisierungsfehler führt und infolgedessen der Durchsatz des Systems abnimmt. Hier müssen Sie nach einem Gleichgewicht zwischen Speicherverbrauch und Leistung suchen.

Die zweite Schwierigkeit besteht darin, dass sich bei verschiedenen Operationen mit dem Index (z. B. aufgrund der Aufteilung der Indexseiten beim Einfügen neuer Zeilen) die Anzahl der Blattseiten, die den Lesebereich abdecken, ändern kann. Die Umsetzung berücksichtigt jedoch:

 => INSERT INTO pred SELECT 1001 FROM generate_series(1,1000); => SELECT locktype, relation::regclass, page, tuple FROM pg_locks WHERE mode = 'SIReadLock' AND pid = 12763; 
  locktype | relation | page | tuple ----------+------------+------+------- page | pred | 3 | page | pred_n_idx | 211 | page | pred_n_idx | 212 | page | pred_n_idx | 22 | (4 rows) 

 | => ROLLBACK;

Prädikatsperren werden übrigens nicht immer unmittelbar nach Abschluss der Transaktion entfernt, da sie zur Verfolgung der Abhängigkeiten zwischen mehreren Transaktionen benötigt werden. In jedem Fall werden sie jedoch automatisch verwaltet.

Nicht alle Indextypen in PostgreSQL unterstützen Prädikatsperren. Bisher konnten sich nur B-Bäume damit rühmen, aber in PostgreSQL 11 verbesserte sich die Situation: Hash-Indizes, GiST und GIN wurden der Liste hinzugefügt. Wenn der Indexzugriff verwendet wird und der Index nicht mit Prädikatsperren funktioniert, ist der gesamte Index an die Sperre gebunden. Dies erhöht natürlich auch die Anzahl falscher Transaktionsunterbrechungen.

Abschließend stelle ich fest, dass es bei Verwendung von Prädikatsperren eine Einschränkung gibt, dass alle Transaktionen auf der Ebene der Serialisierbarkeit ausgeführt werden müssen, um eine vollständige Isolation zu gewährleisten. Wenn eine Transaktion eine andere Ebene verwendet, werden Prädikatsperren einfach nicht gesetzt (und überprüft).

Traditionell hinterlasse ich einen Link zu README für Prädikatsperren , von dem aus Sie mit dem Studium des Quellcodes beginnen können.

Fortsetzung folgt .

Source: https://habr.com/ru/post/de465263/

More articles:


All Articles