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)]
        -- Liest alle bestätigte Änderungen
        -- von vor Beginn der Transaktion
        -- Transaktion bleibt offen

    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)]
        -- Liest alle bestätigte Änderungen
        -- die in der Zwischenzeit erfolgt sind
        TRead: commit and close

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

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)]
        -- Liest alle bestätigte Änderungen
        -- von vor Beginn der Transaktion
        -- Transaktion bleibt offen

    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)]
        -- Änderungen von T2 und T3 waren
        -- zu Begin von TREAD nicht bestätigt
        -- Sind daher nicht sichtbar
        -- TREAD hat REPEATABLE_READ
        TRead: commit and close

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

Lost Update - Read Comitted

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 und T2 haben denselben DB-Zustand gelesen

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 überschreibt die Änderung von T1
    -- Ist möglich, da READ_COMMITTED
    T2: commit and close

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

Lost Update - Repeatable Read

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 ist auf REPEATABLE_READ gesetzt
    T2: select * from personal order by pid
    T2:[(100, 40000), (200, 50000)]
    -- T1 und T2 haben denselben DB-Zustand gelesen

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

    -- T2 versucht update
    T2: update personal set gehalt=42000 where pid=100
    T2: SerializationFailure
    -- Erkennt Änderung von T1
    -- Bricht ab, um Lost Update zu vermeiden
    -- Neustart erforderlich
    -- Basiert dann auf von T1 geänderten Werten

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

Write Skew - Repeatable Read

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

T1: START: REPEATABLE_READ
T1: select * from konto order by kid
T1:[(100, 80), (200, 50)]
-- T1 stellt fest, dass Konto 100 und 200
-- zusammen 130 Euro haben

    T2: START: REPEATABLE_READ
    T2: select * from konto order by kid
    T2:[(100, 80), (200, 50)]
    -- T2 stellt ebenfalls fest, dass Konto 100 und 200
    -- zusammen 130 Euro haben

T1: update konto set betrag = betrag -90 where kid=100
T1:[(100, -10), (200, 50)]
-- T1 hebt 90 Euro von Konto 100 ab, da negativer 
-- Kontostand durch Konto 200 gedeckt ist
T1: commit and close

    T2: update konto set betrag = betrag -50 where kid=200
    T2:[(100, 80), (200, 0)]
    -- T2 hebt 50 Euro von Konto 200 ab
    -- Ist ok, da Konto 200 den Betrag deckt
    T2: commit and close

-- Invariante verletzt, da Summe
-- auf beiden Konten nicht negativ sein darf
After  (kid, betrag): [(100, -10), (200, 0)]

Write Skew - SERIALIZABLE - T2->T1

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

T1: START: SERIALIZABLE
-- T1 ist nun auf SERIALIZABLE eingestellt
T1: select * from konto order by kid
T1:[(100, 80), (200, 50)]
-- T1 stellt fest, dass Konto 100 und 200
-- zusammen 130 Euro haben

    T2: START: SERIALIZABLE
    -- T2 ist nun auf SERIALIZABLE eingestellt
    T2: select * from konto order by kid
    T2:[(100, 80), (200, 50)]
    -- T2 stellt ebenfalls fest, dass Konto 100 und 200
    -- zusammen 130 Euro haben

T1: update konto set betrag = betrag -90 where kid=100
T1:[(100, -10), (200, 50)]
-- T1 hebt 90 Euro von Konto 100 ab, da negativer 
-- Kontostand durch Konto 200 gedeckt ist
T1: commit and close

    -- T2 versucht 50 Euro von Konto 200 abzuheben
    T2: update konto set betrag = betrag -50 where kid=200
    T2: SerializationFailure
    -- Ist nicht möglich, da T2 vom gelesenen Zustand
    -- von Konto 100 abhängt, dieser wurde aber geändert
    -- Neustart erforderlich
    -- Basiert dann auf von T1 geänderten Werten

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

Write Skew - SERIALIZABLE - T2-/-> T1

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

T1: START: SERIALIZABLE
-- T1 immer noch auf SERIALIZABLE
T1: select * from konto order by kid
T1:[(100, 80), (200, 50)]
-- T1 stellt fest, dass Konto 100 und 200
-- zusammen 130 Euro haben

T1: update konto set betrag = betrag -90 where kid=100
T1:[(100, -10), (200, 50)]
-- T1 hebt 90 Euro von Konto 100 ab, da negativer 
-- Kontostand durch Konto 200 gedeckt ist
T1: commit and close

    T2: START: SERIALIZABLE
    -- Hat den Zustand von Konto 100 nicht gelesen
    T2: update konto set betrag = betrag -50 where kid=200
    T2:[(100, -10), (200, 0)]
    -- Update möglich, da die Änderung von Konto 100
    -- für T2 nicht relevant ist
    T2: commit and close

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

Serialisierungsfehler

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)]
T1: commit and close

    T2: START: REPEATABLE_READ
    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

-- Erst T1 komplett, dann T2
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)]

    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: START: REPEATABLE_READ
T1: insert into cv select 2, sum(val) from cv where cls=1
T1:[(1, 10), (1, 20), (1, 300), (2, 100), (2, 200), (2, 330)]
T1: commit and close

-- Erst T2 komplett, dann T1
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 1, sum(val) from cv where cls=2
T1:[(1, 10), (1, 20), (1, 300), (2, 100), (2, 200)]

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

T1: commit and close

-- Jetzt T1 und T2 verzahnt
-- REPEATABLE_READ
-- Ergebnis entspricht keiner serialisierten Ausführung
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 1, sum(val) from cv where cls=2
T1:[(1, 10), (1, 20), (1, 300), (2, 100), (2, 200)]

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

T1: SerializationFailure

-- Erneut T1 und T2 verzahnt
-- SERIALIZABLE
-- Serialisierungsfehler wird erkannt
After  (cls, val): [(1, 10), (1, 20), (2, 30), (2, 100), (2, 200)]

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)]
-- T1 hat Datensatz 100 geändert, aber noch nicht bestätigt
-- T1 hält Sperre auf Datensatz 100

    -- T2 versucht auch, Datensatz 100 zu ändern
    -- Bleibt stehen, wegen Sperre von T1
    T2: START: READ_COMMITTED
    T2: update personal set gehalt= gehalt + 2000 where pid=100

T1: rollback and close
-- T1 macht Rollback, Sperre fällt weg

    -- T2 kann weiterlaufen
    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)]
-- T1 hat Datensatz 100 geändert, aber noch nicht bestätigt
-- T1 hält Sperre auf Datensatz 100

    -- T2 versucht auch, Datensatz 100 zu ändern
    -- Bleibt stehen, wegen Sperre von T1
    T2: START: READ_COMMITTED
    T2: update personal set gehalt= gehalt + 2000 where pid=100

T1: commit and close
-- T1 macht Commit, Sperre fällt weg

    -- T2 kann weiterlaufen
    -- Aktualisiert wegen READ_COMMITTED den Zustand von 100
    -- Macht update auf geänderten Zustand
    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)]
-- T1 hat Datensatz 100 gelöscht, aber noch nicht bestätigt
-- T1 hält Sperre auf Datensatz 100

    -- T2 versucht, Datensatz 100 zu ändern
    -- Bleibt stehen, wegen Sperre von T1
    T2: START: READ_COMMITTED
    T2: update personal set gehalt= gehalt + 2000 where pid=100

T1: commit and close
-- T1 macht Commit, Sperre fällt weg

    -- T2 kann weiterlaufen
    -- Aktualisiert wegen READ_COMMITTED den Zustand von 100,
    -- d.h. die Löschung
    -- Update von T2 läuft ins Leere
    T2:[(200, 50000)]
    T2: commit and close

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

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)]
-- T1 hat Datensatz 100 geändert, aber noch nicht bestätigt
-- T1 hält Sperre auf Datensatz 100

    -- T2 versucht auch, Datensatz 100 zu ändern
    -- Bleibt stehen, wegen Sperre von T1
    -- T2 ist auf REPEATABLE_READ
    T2: START: REPEATABLE_READ
    T2: update personal set gehalt= gehalt + 2000 where pid=100

T1: rollback and close
-- T1 macht Rollback, Sperre fällt weg

    -- T2 kann weiterlaufen
    -- Keine Veränderung von Datensatz 100 wegen Rollback
    -- T2 kann erfolgreich abgeschlossen werden
    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)]
-- T1 hat Datensatz 100 geändert, aber noch nicht bestätigt
-- T1 hält Sperre auf Datensatz 100

    -- T2 versucht auch, Datensatz 100 zu ändern
    -- Bleibt stehen, wegen Sperre von T1
    -- T2 ist auf REPEATABLE_READ
    T2: START: REPEATABLE_READ
    T2: update personal set gehalt= gehalt + 2000 where pid=100

T1: commit and close
-- T1 macht Commit, Sperre fällt weg

    -- T2 kann weiterlaufen
    -- Erkennt Änderung auf Datensatz 100
    -- Bricht ab
    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)]
-- T1 hat Datensatz 100 gelöscht, aber noch nicht bestätigt
-- T1 hält Sperre auf Datensatz 100

    -- T2 versucht, Datensatz 100 zu ändern
    -- Bleibt stehen, wegen Sperre von T1
    -- T2 ist auf REPEATABLE_READ
    T2: START: REPEATABLE_READ
    T2: update personal set gehalt= gehalt + 2000 where pid=100

T1: commit and close
-- T1 macht Commit, Sperre fällt weg

    -- T2 kann weiterlaufen
    -- Erkennt Änderung (Löschung) auf Datensatz 100
    -- Bricht ab
    T2: SerializationFailure

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

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)]
-- Hits werden für Site 100 und 200 um 1 erhöht
-- Sperren auf beiden Datensätzen

    -- T2 versucht, Datensätze mit 10 Hits zu löschen
    -- Bleibt stehen, wegen Sperren von T1
    -- Datensatz 200 ist in der Löschmenge
    T2: START: READ_COMMITTED
    T2: delete from website where hits = 10

T1: commit and close
-- T1 macht Commit, Sperren fallen weg

    -- T2 kann weiterlaufen
    -- Aktualisiert Löschmenge, d.h. Datensatz 200
    -- Hat jetzt 11 Hits, erfüllt die Löschbedingung nicht mehr
    -- Löschung läuft ins Leere
    T2:[(100, 10), (200, 11)]
    T2: commit and close

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

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)]
-- Hits werden für Site 100 und 200 um 1 erhöht
-- Sperren auf beiden Datensätzen

    -- T2 versucht, Datensätze mit 10 Hits zu löschen
    -- Bleibt stehen, wegen Sperren von T1
    -- Datensatz 200 ist in der Löschmenge
    T2: START: REPEATABLE_READ
    T2: delete from website where hits = 10

T1: commit and close
-- T1 macht Commit, Sperren fallen weg

    -- T2 kann weiterlaufen
    -- Datensatz 200 aus der Löschmenge wurde geändert
    -- Bricht ab
    T2: SerializationFailure

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

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)]
-- T1 liest Kontostände
-- T1 hält Sperre auf Datensatz 100 und 200

    -- T2 versucht auch, Sperren auf Datensätze 100 und 200 zu setzen
    -- Bleibt stehen, wegen Sperren von T2
    T2: START: READ_COMMITTED
    T2: select * from konto order by kid for update

T1: update konto set betrag = betrag -90 where kid=100
T1:[(100, -10), (200, 50)]
T1: commit and close
-- T1 hebt 90 Euro von Konto 100 ab
-- Bestätigt und entfernt alle Sperren

    -- T2 läuft weiter
    -- Stellt fest, dass Abhebung von 50 Euro nicht gedeckt ist
    T2:[(100, -10), (200, 50)]
    T2: commit and close

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