close

Anmelden

Neues Passwort anfordern?

Anmeldung mit OpenID

1 Transaktionen in SQL 2 Was ist eine - MultiAugustinum

EinbettenHerunterladen
PostgreSQL
E3A
1 Transaktionen in SQL
Um Daten in einer SQL-Datenbank konsistent zu halten, gibt es einerseits die Möglichkeit
der Normalisierung, andererseits sog. Transaktionen.
2 Was ist eine Transaktion
Eine Transaktion kann aus beliebigen SQL-Befehlen bestehen, z.B. einfachen SELECTStatements oder mehreren UPDATE-Befehlen. Angenommen, es wird in einer Bank von
einem Konto A Geld auf ein Konto B überwiesen, so muss nach diesem Vorgang die Datenbank konsistent sein. Bei diesem Vorgang sind mehrere Operationen beteiligt:
1. Lesen des Kontostandes Konto A
2. Reduzieren des Kontostandes Konto A (z.B. e 500,–)
3. Schreiben des neuen Kontostandes Konto A
4. Lesen des Kontostandes Konto B
5. Erhöhen des Kontostandes B (um e 500,–)
6. Schreiben des neuen Kontostandes Konto B
In diesem Beispiel wären 2 UPDATE-Befehle beteiligt:
UPDATE konto set betrag = betrag − 500 where konto = ”A”
UPDATE konto set betrag = betrag + 500 where konto = ”B”
Angenommen, nach dem ersten UPDATE stürzt der Rechner ab, so wird zwar vom
ersten Konto der Betrag abgebucht, aber nicht dem Konto B gutgeschrieben. Um dies zu
vermeiden, wurden in SQL-Datenbanken Transaktionen eingeführt. Die beiden Schritte im
obigen Beispiel werden durch die Transaktion zu einem Schritt zusammengefasst, entweder
werden beide Schritte ausgeführt oder keiner. Bei einer Transaktion werden zuerst die
Daten von der Festplatte gelesen, dann in den Hauptspeicher geladen, verändert und wieder
auf die Festplatte geschrieben. Es darf währenddessen keine andere zweite Transaktion diese
beeinflussen, da sich während einer Transaktion die Daten in einem inkonsistenten Zustand
befinden. Erst nach Abschluss sind die Daten wieder konsistent und weitere Transaktionen
können auf diese Daten wirken.
3 Eigenschaften einer Transaktion
Die Eigenschaften, die gewährleisten, dass eine Datenbank von einem konsistenten in einen
anderen konsistenten Zustand überführt wird, werden unter dem Begriff ACID zusammengefasst.
• Atomarity (Atomicity)
Eine Transaktion muss als Ganzes betrachtet werden. Es dürfen nicht nur Teile davon ablaufen, sondern stets die gesamte Transaktion. Schlägt ein Teil fehl, so werden die bisherigen
Vorgänge zurückgenommen (ROLLBACK). Es gibt keine Zwischenzustände.
• Konsistenz (Consistency)
Die Datenbank muss nachher wieder konsistent sein.
1
PostgreSQL
E3A
• Isolation
Transaktionen müssen isoliert von einander ablaufen. Läuft eine, so dürfen keine weiteren
Transaktionen auf diesen Datensatz zugreifen.
• Dauerhaftigkeit (Durability)
Ist eine Transaktion erfolgreich, so müssen die Daten dauerhaft gespeichert werden.
4 Transaktionsverwaltung
• Eine Transaktion wird mit begin of transaction (BOT) eingeleitet. In PostgreSQL
geschieht dies mit dem Befehl begin.
• commit: Alle Änderungen werden geschrieben.
• abort (rollback): die bisherigen Änderungen werden zurückgesetzt auf den Stand von
vor Beginn der Transaktion. Ein ROLLBACK geschieht durch ein Programm oder
durch Fehler, z. B. Absturz des Rechners.
In PostgreSQL könnte ein Programm beispielsweise so aussehen:
begin
UPDATE konto set betrag = betrag − 500 where konto = ”A”;
UPDATE konto set betrag = betrag + 500 where konto = ”B”;
commit;
5 Probleme im Mehrbenutzerbetrieb
Greifen mehrere Transaktionen auf denselben Datenbestand zu, können folgende Probleme
auftreten:
• Inkonsistentes Lesen (Nonrepeatable Read):
T1
T2
update A (1 → 2)
lese A (2)
rollback
update A(2 → 3)
commit
liest 3 statt 2
Hier liest eine Transaktion (T2) nicht-bestätigte Werte, die bei einem erneuten Aufruf
nicht mehr verifiziert werden können.
• Dirty Read: hier werden Daten von einer Transaktion gelesen, die von einer anderen
noch nicht freigegeben wurden.
2
PostgreSQL
E3A
T1
T2
select * from konto (2)
update konto set betrag =
betrag + 100 (102)
select * from konto (102)
update konto set betrag = betrag + 1
(103)
commit
rollback
Hier wird die Transaktion T1 abgebrochen, T2 arbeitet aber bereits mit mit einem
veränderten Wert A, der von T1 noch nicht bestätigt (commit) wurde.
• Phantom Read: hier handelt es sich um Datensätze, die nicht existieren aber eingefügt werden können.
T1
T2
select count(*) into x
from kunde
insert into kunde
values(...)
update kunde set
bonus = bonus + 1000/x;
In T1 sollen insgesamt e 1000,– auf die Kunden (X = 10) verteilt werden. Jeder
würde e 100,– erhalten. Durch zwischenzeitliches Einfügen eines weiteren Kunden
(nun sind es 11) erhalten alle e 100,–, somit wurden e 1100,– statt e 1000,– verteilt.
• Lost Update: Greifen zwei Transaktionen auf einen Datenstand ändernd (update)
zu, so wird eine Änderung verworfen. Angenommen, ein Kontostand beträgt 100:
T1
T2
A
100
update konto set betrag = 200
200
update konto set betrag = 250;
commit
rollback
250
100
Dieser Fall ist ähnlich dem Inkonsistenten Lesen, allerdings lesen beide Transaktionen
denselben Ausgangszustand. Eine der beiden Änderungen geht verloren.
6 Sperren und Isolationsstufen
SQL erlaubt es, die ACID-Kriterien aufzuweichen, um im Endeffekt eine höhere Geschwindigkeit zu erzielen. Es mssen dann weniger Transaktionen warten bzw. abgebrochen werden.
3
PostgreSQL
E3A
Dazu sind laut SQL-Standard folgende Isolationsstufen möglich:
• Read Uncommitted: Das ist die schwächste Stufe, es ist möglich, Daten zu lesen,
die noch nicht bestätigt wurden. Sie wird verwendet, wenn ein genaues Lesen der
Daten nicht notwendig ist.
• Read Committed: Erlaubt nur das Lesen mit commit bestätigter Werte. Ein Nonrepeatable Read ist nach wie vor möglich.
• Repeatable Write: Hier sind Nonrepeatable Reads nicht mehr möglich, nur noch
Phantom Reads.
• Serializable: die höchste Stufe; die obigen Fehlermöglichkeiten treten nicht mehr
auf.
In PostgreSQL wird die Transaktionsstufe für eine Sitzung folgendermaßen gesetzt:
SET SESSION CHARACTERISTICS AS
TRANSACTION ISOLATION LEVEL { READ COMMITTED | SERIALIZABLE }
wobei in PostgreSQL nur diese beiden Stufen implementiert sind. Neben diesen Angaben
können für Transaktionen noch Sperren gesetzt werden:
• Sperren auf Datenbankebene: die gesamte Datenbank wird für eine Transaktion gesperrt. Der Einsatz erfolgt nur bei extrem kritischen Abläufen.
• Sperren auf Tabellenebene: Nicht die Datenbank, nur die Tabelle, auf die die Transaktion Einfluss hat, wird gesperrt.
• Sperren auf Seitenebene: Eine Tabelle wird je nach Einstellung in mehrere Seiten
gegliedert, das sind Blöcke zu z.B. 4 , 8 oder 16 Kbyte.
• Sperren auf Datensatzebene bzw. auf Feldebene: Ein einzelner Datensatz bzw. ein
Feld wird gesperrt. Sperren auf Feldebene spielen in der Praxis kaum eine Rolle.
MySQL verwendet Datensatzsperren.
Der Aufwand für den Datenbankserver wird von oben nach unten größer, die Flexibilität
allerdings auch.
Isolationsstufe
DirtyRead
Non-Repeatable
Read
PhantomRead
Read Uncommitted
Ja
Ja
Ja
Read Committed
Nein
Ja
Ja
Repeatable Read
Nein
Nein
Ja
Serializable
Nein
Nein
Nein
4
PostgreSQL
E3A
7 Beispiele für Sperrstufen
• READ COMMITTED
T1
T2
begin;
BEGIN
select betrag from konto;
betrag
-------100
(1 Zeile)
BEGIN
update konto set betrag = betrag + 20;
UPDATE 1
select * from konto;
betrag
-------120
select betrag from konto;
betrag
-------100
(1 Zeile)
commit;
select betrag from konto;
betrag
-------120
(1 Zeile)
update konto set betrag = betrag + 20;
UPDATE 1
select * from konto;
betrag
-------140
(1 Zeile)
commit;
5
PostgreSQL
E3A
• SERIALIZABLE
T1
T2
test=# begin;
BEGIN
test=# set transaction isolation
test-# level serializable;
SET
test=# select * from konto;
betrag
-------140
(1 Zeile)
test=# begin;
BEGIN
test=# update konto set betrag = betrag + 20;
UPDATE 1
test=# select * from konto;
betrag
-------160
(1 Zeile)
test=# commit;
COMMIT
test=# select * from konto;
betrag
-------140
(1 Zeile)
test=# update konto set betrag = betrag + 20;
FEHLER: kann Zugriff nicht serialisieren
wegen gleichzeitiger Aktualisierung
test=# rollback;
ROLLBACK
Transaktion T1 wird auch nicht durch das COMMIT der T2 beeinflußt. Ein Update
wird hier untersagt, da T1 noch vom alten Datenbestand ’gespeichert’ hat. Erfolgt
allerdings bei T2 ein ROLLBACK, so kann die T1 erfolgreich ablaufen.
6
PostgreSQL
E3A
• Implizites Sperren Was passiert beim Ändern von Werten, wenn keine Transaktion
abgeschlossen wurde?
T1
T2
test=# begin;
BEGIN
test=# begin;
BEGIN
test=# update konto set betrag = betrag + 20;
UPDATE 1
test=# select * from konto;
betrag
-------200
test=# update konto set
betrag = betrag + 20;
[cursor blinkt]
test=# commit ;
COMMIT
test=#
UPDATE 1
test=# commit;
COMMIT
Hier wird bei T1 eine Schreibsperre angelegt (Cursor blinkt), da T2 noch nicht abgeschlossen wurde.
7
PostgreSQL
E3A
• Explizites Sperren
– SELECT...FOR UPDATE
T1
T2
test=# begin;
BEGIN
test=#
test=# set transaction isolation
level serializable;
SET
test=# begin;
BEGIN
test=# select * from konto for update;
betrag
-------220
(1 Zeile)
test=# update konto set betrag = betrag + 20;
[cursor blinkt]
test=# update konto set
betrag = betrag + 20;
UPDATE 1
test=# commit;
COMMIT
UPDATE 1
test=# commit;
COMMIT
8
PostgreSQL
E3A
– LOCK Damit wird eine komplette Tabelle im Gegensatz zu einer Datenzeile gesperrt.
T1
T2
test=# begin;
BEGIN
test=# begin;
BEGIN
test=# lock table konto;
LOCK TABLE
test=# select * from konto;
betrag
[cursor blinkt]
test=# rollback;
ROLLBACK
betrag
-------260
(1 Zeile)
– DEADLOCK Deadlocks entstehen, wenn zwei oder mehrere Transaktionen versuchen, über Kreuz Tabellen zu sperren. PostgreSQL hat einen eingebauten Mechanismus, der solche Deadlocks automatisch auflöst, sonst würde jede Transaktion theoretisch ewig warten. Beispiel:
9
PostgreSQL
E3A
T1
T2
test=# begin;
BEGIN
test=# begin;
BEGIN
test=# lock table konto;
LOCK TABLE
test=# lock table person;
LOCK TABLE
test=# lock table person;
LOCK TABLE
test=# lock table konto;
FEHLER: Verklemmung (Deadlock) entdeckt
DETAIL:
Prozess 30323 wartet auf AccessExclusiveLock-Sperre
auf Relation 17128 der Datenbank 17068; blockiert von Prozess 30320.
Prozess 30320 wartet auf AccessExclusiveLock-Sperre
auf Relation 27928 der Datenbank 17068; blockiert von Prozess 30323.
TIP: Einzelheiten zur Anfrage finden Sie im Serverlog.
Deadlocks können theoretisch auch bei Sperren im Row-Level-Mode (sperren
einzelner Tabellenzeilen) entstehen. Beispiel:
10
PostgreSQL
E3A
T1
T2
test=# begin;
BEGIN
test=# begin;
BEGIN
test=# lock table konto1 in
row exclusive mode;
LOCK TABLE
test=# lock table konto1 in
row exclusive mode;
LOCK TABLE
test=# update konto1 set betrag = 270
where kontorn = 100;
UPDATE 1
test=# update konto1 set betrag = 322
where kontorn = 200;
UPDATE 1
test=# update konto1 set betrag = 280
where kontorn = 100;
UPDATE 1
test=# update konto1 set betrag = 3223
where kontorn = 200;
FEHLER: Verklemmung (Deadlock) entdeckt
DETAIL:
Prozess 30320 wartet auf ShareLock-Sperre auf Transaktion 2740; blockiert von Prozess 30323.
Prozess 30323 wartet auf ShareLock-Sperre auf Transaktion 2739; blockiert von Prozess 30320.
TIP: Einzelheiten zur Anfrage finden Sie im Serverlog.
11
Document
Kategorie
Technik
Seitenansichten
6
Dateigröße
99 KB
Tags
1/--Seiten
melden