Die Besonderheiten der Funktionsweise der internen Mechanismen von PostgreSQL ermöglichen es ihm, in einigen Situationen sehr schnell zu sein und in anderen „nicht so sehr“. Heute konzentrieren wir uns auf ein klassisches Beispiel für den Konflikt zwischen dem, wie das DBMS funktioniert, und dem, was der Entwickler damit macht — UPDATE vs MVCC-Prinzipien.
Kurz gesagt, die Handlung aus :
Wenn eine Zeile mit dem Befehl UPDATE geändert wird, werden tatsächlich zwei Operationen ausgeführt: DELETE und INSERT. In der aktuellen Version der Zeile wird xmax auf die Transaktionsnummer gesetzt, die das UPDATE ausgeführt hat. Dann wird eine Neue Version gleicher Zeile erstellt; der Wert von xmin entspricht dem Wert von xmax der vorherigen Version.
Nachdem diese Transaktion abgeschlossen ist, werden die alte oder neue Version, je nach COMMIT/ROLLBACK, als „tot“ (dead tuples) beim Durchlauf VACUUM der Tabelle erkannt und bereinigt.
Doch das wird nicht sofort geschehen, während Probleme mit „toten“ Zeilen sehr schnell entstehen können — bei mehrfachen oder in einer großen Tabelle, und kurze Zeit später könnten Sie mit der Situation konfrontiert werden, dass selbst .
#1: I Like To Move It
Angenommen, Ihre Geschäftslogik funktioniert und plötzlich stellt sie fest, dass das Feld X in einem bestimmten Datensatz aktualisiert werden muss:
UPDATE tbl SET X = WHERE pk = $1;Dann stellt sich während der Ausführung heraus, dass auch das Feld Y aktualisiert werden muss:
UPDATE tbl SET Y = WHERE pk = $1;… und dann auch noch Z — warum kleinlich sein?
UPDATE tbl SET Z = WHERE pk = $1;Wie viele Versionen dieses Datensatzes haben wir jetzt in der Datenbank? Aha, 4 Stück! Davon ist eine aktuell, und 3 müssen von [auto]VACUUM bereinigt werden.
So sollte es nicht sein! Verwenden Sie die Aktualisierung aller Felder in einem einzigen Befehl, — fast immer kann die Logik der Methode so geändert werden:
UPDATE tbl SET X = , Y = , Z = WHERE pk = $1;#2: Use IS DISTINCT FROM, Luke!
Nun, Sie möchten also viele Datensätze in der Tabelle UPDATE tbl SET X = WHERE pk BETWEEN $1 AND $2;
In dieser Form kommt der Befehl ziemlich oft vor, und fast immer nicht zur Auffüllung eines leeren neuen Feldes, sondern zur Korrektur von Fehlern in den Daten. Dabei wird dieKorrektheit bereits existierender Daten überhaupt nicht berücksichtigt — und das ist ein Fehler! Das heißt, der Datensatz wird überschrieben, selbst wenn dort genau das lag, was gewollt war — aber warum? Lassen Sie uns das verbessern: UPDATE tbl SET X = WHERE pk BETWEEN $1 AND $2 AND X IS DISTINCT FROM ;
UPDATE tbl SET X = WHERE pk BETWEEN $1 AND $2 AND X IS DISTINCT FROM ; Viele sind sich der Existenz dieses wunderbaren Operators nicht bewusst, daher hier eine Zusammenfassung zu IS DISTINCT FROM und anderen logischen Operatoren als Hilfe:
… und ein wenig über Operationen mit komplexen ROW()-Ausdrücken:
#3: А я милого узнаю по… блокировке
Es werden zwei identische parallele Prozesse gestartet,von denen jeder versucht, in den Datensätzen anzugeben, dass diese sich "in Bearbeitung" befinden:
UPDATE tbl SET processing = TRUE WHERE pk = $1;Selbst wenn diese Prozesse tatsächlich unabhängige Dinge tun, aber im Rahmen einer ID, wird der zweite Client in dieser Anfrage "blockiert", bis die erste Transaktion abgeschlossen ist.
Lösung Nr. 1: Die Aufgabe ist auf die vorherige reduziert.
Fügen wir einfach erneut hinzu IS DISTINCT FROM:
UPDATE tbl SET processing = TRUE WHERE pk = $1 AND processing IS DISTINCT FROM TRUE;In dieser Form wird die zweite Anfrage einfach nichts in der Datenbank ändern, da bereits "alles in Ordnung" ist — daher wird auch keine Sperre auftreten. Der Fakt des "Nichtvorhandenseins" des Datensatzes wird dann im Anwendungsalgorithmus bearbeitet.
Lösung Nr. 2: Advisory Locks
Ein großes Thema für einen separaten Artikel, in dem Sie über .
Lösung Nr. 3: Unbedachte Aufrufe
Hier muss definitiv eine gleichzeitige Arbeit mit demselben Datensatz stattfinden.? Или вы все-таки накосячили с алгоритмами вызовов бизнес-логики со стороны клиента, например? А если подумать?..
Quelle: habr.com
