20_8335_310-SQL-Isolation - Offene

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.
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.
1
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.
2
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.
4
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
6
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.
7
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.
8
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.
9
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:
10
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.
11
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
12
• 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.
13
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
14
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,
15
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 DatenbankManagement 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?
17
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.
19
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/dede/library/ms175519(v=sql.105).aspx
• Vgl. ORACLE 
http://docs.oracle.com/cd/E11882_01/server.112/e41084/ap_locks001.htm#SQLRF555
02
•
20
Kenntnisse über die Lock-Arten und deren Verwendung sind für den Multi-UserBetrieb 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.
21
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
23
24