Transaktionen bei PostgreSQL
Werbung
Transaktionen bei PostgreSQL Holger Jakobs – [email protected], [email protected] 2009-12-27 Inhaltsverzeichnis 1 Sperrstrategien 1 2 Isolationslevel 2.1 ANSI/ISO SQL-Isolationslevel . . . . . . . . . . . . . . . . . . . . . . . . . 2.2 PostgreSQL-Isolationslevel . . . . . . . . . . . . . . . . . . . . . . . . . . . 2.3 Einstellen des Isolationslevels . . . . . . . . . . . . . . . . . . . . . . . . . 1 1 2 3 3 Deadlocks & Timeouts 4 1 Sperrstrategien PostgreSQL verwendet bei gleichzeitigen Zugriffen auf eine Datenbank ein Verfahren mit dem Namen Multi-Version Concurrency Control (MVCC)“1 . Jede Transaktion, die eine ” Datenbank abfragt, sieht einen Schnappschuss der Daten (eine Datenbankversion). Dieser Schnappschuss zeigt immer den letzten konsistenten Zustand der Daten, egal, was danach mit diesen passiert ist. Auf diese Weise kann die Transaktion niemals inkonsistente Daten zeigen, weil andere Transaktionen zwischenzeitlich Änderungen vorgenommen (aber noch nicht committet haben.). So wird jede einzelne Transaktion von den anderen isoliert. Der wesentliche Unterschied zwischen MVCC und den Sperr-Modellen ist, dass bei MVCC die Sperren, die beim Lesen von Daten verwendet werden, nicht das Schreiben verhindern und umgekehrt. Mittlerweile verwenden sehr viele Datenbanksysteme MVCC, wenn auch teilweise unter anderen Bezeichnungen wie z. B. row versioning“. ” 2 Isolationslevel 2.1 ANSI/ISO SQL-Isolationslevel Laut ANSI/ISO SQL gibt es vier Standard-Isolationslevel für Transaktionen. Je nach verwendetem Isolationslevel werden mehr oder weniger der folgenden unerwünschten Effekte vermieden: 1) http://en.wikipedia.org/wiki/Multiversion concurrency control 1 2.2 PostgreSQL-Isolationslevel 2 ISOLATIONSLEVEL dirty reads Eine Transaktion liest neu geschriebene/veränderte Daten anderer Transaktionen, die aber noch nicht abgeschlossen sind und daher eigentlich noch gar nicht (endgültig) existieren. non-repeatable reads Eine Transaktion liest Daten erneut und stellt fest, dass diese zwischenzeitlich durch eine parallele Transaktion dauerhaft verändert wurden. phantom reads Eine Transaktion führt eine Abfrage erneut aus und bekommt mehr Tupel geliefert als bei derselben Abfrage kurz zuvor. Für die letzten beiden ist es wichtig zu erwähnen, dass es sich nicht um Abfragen aus verschiedenen Transaktionen handelt, sondern das jeweils in derselben Transaktion verschiedene Ergebnisse auftauchen. Die vier Isolationslevel korrespondieren mit den oben beschriebenen Effekten. Hier ist aufgeführt, welche unerwünschten Effekte bei welchem Isolationslevel auftreten können: Isol.-Level \ Effekt Dirty Read Read Uncommitted ja Read Committed nein Repeatable Read nein Serializable nein Non-Repeatable Read Phantom Read ja ja ja ja nein ja nein nein 2.2 PostgreSQL-Isolationslevel PostgreSQL verwendet ohne weitere Angaben den Isolationslevel Read Committed“. Da” durch sieht jede einzelne Anweisung immer nur den Zustand, wie er zu Beginn der Anweisung war, aber nie Änderungen, die während der Anweisung aufgetreten sind, d. h. eine einzelne Anweisung sieht immer konsistente Daten. Wohl aber kann dieselbe Anweisung – mehrfach hintereinander in derselben Transaktion ausgeführt – verschiedene Zustände sehen, falls andere, parallele Transaktionen mittlerweile Änderungen per commit dauerhaft gemacht haben; Bei konkurrierenden Schreibzugriffen wird die zweite schreibende (update, delete, select for update) Transaktion gestoppt, bis die erste zu Ende gegangen ist. Wenn die erste Transaktion zurückgefahren wird (rollback), kann die zweite Transaktion fortgesetzt werden. Wird sie dagegen durchgeführt (commit), so wird geprüft, ob das betreffende Tupel weiterhin existiert und ob die Suchkriterien weiterhin zutreffen. Wenn ja, wird die Aktion entsprechend vorgenommen. Parallele select- oder insert-Kommandos sind hiervon aber nicht betroffen, sofert bei inserts keine Verletzungen von Primärschlüsseln oder unique-Constraints auftreten. Hat 2 2 ISOLATIONSLEVEL 2.3 Einstellen des Isolationslevels man als Primärschlüssel ein serial-Feld (auto-increment), so gibt es auch hier garantiert keine Primärschlüsselverletzung. Wahlweise gibt es noch den Transaktionslevel serializable“, der die stärkste Isolation ” zwischen Transkationen bewirkt. Bei der gleichen Situation wie oben beschrieben wird die zweite Transaktion nur durchgeführt, wenn die erste Transaktion zurückgefahren wird. Bei Durchführung der ersten Transaktion wird die zweite Transaktion mit einem Fehler abgebrochen: ERROR: Can’t serialize access due to concurrent update“, denn eine ” serialisierbare Transaktion kann keine Tupel verändern, die von einer anderen Transaktion bearbeitet wurde, nachdem sie selbst begonnen hat. Parallele SELECT oder INSERT-Kommandos sind hiervon aber ebenfalls nicht betroffen – Ausnahmen wie oben. Serializable“ Transaktionen sehen immer nur den Zustand wie er zu Beginn der gesam” ten Transaktion herrschte (im Gegensatz zu Read Committed“, wo man den Zustand zu ” Beginn der einzelnen Anweisung sieht). Eine Möglichkeit, Daten anderer Transaktionen zu sehen, bevor sie mit commit dauerhaft gemacht werden, gibt es bei PostgreSQL (zum Glück?) nicht. 2.3 Einstellen des Isolationslevels Bei psql setzt man den Isolationslevel mit dem Kommando set transaction isolation level serializable direkt nach dem Kommando begin work, das den Beginn einer Transaktion kennzeichnet. Gibt man Kommandos ein, ohne vorher eine Transaktion zu beginnen, so wird jedes einzelne Kommando sofort durchgeführt (sogenanntes autocommit“). Bei Programmen ” mit Embedded SQL kann man selbst wählen, ob standardmäßig autocommit verwendet werden soll oder automatisch Transaktionen begonnen werden sollen. Diese Entscheidung trifft man über den Parameter -t beim Precompiler ecpg für C/C++ und bei JDBC durch Aufrufen der Methode setAutoCommit(false) eines Datenbankverbindungsobjekts. Gemäß dem SQL-Standard, der kein begin kennt, wird automatisch bei jeder beliebigen Anweisung eine Transaktion begonnen und muss dann auch mit commit oder rollback abgeschlossen werden – der Standard kennt eben kein autocommit“. Bei Embedded SQL ” ruft man diese Kommandos mit exec sql auf, bei JDBC verwendet man die Methoden commit() und rollback() des Verbindungsobjekts. Seit SQL99 gibt es start transaction, bei dem man den Isolationslevel und die Zugriffsmethode (read only und/oder read write) angibt, so dass set transaction ... entbehrlich wird. Dies wird von PostgreSQL auch angeboten, aber halt nur zwei der vier Isolationslevel. Transaktionen in PostgreSQL genügen den ACID-Anforderungen atomic, consistent, isolatable, durable – siehe Tabelle 1 auf der nächsten Seite. 3 3 DEADLOCKS & TIMEOUTS Anforderung atomic consistent isolatable durable Beschreibung Eine Transaktion ist unteilbar, d. h. sie wird immer entweder vollständig oder gar nicht durchgeführt. Das ist dann wichtig, wenn mehrere Änderungen vorgenommen werden, die nur in ihrer Gesamtheit wieder einen konsistenten Zustand der Datenbank ergeben. Beispiel: Verheiraten von zwei Personen. Trüge man dies nur bei einer der Beteiligten ein, so wäre diese Person mit einer ledigen Person verheiratet. Vor und nach einer Transaktionen muss die Datenbank in einem konsistenten, d. h. widerspruchsfreien Zustand sein. Da andere Transaktionen den Zustand innerhalb einer Transaktion ohnehin nicht sehen können, kann dieser durchaus gegen Konsistenzregeln verstoßen. Das ist beispielsweise durch das Verzögern von Fremdschlüsselprüfungen (deferring of referential constraints) möglich. Transaktionen sind voneinander isolierbar, d. h. sie beeinflussen sich nicht unkontrolliert gegenseitig. Die Ergebnisse von Transaktionen sind dauerhaft, d. h. auch durch einen anschließenden Systemabsturz oder Stromausfall sind sie nicht gefährdet. Ein totaler Hardwareausfall kann natürlich trotzdem noch zu einem Datenverlust führen. Tabelle 1: ACID-Anforderungen an Datenbanken 3 Deadlocks & Timeouts Wenn Datenbank-Anweisungen durch parallele Transaktionen behindert werden, kann es lange dauern, bis der Anwender eine Rückmeldung bekommt. Es sollte nicht so sein, aber durch schlampige Programmierung kann es vorkommen, dass eine Transaktion Tupel oder gar Tabellen gesperrt hält, weil sie nicht commmittet wurde. Durch das MVCC-Verfahren (siehe Abschnitt 1 auf Seite 1) bei PostgreSQL wird zwar verhindert, dass eine rein lesende Transaktion von einer schreibenden Transaktion behindert wird und umgekehrt, aber mehrere schreibende Transaktionen müssen einander behindern, wenn man keine Inkonsistenzen riskieren will. Sollte zwischen Transaktionen ein Deadlock entstehen, so kann dieser vom Datenbanksystem erkannt werden, so dass nur eine der beiden Transaktionen erfolgreich durchgeführt wird. Die andere wird der Anwendung einen Fehler zurückliefern. Aber auch dann, wenn kein Deadlock entsteht, ist eine Wartezeit über ca. 10 Sekunden für Anwender kaum tolerierbar. Daher lässt sich eine maximale Durchführungszeit 4 3 DEADLOCKS & TIMEOUTS für eine Anweisung einstellen. Als default ist die Wartezeit nicht begrenzt. set statement_timeout to 2000 setzt die Ausführungszeit (und damit die Wartezeit) auf maximal 2000 ms (Millisekunden) fest. Falls die Anweisung dann nicht beendet wurde, wird sie vom System beendet, woraufhin ein Fehler zurückgeliefert wird. $RCSfile: transaktionen_pg.tex,v $ $Date: 2008/11/11 14:10:30 $ $Revision: 2becfe77f2be $ 5
