Optimistic and Multiversion Concurrency Control - HSR-Wiki

Optimistic and Multiversion
Concurrency Control
Am Beispiel PostgreSQL
Seminar Datenbanksysteme
Master of Science in Engineering
Vertiefung Software and Systems
HSR Hochschule für Technik Rapperswil
www.hsr.ch/mse
Supervisor: Prof. Stefan Keller
Autor: Gian Poltéra
Rapperswil, Juni 2014
Version: 1.3
O PTIMISTIC
AND
M ULTI V ERSION C ONCURRENCY C ONTROL
Zusammenfassung
Im Rahmen dieser Seminararbeit wurde in einem ersten Teil das Paper von Momjian [1] studiert und versucht die wesentlichen Punkte verständlich zu erläutern. Dieses Paper befasst sich
mit Multiversion Concurrency Control (MVCC) in PostgreSQL. MVCC ist ideal für stark ausgelastete Datenbanken in denen sehr häufig nur gelesen wird. Das Besondere an MVCC ist, dass
nie auf ein Datenbankobjekt gewartet werden muss, da verschiedene Versionen eines Objekts
gehalten werden. In einem Versuch, die Geschwindigkeit mit und ohne MVCC zu messen, wurde
ein erheblich höherer Zeitbedarf bei der Durchführung von Updates mit MVCC festgestellt. Dies
ist, neben dem erhöhten Platzbedarf, einer der Nachteile von MVCC. Ergänzt werden die theoretischen Erläuterungen jeweils mit praktischen Beispielen. Im zweiten Teil dieser Seminararbeit
wurde versucht, diverse Anomalien gemäss Paper von Berenson et al. [2], in PostgreSQL zu simulieren. Zuerst wurden diese Anomalien theoretisch erläutert und anschliessend wurde für Demo-Zwecke ein kleines Java Programm geschrieben. Es konnten erfolgreich drei Anomalien erzeugt werden.
Abstract
In a first part of this seminar, the paper written by Momjian [1] has been studied. Key points of
the paper have been identified and described in a comprehensible manner. This paper is concerned with multiversion concurrency control (MVCC) in PostgreSQL. MVCC is ideally suited
for heavily used databases on which mainly read accesses occur. A special feature of MVCC is
that database objects are always immediately ready for access with no delays. This is because
different versions of the object are kept in the database at the same time. In an attempt to measure the speed with and without MVCC used, a considerably higher time requirement was detected to perform updates. This is, in addition to the increased space requirement, one of the
downsides of MVCC. The theoretical explanations in the paper are each accompanied with practical examples. In the second part of this seminar paper, it was tried to simulate the various
anomalies described in the paper of Berenson et al. [2]. This was done by first explaining those
anomalies in detail. Afterwards, small demo applications written in Java have been created in
order to verify them. From all of the anomalies described, three could be successfully simulated.
2
O PTIMISTIC
AND
M ULTI V ERSION C ONCURRENCY C ONTROL
Inhaltsverzeichnis
1
2
3
Einführung und Überblick ........................................................................................................ 4
1.1
OCC .................................................................................................................................. 4
1.2
MVCC ............................................................................................................................... 4
1.3
Vor- und Nachteile von MVCC ........................................................................................ 6
1.4
Geschwindigkeit von MVCC ............................................................................................. 6
MVCC in PostgreSQL .............................................................................................................. 8
2.1
Grundlagen ....................................................................................................................... 8
2.2
INSERT............................................................................................................................. 9
2.3
DELETE ........................................................................................................................... 9
2.4
UPDATE......................................................................................................................... 10
2.5
ROLLBACK ................................................................................................................... 10
2.6
Statusmarkierungen ........................................................................................................ 11
2.7
Multi-Statement Transaktionen ...................................................................................... 11
2.8
Cleanup ........................................................................................................................... 12
Praktischer Teil....................................................................................................................... 14
3.1
Grundlagen ..................................................................................................................... 14
3.2
Dirty Read ...................................................................................................................... 15
3.3
Nonrepeatable Read........................................................................................................ 15
3.4
Phantom Read ................................................................................................................ 16
3.5
Lost Update .................................................................................................................... 16
3.6
Read Skew....................................................................................................................... 17
3.7
Write Skew ...................................................................................................................... 18
4
Fazit und Ausblick.................................................................................................................. 19
5
Literaturverzeichnis ................................................................................................................ 20
6
Abbildungsverzeichnis ............................................................................................................ 21
7
Tabellenverzeichnis ................................................................................................................. 21
8
Anhang ................................................................................................................................... 22
8.1
Planning .......................................................................................................................... 22
8.2
MSSQL Snapshot Test.................................................................................................... 23
8.3
Documentation................................................................................................................ 24
8.4
Classes ............................................................................................................................. 26
8.5
Source Code .................................................................................................................... 26
3
O PTIMISTIC
AND
M ULTI V ERSION C ONCURRENCY C ONTROL
1 Einführung und Überblick
1.1 OCC
Beim Optimistic Concurrency Control (OCC) wird allen Benutzern einen parallelen Lesezugriff
auf die Datensätze gewährt. Es wird der Benutzer privilegiert, der zuerst einen Datensatz geändert hat. OCC ist vorwiegend für Lesezugriffe geeignet. Beim Pessimistic Concurrency werden
beim Zugriff auf ein Objekt, die Schreib- und Lesezugriffe für alle anderen Benutzer gesperrt.
1.2 MVCC
Das Multiversion Concurrency Control (MVCC) ist eine Erweiterung von OCC. Es kommt ohne
Blockierung und ohne die Konsistenz der Datenbank zu gefährden aus. Dabei muss eine Transaktion nie auf ein Datenbankobjekt warten. Dies wird intern durch die Haltung von verschiedenen
Versionen des Objekts gelöst. MVCC ist ideal für stark ausgelastete Datenbanken in denen sehr
häufig nur gelesen wird. Die meisten aktuellen Datenbanksysteme, wie PostgreSQL, Oracle, DB2,
MySQL InnoDB und MSSQL (optional) unterstützen MVCC [1].
Das Verhalten von MVCC wird mithilfe der Abbildung 1 anhand eines Beispiels erläutert.
INSERT
CREATE
EXPIRE
DELETE
CREATE
EXPIRE
UPDATE
CREATE
EXPIRE
CREATE
EXPIRE
Ein neuer Wert wird in die Datenbank geschrieben.
40
Es wird der Wert der aktuellen Transaktions-ID in
CREATE gesetzt.
Ein Wert wird aus der Datenbank gelöscht. Der
40
47
Datensatz bleibt erhalten und die aktuelle Transaktions-ID in EXPIRE gesetzt.
Bei einem Update werden zwei Datensätze gehal-
40
78
78
ten. Zuerst wird der Datensatz, analog zum vorhe-
alt (delete)
rigen Beispiel, als gelöscht markiert, anschliessend
neu (insert)
wird ein neuer Datensatz erstellt. Die aktuelle
Transaktions-ID wird beim alten als EXPIRE und
beim neuen als CREATE eingetragen.
Abbildung 1: MVCC Verhalten [1]
Die Transaktions-ID ist ein Zeitstempel oder eine fortlaufende Transaktions-Nummer:
𝑇𝑗 mit 𝑗 = maxarg{TS(𝑇𝑏𝑒𝑙 ): TS(𝑇𝑏𝑒𝑙 ) < TS(𝑇𝑖 )}
∀ bel
4
(1)
O PTIMISTIC
AND
M ULTI V ERSION C ONCURRENCY C ONTROL
Die MVCC Snapshots kontrollieren welche Datensätze für SQL-Statements sichtbar sind. Ein
Snapshot wird je nach Isolationslevel beim Start jedes SQL-Statements (READ COMMITTED)
oder beim Transaktionsstart (SERIALIZABLE) erstellt. Die verschiedenen Isolationslevels werden in Kapitel 3.1 genauer beschrieben. Beim Erstellen eines neuen Snapshots werden die höchste Transaktions-ID, die committed wurde, und die aktuell ausgeführten Transaktions-IDs gesammelt. Durch die Information dieser Snapshots weiss das System welche Datensätze für eine
Anweisung sichtbar sind. [1]
Die Bestimmung mittels Snapshots, ob eine Zeile sichtbar ist, wird mithilfe von Abbildung 2 und
Abbildung 3 anhand eines Beispiels erläutert:
Ein Snapshot wird erstellt.
Die
höchste
Transaktions-ID
committed
ist
100,
offene Transaktionen sind
25, 50 und 75. Alle anderen
Transaktionen
wurden
committed.
Abbildung 2: MVCC Snapshot Zeitleiste [1]
Nur CREATE
CREATE
EXPIRE
CREATE
EXPIRE
CREATE
EXPIRE
Verschiedene Transaktionen schreiben einen neuen
30
50
110
Sichtbar
Wert in die Datenbank. Der erste Datensatz ist
sichtbar, die Transaktion 30 ist weder offen noch
Unsichtbar
grösser als der höchste committed Wert. Der zweite
Datensatz ist nicht sichtbar, weil die Transaktion 50
Unsichtbar
noch offen ist. Der letzte Datensatz ist nicht sichtbar, weil die Transaktion 110 höher ist als der
höchste committed Wert.
CREATE & EXPIRE
CREATE
30
EXPIRE
80
CREATE
30
EXPIRE
75
CREATE
30
EXPIRE
110
Verschiedene Transaktionen ändern den Datensatz
Unsichtbar
mit CREATE 30. Der erste Datensatz ist unsichtbar weil die Transaktion 80 nicht offen und kleiner
Sichtbar
als der höchste committed Wert ist. Dieser Datensatz ist somit abgelaufen. Der zweite Datensatz ist
Sichtbar
noch sichtbar da die Transaktion 75 noch offen ist.
Der letzte Datensatz ist sichtbar weil die Transaktion 110 höher als der höchste committed Wert ist.
Abbildung 3: MVCC Snapshots Zeilensichtbarkeit [1]
5
O PTIMISTIC
AND
M ULTI V ERSION C ONCURRENCY C ONTROL
1.3 Vor- und Nachteile von MVCC
Der grosse Vorteil von MVCC ist einerseits, dass Leser nie die Schreiber blockieren und anderseits Schreiber nie die Leser blockieren. Lesen ist dabei jederzeit und ohne zu warten möglich.
Der grosse Nachteil ist die sehr speicherintensive Haltung von mehreren Datensatzversionen. Dies
benötigt nicht nur einen grösseren Speicherplatz, sondern auch mehr Zeit bei Updates. Ein weiterer Nachteil ist die zwingend notwendige Wartung bzw. das Aufräumen, welches zwischendurch
notwendig ist. Als letzte kleine Einschränkung ist zu erwähnen, dass die Transaktions-ID in
PostgreSQL “nur” 32 Bit gross sein kann, das heisst: 4 Milliarden Transaktionen sind möglich,
anschliessend beginnt die Zählung wieder bei null.
1.4 Geschwindigkeit von MVCC
Da sich MVCC bei PostgreSQL nicht deaktivieren lässt und keine Referenzen im Internet vorhanden sind, wurde eine eigene kleine Testumgebung aufgebaut. Ziel war es, ein Bild davon zu
erhalten, wie sich MVCC auf die Lese- und Schreibgeschwindigkeit auswirkt. Microsoft MS SQL
bietet eine ähnliche Funktion wie MVCC diese lässt sich jedoch ein- und ausschalten:
ALTER DATABASE Datenbankname SET READ_COMMITTED_SNAPSHOT ON;
Die folgenden Werte wurden durch das Einfügen bzw. Löschen einfacher Einträge gemessen. Das
Einfügen und Löschen wurde bewusst einzeln in einer Schleife durchgeführt um realistischere
Werte zu erhalten. Die Testumgebung und die SQL-Skripts sind im Anhang unter 8.2 aufgeführt.
Anzahl Einträge
INSERT
SELECT
UPDATE
DELETE
ON
OFF
ON
OFF
ON
OFF
ON
OFF
100‘000
00:20
00:30
00:00
00:00
00:18
00:02
00:16
00:17
1‘000‘000
03:33
04:25
00:04
00:03
03:17
00:09
03:04
03:11
10‘000‘000
-
-
00:40
00:34
-
01:41
-
-
Tabelle 1: Messresultate MSSQL READ_COMMITED_SNAPSHOT ON/OFF
6
O PTIMISTIC
AND
M ULTI V ERSION C ONCURRENCY C ONTROL
Die Grafische Ansicht der erhaltenen Resultate sind in Abbildung 4 bis Abbildung 7 abgebildet.
Fehlende Werte wurden hochgerechnet und die Linien wurden interpoliert. Die X-Achse entspricht jeweils der Anzahl Einträge und die Y-Achse entspricht der benötigten Zeit in Sekunden.
Abbildung 4: Messresultate INSERT
Abbildung 5: Messresultate SELECT
Abbildung 6: Messresultate UPDATE
Abbildung 7: Messresultate DELETE
Die Geschwindigkeitsunterschiede bei INSERT, SELECT und DELETE sind nicht gravierend ausgefallen. Beim UPDATE braucht die Variante mit MVCC rund 20-mal länger.
In der Praxis wäre der Unterschied insbesondere beim SELECT erheblich grösser gewesen, da bei
gleichzeitigen Schreib- und Leseoperationen die Leser, durch die Aktivierung von MVCC, nie
blockiert werden. Denn erst bei parallelen Transaktionen kommt MVCC voll zum Tragen. Je
nach Konstellation ist MVCC beim SELECT erheblich schneller. Auf das Testen mit parallelen
Transaktionen wurde an dieser Stelle verzichtet, da es sehr viele mögliche Kombinationen gibt
und diese von Anwendung zu Anwendung unterschiedlich sind.
7
O PTIMISTIC
2
AND
M ULTI V ERSION C ONCURRENCY C ONTROL
MVCC in PostgreSQL
2.1 Grundlagen
Jede Transaktion in PostgreSQL erhält eine eigene Transaktions-ID namens xid. Zum Anzeigen
der aktuell verwendeten xid’s einer Tabelle müssen diese explizit in der SELECT-Query angegeben
werden. Ein blosses setzten des * Operators reicht dabei nicht aus.
Nachfolgend werden die wichtigsten Befehle für PostgreSQL und MVCC erläutert. Diese sind für
das Verständnis dieser Arbeit erforderlich. Auf eine Beschreibung der Grundlegenden SQLFunktionen wird an dieser Stelle verzichtet und auf entsprechende Fachliteratur verwiesen [3].
Befehl
SELECT xmin
SELECT xmax
Erläuterung
Transaktions-ID für CREATE, wird erstellt bei einem INSERT
oder UPDATE.
Transaktions-ID für EXPIRE, wird ergänzt bei einen UPDATE
oder DELETE.
SELECT cmin
SELECT cmax
Systembefehle-ID, entspricht der Position in der ein Befehl
SELECT txid_current()
Gibt die aktuelle txid zurück.
BEGIN TRANSACTION
Beginn einer neuen Transaktion.
ROLLBACK TRANSACTION
Rückgängig machen aller bisherigen UPDATE und DELETE
innerhalb einer Transaktion ausgeführt wurde.
Befehle innerhalb einer Transaktion.
COMMIT TRANSACTION
Definitiver Abschluss einer Transaktion.
Tabelle 2: Befehle PostgreSQL und MVCC
8
O PTIMISTIC
AND
M ULTI V ERSION C ONCURRENCY C ONTROL
Die nachfolgenden Beispiele für INSERT, DELETE, UPDATE und ROLLBACK wurden vom Paper
von Momjian [1] abgeleitet und die Testdaten bzw. das entsprechende SQL-Script sind auf seiner Webseite [4] verfügbar. Zur eigentlichen Testtabelle mvcc_demo wird noch eine VIEW
mvcc_demo_page0 erstellt, welche die Snapshot Datensätze anzeigt.
2.2 INSERT
Beim Einfügen von neuen Objekten wird eine fortlaufende xid zu jedem Objekt erstellt und als
xmin eingetragen. Im nachfolgenden Beispiel wurden alle Objekte in einer Transaktion eingefügt
und verfügen deshalb über die gleiche xid. Werden diese Objekte jeweils in einer separaten
Transaktion eingefügt, hat jedes Objekt eine eigene xid.
INSERT INTO mvcc_demo VALUES (10), (20), (30), (40), (50);
SELECT xmin, xmax, val FROM mvcc_demo;
SELECT * FROM mvcc_demo_page0;
Abbildung 8: PostgreSQL INSERT
2.3 DELETE
Wird ein Objekt gelöscht, ist es in der normalen Abfrage nicht mehr sichtbar. Auf der rechten
Seite ist ersichtlich, dass das Objekt jedoch weiterhin vorhanden ist und lediglich der Wert xmax
mit der aktuellen xid der Query eingetragen wurde.
DELETE FROM mvcc_demo WHERE val = 20;
SELECT xmin, xmax, val FROM mvcc_demo;
SELECT * FROM mvcc_demo_page0;
Abbildung 9: PostgreSQL DELETE
9
O PTIMISTIC
AND
M ULTI V ERSION C ONCURRENCY C ONTROL
2.4 UPDATE
Bei einem Update eines Datensatzes wird der alte Wert mit xmax ergänzt und ein neues Objekt
mit der aktuellen xid erstellt. Auf der rechten Seite ist ersichtlich, dass bei einem Update ein
neues Objekt erstellt wurde.
UPDATE mvcc_demo SET val = val + 1 WHERE val = 10;
SELECT xmin, xmax, val FROM mvcc_demo;
SELECT * FROM mvcc_demo_page0;
Abbildung 10: PostgreSQL UPDATE
2.5 ROLLBACK
Mit dem Befehl Rollback kann eine laufende Transaktion rückgängig gemacht werden. Dies ist
jedoch nur innerhalb einer Transaktion und bevor committed wurde möglich. Auf der rechten
Seite ist ersichtlich, dass beim Rollback der Datensatz, wie beim Löschen, markiert wurde. Beim
normalen Abrufen der Tabelle ist er jedoch weiterhin vorhanden.
BEGIN WORK;
DELETE FROM mvcc_demo WHERE val = 30;
ROLLBACK WORK;
SELECT xmin, xmax, val FROM mvcc_demo;
SELECT * FROM mvcc_demo_page0;
Abbildung 11: PostgreSQL ROLLBACK
10
O PTIMISTIC
AND
M ULTI V ERSION C ONCURRENCY C ONTROL
2.6 Statusmarkierungen
Damit ein Rollback wie in Kapitel 2.5 funktioniert, benötigt es ein zusätzliches System, das die
Transaktionen mit Statusinformationen markiert. Die Abbildung 12 zeigt das pg_clog von
PostgreSQL, dabei wird jede Transaktion xid durch einen Status ergänzt. Es sind drei verschiedene Statusmarkierungen möglich, diese sind: laufend, abgebrochen und committed. Ein Rollback
markiert die Transaktions-ID somit als abgebrochen und alle anderen Sessions ignorieren diese
Transaktion. Es sind keine weiteren Anpassungen in den Datensätzen oder den Snapshots notwendig. [1]
Abbildung 12: PostgreSQL Statusmarkierungen [1]
2.7 Multi-Statement Transaktionen
Multi-Statement Transaktionen sind Transaktionen in denen mehrere Befehle ausgeführt werden.
Weil jedes Statement seine eigenen Sichtbarkeitsregeln hat, benötigen Multi-Statement Transaktionen ein zusätzliches Tracking. Der Inhalt eines Cursors zum Beispiel muss unverändert bleiben, auch wenn spätere Befehle in derselben Transaktion Datensätze modifizieren. So ein Tracking ist in PostgreSQL durch die Systembefehle-IDs cmin und cmax implementiert, diese werden intern jedoch nur in einer einzelnen Spalte gehalten. Deshalb ist es unmöglich den Status
eines Datensatzes, der innerhalb einer Multi-Statement Transaktion erstellt und wieder gelöscht
wurde, aufzuzeichnen. In diesem Fall wird in PostgreSQL eine spezielle Combo-Command-ID
erzeugt, welche sich auf den lokalen Memory Hash, der den Inhalt der aktuellen cmin und cmax
Werte enthält, referenziert. [1]
11
O PTIMISTIC
AND
M ULTI V ERSION C ONCURRENCY C ONTROL
Nachfolgend ein Beispiel wie die Ausgabe in PostgreSQL aussieht. Im Paper von Momjian [1]
sind viele weitere Beispiele aufgeführt. Der Wert von cmin entspricht der Position innerhalb der
Transaktion, an der der INSERT-Befehl ausgeführt wurde.
BEGIN WORK;
INSERT INTO mvcc_demo VALUES (10);
INSERT INTO mvcc_demo VALUES (20);
INSERT INTO mvcc_demo VALUES (30);
COMMIT WORK;
BEGIN WORK;
INSERT INTO mvcc_demo VALUES (10), (20), (30);
INSERT INTO mvcc_demo VALUES (60), (50), (40);
COMMIT WORK;
BEGIN WORK;
INSERT INTO mvcc_demo VALUES (60);
INSERT INTO mvcc_demo VALUES (50);
INSERT INTO mvcc_demo VALUES (40);
COMMIT WORK;
SELECT xmin, cmin, val FROM mvcc_demo;
SELECT xmin, cmin, val FROM mvcc_demo;
Abbildung 13: PostgreSQL Multi-Statement Transaktion
2.8 Cleanup
Da PostgreSQL bei einem UPDATE oder DELETE Befehl einen Datensatz nicht physikalisch löscht,
wird jedes Mal eine zweite Version angelegt bzw. die alte als gelöscht markiert. Diese müssen
solange beibehalten werden bis andere Transaktionen diese Zeilenversion nicht mehr sehen können. Wird eine Tabelle stark durch UPDATE oder DELETE Befehle ausgelastet, steigt der tote
Speicherplatz stark an. Dagegen hilft der Befehl VACUUM, der bei PostgreSQL seit der Einführung
von MVCC zur Verfügung steht. Dieser funktioniert wie eine Art Staubsauger und defragmentiert dabei den Heap. Damit man diese Arbeit nicht jedes Mal von Hand erledigen muss, gibt es
die Funktion AUTOVACUUM. Des Weiteren führt PostgreSQL beim Aufruf eines SELECT, UPDATE
und DELETE Befehls bei Bedarf eine Bereinigung einer einzelnen Heap Page durch. [5], [1]
HOT
Item
State
Clean
Indexes?
Update
FSM?
Ja
Non-HOT
Item
State
Tot
Unbenutzt
Nein
Nein
Ja
Unbenutzt
Unbenutzt
Ja
Ja
Cleanup
Methode
Ausgelöst
durch
Anwendungsbereich
Wiederverwendung
Heap Tuples?
SinglePage
SELECT,
UPDATE,
DELETE
Einzelne Heap Page
VACUUM
VACUUM,
AUTOVACUUM
Alle potenziellen Heap
Pages
Tabelle 3: PostgreSQL Cleanup Zusammenfassung [1]
In Tabelle 3 werden in einer Zusammenfassung die beiden Cleanup Methoden beschrieben.
12
O PTIMISTIC
AND
M ULTI V ERSION C ONCURRENCY C ONTROL
Der Aufbau einer Heap Page ist in der Abbildung 14 aufgezeichnet. Die Heap Page besteht aus
dem Page Header, Items und Tupel. Der Item Pointer zeigt von einem Item zu einem Tupel.
Beim Aufruf von SELECT, UPDATE und DELETE wird nur die Heap Page bereinigt. Durch VACUUM
werden zusätzlich auch die Indexe bereinigt, die auf die einzelnen Tupel zeigen.
Abbildung 14: PostgreSQL Aufbau Heap Page und Indexe [1]
Nachfolgend einen kurzen Überblick über die Befehle für das Aufräumen in PostgreSQL:
Befehl
TRUNCATE
VACUUM
AUTOVACUUM
VACUUM VERBOSE
CREATE INDEX
REINDEX TABLE
VACUUM FULL
Erläuterung
Leert eine Tabelle, ist schneller als DELETE da keine
Bedingungen definiert werden können. Kann nicht zurückgerollt werden.
Manuelles VACUUM
Führt ein automatisches VACUUM durch und muss in der
Datei PostgreSQLql.conf aktiviert werden: autovacuum
= on.
Ausgabe des Status der Free Space Map
Erstellt ein Index für eine Tabelle
Reorganisation des Tabellenindexes
Verkleinert die Tabellendatei auf ein Minimum, benötigt
jedoch eine exklusive Tabellen-Sperre. Nicht für die tägliche Wartung. Falls jedoch trotzdem notwendig, zuerst
VACUUM FULL und anschliessend REINDEX, insbesondere bei älteren PostgreSQL Versionen, notwendig.
Tabelle 4: PostgreSQL Cleanup Befehle
13
O PTIMISTIC
AND
M ULTI V ERSION C ONCURRENCY C ONTROL
3 Praktischer Teil
Ziel dieses Abschnittes war es, einige Anomalien in PostgreSQL zu simulieren. Dafür wurde ein
kleines Java Programm geschrieben und versucht, die einzelnen Anomalien zu erzeugen. Die
Anomalien über Skew wurden dabei nicht weiter verfolgt. Die technische Umsetzung und Dokumentation des praktischen Teiles sind im Anhang beschrieben.
3.1 Grundlagen
Zunächst folgt eine Auflistung der häufigsten und bekanntesten Anomalien und deren Erläuterung. Die theoretischen Ansätze basieren auf dem Dokument von Berenson et al. [2].
Das Isolation Level und die jeweils möglichen Anomalien:
Isolation Level
Read uncommitted
Read committed
Repeatable read
Serializable
Dirty Read
Möglich
Unmöglich
Unmöglich
Unmöglich
Lost Updates
Möglich
Möglich
Unmöglich
Unmöglich
Nonrepeatable
Read
Möglich
Möglich
Unmöglich
Unmöglich
Phantom Read
Möglich
Möglich
Möglich
Unmöglich
Tabelle 5: ANSI SQL Isolation Levels [2]
Die Isolation Level und Anomalien bei Systemen mit MVCC:
Isolation Level
Dirty Read
Read committed
Repeatable read
Serializable
Unmöglich
Unmöglich
Unmöglich
Nonrepeatable
Read
Möglich
Unmöglich
Unmöglich
Phantom Read
Lost Updates
Möglich
Unmöglich
Unmöglich
Möglich
Möglich
Unmöglich
Tabelle 6: MVCC Isolation Levels [6]
In PostgreSQL ist MVCC nicht deaktivierbar. Das Isolation Level kann jedoch folgendermassen
angepasst werden:
SET TRANSACTION ISOLATION LEVEL ‘ISOLATION LEVEL’;
Dieser Befehl muss jeweils am Anfang der Transaktion angegeben werden und kann nach der
ersten Query nicht mehr geändert werden. PostgreSQL verwendet den Standardwert READ COMMITTED. In der Tabelle 6 ist ersichtlich, dass bei Systemen mit MVCC das Isolation Level READ
UNCOMMITTED gar nicht notwendig ist. PostgreSQL unterstützt dieses Level jedoch ebenfalls,
dabei wird es intern einfach auf den Standardwert gesetzt. [6]
14
O PTIMISTIC
AND
M ULTI V ERSION C ONCURRENCY C ONTROL
Durch die Serializable Snapshot Isolation (SSI) Technik, die in PostgreSQL dem Isolation Level
SERIALIZABLE entspricht, wird sichergestellt, dass bei jeder möglichen Kombination von seriellen Zugriffen jederzeit das Richtige gemacht wird. Falls notwendig wird automatisch ein Rollback
mit einem serialization Fehler durchgeführt. [7]
3.2 Dirty Read
Andere Transaktionen können Daten der aktuellen Transaktion lesen, obwohl die Änderungen
noch nicht committed wurden
Zeitpunkt
1
2
3
4
Transaktion 𝑻𝟏
READ 𝑥
Transaktion 𝑻𝟐
UPDATE 𝑥
READ 𝑥
ROLLBACK
Tabelle 7: Dirty Read
1.
2.
3.
4.
5.
Transaktion 𝑇1 liest einen Datensatz.
Transaktion 𝑇2 editiert den Datensatz.
𝑇1 liest den Datensatz bevor 𝑇2 einen COMMIT oder ROLLBACK durchführt.
𝑇2 führt einen ROLLBACK durch.
𝑇1 hat einen Datensatz gelesen der nie committed wurde und so nie wirklich existiert
hat.
3.3 Nonrepeatable Read
Bei mehrfachen, gleichen Lesezugriffen innerhalb einer Transaktion gibt es unterschiedliche Ergebnisse.
Zeitpunkt
1
2
3
4
Transaktion 𝑻𝟏
READ 𝑥
Transaktion 𝑻𝟐
UPDATE 𝑥
COMMIT
READ 𝑥
Tabelle 8: Nonrepeatable Read
1.
2.
3.
4.
5.
Transaktion 𝑇1 liest einen Datensatz.
Transaktion 𝑇2 editiert den Datensatz.
𝑇2 führt ein COMMIT durch.
𝑇1 liest den Datensatz erneut.
𝑇1 erhält einen modifizerten Wert oder stellt fest, dass der Datensatz gelöscht wurde.
15
O PTIMISTIC
AND
M ULTI V ERSION C ONCURRENCY C ONTROL
3.4 Phantom Read
Durch das Einfügen, Ändern oder Löschen gibt es in anderen Transaktionen bei gleichen Bedingungen unterschiedliche Ergebnisse.
Zeitpunkt
1
2
3
4
Transaktion 𝑻𝟏
READ
Transaktion 𝑻𝟐
INSERT
COMMIT
READ
Tabelle 9: Phantom Read
1.
2.
3.
4.
5.
Transaktion 𝑇1 liest eine Datensatzmenge, z.B. die Zeilenanzahl.
Transaktion 𝑇2 fügt einen neuen Datensatz ein.
𝑇2 führt ein COMMIT durch.
𝑇1 liest die Datensatzmenge erneut.
𝑇1 erhält eine unterschiedliche Datensatzmenge im Vergleich zur ersten Abfrage.
3.5 Lost Update
Durch das Aktualisieren des gleichen Datensatzes durch zwei verschiedene Transaktionen geht ein
Update verloren.
Zeitpunkt
1
2
3
4
5
6
Transaktion 𝑻𝟏
READ 𝑥
Transaktion 𝑻𝟐
READ 𝑥
UPDATE 𝑥
UPDATE 𝑥
COMMIT
COMMIT
Tabelle 10: Lost Update
1.
2.
3.
4.
5.
6.
7.
Transaktion 𝑇1 liest einen Datensatz 𝑥.
Transaktion 𝑇2 liest ebenfalls den Datensatz 𝑥.
𝑇1 editiert den Datensatz 𝑥.
𝑇2 editiert ebenfalls den Datensatz 𝑥.
𝑇1 führt ein COMMIT durch.
𝑇2 führt ein COMMIT durch.
Es wird nur die Änderung von 𝑇2 in der Datenbank gespeichert, die Änderung von 𝑇1
geht verloren.
16
O PTIMISTIC
AND
M ULTI V ERSION C ONCURRENCY C ONTROL
3.6 Read Skew
Durch das Lesen und gleichzeitige Ändern von abhängigen Datensätzen werden inkonsistente
Ergebnisse ausgegeben.
Zeitpunkt
1
2
3
4
Transaktion 𝑻𝟏
READ 𝑥
Transaktion 𝑻𝟐
UPDATE 𝑥, 𝑦
COMMIT
READ 𝑦
Tabelle 11: Read Skew
1. Transaktion 𝑇1 liest einen Datensatz 𝑥.
2. Transaktion 𝑇2 editiert den Datensatz 𝑥 und einen Datensatz 𝑦, diese haben eine Abhängigkeit.
3. 𝑇2 führt ein COMMIT durch.
4. Transaktion 𝑇1 liest den Datensatz 𝑦.
5. 𝑇1 sieht einen inkonsistenten Zustand.
Beispiel: Eine Tabelle enthält Informationen über Personen. Alle Personeneinträge haben ein
Attribut, das angibt, ob eine Person verheiratet ist oder nicht und ein zweites Attribut, das angibt, ob eine Person tot ist oder nicht. Wir nehmen an, dass 𝑥 und 𝑦 jeweils Personeneinträge
sind und sie miteinander verheiratet sind. Transaktion 𝑇2 markiert 𝑥 als eine tote Person und
Person 𝑦 als Witwe. Transaktion 𝑇1 hat einen inkonsistenten Zustand gelesen und sieht, dass Person 𝑦 eine Witwe ist, aber ihr Mann 𝑥 lebt. [8]
17
O PTIMISTIC
AND
M ULTI V ERSION C ONCURRENCY C ONTROL
3.7 Write Skew
Durch das gleichzeitige Ändern von gleichen, abhängigen Datensätzen wird die Datenbank inkonsistent und Abhängigkeiten werden verletzt.
Zeitpunkt
1
2
3
4
5
6
Transaktion 𝑻𝟏
READ 𝑥, 𝑦
Transaktion 𝑻𝟐
READ 𝑥, 𝑦
UPDATE 𝑥
COMMIT
UPDATE 𝑦
COMMIT
Tabelle 12: Write Skew
1.
2.
3.
4.
5.
6.
7.
Transaktion 𝑇1 liest die Datensätze 𝑥 und 𝑦, diese haben eine Abhängigkeit.
Transaktion 𝑇2 liest ebenfalls die Datensätze 𝑥 und 𝑦.
𝑇2 editiert den Datensatz 𝑥.
𝑇2 führt ein COMMIT durch.
𝑇1 editiert den Datensatz 𝑦.
𝑇1 führt ein COMMIT durch.
Die Abhängigkeit zwischen 𝑥 und 𝑦 wurde verletzt.
Beispiel: Eine Tabelle enthält Informationen über Personen. Alle Personeneinträge haben ein
Attribut, das angibt, ob eine Person verheiratet ist oder nicht und ein zweites Attribut, das angibt, ob eine Person tot ist oder nicht. Wir nehmen an, dass 𝑥 und 𝑦 jeweils Personeneinträge
sind und sie miteinander verheiratet sind. Transaktion 𝑇2 markiert 𝑥 als eine tote Person und
Transaktion 𝑇1 markiert 𝑦 als Witwe. In diesem Fall ist die Datenbank inkonsistent bis die
Transaktion 𝑇1 committed wurde. [8]
Die beiden Anomalien Read und Write Skew werden in dieser Arbeit nicht weiter verfolgt.
18
O PTIMISTIC
AND
M ULTI V ERSION C ONCURRENCY C ONTROL
4 Fazit und Ausblick
MVCC eignet sich hervorragend für sehr viele Lesezugriffe und wenig Schreiboperationen. Durch
die Haltung von verschiedenen Versionen eines Datensatzes kann dieser jederzeit gelesen werden.
Die Geschwindigkeit ist jedoch, insbesondere beim Aktualisieren, rund 20-mal langsamer. Weitere
Nachteile von MVCC sind der erhöhte Speicherplatzbedarf und der Wartungsaufwand. Durch ein
geeignetes Wartungskonzept kann die Datenbankgrösse reduziert werden. Das Schreiben der
Snapshots auf die physische Harddisk kann auch komplett deaktiviert werden. Dadurch ist PostgreSQL bei einem Serverabsturz jedoch nicht mehr in der Lage, einen konsistenten Zustand der
Datenbank zu garantieren. Ob eine relationale Datenbank mit der Funktionalität von MVCC
gewählt werden soll, hängt sehr stark von den Anforderungen ab. Die meisten bekannten Hersteller setzten bereits auf dieses Konzept oder bieten es zumindest als Option an.
Im praktischen Teil konnten die Anomalien Dirty Read, Nonrepeatable Read, Phantom Read
und Lost Update erfolgreich in einem Java Programm umgesetzt werden. Dieses kleine Demo
Programm zeigt dabei jeweils auf, wie sich die Werte bei verschiedenen Isolation Levels ändern.
Bei der Anomalie Dirty Read konnte kein Fehler generiert werden. Dies ist auf die fehlende Anfälligkeit von MVCC-Systemen auf diese Anomalie zurückzuführen (Tabelle 6) und wurde bereits
im Vorfeld so angenommen. In PostgreSQL kann das Isolation Level sehr einfach, am Anfang
einer Transaktion angepasst werden. Je höher das Isolation Level gewählt wird, desto weniger
Anomalien sind möglich. Dabei werden jedoch auch die Restriktionen erhöht und gleichzeitig
schreibende Transaktionen können sich blockieren. Mit dem von PostgreSQL verwendeten Standardwert sind Anomalien zwar möglich. Dieser Wert scheint hinsichtlich Performance und Komplexität gut gewählt und kann bei Bedarf angepasst werden.
Das Konzept für MVCC wurde schon 1978 durch Reed [9] bzw. 1981 durch Bernstein und
Goodman [10] entwickelt. Während einige Hersteller wie Oracle diese Technologie bereits 1984
eingeführt haben, dauerte es bei Microsoft bis zum Jahre 2005. Bei PostgreSQL ist MVCC seit
der Version 6.5 aus dem Jahre 1999 fester Bestandteil. Neue Technologien die das bewährte
MVCC in relationalen Datenbanken ablösen scheinen in naher Zukunft keine zu erscheinen. Die
aktuellen Trends gehen eher weg von relationalen Datenbanken hin zu Datenbanken, die einen
nicht-relationalen Ansatz verfolgen. Einige dieser Datenbanken mit dem sogenannten NoSQLAnsatz, wie Beispielsweise CouchDB und MongoDB, verwenden jedoch auch wiederum MVCC.
19
O PTIMISTIC
AND
M ULTI V ERSION C ONCURRENCY C ONTROL
5 Literaturverzeichnis
[1] B. Momjian, „MVCC Unmasked,“ Pennsylvania, 2013.
[2] H. Berenson, P. Bernstein, J. Gray, J. Melton, E. O'Neil und P. O'Neil, „A critique of ANSI
SQL isolation levels,“ Microsoft Corporation, Redmond, 1995.
[3] K. Douglas und D. Susan, PostgreSQL: a comprehensive guide to building, programming,
and administering PostgresSQL databases, SAMS publishing, 2005.
[4] B. Momjian, „SQL Script,“ [Online]. Available:
http://momjian.us/main/writings/pgsql/mvcc.sql. [Zugriff am 15 März 2014].
[5] B. Helmle, „VACUUM FULL,“ [Online]. Available: https://www.credativ.de/credativblog/2011/04/postgresql-vs-vacuum-full. [Zugriff am 25 März 2014].
[6] PostgreSQL, „Transaction Isolation,“ [Online]. Available:
http://www.postgresql.org/docs/current/static/transaction-iso.html. [Zugriff am 05 Mai
2014].
[7] PostgreSQL, „SSI,“ [Online]. Available: http://wiki.postgresql.org/wiki/SSI. [Zugriff am 29
Mai 2014].
[8] L. Frank, „Countermeasures against isolation anomalies,“ Department of Informatics
Copenhagen Business School, Copenhagen, 2013.
[9] R. David P., „Naming and synchronization in a decentralized computer system,“
Massachusetts Institute of Technology, Cambridge, 1978.
[10] P. A. Bernstein und N. Goodman, „Concurrency control in distributed database systems,“
(CSUR), ACM Computing Surveys, pp. 185-221, 1981.
20
O PTIMISTIC
AND
M ULTI V ERSION C ONCURRENCY C ONTROL
6 Abbildungsverzeichnis
Abbildung
Abbildung
Abbildung
Abbildung
Abbildung
Abbildung
Abbildung
Abbildung
Abbildung
Abbildung
Abbildung
Abbildung
Abbildung
Abbildung
1: MVCC Verhalten [1] .................................................................................................. 4
2: MVCC Snapshot Zeitleiste [1] ................................................................................... 5
3: MVCC Snapshots Zeilensichtbarkeit [1] .................................................................... 5
4: Messresultate INSERT ............................................................................................... 7
5: Messresultate SELECT .............................................................................................. 7
6: Messresultate UPDATE ............................................................................................. 7
7: Messresultate DELETE ............................................................................................. 7
8: PostgreSQL INSERT ................................................................................................. 9
9: PostgreSQL DELETE ................................................................................................ 9
10: PostgreSQL UPDATE............................................................................................ 10
11: PostgreSQL ROLLBACK ...................................................................................... 10
12: PostgreSQL Statusmarkierungen [1] ...................................................................... 11
13: PostgreSQL Multi-Statement Transaktion ............................................................ 12
14: PostgreSQL Aufbau Heap Page und Indexe [1] ..................................................... 13
7 Tabellenverzeichnis
Tabelle
Tabelle
Tabelle
Tabelle
Tabelle
Tabelle
Tabelle
Tabelle
Tabelle
Tabelle
Tabelle
Tabelle
1: Messresultate MSSQL READ_COMMITED_SNAPSHOT ON/OFF ......................... 6
2: Befehle PostgreSQL und MVCC..................................................................................... 8
3: PostgreSQL Cleanup Zusammenfassung [1].................................................................. 12
4: PostgreSQL Cleanup Befehle ........................................................................................ 13
5: ANSI SQL Isolation Levels [2] ...................................................................................... 14
6: MVCC Isolation Levels [6] ............................................................................................ 14
7: Dirty Read ..................................................................................................................... 15
8: Nonrepeatable Read ...................................................................................................... 15
9: Phantom Read............................................................................................................... 16
10: Lost Update ................................................................................................................. 16
11: Read Skew ................................................................................................................... 17
12: Write Skew .................................................................................................................. 18
21
O PTIMISTIC
AND
M ULTI V ERSION C ONCURRENCY C ONTROL
8 Anhang
8.1 Planning
Milestones
•
Distribution of tasks
•
Kickoff Meeting
•
Delivery task
•
Interim presentation
•
Final practical part
•
Final presentation
22
O PTIMISTIC
AND
M ULTI V ERSION C ONCURRENCY C ONTROL
8.2 MSSQL Snapshot Test
SQL-Scripts:
INSERT
1
2
3
4
5
6
7
8
9
10
11
12
13
ALTER DATABASE test SET READ_COMMITTED_SNAPSHOT ON;
CREATE TABLE test (id int IDENTITY(1,1)PRIMARY KEY CLUSTERED, value int);
declare @max
@rc
integer,
integer;
SET @max=1000000;
SET @rc=1;
while @rc<=@max begin
INSERT INTO test (value) VALUES (1);
SET @rc=@rc+1;
end
SELECT:
1
2
3
4
5
6
7
8
9
10
11
12
declare @max
@rc
@sum
integer,
integer,
integer;
SET @max=1000000;
SET @rc=1;
SET @sum=0;
while @rc<=@max begin
SET @sum=@sum + (SELECT value FROM test WHERE id=1);
SET @rc=@rc+1;
end
UPDATE:
1
2
3
4
5
6
7
8
9
10
declare @max
@rc
integer,
integer;
SET @max=100000;
SET @rc=1;
while @rc<=@max begin
UPDATE test SET value=2 WHERE id=1;
SET @rc=@rc+1;
end
23
O PTIMISTIC
AND
M ULTI V ERSION C ONCURRENCY C ONTROL
DELETE:
1 declare @max
2
@rc
3
4
5
6
7
8
9
integer,
integer;
SET @max=1000000;
SET @rc=1;
while @rc<=@max begin
DELETE FROM test WHERE id=@rc;
SET @rc=@rc+1;
end
Test Environment:
Type
Value
VM-Player
VMware Player 6.0.2
Operating System
MS Windows 7 64 Bit Service Pack 1
Assigned CPU
4 Processor Cores from Intel i7-4770K @ 3.50 GHz
Assigned RAM
8 GB
Assigned HDD
60 GB SATA
SQL-Version
MS SQL Server 2008 R2 Express 64 Bit with Mgmt
8.3 Documentation
Requirements:
•
•
An installation of PostgreSQLQL 9.3.4 on the local host or on a remote system.
PostgreSQLQL JDBC Driver Version 9.3-1101 Library, is included in the JAR-File.
•
Adapting at the firewall on local host or the remote system, PostgreSQL standard port is
5432.
•
Editing the Pg_hba.conf File for access outside the local host and restart PostgreSQL.
o
•
Example:
host
all
all
192.168.1.0/24
md5
host
all
all
192.168.123.0/24
md5
Creating a new database with name Practices and user/password PostgreSQL.
User Guide:
Editing the BasicParameters in the DBConnection.java:
•
•
•
DB_URL_POSTGRESQL_PREFIX for example: jdbc:PostgreSQLql://192.168.123.128:5432/Practices
DB_POSTGRESQL_USER for example: PostgreSQL
DB_POSTGRESQL_PASSWORD for example: PostgreSQL
24
O PTIMISTIC
AND
M ULTI V ERSION C ONCURRENCY C ONTROL
Run the DBSeminar.java the output in the console looks like this:
Start Nonrepeatable Read with READ COMMITTED
-------------------------------------------T1 begin work
T1 read the sum -> 330
T2 begin work
T2 update a value, sum -> 311
T2 commit
T1 read the sum -> 311
T1 commit
FAILURE: NonRepeateableRead
--------------------------------------------
Start Nonrepeatable Read with REPEATABLE READ
-------------------------------------------T1 begin work
T1 read the sum -> 330
T2 begin work
T2 update a value, sum -> 311
T2 commit
T1 read the sum -> 330
T1 commit
OK: No NonRepeateableRead
--------------------------------------------
Start LostUpdate with REPEATABLE READ
-------------------------------------------T1 begin work
T2 begin work
T1 read the first value -> 10
T2 read the first value -> 10
T1 update first value, from 10 to 110
T2 update first value, from 10 to 5
T1 commit
T2 commit
Update failed
OK: No LostUpdate, Value in DB -> 110
--------------------------------------------
25
O PTIMISTIC
AND
M ULTI V ERSION C ONCURRENCY C ONTROL
The demo makes per default the following tests, you can easy adapt this by editing the code in
the DBSeminar.java File.
Test
DirtyRead
NonRepeateableRead1
NonRepeateableRead2
PhantomRead1
PhantomRead2
LostUpdate1
LostUpdate2
Isolationlevel
1, READ UNCOMMITTED
2, READ COMMITTED
3, REPEATABLE READ
2, READ COMMITTED
3, REPEATABLE READ
2, READ COMMITTED
3, REPEATABLE READ
Output
Always OK (MVCC)
FAILURE
OK
FAILURE
OK
FAILURE
OK, Second Update failed
8.4 Classes
Class
DBSeminar
DBConnection
DBHelper
Package anomalies
Description
Main class, starts the anomalies
Class for the database connection
Helper Class with many methods for accessing table
Contains the four anomalies classes
8.5 Source Code
This code is hardcoded and only for demonstration purposes, it is therefore not very nice. The
source code of the project is also available on the internet under:
https://github.com/gpoltera/DBSeminar
Class DBSeminar
1 public class DBSeminar {
2
3
private static final String LEVEL1 = "READ UNCOMMITTED";
4
private static final String LEVEL2 = "READ COMMITTED";
5
private static final String LEVEL3 = "REPEATABLE READ";
6
private static final String LEVEL4 = "SERIALIZABLE";
7
8
/**
9
* @param args the command line arguments
10
*/
11
public static void main(String[] args) throws SQLException {
12
startDirtyRead(); //starts the DirtyRead Demo
13
startNonRepeateableRead(); //starts the startNonRepeateableRead Demo
14
startPhantomRead(); //starts the PhantomRead Demo
15
startLostUpdate(); //starts the LostUpdate Demo
16
}
17
18
private static void startDirtyRead() throws SQLException {
19
//Dirty Read
20
System.out.println("Start DirtyRead with " + LEVEL1);
21
System.out.println("--------------------------------------------");
22
new DirtyRead().dirtyRead(LEVEL1);
23
System.out.println("\n--------------------------------------------\n");
24
}
25
26
private static void startNonRepeateableRead() throws SQLException {
26
O PTIMISTIC
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67 }
AND
M ULTI V ERSION C ONCURRENCY C ONTROL
//Nonrepeatable Read 1
System.out.println("Start Nonrepeatable Read with " + LEVEL2);
System.out.println("--------------------------------------------");
new NonRepeateableRead().nonRepeateableRead(LEVEL2);
System.out.println("\n--------------------------------------------\n");
//Nonrepeatable Read 2
System.out.println("Start Nonrepeatable Read with " + LEVEL3);
System.out.println("--------------------------------------------");
new NonRepeateableRead().nonRepeateableRead(LEVEL3);
System.out.println("\n--------------------------------------------\n");
}
private static void startPhantomRead() throws SQLException {
//PhantomRead 1
System.out.println("Start PhantomRead with " + LEVEL2);
System.out.println("--------------------------------------------");
new PhantomRead().phantomRead(LEVEL2);
System.out.println("\n--------------------------------------------\n");
//PhantomRead 2
System.out.println("Start PhantomRead with " + LEVEL3);
System.out.println("--------------------------------------------");
new PhantomRead().phantomRead(LEVEL3);
System.out.println("\n--------------------------------------------\n");
}
private static void startLostUpdate() throws SQLException {
//LostUpdate 1
System.out.println("Start LostUpdate with " + LEVEL2);
System.out.println("--------------------------------------------");
new LostUpdate().lostUpdate(LEVEL2);
System.out.println("\n--------------------------------------------\n");
//LostUpdate 2
System.out.println("Start LostUpdate with " + LEVEL3);
System.out.println("--------------------------------------------");
new LostUpdate().lostUpdate(LEVEL3);
System.out.println("\n--------------------------------------------\n");
}
Class DBConnection
1 public class DBConnection {
2
3
private static final String DB_POSTGRESQL_DRIVER = "org.PostgreSQLql.Driver";
//PostgreSQL Driver
4
private static final String DB_URL_POSTGRESQL_PREFIX =
"jdbc:PostgreSQLql://192.168.123.128:5432/Practices";
//URL to the PostgreSQL Server (Localhost or Remote)
5
private static final String DB_POSTGRESQL_USER = "PostgreSQL"; //DB username
6
private static final String DB_POSTGRESQL_PASSWORD = "PostgreSQL"; //DB password
7
private static final boolean LOG_ON = false; //Acitvates the extended Logging
8
9
public Connection connect() {
10
11
//Extended log-output if enabled
12
if(LOG_ON) { DriverManager.setLogWriter( new PrintWriter( System.out ) ); }
13
14
//Verify that the PostgreSQL driver ist inlucded in the library
15
try {
16
Class.forName(DB_POSTGRESQL_DRIVER);
17
} catch (ClassNotFoundException e) {
18
System.out.println("PostgreSQLQL JDBC Driver not found, include in your library");
19
}
20
21
Connection connection = null;
22
23
//Connecetion to the database
24
try {
25
connection = DriverManager.getConnection(DB_URL_POSTGRESQL_PREFIX,
27
O PTIMISTIC
M ULTI V ERSION C ONCURRENCY C ONTROL
DB_POSTGRESQL_USER, DB_POSTGRESQL_PASSWORD);
} catch (SQLException e) {
System.out.println("Connection failed, check Firewall, Adress, Port,
Username and Password");
}
26
27
28
29
30
31
32
33
34
35
36
37
38 }
AND
if (connection != null) {
//System.out.println("Connected to the database");
} else {
System.out.println("Connection failed");
}
return connection;
}
Class DBHelper
1 public class DBHelper {
2
3
private static final String TABLENAME = "testdata"; //name of the created table
4
5
private static final String QUERYCREATETABLE = "DROP TABLE IF EXISTS " + TABLENAME + ";\n"
6
+ "CREATE TABLE " + TABLENAME + " (id INT, value INT);\n"
7
+ "INSERT INTO " + TABLENAME + " (id, value) VALUES (1,10), (2,20),
(3,100), (4,200);\n";
8
9
//drop the old table and create a new table
10
public static void createTable(Connection connection) {
11
PreparedStatement ps = null;
12
try {
13
ps = connection.prepareStatement(QUERYCREATETABLE);
14
ps.executeUpdate();
15
ps.close();
16
} catch (SQLException ex) {
17
System.out.println("Table creation failed");
18
}
19
}
20
21
//begin the transaction
22
public static void beginTransaction(Connection connection) {
23
try {
24
connection.createStatement().executeUpdate("BEGIN TRANSACTION;");
25
} catch (SQLException ex) {
26
System.out.println("Begin transaction failed");
27
}
28
}
29
30
//commits a transaction
31
public static void commitTransaction(Connection connection) {
32
try {
33
connection.createStatement().executeUpdate("COMMIT TRANSACTION;");
34
} catch (SQLException ex) {
35
System.out.println("Commit transaction failed");
36
}
37
}
38
39
//makes a rollback of the transaction
40
public static void rollbackTransaction(Connection connection) {
41
try {
42
connection.createStatement().executeUpdate("ROLLBACK TRANSACTION;");
43
} catch (SQLException ex) {
44
System.out.println("Rollback transaction failed");
45
}
46
}
47
48
//sets the isolation level
49
public static void setIsolationLevel(Connection connection, String isolationlevel) {
50
try {
51
connection.createStatement().executeUpdate("SET TRANSACTION ISOLATION
LEVEL " + isolationlevel + ";");
52
} catch (SQLException ex) {
28
O PTIMISTIC
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
AND
M ULTI V ERSION C ONCURRENCY C ONTROL
System.out.println("Set Isolation Level failed");
}
}
//inserts a new value in the table
public static void insertValue(Connection connection) {
Statement st = null;
try {
st = connection.createStatement();
st.executeUpdate("INSERT INTO " + TABLENAME + " (id, value) VALUES (5, 30);");
st.close();
} catch (SQLException ex) {
System.out.println("Insert failed");
}
}
//updates a value in the table
public static void updateValue(Connection connection) {
Statement st = null;
try {
st = connection.createStatement();
st.executeUpdate("UPDATE " + TABLENAME + " SET value=1 WHERE id=2;");
st.close();
} catch (SQLException ex) {
System.out.println("Update failed");
}
}
//updates the first value in the table
public static void updateFirst(Connection connection, int value) {
Statement st = null;
try {
st = connection.createStatement();
st.executeUpdate("UPDATE " + TABLENAME + " SET value=" + value +" WHERE id=1;");
st.close();
} catch (SQLException ex) {
System.out.println("Update failed");
}
}
//deletes a value from the table
public static void deleteValue(Connection connection) {
Statement st = null;
try {
st = connection.createStatement();
st.executeUpdate("DELETE FROM " + TABLENAME + " WHERE id=1;");
st.close();
} catch (SQLException ex) {
System.out.println("Delete failed");
}
}
//gets the sum over all values in the table
public static int getSum(Connection connection) {
Statement st = null;
ResultSet rs = null;
int sum = 0;
try {
st = connection.createStatement();
rs = st.executeQuery("SELECT SUM(value) FROM " + TABLENAME + ";");
rs.next();
sum = rs.getInt(1);
rs.close();
st.close();
} catch (SQLException ex) {
System.out.println("Failed to calculate the sum");
}
return sum;
}
//gets the first value from the table
public static int getFirst(Connection connection) {
Statement st = null;
29
O PTIMISTIC
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170 }
AND
M ULTI V ERSION C ONCURRENCY C ONTROL
ResultSet rs = null;
int first = 0;
try {
st = connection.createStatement();
rs = st.executeQuery("SELECT value FROM " + TABLENAME + " WHERE id=1;");
rs.next();
first = rs.getInt(1);
rs.close();
st.close();
} catch (SQLException ex) {
System.out.println("Failed to get first value");
}
return first;
}
//select table gives the whole table
private static final String QUERYSELECTTABLE = "SELECT id, value FROM " + TABLENAME + ";";
public static void selectTable(Connection connection) {
Statement st = null;
ResultSet rs = null;
ResultSetMetaData rsmd = null;
int spalten;
try {
st = connection.createStatement();
rs = st.executeQuery(QUERYSELECTTABLE);
rsmd = rs.getMetaData();
spalten = rsmd.getColumnCount();
while (rs.next()) {
int i = 1;
while (i < spalten + 1) {
System.out.print(rs.getString(i) + " ");
i++;
}
System.out.print("\n");
}
rs.close();
st.close();
} catch (SQLException ex) {
System.out.println("Select failed");
}
}
Class DirtyRead
1 public class DirtyRead {
2
3
public void dirtyRead(String isolationlevel) throws SQLException {
4
//Dirty Read
5
Connection connection = new DBConnection().connect();
6
DBHelper.createTable(connection);
7
connection.close();
8
9
//T1 begin
10
System.out.println("T1 begin work");
11
Connection T1 = new DBConnection().connect();
12
DBHelper.beginTransaction(T1);
13
DBHelper.setIsolationLevel(T1, isolationlevel);
14
15
//T1 read
16
int sum1 = DBHelper.getSum(T1);
17
System.out.println("T1 read the sum -> " + sum1);
18
19
//T2 begin
20
System.out.println("
T2 begin work");
21
Connection T2 = new DBConnection().connect();
22
DBHelper.beginTransaction(T2);
23
DBHelper.setIsolationLevel(T2, isolationlevel);
24
30
O PTIMISTIC
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47 }
AND
M ULTI V ERSION C ONCURRENCY C ONTROL
//T2 update
System.out.print("
DBHelper.updateValue(T2);
System.out.println(DBHelper.getSum(T2));
T2 update a value, sum -> ");
//T1 read
int sum2 = DBHelper.getSum(T1);
System.out.println("T1 read the sum -> " + sum2);
T1.close();
//T2 rollback
System.out.println("
DBHelper.rollbackTransaction(T2);
T2.close();
T2 makes a rollback");
//Check
if (sum1 == sum2) {
System.out.println("OK: No Dirty Read");
} else {
System.out.println("FAILURE: Dirty Read");
}
}
Class NonRepeateableRead
1 public class NonRepeateableRead {
2
3
public void nonRepeateableRead(String isolationlevel) throws SQLException {
4
//Nonrepeatable Read
5
Connection connection = new DBConnection().connect();
6
DBHelper.createTable(connection);
7
connection.close();
8
9
//T1 begin
10
System.out.println("T1 begin work");
11
Connection T1 = new DBConnection().connect();
12
DBHelper.beginTransaction(T1);
13
DBHelper.setIsolationLevel(T1, isolationlevel);
14
15
//T1 read
16
int sum1 = DBHelper.getSum(T1);
17
System.out.println("T1 read the sum -> " + sum1);
18
19
//T2 begin
20
System.out.println("
T2 begin work");
21
Connection T2 = new DBConnection().connect();
22
DBHelper.beginTransaction(T2);
23
DBHelper.setIsolationLevel(T2, isolationlevel);
24
25
//T2 update
26
System.out.print("
T2 update a value, sum -> ");
27
DBHelper.updateValue(T2);
28
System.out.println(DBHelper.getSum(T2));
29
30
//T2 commit
31
System.out.println("
T2 commit");
32
DBHelper.commitTransaction(T2);
33
T2.close();
34
35
//T1 read
36
int sum2 = DBHelper.getSum(T1);
37
System.out.println("T1 read the sum -> " + sum2);
38
39
//T1 commit
40
System.out.println("T1 commit");
41
DBHelper.commitTransaction(T1);
42
T1.close();
43
44
//Check
45
if (sum1 == sum2) {
46
System.out.println("OK: No NonRepeateableRead");
31
O PTIMISTIC
47
48
49
50
51 }
AND
M ULTI V ERSION C ONCURRENCY C ONTROL
} else {
System.out.println("FAILURE: NonRepeateableRead");
}
}
Class PhantomRead
1 public class PhantomRead {
2
3
public void phantomRead(String isolationlevel) throws SQLException {
4
//PhantomRead
5
Connection connection = new DBConnection().connect();
6
DBHelper.createTable(connection);
7
connection.close();
8
9
//T1 begin
10
System.out.println("T1 begin work");
11
Connection T1 = new DBConnection().connect();
12
DBHelper.beginTransaction(T1);
13
DBHelper.setIsolationLevel(T1, isolationlevel);
14
15
//T1 read
16
int sum1 = DBHelper.getSum(T1);
17
System.out.println("T1 read the sum -> " + sum1);
18
19
//T2 begin
20
System.out.println("
T2 begin work");
21
Connection T2 = new DBConnection().connect();
22
DBHelper.beginTransaction(T2);
23
DBHelper.setIsolationLevel(T2, isolationlevel);
24
25
//T2 insert
26
System.out.print("
T2 insert a value, sum -> ");
27
DBHelper.insertValue(T2);
28
System.out.println(DBHelper.getSum(T2));
29
30
//T2 commit
31
System.out.println("
T2 commit");
32
DBHelper.commitTransaction(T2);
33
T2.close();
34
35
//T1 read
36
int sum2 = DBHelper.getSum(T1);
37
System.out.println("T1 read the sum -> " + sum2);
38
39
//T1 commit
40
System.out.println("T1 commit");
41
DBHelper.commitTransaction(T1);
42
T1.close();
43
44
//Check
45
if (sum1 == sum2) {
46
System.out.println("OK: No PhantomRead");
47
} else {
48
System.out.println("FAILURE: PhantomRead");
49
}
50
}
51 }
Class LostUpdate
1 public class LostUpdate {
2
3
private Connection T1 = new DBConnection().connect();
4
private Connection T2 = new DBConnection().connect();
5
6
public void lostUpdate(String isolationlevel) throws SQLException {
7
//LostUpdate
8
Connection connection = new DBConnection().connect();
9
DBHelper.createTable(connection);
10
connection.close();
32
O PTIMISTIC
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75 }
AND
M ULTI V ERSION C ONCURRENCY C ONTROL
//T1 begin
System.out.println("T1 begin work");
DBHelper.beginTransaction(T1);
DBHelper.setIsolationLevel(T1, isolationlevel);
//T2 begin
System.out.println("
DBHelper.beginTransaction(T2);
DBHelper.setIsolationLevel(T2, isolationlevel);
T2 begin work");
//T1 read
System.out.print("T1 read the first value -> ");
int before1 = DBHelper.getFirst(T1);
System.out.println(before1);
//T2 read
System.out.print("
int before2 = DBHelper.getFirst(T1);
System.out.println(before2);
T2 read the first value -> ");
//T1 update
int after1 = before1 + 100;
System.out.println("T1 update first value, from " + before1 + " to " + after1);
DBHelper.updateFirst(T1, after1);
//T2 update
final int after2 = before2 - 5;
System.out.println("
T2 update first value,
from " + before2 + " to " + after2);
//open the second update in a new Thread for waiting of the commit from T1
new Thread() {
public void run() {
try {
DBHelper.updateFirst(T2, after2);
} catch (Exception ex) {
System.out.println("Failure in Thread");
}
}
}.start();
//sleep for two seconds to be sure that the thread is started
try {
Thread.sleep(2000);
} catch (InterruptedException e) {
}
//T1 commit
System.out.println("T1 commit");
DBHelper.commitTransaction(T1);
//T2 commit
System.out.println("
DBHelper.commitTransaction(T2);
T2 commit");
//check
int dbvalueafter = DBHelper.getFirst(T1);
if (dbvalueafter == after1) {
System.out.println("OK: No LostUpdate, Value in DB -> " + dbvalueafter);
} else {
System.out.println("FAILURE: LostUpdate, Value in DB -> " + dbvalueafter);
}
T1.close();
T2.close();
}
33