Transaktionskonzepte in Postgres

Multiversion Concurrency Control, MVCC
Snapshot Isoloation, SI
Echte Serialisierbarkeit bei Snapshots, Serializable Snapshot Isoloation, SSI
Reader don't block Writer and Writer don't block Reader
Zeilensperren aber ebenfalls verfügbar, S2PL umsetzbar

Umsetzung SQL-Konsistenzstufen

Isolation Level	Dirty Read	Nonrepeatable Read	Phantom Read	Serialization Anomaly
Read uncommitted	Allowed, but not in PG	Possible	Possible	Possible
Read committed	Not possible	Possible	Possible	Possible
Repeatable read	Not possible	Not possible	Allowed, but not in PG	Possible
Serializable	Not possible	Not possible	Not possible	Not possible

Snapshot - Read Committed

Before (kid, betrag): [(100, 2), (200, 6)]

T1: START: READ_COMMITTED
T1: update konto set betrag = 3 where kid=100
T1: [(100, 3), (200, 6)]
T1: commit and close

                    TRead: START: READ_COMMITTED
                    TRead: select * from konto order by kid
                    TRead: [(100, 3), (200, 6)]

          T2: START: READ_COMMITTED
          T2: update konto set betrag = 7 where kid=200
          T2: [(100, 3), (200, 7)]
          T2: commit and close

T3: START: READ_COMMITTED
T3: insert into konto values (300, 1)
T3: [(100, 3), (200, 7), (300, 1)]
T3: commit and close

                    TRead: select * from konto order by kid
                    TRead: [(100, 3), (200, 7), (300, 1)]
                    TRead: commit and close

After  (kid, betrag): [(100, 3), (200, 7), (300, 1)]

TRead liest alle bestätigten Änderungen zum Zeitpunkt des Lesens.

Snapshot - Repeatble Read

Before (kid, betrag): [(100, 2), (200, 6)]

T1: START: READ_COMMITTED
T1: update konto set betrag = 3 where kid=100
T1: [(100, 3), (200, 6)]

T1: commit and close

                    TRead: START: REPEATABLE_READ
                    TRead: select * from konto order by kid
                    TRead: [(100, 3), (200, 6)]

          T2: START: READ_COMMITTED
          T2: update konto set betrag = 7 where kid=200
          T2: [(100, 3), (200, 7)]

          T2: commit and close

T3: START: READ_COMMITTED
T3: insert into konto values (300, 1)
T3: [(100, 3), (200, 7), (300, 1)]

T3: commit and close

                    TRead: select * from konto order by kid
                    TRead: [(100, 3), (200, 6)]
                    TRead: commit and close

After  (kid, betrag): [(100, 3), (200, 7), (300, 1)]

TRead liest alle bestätigten Änderungen zum Zeitpunkt des Starts dieser Transaktion

Lost Update

Before (pid, gehalt): [(100, 40000), (200, 50000)]

T1: START: READ_COMMITTED
T1: select * from personal order by pid
T1: [(100, 40000), (200, 50000)]

          T2: START: READ_COMMITTED
          T2: select * from personal order by pid
          T2: [(100, 40000), (200, 50000)]

T1: update personal set gehalt=41000 where pid=100
T1: [(100, 41000), (200, 50000)]
T1: commit and close

          T2: update personal set gehalt=42000 where pid=100
          T2: [(100, 42000), (200, 50000)]
          T2: commit and close

**********

Before (pid, gehalt): [(100, 40000), (200, 50000)]

T1: START: REPEATABLE_READ
T1: select * from personal order by pid
T1: [(100, 40000), (200, 50000)]

          T2: START: REPEATABLE_READ
          T2: select * from personal order by pid
          T2: [(100, 40000), (200, 50000)]

T1: update personal set gehalt=41000 where pid=100
T1: [(100, 41000), (200, 50000)]
T1: commit and close

          T2: update personal set gehalt=42000 where pid=100
          T2: SerializationFailure

After  (pid, gehalt): [(100, 41000), (200, 50000)]

Lost Update bei read committed möglich.

Kein Lost Update bei repeatable read.
Stattdessen Serialisierungsfehler mit Rollback.
Erneutes Starten der abgebrochenen Transktion.

Write Skew

Before (kid, betrag): [(100, 80), (200, 50)]

T1: START: REPEATABLE_READ
T1: select * from konto order by kid
T1: [(100, 80), (200, 50)]

          T2: START: REPEATABLE_READ
          T2: select * from konto order by kid
          T2: [(100, 80), (200, 50)]

T1: update konto set betrag = betrag -90 where kid=100
T1: [(100, -10), (200, 50)]
T1: commit and close

          T2: update konto set betrag = betrag -50 where kid=200
          T2: [(100, 80), (200, 0)]
          T2: commit and close

After  (kid, betrag): [(100, -10), (200, 0)]

**********

Before (kid, betrag): [(100, 80), (200, 50)]

T1: START: SERIALIZABLE
T1: select * from konto order by kid
T1: [(100, 80), (200, 50)]

          T2: START: SERIALIZABLE
          T2: select * from konto order by kid
          T2: [(100, 80), (200, 50)]

T1: update konto set betrag = betrag -90 where kid=100
T1: [(100, -10), (200, 50)]
T1: commit and close

          T2: update konto set betrag = betrag -50 where kid=200
          T2: SerializationFailure

After  (kid, betrag): [(100, -10), (200, 50)]

Write Skew bei repeatable read möglich.

Kein Write Skew bei serializable.
Stattdessen Serialisierungsfehler mit Rollback.
Erneutes Starten der abgebrochenen Transktion.

Update Conflicts 1 - READ COMMITTED

Before (pid, gehalt): [(100, 40000), (200, 50000)]

T1: START: READ_COMMITTED
T1: update personal set gehalt= gehalt + 1000 where pid=100
T1: [(100, 41000), (200, 50000)]

          T2: START: READ_COMMITTED
T1: rollback and close

          T2: update personal set gehalt= gehalt + 2000 where pid=100
          T2: [(100, 42000), (200, 50000)]
          T2: commit and close

After  (pid, gehalt): [(100, 42000), (200, 50000)]

**********

Before (pid, gehalt): [(100, 40000), (200, 50000)]

T1: START: READ_COMMITTED
T1: update personal set gehalt= gehalt + 1000 where pid=100
T1: [(100, 41000), (200, 50000)]

          T2: START: READ_COMMITTED
T1: commit and close

          T2: update personal set gehalt= gehalt + 2000 where pid=100
          T2: [(100, 43000), (200, 50000)]
          T2: commit and close

After  (pid, gehalt): [(100, 43000), (200, 50000)]

**********

Before (pid, gehalt): [(100, 40000), (200, 50000)]

T1: START: READ_COMMITTED
T1: delete from personal where pid=100
T1: [(200, 50000)]

          T2: START: READ_COMMITTED
T1: commit and close

          T2: update personal set gehalt= gehalt + 2000 where pid=100
          T2: [(200, 50000)]
          T2: commit and close

After  (pid, gehalt): [(200, 50000)]

Update Datensatz 100 von T1 und T2. T2 bleibt stehen, da Sperre durch T1.

Abschluss T1 mit Rollback.
T2 läuft weiter, liest alten Wert und ändert 100.

Abschluss T1 mit Commit.
T2 läuft weiter, liest neuen Wert und ändert 100.

Bei Löschung: Erneutes Lesen liefert keinen Datensatz 100 mehr.
Daher zweites Update ohne Effekt.

Update Conflicts 1 - REPEATABLE READ

Before (pid, gehalt): [(100, 40000), (200, 50000)]

T1: START: READ_COMMITTED
T1: update personal set gehalt= gehalt + 1000 where pid=100
T1: [(100, 41000), (200, 50000)]

          T2: START: REPEATABLE_READ
T1: commit and close

          T2: SerializationFailure

After  (pid, gehalt): [(100, 41000), (200, 50000)]

**********

Before (pid, gehalt): [(100, 40000), (200, 50000)]

T1: START: READ_COMMITTED
T1: delete from personal where pid=100
T1: [(200, 50000)]

          T2: START: REPEATABLE_READ
T1: commit and close

          T2: SerializationFailure

After  (pid, gehalt): [(200, 50000)]

T2 mit repeatable read.

Update Datensatz 100 von T1 und T2. T2 bleibt stehen, da Sperre durch T1.

Abschluss T1 mit Rollback.
Gleiches Ergebnis wie bei repeatable read.

Abschluss T1 mit Commit.
T2 Serialisierungsfehler.

Bei Löschung: T2 Serialisierungsfehler.

Update Conflicts 2 - READ COMMITTED

Before (wsid, hits): [(100, 9), (200, 10)]

T1: START: READ_COMMITTED
T1: update website set hits = hits + 1
T1: [(100, 10), (200, 11)]

          T2: START: READ_COMMITTED
T1: commit and close

          T2: delete from website where hits = 10
          T2: [(100, 10), (200, 11)]
          T2: commit and close

After  (wsid, hits): [(100, 10), (200, 11)]

T1 ändert alle Datensätze. Aber noch nicht bestätigt.

T2 liest Snapshot der zu löschenden Daten, enthält (200, 10)
Bleibt stehen.

T1 bestätigt Änderungen, wirkt sich auf Lösch-Snapshot aus.

T2 evaluiert Lösch-Snapshot, ist leer, da (200, 11) die Bedingung hits=10 nicht erfüllt.
Delete-Befehl ohne Effekt.

Update Conflicts 2 - REPEATABLE READ

Before (wsid, hits): [(100, 9), (200, 10)]

T1: START: READ_COMMITTED
T1: update website set hits = hits + 1
T1: [(100, 10), (200, 11)]

          T2: START: REPEATABLE_READ
T1: commit and close

          T2: SerializationFailure

After  (wsid, hits): [(100, 10), (200, 11)]

Serialisierungsfehler

Before (cls, val): [(1, 10), (1, 20), (2, 100), (2, 200)]

T1: START: SERIALIZABLE
T1: insert into cv select 2, sum(val) from cv where cls=1
T1: [(1, 10), (1, 20), (2, 30), (2, 100), (2, 200)]
T1: commit and close

          T2: START: SERIALIZABLE
          T2: insert into cv select 1, sum(val) from cv where cls=2
          T2: [(1, 10), (1, 20), (1, 330), (2, 30), (2, 100), (2, 200)]
          T2: commit and close

After  (cls, val): [(1, 10), (1, 20), (1, 330), (2, 30), (2, 100), (2, 200)]

**********

Before (cls, val): [(1, 10), (1, 20), (2, 100), (2, 200)]

T1: START: SERIALIZABLE
T1: insert into cv select 1, sum(val) from cv where cls=2
T1: [(1, 10), (1, 20), (1, 300), (2, 100), (2, 200)]
T1: commit and close

          T2: START: SERIALIZABLE
          T2: insert into cv select 2, sum(val) from cv where cls=1
          T2: [(1, 10), (1, 20), (1, 300), (2, 100), (2, 200), (2, 330)]
          T2: commit and close

After  (cls, val): [(1, 10), (1, 20), (1, 300), (2, 100), (2, 200), (2, 330)]

**********

Before (cls, val): [(1, 10), (1, 20), (2, 100), (2, 200)]

T1: START: REPEATABLE_READ
T1: insert into cv select 2, sum(val) from cv where cls=1
T1: [(1, 10), (1, 20), (2, 30), (2, 100), (2, 200)]

          T2: START: REPEATABLE_READ
          T2: insert into cv select 1, sum(val) from cv where cls=2
          T2: [(1, 10), (1, 20), (1, 300), (2, 100), (2, 200)]
          T2: commit and close

T1: commit and close

After  (cls, val): [(1, 10), (1, 20), (1, 300), (2, 30), (2, 100), (2, 200)]

**********

Before (cls, val): [(1, 10), (1, 20), (2, 100), (2, 200)]

T1: START: SERIALIZABLE
T1: insert into cv select 2, sum(val) from cv where cls=1
T1: [(1, 10), (1, 20), (2, 30), (2, 100), (2, 200)]

          T2: START: SERIALIZABLE
          T2: insert into cv select 1, sum(val) from cv where cls=2
          T2: [(1, 10), (1, 20), (1, 300), (2, 100), (2, 200)]
          T2: commit and close

T1: SerializationFailure

After  (cls, val): [(1, 10), (1, 20), (1, 300), (2, 100), (2, 200)]

Erster Durchlauf: Keine Verzahnung, d.h. serialisierter Ablauf.

Zweiter Durchlauf: Keine Verzahnung, d.h. serialisierter Ablauf.

Dritter Durchlauf: Repatable read und Verzahnung.
Ergebnis entspricht keinem serialisierten Ablauf.

Vierter Durchlauf: Serializable und Verzahnung.
Serialisierungsfehler wird erkannt.

Write Skew Avoidance - with Locks

Before (kid, betrag): [(100, 80), (200, 50)]

T1: START: READ_COMMITTED
T1: select * from konto order by kid for update
T1: [(100, 80), (200, 50)]

          T2: START: READ_COMMITTED
T1: update konto set betrag = betrag -90 where kid=100
T1: [(100, -10), (200, 50)]
T1: commit and close

          T2: select * from konto order by kid for update
          T2: [(100, -10), (200, 50)]

          T2: commit and close

After  (kid, betrag): [(100, -10), (200, 50)]

Explizites Setzen von Sperren (select for update).

T2 bleibt stehen, kann nicht einmal lesen.

T2 kann erst lesen, wenn T1 abgeschlossen.