Beim letzten Mal haben wir über Sperren auf Objektebene gesprochen, insbesondere über Sperren für Beziehungen. Heute werden wir sehen, wie Zeilensperren in PostgreSQL angeordnet sind und wie sie zusammen mit Objektsperren verwendet werden. Lassen Sie uns über wartende Warteschlangen und über diejenigen sprechen, die aus der Kurve klettern.

Zeilensperren

Gerät

Ich möchte Sie an einige wichtige Schlussfolgerungen aus dem letzten Artikel erinnern.

• Irgendwo im gemeinsam genutzten Speicher des Servers muss eine Sperre vorhanden sein.
• Je höher die Granularität von Sperren ist, desto weniger Konkurrenz (Konkurrenz) zwischen gleichzeitig ausgeführten Prozessen.
• Je höher die Granularität ist, desto mehr Speicherplatz wird von Sperren belegt.

Wir möchten sicher, dass durch die Änderung einer Zeile keine anderen Zeilen derselben Tabelle blockiert werden. Wir können es uns jedoch nicht leisten, jede Zeile mit einem eigenen Schloss zu beginnen.

Es gibt verschiedene Möglichkeiten, dieses Problem zu lösen. In einigen DBMS steigt die Ebene der Sperren: Wenn zu viele Sperren auf Zeilenebene vorhanden sind, werden sie durch eine allgemeinere Sperre ersetzt (z. B. Seitenebene oder die gesamte Tabelle).

Wie wir später sehen werden, verwendet PostgreSQL diesen Mechanismus ebenfalls, jedoch nur für Prädikatsperren. Leitungssperren sind unterschiedlich.

In PostgreSQL werden Informationen darüber, dass eine Zeile gesperrt ist, ausschließlich in der Version der Zeile auf der Datenseite (und nicht im RAM) gespeichert. Das heißt, dies ist überhaupt kein Block im üblichen Sinne, sondern nur ein Zeichen. Dieses Vorzeichen ist tatsächlich die xmax-Transaktionsnummer in Kombination mit zusätzlichen Informationsbits. wenig später werden wir im Detail sehen, wie das funktioniert.

Das Plus ist, dass wir so viele Zeilen blockieren können, wie wir möchten, ohne Ressourcen zu verbrauchen.

Es gibt jedoch ein Minus : Da Informationen über die Sperre nicht im RAM angezeigt werden, können andere Prozesse nicht in einer Reihe stehen. Und es gibt keine Überwachungsmöglichkeit (um die Sperren zu berechnen, müssen Sie die gesamte Tabelle lesen).

Nun, die Überwachung ist in Ordnung, aber mit der Warteschlange muss etwas getan werden. Dazu müssen Sie noch "normale" Sperren verwenden. Wenn wir warten müssen, bis die Zeile freigegeben ist, müssen wir tatsächlich bis zum Ende der blockierenden Transaktion warten - alle Sperren werden beim Festschreiben oder Zurücksetzen freigegeben. Und dafür können Sie eine Sperrnummer einer Sperrtransaktion anfordern (die, wie ich mich erinnere, von der Transaktion selbst im Ausnahmemodus gehalten wird). Somit ist die Anzahl der verwendeten Sperren proportional zur Anzahl der gleichzeitig ausgeführten Prozesse und nicht zur Anzahl der zu ändernden Zeilen.

Außergewöhnliche Modi

Insgesamt gibt es 4 Modi, in denen Sie die Leitung sperren können. Von diesen stellen zwei Modi exklusive Sperren dar, die jeweils nur eine Transaktion halten kann.

• Der FOR UPDATE-Modus impliziert eine vollständige Änderung (oder Löschung) einer Zeile.
• FOR NO KEY UPDATE-Modus - Ändern Sie nur die Felder, die nicht in eindeutigen Indizes enthalten sind (mit anderen Worten, bei einer solchen Änderung bleiben alle Fremdschlüssel unverändert).

Der Befehl UPDATE selbst wählt den minimal geeigneten Sperrmodus aus. Normalerweise werden Zeilen im FOR NO KEY UPDATE-Modus gesperrt.

Wie Sie sich erinnern , wird beim Löschen oder Ändern einer Zeile die aktuelle Transaktionsversionsnummer in das Feld xmax der aktuellen aktuellen Version geschrieben. Es zeigt, dass die Version der Zeile durch diese Transaktion gelöscht wurde. Daher wird dieselbe xmax-Nummer als Zeichen der Blockierung verwendet. Wenn xmax in der Version der Zeile einer aktiven (noch nicht abgeschlossenen) Transaktion entspricht und wir diese bestimmte Zeile aktualisieren möchten, müssen wir auf den Abschluss der Transaktion warten, sodass kein zusätzliches Vorzeichen erforderlich ist.

Mal sehen. Erstellen Sie eine Kontenübersicht wie im vorherigen Artikel.

=> CREATE TABLE accounts( acc_no integer PRIMARY KEY, amount numeric ); => INSERT INTO accounts VALUES (1, 100.00), (2, 200.00), (3, 300.00); 

Zum Betrachten der Seiten benötigen wir natürlich die bereits bekannte pageinspect-Erweiterung.

 => CREATE EXTENSION pageinspect; 

Erstellen Sie der Einfachheit halber eine Ansicht, in der nur die Informationen angezeigt werden, an denen wir interessiert sind: xmax und einige Informationsbits.

 => CREATE VIEW accounts_v AS SELECT '(0,'||lp||')' AS ctid, t_xmax as xmax, CASE WHEN (t_infomask & 128) > 0 THEN 't' END AS lock_only, CASE WHEN (t_infomask & 4096) > 0 THEN 't' END AS is_multi, CASE WHEN (t_infomask2 & 8192) > 0 THEN 't' END AS keys_upd, CASE WHEN (t_infomask & 16) > 0 THEN 't' END AS keyshr_lock, CASE WHEN (t_infomask & 16+64) = 16+64 THEN 't' END AS shr_lock FROM heap_page_items(get_raw_page('accounts',0)) ORDER BY lp; 

Also starten wir die Transaktion und aktualisieren den Betrag des ersten Kontos (der Schlüssel ändert sich nicht) und die Nummer des zweiten Kontos (der Schlüssel ändert sich):

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

Wir schauen in die Ansicht:

 => SELECT * FROM accounts_v LIMIT 2; 

  ctid | xmax | lock_only | is_multi | keys_upd | keyshr_lock | shr_lock -------+--------+-----------+----------+----------+-------------+---------- (0,1) | 530492 | | | | | (0,2) | 530492 | | | t | | (2 rows) 

Der Sperrmodus wird durch das Informationsbit keys_updated bestimmt.

Das gleiche xmax-Feld wird auch beim Sperren einer Zeile mit dem Befehl SELECT FOR UPDATE verwendet. In diesem Fall wird jedoch ein zusätzliches Informationsbit (xmax_lock_only) abgelegt, das angibt, dass die Version der Zeile nur gesperrt, aber nicht gelöscht ist und weiterhin relevant ist.

 => ROLLBACK; => BEGIN; => SELECT * FROM accounts WHERE acc_no = 1 FOR NO KEY UPDATE; => SELECT * FROM accounts WHERE acc_no = 2 FOR UPDATE; 

 => SELECT * FROM accounts_v LIMIT 2; 

  ctid | xmax | lock_only | is_multi | keys_upd | keyshr_lock | shr_lock -------+--------+-----------+----------+----------+-------------+---------- (0,1) | 530493 | t | | | | (0,2) | 530493 | t | | t | | (2 rows) 

 => ROLLBACK; 

Gemeinsame Modi

Zwei weitere Modi stellen gemeinsam genutzte Sperren dar, die von mehreren Transaktionen gehalten werden können.

• Der FOR SHARE-Modus wird verwendet, wenn Sie eine Zeichenfolge lesen müssen, aber nicht zulassen dürfen, dass sie durch eine andere Transaktion in irgendeiner Weise geändert wird.
• Der FOR KEY SHARE-Modus ermöglicht das Ändern der Zeichenfolge, jedoch nur von Nicht-Schlüsselfeldern. Insbesondere dieser Modus wird von PostgreSQL beim Überprüfen von Fremdschlüsseln automatisch verwendet.

Mal sehen.

 => BEGIN; => SELECT * FROM accounts WHERE acc_no = 1 FOR KEY SHARE; => SELECT * FROM accounts WHERE acc_no = 2 FOR SHARE; 

In Zeilenversionen sehen wir:

 => SELECT * FROM accounts_v LIMIT 2; 

  ctid | xmax | lock_only | is_multi | keys_upd | keyshr_lock | shr_lock -------+--------+-----------+----------+----------+-------------+---------- (0,1) | 530494 | t | | | t | (0,2) | 530494 | t | | | t | t (2 rows) 

In beiden Fällen wird das Bit keyshr_lock gesetzt, und der SHARE-Modus kann durch Betrachten eines weiteren Informationsbits erkannt werden.

So sieht die Kompatibilitätsmatrix für den allgemeinen Modus aus.


Es zeigt, dass:

• außergewöhnliche Modi stehen in Konflikt miteinander;
• gemeinsame Modi sind miteinander kompatibel;
• Der gemeinsam genutzte FOR KEY SHARE-Modus ist mit dem exklusiven FOR NO KEY UPDATE-Modus kompatibel (dh Sie können Nicht-Schlüsselfelder gleichzeitig aktualisieren und sicherstellen, dass sich der Schlüssel nicht ändert).

Multi-Transaktion

Bisher dachten wir, dass die Sperre durch die Nummer der blockierenden Transaktion im xmax-Feld dargestellt wird. Gemeinsame Sperren können jedoch von mehreren Transaktionen gehalten werden, und mehrere Nummern können nicht in dasselbe xmax-Feld geschrieben werden. Wie man ist

Für gemeinsam genutzte Sperren werden die sogenannten Multi-Transaktionen (MultiXact) verwendet. Dies ist eine Transaktionsgruppe, der eine separate Nummer zugewiesen ist. Diese Nummer hat dieselbe Dimension wie eine reguläre Transaktionsnummer, die Nummern werden jedoch unabhängig voneinander zugewiesen (dh das System kann dieselbe Transaktions- und Mehrfachtransaktionsnummer haben). Zur Unterscheidung wird ein anderes Informationsbit (xmax_is_multi) verwendet, und detaillierte Informationen zu den Mitgliedern einer solchen Gruppe und den Sperrmodi befinden sich in den Dateien im Verzeichnis $ PGDATA / pg_multixact /. Natürlich werden die zuletzt verwendeten Daten in Puffern im gemeinsam genutzten Speicher des Servers gespeichert, um einen schnelleren Zugriff zu ermöglichen.

Fügen Sie zu den vorhandenen Sperren eine weitere Ausnahme hinzu, die von einer anderen Transaktion ausgeführt wird (wir können dies tun, da die Modi FOR KEY SHARE und FOR NO KEY UPDATE miteinander kompatibel sind):

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

 => SELECT * FROM accounts_v LIMIT 2; 

  ctid | xmax | lock_only | is_multi | keys_upd | keyshr_lock | shr_lock -------+--------+-----------+----------+----------+-------------+---------- (0,1) | 61 | | t | | | (0,2) | 530494 | t | | | t | t (2 rows) 

In der ersten Zeile sehen wir, dass die übliche Nummer durch eine Multitransaktionsnummer ersetzt wurde - dies wird durch das Bit xmax_is_multi angezeigt.

Um nicht in die Interna der Multitransaction-Implementierung einzutauchen, können Sie eine andere Erweiterung verwenden, mit der Sie alle Informationen zu allen Arten von Zeilensperren auf bequeme Weise anzeigen können.

 => CREATE EXTENSION pgrowlocks; => SELECT * FROM pgrowlocks('accounts') \gx 

 -[ RECORD 1 ]----------------------------- locked_row | (0,1) locker | 61 multi | t xids | {530494,530495} modes | {"Key Share","No Key Update"} pids | {5892,5928} -[ RECORD 2 ]----------------------------- locked_row | (0,2) locker | 530494 multi | f xids | {530494} modes | {"For Share"} pids | {5892} 

 => COMMIT; 

 | => ROLLBACK; 

Einstellung einfrieren

Da für Multitransaktionen, die in das xmax-Feld von Zeilenversionen geschrieben werden, aufgrund der Begrenzung der Bitkapazität des Zählers separate Nummern zugewiesen werden, tritt das gleiche xid-Umlaufproblem auf wie bei einer regulären Nummer.

Daher ist es für Mehrfachtransaktionsnummern auch erforderlich, ein Analogon zum Einfrieren durchzuführen - ersetzen Sie die alten Nummern durch neue (oder durch eine reguläre Transaktionsnummer, wenn zum Zeitpunkt des Einfrierens die Sperre nur von einer Transaktion gehalten wird).

Beachten Sie, dass das Einfrieren gewöhnlicher Transaktionsnummern nur für das Feld xmin durchgeführt wird (da die Version der Zeile ein nicht leeres xmax-Feld enthält, handelt es sich entweder um eine irrelevante Version, die gelöscht wird, oder die xmax-Transaktion wird abgebrochen und ihre Nummer interessiert uns nicht). Bei Mehrfachtransaktionen handelt es sich jedoch um das xmax-Feld der aktuellen Version der Zeile, das zwar relevant bleiben kann, jedoch in einem gemeinsam genutzten Modus ständig von verschiedenen Transaktionen blockiert wird.

Für das Einfrieren von Mehrfachtransaktionen sind Parameter verantwortlich, die den Parametern des üblichen Einfrierens ähnlich sind: vakuum_multixact_freeze_min_age , vakuum_multixact_freeze_table_age , autovacuum_multixact_freeze_max_age .

Wer ist das Extrem?

Allmählich nähern Sie sich dem Süßen. Lassen Sie uns sehen, wie das Bild von Sperren aussieht, wenn mehrere Transaktionen dieselbe Zeile aktualisieren.

Beginnen wir mit der Erstellung einer Ansicht über pg_locks. Erstens werden wir die Schlussfolgerung etwas kompakter machen und zweitens werden wir uns auf interessante Sperren beschränken (tatsächlich verwerfen wir die Sperren virtueller Transaktionsnummern, den Index in der Kontentabelle, pg_locks und die Ansicht selbst - im Allgemeinen alles, was irrelevant ist und nur ablenkend).

 => CREATE VIEW locks_v AS SELECT pid, locktype, CASE locktype WHEN 'relation' THEN relation::regclass::text WHEN 'transactionid' THEN transactionid::text WHEN 'tuple' THEN relation::regclass::text||':'||tuple::text END AS lockid, mode, granted FROM pg_locks WHERE locktype in ('relation','transactionid','tuple') AND (locktype != 'relation' OR relation = 'accounts'::regclass); 

Starten Sie nun die erste Transaktion und aktualisieren Sie die Zeile.

 => BEGIN; => SELECT txid_current(), pg_backend_pid(); 

  txid_current | pg_backend_pid --------------+---------------- 530497 | 5892 (1 row) 

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

 UPDATE 1 

Was ist mit Schlössern?

 => SELECT * FROM locks_v WHERE pid = 5892; 

  pid | locktype | lockid | mode | granted ------+---------------+----------+------------------+--------- 5892 | relation | accounts | RowExclusiveLock | t 5892 | transactionid | 530497 | ExclusiveLock | t (2 rows) 

Die Transaktion enthält die Tabelle und eigene Nummernsperren. Bisher wird alles erwartet.

Wir starten die zweite Transaktion und versuchen, dieselbe Zeile zu aktualisieren.

 | => BEGIN; | => SELECT txid_current(), pg_backend_pid(); 

 | txid_current | pg_backend_pid | --------------+---------------- | 530498 | 5928 | (1 row) 

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

Was ist mit zweiten Transaktionssperren?

 => SELECT * FROM locks_v WHERE pid = 5928; 

  pid | locktype | lockid | mode | granted ------+---------------+------------+------------------+--------- 5928 | relation | accounts | RowExclusiveLock | t 5928 | transactionid | 530498 | ExclusiveLock | t 5928 | transactionid | 530497 | ShareLock | f 5928 | tuple | accounts:1 | ExclusiveLock | t (4 rows) 

Und hier ist es interessanter. Zusätzlich zum Sperren der Tabelle und der eigenen Nummer sehen wir zwei weitere Sperren. Bei der zweiten Transaktion wurde festgestellt, dass die Zeile zuerst gesperrt war und auf ihre Nummer wartete (gewährt = f). Aber woher und warum kam die Zeilenversionssperre (locktype = tuple)?

Verwechseln Sie nicht die Zeilenversionssperre (Tupel-Sperre) und die Zeilen-Sperre (Zeilen-Sperre). Die erste ist eine reguläre Sperre vom Typ Tupel, die in pg_locks sichtbar ist. Die zweite ist eine Markierung auf der Datenseite: xmax und Informationsbits.

Wenn eine Transaktion eine Zeile ändern soll, führt sie die folgende Abfolge von Aktionen aus:

1. Erfasst eine exklusive Sperre für eine veränderbare Version eines Strings (Tupel).
2. Wenn xmax- und Informationsbits anzeigen, dass die Leitung gesperrt ist, werden Sie aufgefordert, die xmax-Transaktionsnummer zu sperren.
3. Verschreibt die xmax- und erforderlichen Informationsbits.
4. Löst die Zeilenversionssperre auf.

Wenn die Zeile durch die erste Transaktion aktualisiert wurde, wurde auch die Zeilenversionssperre aktiviert (Schritt 1), diese jedoch sofort freigegeben (Schritt 4).

Als die zweite Transaktion eintraf, erfasste sie die Sperre der Zeilenversion (Element 1), musste jedoch eine Sperre für die Nummer der ersten Transaktion (Element 2) anfordern und daran festhalten.

Was passiert, wenn eine dritte ähnliche Transaktion angezeigt wird? Sie wird versuchen, die Sperre der Version der Zeile (Punkt 1) zu erfassen und wird bereits bei diesem Schritt hängen. Schau es dir an.

 || => BEGIN; || => SELECT txid_current(), pg_backend_pid(); 

 || txid_current | pg_backend_pid || --------------+---------------- || 530499 | 5964 || (1 row) 

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

 => SELECT * FROM locks_v WHERE pid = 5964; 

  pid | locktype | lockid | mode | granted ------+---------------+------------+------------------+--------- 5964 | relation | accounts | RowExclusiveLock | t 5964 | tuple | accounts:1 | ExclusiveLock | f 5964 | transactionid | 530499 | ExclusiveLock | t (3 rows) 

Die vierten, fünften usw. Transaktionen, die dieselbe Zeile aktualisieren möchten, unterscheiden sich nicht von Transaktion 3 - sie hängen alle an derselben Zeilenversionssperre.

Fügen Sie dem Heap eine weitere Transaktion hinzu.

 ||| => BEGIN; ||| => SELECT txid_current(), pg_backend_pid(); 

 ||| txid_current | pg_backend_pid ||| --------------+---------------- ||| 530500 | 6000 ||| (1 row) 

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

 => SELECT * FROM locks_v WHERE pid = 6000; 

  pid | locktype | lockid | mode | granted ------+---------------+------------+------------------+--------- 6000 | relation | accounts | RowExclusiveLock | t 6000 | transactionid | 530500 | ExclusiveLock | t 6000 | tuple | accounts:1 | ExclusiveLock | f (3 rows) 

Ein allgemeines Bild der aktuellen Erwartungen finden Sie in der Ansicht pg_stat_activity, in der Informationen zu Blockierungsprozessen hinzugefügt werden:

 => SELECT pid, wait_event_type, wait_event, pg_blocking_pids(pid) FROM pg_stat_activity WHERE backend_type = 'client backend'; 

  pid | wait_event_type | wait_event | pg_blocking_pids ------+-----------------+---------------+------------------ 5892 | | | {} 5928 | Lock | transactionid | {5892} 5964 | Lock | tuple | {5928} 6000 | Lock | tuple | {5928,5964} (4 rows) 

Es stellt sich eine Art „Warteschlange“ heraus, in der sich die erste (diejenige, die die Sperrversion der Zeichenfolge hält) und alle anderen für die erste anstellen.

Warum brauchen wir ein so anspruchsvolles Design? Angenommen, wir hätten keine Versionssperre für die Zeichenfolge. Dann würden die zweite und dritte (und so weiter) Transaktion auf die Blockierung der Nummer der ersten Transaktion warten. Zum Zeitpunkt des Abschlusses der ersten Transaktion verschwindet die blockierte Ressource ( und was machen Sie hier, was? Die Transaktion wurde beendet ). Jetzt hängt alles davon ab, welcher der Wartevorgänge zuerst vom Betriebssystem aktiviert wird und dementsprechend Zeit hat, die Leitung zu sperren. Alle anderen Prozesse werden ebenfalls aktiviert, müssen jedoch erneut in die Warteschlange gestellt werden - jetzt nach einem anderen Prozess.

Dies ist mit der Tatsache behaftet, dass einige der Transaktionen auf unbestimmte Zeit warten können, bis sie an die Reihe kommen, wenn sie aufgrund eines unglücklichen Zusammentreffens der Umstände andere Transaktionen immer „umrunden“. Im Englischen wird diese Situation als Schleusenhunger bezeichnet.

In unserem Fall stellt sich heraus, dass es ungefähr dasselbe ist, aber immer noch ein bisschen besser: Die Transaktion, die in der zweiten kam, garantiert, dass sie Zugriff auf die nächste Ressource erhält. Aber was passiert mit Folgendem (drittes und viertes)?

Wenn die erste Transaktion mit einem Rollback endet, ist alles in Ordnung: Die eingehenden Transaktionen werden in der Reihenfolge ausgeführt, in der sie in einer Reihe standen.

Aber - das ist Pech - wenn die erste Transaktion mit einem Commit abgeschlossen wird, verschwindet nicht nur die Transaktionsnummer, sondern auch die Version der Zeile! Das heißt, die Version bleibt natürlich erhalten, ist jedoch nicht mehr relevant, und es muss eine völlig andere, neueste Version (derselben Zeile) aktualisiert werden. Die Ressource, die an der Reihe war, verschwindet und jeder organisiert ein Rennen um den Besitz einer neuen Ressource.

Lassen Sie die erste Transaktion mit Commit abschließen.

 => COMMIT; 

Die zweite Transaktion wird aufgeweckt und Absätze ausgeführt. 3 und 4.

 | UPDATE 1 

 => SELECT * FROM locks_v WHERE pid = 5928; 

  pid | locktype | lockid | mode | granted ------+---------------+----------+------------------+--------- 5928 | relation | accounts | RowExclusiveLock | t 5928 | transactionid | 530498 | ExclusiveLock | t (2 rows) 

Was ist mit der dritten Transaktion? Sie überspringt Schritt 1 (weil die Ressource verschwunden ist) und bleibt bei Schritt 2 hängen:

 => SELECT * FROM locks_v WHERE pid = 5964; 

  pid | locktype | lockid | mode | granted ------+---------------+----------+------------------+--------- 5964 | relation | accounts | RowExclusiveLock | t 5964 | transactionid | 530498 | ShareLock | f 5964 | transactionid | 530499 | ExclusiveLock | t (3 rows) 

Und das Gleiche passiert mit der vierten Transaktion:

 => SELECT * FROM locks_v WHERE pid = 6000; 

  pid | locktype | lockid | mode | granted ------+---------------+----------+------------------+--------- 6000 | relation | accounts | RowExclusiveLock | t 6000 | transactionid | 530498 | ShareLock | f 6000 | transactionid | 530500 | ExclusiveLock | t (3 rows) 

Das heißt, sowohl die dritte als auch die vierte Transaktion warten auf den Abschluss der zweiten. Die Linie verwandelte sich in eine Kürbismenge .

Wir schließen alle gestarteten Transaktionen ab.

 | => COMMIT; 

 || UPDATE 1 

 || => COMMIT; 

 ||| UPDATE 1 

 ||| => COMMIT; 

Weitere Informationen zum Blockieren von Zeichenfolgen finden Sie in README.tuplock .

Du standest nicht hier

Die Idee eines zweistufigen Blockierungsschemas besteht also darin, die Wahrscheinlichkeit eines ewigen Wartens auf eine "Pech" -Transaktion zu verringern. Wie wir bereits gesehen haben, ist eine solche Situation jedoch durchaus möglich. Und wenn die Anwendung gemeinsam genutzte Sperren verwendet, kann alles noch trauriger werden.

Lassen Sie die erste Transaktion die Zeile im gemeinsam genutzten Modus sperren.

 => BEGIN; => SELECT txid_current(), pg_backend_pid(); 

  txid_current | pg_backend_pid --------------+---------------- 530501 | 5892 (1 row) 

 => SELECT * FROM accounts WHERE acc_no = 1 FOR SHARE; 

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

Die zweite Transaktion versucht, dieselbe Zeile zu aktualisieren, kann dies jedoch nicht. Die Modi SHARE und NO KEY UPDATE sind nicht kompatibel.

 | => BEGIN; | => SELECT txid_current(), pg_backend_pid(); 

 | txid_current | pg_backend_pid | --------------+---------------- | 530502 | 5928 | (1 row) 

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

Die zweite Transaktion wartet auf den Abschluss der ersten und hält die Zeilenversionssperre - im Moment ist alles wie beim letzten Mal.

 => SELECT * FROM locks_v WHERE pid = 5928; 

  pid | locktype | lockid | mode | granted ------+---------------+-------------+------------------+--------- 5928 | relation | accounts | RowExclusiveLock | t 5928 | tuple | accounts:10 | ExclusiveLock | t 5928 | transactionid | 530501 | ShareLock | f 5928 | transactionid | 530502 | ExclusiveLock | t (4 rows) 

Und dann erscheint eine dritte Transaktion, die eine gemeinsame Sperre wünscht. Das Problem ist, dass es nicht versucht, die Sperre für die Version der Zeile zu erfassen (weil es die Zeile nicht ändern wird), sondern einfach aus der Reihe kriecht - es ist mit der ersten Transaktion kompatibel.

 || BEGIN || => SELECT txid_current(), pg_backend_pid(); 

 || txid_current | pg_backend_pid || --------------+---------------- || 530503 | 5964 || (1 row) 

 || => SELECT * FROM accounts WHERE acc_no = 1 FOR SHARE; 

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

Und jetzt blockieren zwei Transaktionen die Zeile:

 => SELECT * FROM pgrowlocks('accounts') \gx 

 -[ RECORD 1 ]--------------- locked_row | (0,10) locker | 62 multi | t xids | {530501,530503} modes | {Share,Share} pids | {5892,5964} 

Was passiert jetzt, wenn die erste Transaktion abgeschlossen ist? Die zweite Transaktion wird aufgeweckt, es wird jedoch festgestellt, dass die Zeilensperre nirgendwo verschwunden ist, und sie wird erneut in der „Warteschlange“ stehen - diesmal für die dritte Transaktion:

 => COMMIT; => SELECT * FROM locks_v WHERE pid = 5928; 

  pid | locktype | lockid | mode | granted ------+---------------+-------------+------------------+--------- 5928 | relation | accounts | RowExclusiveLock | t 5928 | tuple | accounts:10 | ExclusiveLock | t 5928 | transactionid | 530503 | ShareLock | f 5928 | transactionid | 530502 | ExclusiveLock | t (4 rows) 

Und nur wenn die dritte Transaktion abgeschlossen ist (und während dieser Zeit keine anderen freigegebenen Sperren angezeigt werden), kann die zweite die Aktualisierung durchführen.

 || => COMMIT; 

 | UPDATE 1 

 | => ROLLBACK; 

Vielleicht ist es Zeit, einige praktische Schlussfolgerungen zu ziehen.

• Es ist keine gute Idee, in vielen parallelen Prozessen dieselbe Zeile in einer Tabelle gleichzeitig zu aktualisieren.
• Wenn Sie in der Anwendung freigegebene Sperren vom Typ SHARE verwenden, dann diskret.
• Das Überprüfen von Fremdschlüsseln sollte nicht stören, da sich die Schlüsselfelder normalerweise nicht ändern und die Modi KEY SHARE und NO KEY UPDATE kompatibel sind.

Gebeten, nicht zu leihen

In der Regel erwarten SQL-Befehle, dass sie die benötigten Ressourcen freigeben. Manchmal möchten Sie jedoch die Ausführung des Befehls ablehnen, wenn die Sperre nicht sofort verfügbar ist. Mit Befehlen wie SELECT, LOCK, ALTER können Sie dazu den Ausdruck NOWAIT verwenden.

Zum Beispiel:

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

 | => SELECT * FROM accounts FOR UPDATE NOWAIT; 

 | ERROR: could not obtain lock on row in relation "accounts" 

Der Befehl schlägt sofort fehl, wenn die Ressource ausgelastet ist. Im Anwendungscode kann ein solcher Fehler abgefangen und verarbeitet werden.

Sie können die NOWAIT-Phrase für die Befehle UPDATE und DELETE nicht angeben, aber Sie können zuerst SELECT FOR UPDATE NOWAIT ausführen und dann, wenn möglich, die Zeile aktualisieren oder löschen.

Es gibt noch eine andere Option, um nicht zu warten - verwenden Sie den Befehl SELECT FOR mit der Phrase SKIP LOCKED. Ein solcher Befehl überspringt gesperrte Zeilen, verarbeitet jedoch freie.

 | => BEGIN; | => DECLARE c CURSOR FOR | SELECT * FROM accounts ORDER BY acc_no FOR UPDATE SKIP LOCKED; | => FETCH c; 

 | acc_no | amount | --------+-------- | 2 | 200.00 | (1 row) 

In diesem Beispiel wurde die erste blockierte Zeile übersprungen und die zweite wurde sofort empfangen (und blockiert).

In der Praxis können Sie so die Multithread-Verarbeitung von Warteschlangen organisieren. Sie sollten keine andere Anwendung für diesen Befehl entwickeln. Wenn Sie ihn verwenden möchten, verlieren Sie höchstwahrscheinlich eine einfachere Lösung aus den Augen.

 => ROLLBACK; 

 | => ROLLBACK; 

Fortsetzung folgt .