Datenbank Transaktionen: ACID, Isolation Levels & Deadlocks

Datenbank-Transaktionen sind fundamental für die Gewährleistung von Datenkonsistenz und -integrität in modernen Anwendungen. Sie ermöglichen sichere, zuverlässige Operationen auch bei gleichzeitigem Zugriff mehrerer Benutzer.

Was sind Transaktionen?

Definition und Grundlagen

Eine Transaktion ist eine logische Einheit von Arbeit, die aus einer oder mehreren Datenbankoperationen besteht. Transaktionen müssen als atomare Einheit behandelt werden - entweder werden alle Operationen erfolgreich ausgeführt oder keine.

Transaktions-Eigenschaften

-- Transaktion als logische Einheit
BEGIN TRANSACTION;

-- Operation 1: Konto abbuchen
UPDATE Konten SET saldo = saldo - 100 WHERE konto_id = 1;

-- Operation 2: Konto gutschreiben  
UPDATE Konten SET saldo = saldo + 100 WHERE konto_id = 2;

-- Entweder beide Operationen erfolgreich oder keine
COMMIT;
-- oder bei Fehler: ROLLBACK;

ACID-Eigenschaften

Atomicity (Atomarität)

Atomicity stellt sicher, dass eine Transaktion entweder vollständig oder gar nicht ausgeführt wird.

-- Beispiel für Atomarität
CREATE TABLE Transaktionen (
    trans_id INT PRIMARY KEY AUTO_INCREMENT,
    von_konto INT,
    zu_konto INT,
    betrag DECIMAL(10,2),
    status VARCHAR(20),
    zeitpunkt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Atomare Überweisung
DELIMITER //
CREATE PROCEDURE ueberweisung(
    IN von_konto_id INT,
    IN zu_konto_id INT,
    IN betrag DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- Prüfen ob Saldo ausreichend
    DECLARE aktuelles_saldo DECIMAL(10,2);
    SELECT saldo INTO aktuelles_saldo FROM Konten WHERE konto_id = von_konto_id;
    
    IF aktuelles_saldo < betrag THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Unzureichendes Saldo';
    END IF;
    
    -- Geld abbuchen
    UPDATE Konten SET saldo = saldo - betrag WHERE konto_id = von_konto_id;
    
    -- Geld gutschreiben
    UPDATE Konten SET saldo = saldo + betrag WHERE konto_id = zu_konto_id;
    
    -- Transaktion protokollieren
    INSERT INTO Transaktionen (von_konto, zu_konto, betrag, status)
    VALUES (von_konto_id, zu_konto_id, betrag, 'ERFOLGREICH');
    
    COMMIT;
END //
DELIMITER ;

-- Aufruf
CALL ueberweisung(1, 2, 100.00);

Consistency (Konsistenz)

Consistency stellt sicher, dass die Datenbank nach einer Transaktion in einem konsistenten Zustand verbleibt.

-- Beispiel für Konsistenz-Regeln
CREATE TABLE Konten (
    konto_id INT PRIMARY KEY,
    inhaber VARCHAR(100),
    saldo DECIMAL(10,2) NOT NULL,
    CHECK (saldo >= 0)  -- Saldo darf nicht negativ sein
);

CREATE TABLE Ueberweisungsregeln (
    regel_id INT PRIMARY KEY,
    max_betrag_pro_tag DECIMAL(10,2),
    max_anzahl_pro_tag INT
);

-- Konsistenz-sichernde Transaktion
DELIMITER //
CREATE PROCEDURE konsistente_ueberweisung(
    IN von_konto_id INT,
    IN zu_konto_id INT,
    IN betrag DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- Business-Regel: Maximalbetrag prüfen
    DECLARE max_betrag DECIMAL(10,2);
    SELECT max_betrag_pro_tag INTO max_betrag 
    FROM Ueberweisungsregeln WHERE regel_id = 1;
    
    IF betrag > max_betrag THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Betrag exceeds maximum';
    END IF;
    
    -- Saldo-Prüfung (durch CHECK constraint erzwungen)
    DECLARE aktuelles_saldo DECIMAL(10,2);
    SELECT saldo INTO aktuelles_saldo FROM Konten WHERE konto_id = von_konto_id;
    
    IF aktuelles_saldo < betrag THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds';
    END IF;
    
    -- Operationen ausführen
    UPDATE Konten SET saldo = saldo - betrag WHERE konto_id = von_konto_id;
    UPDATE Konten SET saldo = saldo + betrag WHERE konto_id = zu_konto_id;
    
    COMMIT;
END //
DELIMITER ;

Isolation (Isolation)

Isolation stellt sicher, dass gleichzeitig ausgeführte Transaktionen sich nicht gegenseitig beeinflussen.

-- Beispiel für Isolation
-- Session 1:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;

-- Session 2 (parallel):
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;

-- Session 1 liest Daten
SELECT * FROM Konten WHERE konto_id = 1;

-- Session 2 modifiziert Daten
UPDATE Konten SET saldo = 1500 WHERE konto_id = 1;
COMMIT;

-- Session 1 liest erneut (je nach Isolation Level)
SELECT * FROM Konten WHERE konto_id = 1;

Durability (Dauerhaftigkeit)

Durability stellt sicher, dass die Änderungen einer Transaktion permanent gespeichert werden.

-- Beispiel für Durability
-- Nach COMMIT sind Änderungen permanent
START TRANSACTION;
UPDATE Konten SET saldo = 2000 WHERE konto_id = 1;
COMMIT;  -- Änderungen sind jetzt permanent

-- Auch bei Systemabsturz sind Änderungen erhalten
-- (durch Write-Ahead Logging und andere Mechanismen)

Isolation Levels

READ UNCOMMITTED

Niedrigster Isolation Level - erlaubt “Dirty Reads”.

-- READ UNCOMMITTED Beispiel
-- Session 1:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
UPDATE Konten SET saldo = 500 WHERE konto_id = 1;
-- Noch nicht committed!

-- Session 2:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM Konten WHERE konto_id = 1;
-- Liest den uncommitted Wert (500) - Dirty Read!

-- Session 1:
ROLLBACK;  -- Änderung wird zurückgerollt

-- Session 2 hat ungültige Daten gelesen

READ COMMITTED

Verhindert Dirty Reads, erlaubt aber Non-Repeatable Reads.

-- READ COMMITTED Beispiel
-- Session 1:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT saldo FROM Konten WHERE konto_id = 1;  -- Liest 1000

-- Session 2:
START TRANSACTION;
UPDATE Konten SET saldo = 1500 WHERE konto_id = 1;
COMMIT;

-- Session 1 liest erneut:
SELECT saldo FROM Konten WHERE konto_id = 1;  -- Liest jetzt 1500
-- Non-Repeatable Read!

REPEATABLE READ

Verhindert Dirty Reads und Non-Repeatable Reads, erlaubt aber Phantom Reads.

-- REPEATABLE READ Beispiel
-- Session 1:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM Konten WHERE inhaber LIKE 'A%';  -- Liest 3 Konten

-- Session 2:
START TRANSACTION;
INSERT INTO Konten VALUES (4, 'Anna Schmidt', 2000);
COMMIT;

-- Session 1 liest erneut:
SELECT * FROM Konten WHERE inhaber LIKE 'A%';  -- Immer noch 3 Konten
-- Neue Zeile wird nicht gesehen (kein Phantom Read in MySQL)

SERIALIZABLE

Höchster Isolation Level - verhindert alle Anomalien.

-- SERIALIZABLE Beispiel
-- Session 1:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT AVG(saldo) FROM Konten;  -- Berechnet Durchschnitt

-- Session 2:
START TRANSACTION;
INSERT INTO Konten VALUES (5, 'Bernd Mueller', 3000);
-- Wartet bis Session 1 fertig ist!

-- Session 1:
COMMIT;

-- Session 2 kann jetzt fortfahren
COMMIT;

Konkurrenzkontrolle

Pessimistic Concurrency Control

Sperrt Ressourcen proaktiv, um Konflikte zu vermeiden.

-- Pessimistic Locking Beispiel
-- Explicit Locks
START TRANSACTION;

-- Zeile sperren
SELECT * FROM Konten WHERE konto_id = 1 FOR UPDATE;

-- Andere Transaktionen müssen warten
-- Session 2:
SELECT * FROM Konten WHERE konto_id = 1 FOR UPDATE;
-- Wartet bis Session 1 committed/rolled back

-- Operationen durchführen
UPDATE Konten SET saldo = saldo - 100 WHERE konto_id = 1;

COMMIT;  -- Lock wird freigegeben

Optimistic Concurrency Control

Geht davon aus, dass Konflikte selten sind und löst sie bei Bedarf.

-- Optimistic Concurrency mit Version Column
CREATE TABLE Produkte (
    produkt_id INT PRIMARY KEY,
    name VARCHAR(100),
    preis DECIMAL(10,2),
    bestand INT,
    version INT DEFAULT 0
);

-- Update mit Versionsprüfung
DELIMITER //
CREATE PROCEDURE update_produkt_optimistic(
    IN produkt_id INT,
    IN neuer_preis DECIMAL(10,2),
    IN erwartete_version INT
)
BEGIN
    DECLARE affected_rows INT;
    
    START TRANSACTION;
    
    UPDATE Produkte 
    SET preis = neuer_preis, version = version + 1
    WHERE produkt_id = produkt_id AND version = erwartete_version;
    
    SET affected_rows = ROW_COUNT();
    
    IF affected_rows = 0 THEN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Concurrency conflict - product was modified';
    ELSE
        COMMIT;
    END IF;
END //
DELIMITER ;

-- Anwendung
-- Zuerst lesen
SELECT produkt_id, name, preis, version FROM Produkte WHERE produkt_id = 1;

-- Update mit erwarteter Version
CALL update_produkt_optimistic(1, 29.99, 5);

Deadlocks

Deadlock-Erkennung und -Vermeidung

Deadlocks entstehen, wenn Transaktionen aufeinander warten.

-- Deadlock Beispiel
-- Session 1:
START TRANSACTION;
UPDATE Konten SET saldo = saldo - 100 WHERE konto_id = 1;
-- Wartet auf konto_id = 2
UPDATE Konten SET saldo = saldo + 100 WHERE konto_id = 2;

-- Session 2 (parallel):
START TRANSACTION;
UPDATE Konten SET saldo = saldo - 50 WHERE konto_id = 2;
-- Wartet auf konto_id = 1
UPDATE Konten SET saldo = saldo + 50 WHERE konto_id = 1;

-- DEADLOCK! Beide warten aufeinander

Deadlock-Vermeidungsstrategien

-- 1. Konsistente Reihenfolge der Locks
DELIMITER //
CREATE PROCEDURE sichere_ueberweisung(
    IN von_konto_id INT,
    IN zu_konto_id INT,
    IN betrag DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    -- Immer in gleicher Reihenfolge locken
    IF von_konto_id < zu_konto_id THEN
        SET @lock1 = von_konto_id;
        SET @lock2 = zu_konto_id;
    ELSE
        SET @lock1 = zu_konto_id;
        SET @lock2 = von_konto_id;
    END IF;
    
    START TRANSACTION;
    
    -- Locks in konsistenter Reihenfolge
    SELECT * FROM Konten WHERE konto_id = @lock1 FOR UPDATE;
    SELECT * FROM Konten WHERE konto_id = @lock2 FOR UPDATE;
    
    -- Operationen durchführen
    IF von_konto_id < zu_konto_id THEN
        UPDATE Konten SET saldo = saldo - betrag WHERE konto_id = von_konto_id;
        UPDATE Konten SET saldo = saldo + betrag WHERE konto_id = zu_konto_id;
    ELSE
        UPDATE Konten SET saldo = saldo + betrag WHERE konto_id = zu_konto_id;
        UPDATE Konten SET saldo = saldo - betrag WHERE konto_id = von_konto_id;
    END IF;
    
    COMMIT;
END //
DELIMITER ;

-- 2. Timeout-basierte Wiederholung
DELIMITER //
CREATE PROCEDURE ueberweisung_mit_retry(
    IN von_konto_id INT,
    IN zu_konto_id INT,
    IN betrag DECIMAL(10,2)
)
BEGIN
    DECLARE retry_count INT DEFAULT 0;
    DECLARE max_retries INT DEFAULT 3;
    DECLARE deadlock_detected BOOLEAN DEFAULT FALSE;
    
    retry_loop: WHILE retry_count < max_retries DO
        BEGIN
            DECLARE EXIT HANDLER FOR 1213  -- Deadlock error code
            BEGIN
                SET deadlock_detected = TRUE;
                SET retry_count = retry_count + 1;
                IF retry_count < max_retries THEN
                    -- Kurze Wartezeit vor Retry
                    DO SLEEP(0.1 * retry_count);
                END IF;
            END;
            
            -- Transaktion durchführen
            CALL sichere_ueberweisung(von_konto_id, zu_konto_id, betrag);
            
            -- Erfolgreich - Schleife verlassen
            LEAVE retry_loop;
        END;
        
        IF deadlock_detected AND retry_count >= max_retries THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Max retries exceeded';
        END IF;
        
        SET deadlock_detected = FALSE;
    END WHILE;
END //
DELIMITER ;

Deadlock-Monitoring

-- Deadlock-Informationen abrufen (MySQL)
SHOW ENGINE INNODB STATUS;

-- Deadlock-Transaktionen überwachen
SELECT 
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

-- Lock-Status anzeigen
SELECT 
    object_name,
    lock_type,
    lock_mode,
    lock_status,
    engine_transaction_id
FROM performance_schema.data_locks
WHERE object_name = 'Konten';

Transaktions-Management in verschiedenen Datenbanken

MySQL/MariaDB

-- MySQL-spezifische Features
-- Autocommit steuern
SET autocommit = 0;  -- Manuelle Transaktionssteuerung
SET autocommit = 1;  -- Automatisches Commit (default)

-- Savepoints für partielle Rollbacks
START TRANSACTION;
UPDATE Konten SET saldo = saldo - 100 WHERE konto_id = 1;
SAVEPOINT sp1;

UPDATE Konten SET saldo = saldo - 50 WHERE konto_id = 2;
SAVEPOINT sp2;

-- Zum Savepoint zurückrollen
ROLLBACK TO sp1;
COMMIT;  -- Nur erste Änderung bleibt bestehen

-- XA Transaktionen für verteilte Systeme
XA START 'xid1';
UPDATE Konten SET saldo = saldo - 100 WHERE konto_id = 1;
XA END 'xid1';
XA PREPARE 'xid1';
XA COMMIT 'xid1';

PostgreSQL

-- PostgreSQL-spezifische Features
-- Transaction Isolation Levels
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Advisory Locks (application-level locking)
SELECT pg_advisory_lock(12345);  -- Lock erwerben
SELECT pg_advisory_unlock(12345);  -- Lock freigeben

-- Transaction-Snapshots
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM Konten WHERE konto_id = 1;

-- In anderer Session:
UPDATE Konten SET saldo = 2000 WHERE konto_id = 1;
COMMIT;

-- Original Session sieht immer noch alten Wert
SELECT * FROM Konten WHERE konto_id = 1;

Oracle

-- Oracle-spezifische Features
-- Read-Only Transaktionen
SET TRANSACTION READ ONLY;
SELECT * FROM Konten;  -- Garantiert konsistente Sicht

-- Autonomous Transactions
DELIMITER //
CREATE PROCEDURE log_transaktion(
    IN transaktion_id INT,
    IN beschreibung VARCHAR(200)
)
AS
BEGIN
    -- Autonomous Transaction
    PRAGMA AUTONOMOUS_TRANSACTION;
    
    INSERT INTO Transaktionslog (trans_id, beschreibung, zeitpunkt)
    VALUES (transaktion_id, beschreibung, SYSTIMESTAMP);
    
    COMMIT;  -- Commit nur für autonomous transaction
END;
//

-- Savepoints
SAVEPOINT sp1;
-- Operationen
ROLLBACK TO sp1;

Best Practices für Transaktions-Management

1. Transaktionen kurz halten

-- Schlecht: Lange Transaktion
START TRANSACTION;
SELECT * FROM grosse_tabelle;  -- Lange Abfrage
-- ... viele andere Operationen ...
UPDATE kleine_tabelle SET wert = 1;
COMMIT;

-- Gut: Kurze, fokussierte Transaktion
SELECT * FROM grosse_tabelle;  -- Außerhalb der Transaktion
START TRANSACTION;
UPDATE kleine_tabelle SET wert = 1;
COMMIT;

2. Richtigen Isolation Level wählen

-- Für die meisten Anwendungsfälle
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Für analytische Abfragen
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Für kritische Finanzoperationen
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

3. Fehlerbehandlung implementieren

-- Robuste Fehlerbehandlung
DELIMITER //
CREATE PROCEDURE robuste_transaktion()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
                                   @errno = MYSQL_ERRNO,
                                   @text = MESSAGE_TEXT;
        
        ROLLBACK;
        
        -- Logging
        INSERT INTO error_log (error_time, error_code, error_message)
        VALUES (NOW(), @errno, @text);
        
        -- Fehler weitergeben
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- Transaktionslogik
    INSERT INTO tabelle1 (wert) VALUES (1);
    UPDATE tabelle2 SET wert = 2 WHERE id = 1;
    
    COMMIT;
END //
DELIMITER ;

4. Connection Pooling verwenden

// Java Beispiel mit Connection Pool
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class TransactionService {
    private DataSource dataSource;
    
    public void executeInTransaction(TransactionCallback callback) {
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            conn.setAutoCommit(false);
            
            callback.execute(conn);
            
            conn.commit();
        } catch (SQLException e) {
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            throw new RuntimeException("Transaction failed", e);
        } finally {
            if (conn != null) {
                try {
                    conn.setAutoCommit(true);
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    
    @FunctionalInterface
    public interface TransactionCallback {
        void execute(Connection conn) throws SQLException;
    }
}

Prüfungsrelevante Konzepte

Wichtige ACID-Eigenschaften

Eigenschaft	Beschreibung	Implementierung
Atomicity	Alles oder nichts	Rollback, Write-Ahead Logging
Consistency	Konsistenter Zustand	Constraints, Triggers
Isolation	Keine Interferenzen	Locks, Isolation Levels
Durability	Permanente Speicherung	Redo Logs, Checkpoints

Isolation Levels im Vergleich

Level	Dirty Reads	Non-Repeatable Reads	Phantom Reads	Performance
READ UNCOMMITTED	✅	✅	✅	Höchste
READ COMMITTED	❌	✅	✅	Hoch
REPEATABLE READ	❌	❌	✅ (MySQL: ❌)	Mittel
SERIALIZABLE	❌	❌	❌	Niedrigste

Typische Prüfungsaufgaben

Erklären Sie ACID-Eigenschaften
Vergleichen Sie Isolation Levels
Implementieren Sie Deadlock-Vermeidung
Wählen Sie richtige Transaktionsstrategien
Analysieren Sie Konkurrenzprobleme

Zusammenfassung

Transaktions-Management ist fundamental für zuverlässige Datenbankanwendungen:

ACID-Eigenschaften garantieren Datenintegrität
Isolation Levels steuern Konkurrenzverhalten
Deadlock-Vermeidung sichert Systemstabilität
Optimistic vs Pessimistic Konkurrenzkontrolle
Best Practices optimieren Performance und Zuverlässigkeit

Gutes Transaktionsdesign erfordert Verständnis der Anwendungsanforderungen und der zugrundeliegenden Datenbank-Mechanismen.

Datenbank Transaktionen: ACID, Isolation Levels & Deadlocks

Datenbank Transaktionen: ACID, Isolation Levels & Deadlocks

Was sind Transaktionen?

Definition und Grundlagen

Transaktions-Eigenschaften

ACID-Eigenschaften

Atomicity (Atomarität)

Consistency (Konsistenz)

Isolation (Isolation)

Durability (Dauerhaftigkeit)

Isolation Levels

READ UNCOMMITTED

READ COMMITTED

REPEATABLE READ

SERIALIZABLE

Konkurrenzkontrolle

Pessimistic Concurrency Control

Optimistic Concurrency Control

Deadlocks

Deadlock-Erkennung und -Vermeidung

Deadlock-Vermeidungsstrategien

Deadlock-Monitoring

Transaktions-Management in verschiedenen Datenbanken

MySQL/MariaDB

PostgreSQL

Oracle

Best Practices für Transaktions-Management

1. Transaktionen kurz halten

2. Richtigen Isolation Level wählen

3. Fehlerbehandlung implementieren

4. Connection Pooling verwenden

Prüfungsrelevante Konzepte

Wichtige ACID-Eigenschaften

Isolation Levels im Vergleich

Typische Prüfungsaufgaben

Zusammenfassung

Ähnliche Beiträge

Datenbanken SQL Tutorial 2024 Befehle deutsch

Datenbank Normalisierung: 1NF, 2NF, 3NF, BCNF & Anomalien

Datenbank Management Programmierung SQL 2024 deutsch