SQL – Transaktionen und Datensicherheit

1. Was ist eine Transaktion?

Eine Transaktion ist eine Gruppe von Datenbankoperationen, die als zusammengehörige Einheit behandelt werden.

Entweder werden alle vorgesehenen Änderungen erfolgreich übernommen, oder die Änderungen werden bei Bedarf zurückgenommen.

Transaktionen werden beispielsweise bei Banküberweisungen, Bestellungen und Buchungssystemen verwendet.

Beispiel: Banküberweisung

100 Euro sollen von Konto A auf Konto B übertragen werden. Dafür sind zwei Änderungen notwendig:

1. 100 Euro von Konto A abziehen.
2. 100 Euro auf Konto B hinzufügen.

Beide Änderungen müssen zusammen erfolgreich abgeschlossen werden.

2. START TRANSACTION – Transaktion beginnen

Mit START TRANSACTION wird eine neue Transaktion gestartet.

SQL-Code

START TRANSACTION;

Anschließend können mehrere SQL-Anweisungen ausgeführt werden, bevor die Änderungen bestätigt oder zurückgenommen werden.

Einfaches Beispiel

START TRANSACTION;

UPDATE kunden
SET stadt = 'Hamburg'
WHERE kunden_id = 1;

COMMIT;

Die Änderung wird mit COMMIT bestätigt.

3. COMMIT – Änderungen speichern

COMMIT bestätigt alle noch offenen Änderungen einer Transaktion.

SQL-Code

START TRANSACTION;

UPDATE produkte
SET preis = 99.90
WHERE produkt_id = 1;

COMMIT;

Nach COMMIT sind die Änderungen dauerhaft übernommen und können nicht mehr durch ROLLBACK derselben Transaktion zurückgenommen werden.

4. ROLLBACK – Änderungen rückgängig machen

ROLLBACK macht die noch nicht bestätigten Änderungen einer Transaktion rückgängig.

SQL-Code

START TRANSACTION;

UPDATE produkte
SET preis = 999.00
WHERE produkt_id = 1;

ROLLBACK;

Die Preisänderung wird zurückgenommen. Der ursprüngliche Preis bleibt erhalten.

5. COMMIT und ROLLBACK im Vergleich

6. Praktisches Beispiel: Banküberweisung

Wir erstellen eine Testtabelle mit zwei Bankkonten.

Schritt 1: Tabelle erstellen

CREATE TABLE konten_test (
    konto_id INT AUTO_INCREMENT PRIMARY KEY,
    inhaber VARCHAR(100) NOT NULL,
    kontostand DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB;

Schritt 2: Testdaten einfügen

INSERT INTO konten_test
    (inhaber, kontostand)
VALUES
    ('Anna Müller', 1000.00),
    ('Max Schmidt', 500.00);

Ausgangssituation

Schritt 3: Überweisung durchführen

START TRANSACTION;

UPDATE konten_test
SET kontostand = kontostand - 100
WHERE konto_id = 1;

UPDATE konten_test
SET kontostand = kontostand + 100
WHERE konto_id = 2;

COMMIT;

Erwartetes Ergebnis

Die Gesamtsumme bleibt unverändert: 1500 Euro.

7. Fehler bei einer Transaktion

Wenn während einer Transaktion ein Fehler auftritt, können die bereits ausgeführten Änderungen zurückgenommen werden.

SQL-Code

START TRANSACTION;

UPDATE konten_test
SET kontostand = kontostand - 200
WHERE konto_id = 1;

-- Fehler festgestellt:
ROLLBACK;

Die Abbuchung wird rückgängig gemacht.

8. SAVEPOINT – Speicherpunkt setzen

Mit SAVEPOINT wird innerhalb einer Transaktion ein Speicherpunkt festgelegt.

Zu diesem Punkt kann später zurückgekehrt werden, ohne alle Änderungen der Transaktion zurücknehmen zu müssen.

SQL-Code

START TRANSACTION;

UPDATE kunden
SET stadt = 'Hamburg'
WHERE kunden_id = 1;

SAVEPOINT punkt1;

UPDATE kunden
SET stadt = 'München'
WHERE kunden_id = 2;

ROLLBACK TO SAVEPOINT punkt1;

COMMIT;

Erklärung

1. Die erste Änderung wird durchgeführt.
2. Ein Speicherpunkt wird gesetzt.
3. Die zweite Änderung wird durchgeführt.
4. Die zweite Änderung wird zurückgenommen.
5. Die erste Änderung wird mit COMMIT bestätigt.

9. ROLLBACK TO SAVEPOINT

Mit ROLLBACK TO SAVEPOINT werden Änderungen zurückgenommen, die nach dem angegebenen Speicherpunkt durchgeführt wurden.

SQL-Code

ROLLBACK TO SAVEPOINT punkt1;

Die Transaktion bleibt weiterhin aktiv.

Speicherpunkt entfernen

RELEASE SAVEPOINT punkt1;

RELEASE SAVEPOINT entfernt den Speicherpunkt, ohne die bisherigen Änderungen zurückzunehmen.

10. AUTOCOMMIT – Automatische Bestätigung

Bei aktiviertem AUTOCOMMIT werden einzelne SQL-Anweisungen außerhalb einer expliziten Transaktion automatisch bestätigt.

Aktuellen Wert prüfen

SELECT @@autocommit;

AUTOCOMMIT deaktivieren

SET autocommit = 0;

AUTOCOMMIT aktivieren

SET autocommit = 1;

11. ACID – Eigenschaften von Transaktionen

ACID beschreibt vier grundlegende Eigenschaften zuverlässiger Datenbanktransaktionen.

12. Atomicity – Atomarität

Eine Transaktion wird als zusammengehörige Einheit behandelt. Entweder werden alle Änderungen bestätigt oder die Änderungen werden zurückgenommen.

Beispiel

Bei einer Überweisung dürfen nicht nur 100 Euro abgebucht werden, ohne dass die 100 Euro dem Empfängerkonto gutgeschrieben werden.

13. Consistency – Konsistenz

Eine Transaktion muss die Datenbank von einem gültigen Zustand in einen anderen gültigen Zustand überführen.

Definierte Regeln wie Primärschlüssel, Fremdschlüssel und andere Integritätsbedingungen müssen eingehalten werden.

14. Isolation – Isolation

Mehrere Transaktionen können gleichzeitig ausgeführt werden. Die Isolation bestimmt, wie sie sich gegenseitig beeinflussen und welche Änderungen sichtbar sind.

Isolation Levels

Bei MySQL mit InnoDB ist REPEATABLE READ standardmäßig das voreingestellte Isolation Level.

15. Durability – Dauerhaftigkeit

Nach einem erfolgreichen COMMIT sollen die bestätigten Änderungen dauerhaft gespeichert bleiben, auch wenn anschließend ein Systemfehler auftritt.

Die Datenbank verwendet dafür unter anderem Transaktionsprotokolle.

16. Transaktionen und DDL-Befehle

Nicht alle SQL-Befehle können innerhalb einer Transaktion zurückgenommen werden.

In MySQL führen viele DDL-Befehle zu einem impliziten COMMIT.

Beispiele

CREATE TABLE test (
    id INT PRIMARY KEY
);

ALTER TABLE test
ADD COLUMN name VARCHAR(100);

DROP TABLE test;

17. Transaktionen mit PHP

Auch in PHP können Datenbanktransaktionen verwendet werden. Mit PDO stehen dafür entsprechende Methoden zur Verfügung.

Beispiel mit PDO

<?php

try {
    $pdo->beginTransaction();

    $sql = "UPDATE produkte
            SET preis = :preis
            WHERE produkt_id = :id";

    $stmt = $pdo->prepare($sql);

    $stmt->execute([
        'preis' => 99.90,
        'id' => 1
    ]);

    $pdo->commit();

    echo "Änderung gespeichert.";

} catch (Throwable $e) {

    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }

    echo "Die Änderung wurde nicht abgeschlossen.";
}

?>

Das Beispiel setzt eine bereits bestehende PDO-Datenbankverbindung in der Variablen $pdo voraus.

Bei erfolgreicher Ausführung wird COMMIT aufgerufen. Bei einer Ausnahme wird die Transaktion zurückgenommen, sofern sie noch aktiv ist.

18. Praktische Übungen

Versuchen Sie, die folgenden Aufgaben selbstständig zu lösen.

START TRANSACTION;

UPDATE kunden
SET stadt = 'Berlin'
WHERE kunden_id = 1;

COMMIT;

START TRANSACTION;

UPDATE produkte
SET preis = preis * 1.20
WHERE produkt_id = 1;

ROLLBACK;

START TRANSACTION;

SAVEPOINT sicherung1;

-- Weitere Änderungen

ROLLBACK TO SAVEPOINT sicherung1;

COMMIT;

19. Zusammenfassung

Transaktionen ermöglichen die sichere Verarbeitung zusammengehöriger Datenbankoperationen.

START TRANSACTION beginnt eine Transaktion. COMMIT bestätigt die Änderungen. ROLLBACK nimmt nicht bestätigte Änderungen zurück. Mit SAVEPOINT können Zwischenpunkte innerhalb einer Transaktion festgelegt werden.

Die ACID-Eigenschaften beschreiben wichtige Grundlagen für die Zuverlässigkeit von Transaktionen.