Das diesem Dokument zugrundeliegende Vorhaben wurde mit Mitteln des Bundesministeriums für Bildung und Forschung
unter dem Förderkennzeichen 16OH21005 gefördert.
Die Verantwortung für den Inhalt dieser Veröffentlichung liegt
beim Autor/bei der Autorin.
1 Nachdem wir schon Transaktionen kennengelernt haben, geht es hier um den
sogenannten Isolation Level.
Hier werden wir das Thema Transaktionen und das isolierte Ausführen von Anwendungen noch weiter im Detail untersuchen.
Zunächst wollen wir klären, was Isolation-Level bedeutet und welche Probleme zu lösen sind.
Da ja das parallele Aufführen von Anwendungen im Mittelpunkt steht, müssen wir uns mit dem Thema Nebenläufigkeiten beschäftigen. Nebenläufigkeiten bedeutet, welche Situationen auftreten können, während der gleichzeitigen /parallelen Ausführung von Anwendungen, die auf den gleichen Daten arbeiten.
Hierbei spielen Datenbanksperren eine wichtige Rolle.
Nachdem dann die theoretischen Grundlagen gelegt sind, wenden wir uns wieder der Programmiersprache SQL zu.
Im letzten Teil geht es wieder etwas praktischer zu, und wir beschäftigen uns mit den SQL-Anweisungen, die uns helfen sollen mit Datenbanksperren umzugehen bzw. sie beeinflussen zu können.
Hinweis:
Der Umgang mit Datenbanksperren führt in der Praxis immer wieder zu Problemen, da diese einen enormen Einfluss auf die Performance einer Anwendung haben können.
Daher ist es unbedingt notwendig sowohl für Datenbank –Administratoren, als auch für Datenbank-Anwendungsentwickler, sich mit den verschiedenen Isolation-Level und den damit verbundenen Sperr-Konzepten (DB-Locks) auseinanderzusetzen.
3
Nun endlich zur ersten Frage:
Was ist ISOLATION-LEVEL eigentlich?
Wie schon in dem Abschnitt Transaktion beschrieben, geht es hier wiederum um Mehrbenutzerfähigkeit.
Wir betrachten auch hier wieder, was passiert, wenn mehrere Benutzer gleichzeitig auf Daten zugreifen.
Bei den bisherigen Übungen und Überlegungen im Abschnitt haben wir uns keine Gedanken gemacht über eventuelle Datenbanksperren und warum ein Benutzer Änderungen eines anderen Benutzer nicht „sehen“ bzw. mitgeteilt bekommt.
Genau dies wollen wir in diesem Abschnitt genauer untersuchen.
Schauen wir uns mal folgendes Beispiel an.
Zwei Personen führen SQL Operationen auf der gleichen Datenbank aus.
Die Person auf der linken Seite startet eine Update Operation auf eine Zeile in der Tabelle KONTO, um den Kontostand zu ändern.
Eine weitere Person auf der rechten Seite will den Kontostand ermitteln und führt daher eine Select Operation aus.
Nun stellt sich die Frage:
Welcher Kontostand wird der zweiten Person angezeigt?
Um diese Frage zu beantworten, müssen wird uns näher beschäftigen, welche Mittel es gibt, um mit diesen sogenannten Nebenläufigkeiten umzugehen.
5
In der Abbildung sehen wir ein Szenario, welche mit Titel „UPDAET LOST“
beschrieben bzw. zusammengefasst werden kann.
Sie sehen ein kleines UML Sequenz-Diagramm. Hierbei kennzeichnen die senkrechten Striche die Zeitachse. Die Aktionen erfolgen dabei von oben nach untern ( Zeitpunkt t1 ist vor Zeitpunkt t3).
Bei der Kommunikation sind 3 Parteien beteiligt.
Links sehen Sie ALICE. Sie steht repräsentativ für eine Anwendung, welche eine SELECT Anweisung auf eine ganz bestimmte Tabelle ausführt.
Auf der rechten Seite sehen Sie BOB. Er steht repräsentativ für eine weitere Anwendung, welche ebenfalls eine SELECT Anweisung auf die gleiche Tabelle ausführt.
In Verbindung mit der Zeitachse sehen wir folgende Sequenz / Abfolge:
Die Sequenz:
1. Zunächst führt ALICE einen SELECT ausführt, um die Tupel-Menge P zu lesen
2. Danach führt BOB einen SELECT aus, um die Tupel-Menge P zu lesen 3. Danach führt ALICE einen UPDATE auf die Tupel-Menge P durch.
4. Anschließend führt BOB ebenfalls einen Update auf die Tupel-Menge P durch.
Aus diesem Szenario geht hervor, dass die Änderungen von ALICE verloren gehen!!!!
Der Grund liegt darin, das BOB nicht informiert war, dass ALICE die gleichen DATEN kurz vorher geändert hatte.
Dies ist aber leider nicht das einzige Problem. Schauen wir uns ein weiteres Szenario an.
( Wir merken uns das UPDATE LOST Szenario für später).
Hinweis:
• Wenn Sie mit UML Sequenzdiagrammen nicht vertraut sind, finden Sie eine gute Beschreibungen unter http://www.ibm.com/developerworks/rational/library/3101.html
6
In der Abbildung sehen wir ein Szenario, welche mit Titel „DIRTY READ “ beschrieben bzw. zusammengefasst werden kann.
Hier sehen wir wieder ein kleines UML Sequenz-Diagramm. Beteiligt sind wieder ALICE und BOB
In Verbindung mit der Zeitachse sehen wir folgende Sequenz / Abfolge:
Die Sequenz
1. Zunächst führt ALICE einen UPDATE auf die Tupel-Menge P aus
2. Danach führt BOB einen SELECT aus, um die Tupel-Menge P zu lesen. Hier sind die Änderungen von ALICE enthalten. Er sieht also die Änderungen von ALICE.
3. Danach führt ALICE einen ROLLBACK aus. Dadurch werden ihre vorherigen Änderungen wieder rückgängig gemacht
Aus diesem Szenario geht hervor, dass BOB nun mit falschen Daten weiterarbeitet.
Es gibt noch leider noch weitere Szenarien. Schauen wir uns ein weiteres Szenario an. ( Wir merken uns das DIRTY READ Szenario für später).
7
In der Abbildung sehen wir ein Szenario, welche mit Titel „NON-REPEATABLE READ “ beschrieben bzw. zusammengefasst werden kann.
Hier sehen wir wieder ein kleines UML Sequenz-Diagramm. Beteiligt sind wieder ALICE und BOB
In Verbindung mit der Zeitachse sehen wir folgende Sequenz / Abfolge:
Die Sequenz
1. Zunächst führt ALICE einen SELECT durch, um die Tupel-Menge P zu lesen 2. Danach führt BOB ebenfalls einen SELECT aus, um die Tupel-Menge P zu
lesen.
3. Danach führt ALICE einen UPDATE durch, um die Tupel-Menge P zu ändern.
4. Jetzt führt BOB nochmals den gleichen SELECT wie vorher.
5. Nun führt ALICE einen COMMIT aus, um die Daten persistent zu machen . Die Transaktion wird beendet.
Aus diesem Szenario geht hervor, dass bei dem zweiten Lesen ganz andere Werte erhält als vorher.
Und es gibt noch ein weiteres Szenario. Schauen wir uns ein diese Szenario auch noch an.
( Wir merken uns das NON-REPEATABLE-READ Szenario für später).
8
In der Abbildung sehen wir ein Szenario, welche mit Titel „NON-REPEATABLE READ “ beschrieben bzw. zusammengefasst werden kann.
Hier sehen wir wieder ein kleines UML Sequenz-Diagramm. Beteiligt sind wieder ALICE und BOB
In Verbindung mit der Zeitachse sehen wir folgende Sequenz / Abfolge:
Die Sequenz
1. Zunächst führt ALICE einen SELECT aus und erhält zum Beispiel 5 Zeilen (Tupel-Menge X)
2. Danach führt BOB ebenfalls einen UPDATE aus, bei dem Attributwerte geändert werden, die bei dem SELECT von ALICE in der WHERE Bedingung genannt wurden.
3. Danach führt ALICE erneut den einen SELECT durch und erhält nun eine ganz andere Ergebnismenge. Anstatt wie vorher 5 erhält Sie nun 8 Zeilen
Aus diesem Szenario geht hervor, dass bei dem zweiten Lesen ganz andere Ergebnis-Menge gelesen wird. ALICE erhält Daten, die vorher scheinbar gar nicht vorhanden waren.
Nachdem wir nun vier Szenarien betrachtet haben, stellt sich die Frage:
Wie kann ich die Problem aus den Szenarien lösen?
Genau dieser Frage wollen wir als nächstes nachgehen.
9
Um die Probleme aus den genannten Szenarien lösen zu können, wurde das Konzept der Datenbanksperren eingeführt.
Wie wir bereits wissen, verändern folgende die SQL Anweisungen Daten einer Tabelle:
• INSERT
• UPDATE
• DELETE
Durch eine sogenannte Datenbanksperre ( Lock) wird nun angezeigt, ob ein Datensatz (Zeile) geändert wurde oder nicht.
Dadurch wird allen Anwendungen die Möglichkeit gegeben, festzustellen, ob Daten verändert wurden oder nicht.
Die Abbilddung zeigt ein Beispiel für die einfachste Art von Sperren. Nämlich, dass es für jede Zeile in einer Tabelle möglich ist eine Sperre (LOCK) zu setzen oder entfernen.
HINWEIS:
Später werden wir noch sehen, dass es noch andere arten von Sperren gibt. Für das Verständnis von Sperren genügt es , erst mal so zu tun, als gäbe es nur Sperren für eine Zeile in einer Tabelle.
10
Schauen wir uns nun an, was mit Locks alles machen kann und wie die in der Praxis eingesetzt werden.
Bisher haben wird uns um Locks nicht gekümmert.
Wir haben bisher immer angenommen, dass unsere Änderungen sofort in der Datenbank vorgenommen werden.
Dies ist immer dann der Fall, wenn wir in den sogenannten „AUTO COMMIT“
Mode arbeiten.
In den meisten DBMS ist dies der Default-Fall, sobald man eine Verbindung (Datenbank-Sitzung, engl. Database session) aufgebaut hat.
Will man nun erreichen, dass bei einem Update ( vgl. Abbildung) ein anderer Benutzer die Änderung angezeigt bekommt, dann muss eine entsprechende Sperre gesetzt werden.
Dies erreicht man dadurch, dass man den AUTO-COMMIT Mode ausschaltet.
Set autocommit off;
Das kennen wir schon aus dem Abschnitt Transaktionen.
Nun stellen sich drei Fragen:
1. Wie kann ich beeinflussen, dass innerhalb einer Transaktion nun eine Sperre gesetzt werden soll oder nicht? Hier geht es um den Isolation Level
2. Wie werden die Sperren verwaltet? Für jedes Tupel einzeln oder geht es Tabellenweise? Es geht also um die Granularität
3. Gibt es unterschiedliche Arten von Locks? Kann man an einer Sperre ( Lock)
erkennen, ob ein Satz gelesen, update oder gelöscht wurde? Es geht als um Lock Arten?
Wenden wir uns zunächst erst einmal der ersten Frage nach dem Isolation Level zu.
11
Um festzulegen, welche Sperren gesetzt werden sollen, kann mittels eine SQL Anweisung den sogenannten Isolation Level setzen.
Dies setzt man meist am Anfang einmal, nachdem man eine Verbindung zur Datenbank aufgebaut hat.
In besonderen Fällen kann man auch Isolation Level individuell für eine Transaktion setzen.
Aber zunächst wollen wir uns mit diesem Detail nicht aufhalten und erst einmal grundsätzlich verstehen:
• Wie kann ich den ISOLATION Level setzen ?
• Welche LEVEL gibt es überhaupt.
In dieser Abbildung sehen wir die Syntax des SQL Befehls zum Setzen des Isolation Levels.
Wie Sie sehen unterscheidet man vier verschiedene Level
• Read uncommitted
• Read committed
• Repeatable read
• Serializable
Welche Level man in der Praxis verwenden soll und warum sehen wir gleich in der nächsten Abbildung.
Beachte:
SQL Anweisung ist nicht bei jedem Hersteller gleich!
Wie hier auf die Gemeinsamkeiten, die alle Datenbanken unterstützen.
12
Wie wir bereits wissen, dann mittels der SQL Anweisung SET ISOLAOTION LEVEL der Isolation Level gesetzt werden.
In dieser Abbildung sehen Sie nun eine kleine Tabelle, aus welcher hervorgeht, welches der vorher genannten Probleme man ‚lösen möchte‘.
Sie erinnern sich Die Szenarien waren ja:
• Update lost
• Dirty Read
• Non-repeatable read
• Phantom read
In der Tabelle ist zu entnehmen, bei welchem Isolation Level , welches Problem gelöst werden kann.
Nachdem wir nun einen Überblick über die Isolation Level verschafft haben, bleibt noch die Frage nach der Granularität der Sperren.
Wie bereits schon angedeutet, kann man Sperren nicht nur auf Zeilen setzen, sondern auch auf andere Objekte. Dies wollen wir uns als nächstes ansehen.
Wichtig ist an dieser Stellen, dass wir uns merken, dass wir aus einer Anwendung heraus den Isolation Level setzen können und das in Abhängigkeit des Isolation Level Sperren gesetzt werden, um die beschriebenen Probleme zu lösen.
Daher müssen wir uns als nächstes noch ansehen, auf welche Objekte nun Sperren gesetzt werden können,
Setzt man den Isolation Level auf
• Read uncommitted , so vermeidet man nur das Update Lost Problem
• Read committed, vermeidet man „Update lost“ und „Dirty Read“ Probleme
• Repeatable Read, vermeidet man, „Update Lost“, „Dirty Read“ und „Non Repeatable Read“ Probleme
• Serializable, vermeidet alle Problem
Wie man schon an dem Namen „Serializable“ erahnen kann, erzwingt man dadurch, dass alle SQL Anweisungen aller Anwendungen sequentielle ausgeführt werden. Dies behebt zwar alle Probleme die durch Nebenläufigkeiten entstehen können, dafür geht dies aber erheblich zu Lasten der Performance / Antwortzeiten.
13
Bisher haben wir immer gedacht , dass Sperren für eine Zeile einer Tabelle gesetzt oder gelöscht werden.
Wir sind dabei davon ausgegangen, dass durch eine UPDATE, INSERT, DELETE Anweisung eine Sperre gesetzt wird.
Das Rücksetzen der Sperren erfolgt, sobald eine Transaktion geschlossen wurde.
Entweder durch Commit oder durch ROLLBACK.
Um die Granularität zu verstehen, müssen wir uns etwas näher mit der Implementation von Datenbanken beschäftigen.
Wie aus der Abbildung ersichtlich wird, bietet jedes Datenbanksystem min. drei verschiedene Ebenen der Granularität.
• Die erste Ebene wird als ROW-LEVEL bezeichnet und gibt an, dass für jede Zeile in einer Tabelle eine Sperre gesetzt wird.
• Aus Performancegründen werden Sätze aus einer Tabelle nicht zeilenweise gelesen. Stattdessen werden immer gleich mehrere Zeilen einer Tabelle in den Hauptspeicher gelesen unabhängig davon, ob die Anwendung nur eine Zeile oder mehrere Zeilen benötigt. Die Zeilen werden also in Blöcken gelesen und im Hauptspeicher in sogenannten Pages (Seiten) abgelegt. Sind nun für eine
Tabelle sehr viele Sperren zu verwalten, so verwendet man sogenannte Sperren auf Pages. Damit sinkt die Anzahl der zu verwaltenden Sperren. Dies bedeutet aber auch, dass ein Page.-Sperre (Page-Lock) ALLE Zeilen sperrt, die in einer Page abgelegt sind.
• Da wird es in der Praxis häufig mit sehr großen Mengen an Daten zu tun haben, kann es vorkommen, dass es für eine Tabelle sehr viele Pages im Hauptspeicher gleichzeitig vorgehalten werden müssen. Somit gibt es unter Umständen sehr viel Page Locks. Um den Verwaltungsaufwand für die Page-Locks zu reduzieren, geht man dazu über ganze Tabellen zu sperren. Dies bedeutet, das bei einem Table-Lock ALL Zeilen einer Tabelle gesperrt sind.
Hinweis:
Im Abschnitt Systemarchitektur, werden wird auf das Thema Tabellen und Pages zurückkommen.
14
In dieser Abbildung sind nochmals die einzelnen Ebenen abgebildet.
Die Besonderheit hier ist, dass die Ebene Datenbank hinzugekommen ist.
Wenn es zu viele Sperren auf Tabellenebene gibt, gehen manche Systeme dazu über, eine Sperr für eine ganze Datenbank zu setzen.
Kommen wir wieder auf das Wesentlich zurück und schauen uns in der nächsten Abbildung den Übergang der einzelnen Ebene nochmal im Überblick an,
Diese Abbildung gibt Ihnen nochmal eine graphischen Überblick über die einzelnen Sperr-Ebenen.
Wie Sie sehen, bietet eine Sperre auf Datenbank-Ebene das geringste Maß und paralleler Ausführung, dafür aber den geringsten Verwaltungsaufwand für das Verwalten der Sperren.
Am anderen Ende sehen Sie, dass Sperren auf Zeileneben das höchste Maß und paralleler Ausführung bietet, jedoch ist der Aufwand für das Verwalten der Sperren entsprechend hoch.
16
Die gute Nachricht ist, das wir uns bei der Entwicklung einer Anwendung darüber keine Gedanken machen müssen. Deshalb gibt es ja Datenbank- Management Systeme.
In der Abbildung sehen Sie nochmals zusammengefasst die einzelnen Eskalationsstufen bei der Verwaltung der einzelnen Sperr-Ebenen.
Wichtig an dieser Stelle ist nur, dass wir wissen, dass es diese Möglichkeiten gibt und dies natürlich einen Einfluss auf die Gesamt-Performance haben kann.
Nachdem wir gelernt haben
• Einen Isolation Level zu setzen um bestimmte Probleme zu beheben
• Dass die Sperren unterschiedliche Granularität haben können und dies alleine von dem DBMS verwaltetet werden
Wollen wir als nächstes der Frage nach gehen
Was sind Sperren genau und gibt es unterschiedliche Arten von Sperren?
Was sind Sperren genau und gibt es unterschiedliche Arten von Sperren?
Schauen wir uns als erstes an was Sperren sind?
Aus Anwendungssicht hat eine Sperre einen Boolean Wert.
Ist eine Sperre gesetzt, so hat sie den Wert TRUE und bedeutet, dass z.B. eine Zeile eine Tabelle für Anwendungen nicht zugreifbar sind.
Aus der Sicht einer Datenbank stellt die Sperre eine Semaphore dar. Diese hat die gleiche Bedeutung, wie wir sie von Multitasking-Betriebssystem her kennen, um ein Objekt vor parallelen Zugriffen zu schützen. Stichwirt: „Mutual
Exklusive“ kurz MUTEX genannt.
Die Frage ist nun, welche Bedeutung hat eine Sperre, wenn diese gesetzt ist und was bedeutet dies für eine Anwendung?
Hierzu schauen wir uns die unterschiedlichen Arten von Sperren an,
18
In diesem Kurs verwenden wir den Begriff „Lock Arten“. In der Literatur findet man aber auch andere Begriffe.
In manchen Blogs, Büchern, Magazinen, usw. werden dann Begriffe wie
• Lock Mode
• Lock Type
Verwendet.
All diese Begriffe beschreiben aber den gleichen Sachverhalt.
Je nachdem, um welche Art von Lock es sich handelt, unterscheidet man zwischen folgenden Locks:
1. Shared Lock 2. Update Lock 3. Exclusive Lock
Was bedeuten nun die einzelnen Arten?
Dieser Frage gehen wir als nächstes nach.
Was bedeuten nun die einzelnen Arten?
Wie in der Abbildung dargestellt, unterscheiden sich die einzelnen Arten nur darin, aufgrund welchen Ereignisses die Sperre gesetzt wird.
Shared Lock
Ein Shared Lock wird zum Beispiel für eine Zeile immer dann gesetzt, wenn eine Anwendung eine Zeile gelesen hat. Also, durch SELECT Anweisungen Damit wird angezeigt, dass die Zeile gelesen wurde.
Diese Zeilen können von anderen Transaktionen gelesen werden.
Exclusive Lock
Ein EXCLUSIVE Lock wird zum Beispiel für eine Zeile immer dann gesetzt, wenn eine Anwendung eine Zeile eingefügt, gelöscht oder geändert hat. Also, durch INSERT, DELETE , UPDATE Anweisungen.
Bestand für diese Zeile bereits ein „Shared Lock“ , so wird der „Shared Lock“ zu einem „Exclusive Lock“ umgewandelt.
20
Die einzelnen Datenbank-Implementation unterscheiden noch weitere unterschiedliche Arten von Locks. Die Namen der Lock-Arten sind dementsprechend auch Hersteller spezifisch.
Zum Nachlesen:
• Oracle:
https://docs.oracle.com/cd/E11882_01/server.112/e41084/ap_locks001.htm#SQLRF55 502
• SQL Server: https://technet.microsoft.com/de-de/library/ms175519(v=sql.105).aspx
Hinweis:
• Vgl. SQL Server https://technet.microsoft.com/de- de/library/ms175519(v=sql.105).aspx
• Vgl. ORACLE
http://docs.oracle.com/cd/E11882_01/server.112/e41084/ap_locks001.htm#SQLRF555 02
•
Kenntnisse über die Lock-Arten und deren Verwendung sind für den Multi-User- Betrieb sehr wichtig, da diese einen wesentlichen Einfluss auf das Sperrverhalten und damit auf die Performance haben.
Trifft eine Transaktion nämlich auf ein „Exclusive-Lock“, so muss die Transaktion warten, bis die Sperre gelöscht wird.
Wie wir an der Abbildung erkennen können, kann dies unter Umständen zu sogenannten Deadlock führen.
Schauen wir uns die Abbildung genauer an:
1. User ALICE führt einen Update auf einen Satz X in einer Tabelle A durch.
Dies führt dazu, dass eine Sperre für den Satz X gesetzt wird.
2. Nun führt Bob einen Update auf einen SATZ Y in einer Tabelle B durch. Dies führt zu einer Sperre für den Satz Y in der Tabelle B.
3. Als nächstes möchte ALICE den SATZ Y in der Tabelle B auch ändern und trifft auf die Sperre von Bob. Dies bedeutet, dass die Transaktion „blocked“
ist. Dies bedeutet, dass die Transaktion warten muss bis die Sperre gelöscht wird.
4. Wenn nun BOB seinerseits versucht den SATZ X in der Tabelle A zu ändern, trifft dieser auf die Sperre von ALICE und muss nun ebenfalls warten.
21
5. Nun sind beide „blocked“ und warten gegenseitig aus sich. Die sogenannte DEADLOCK Situation
Für diese Fälle haben die meisten Datenbankhersteller eine „Deadlock-Erkennung“
eingebaut.
Wird ein Deadlock erkannt, so werden in allen beteiligten Transaktionen ( in unseren Fall ALICE und BOB) ein Fehlergemeldet mit dem Hinweis, dass ein Deadlock erkannt wurde.
Somit sind die Applikation nicht mehr „blocked“ und können entsprechend reagieren.
In der Praxis führen beide Applikationen eine ROLLBACK durch, damit alle Sperren gelöscht werden und können erneut die Transaktion von Anfang an wiederholen.
Um nun solche Arten von Deadlocks möglichst zu vermeiden, sollten die Zugriffe auf die Tabellen in den unterschiedlichen Transaktionen immer in der gleichen Reihenfolge erfolgen.
22
24
