Datenbankkonzepte: Transaktionen, ACID, Integrität, Trigger, Stored Procedures und Index

Datenbankkonzepte: Transaktionen, ACID, Integrität, Trigger, Stored Procedures und Index

https://bildung.social/@oerinformatik/

https://oer-informatik.de/datenbankkonzepte

Datenbankmanagementsystem, Transaktion, ACID, Lost Update, Deadlock, Referentielle Integrität, Stored Procedure, Trigger, Index, Datenbank-Dump, Kollation, MariaDB

tl/dr; (ca. 32 min Lesezeit): Ein Datenbankmanagementsystem speichert nicht nur Daten, es schützt sie auch: Transaktionen fassen Anweisungen zu einer unteilbaren Einheit zusammen und erfüllen die ACID-Eigenschaften, Sperren verhindern, dass sich gleichzeitige Zugriffe gegenseitig überschreiben, und Integritätsbedingungen – allen voran die referentielle Integrität – halten die Daten widerspruchsfrei. Stored Procedures und Trigger verlagern Abläufe und Regeln in die Datenbank, ein Index beschleunigt die Suche. Für den Betrieb geht es um Leistungsmerkmale, Sicherheitsmaßnahmen, Ursachen langsamer Server, Datensicherung per Dump und die Kollation. Alle Beispiele sind in MariaDB-Notation [AI-Assisted]. (Zuletzt geändert am 27.10.2026)

Dieser Artikel ist Teil einer Artikelreihe über Datenbanken: Datenbankmanagementsysteme / Tabellen anlegen (DDL) / Constraints / Datensätze löschen / Datenbankkonzepte / Übungsaufgaben zu Datenbankkonzepten

Schon fit in diesem Thema? Nutze die Kann-Liste und die Leitfragen unter dem Artikel als Checkliste, ob Du die relevanten Kompetenzen bereits hast. Hier klicken, um Kann-Liste ein- oder auszublenden

Eine Bestellung im Onlineshop, eine Buchung in der Mitfahr-App, eine Zahlung an der Mensakasse: Hinter jedem dieser Vorgänge stehen mehrere SQL-Anweisungen, die nur zusammen einen Sinn ergeben. Was passiert, wenn der Server mitten im Vorgang ausfällt? Wenn zwei Personen gleichzeitig den letzten freien Platz buchen? Wenn jemand einen Artikel löscht, der noch in alten Bestellungen steht? Die Antworten liefert das Datenbankmanagementsystem (DBMS) – wenn man es richtig einsetzt. Dieser Artikel stellt die Konzepte vor, die dafür sorgen: Transaktionen und ACID, Sperren, Integritätsbedingungen, Stored Procedures, Trigger und Indizes.

Das Beispiel: Guthabenkarten der Schulmensa

Alle Beispiele im Artikel nutzen eine kleine Datenbank mensa: Schüler*innen bezahlen in der Mensa mit einer Guthabenkarte. Karten werden am Automaten aufgeladen, an der Kasse belastet, und Guthaben lässt sich von einer Karte auf eine andere umbuchen (zum Beispiel unter Geschwistern).

CREATE TABLE karte (
  karte_id  INT UNSIGNED  NOT NULL PRIMARY KEY,
  inhaber   VARCHAR(80)   NOT NULL,
  guthaben  DECIMAL(6,2)  NOT NULL DEFAULT 0.00,
  gesperrt  BOOLEAN       NOT NULL DEFAULT FALSE,
  CONSTRAINT chk_guthaben CHECK (guthaben >= 0)
) ENGINE=InnoDB;

CREATE TABLE buchung (
  buchung_id  INT UNSIGNED  NOT NULL AUTO_INCREMENT PRIMARY KEY,
  karte_id    INT UNSIGNED  NOT NULL,
  zeitpunkt   DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  art         ENUM('aufladung','zahlung','umbuchung') NOT NULL,
  betrag      DECIMAL(6,2)  NOT NULL,        -- positiv: Gutschrift, negativ: Belastung
  CONSTRAINT fk_buchung_karte FOREIGN KEY (karte_id) REFERENCES karte (karte_id)
) ENGINE=InnoDB;

INSERT INTO karte (karte_id, inhaber, guthaben, gesperrt) VALUES
  (101, 'Ayşe Kılıç',     12.50, FALSE),
  (102, 'Jan Hoffmeister', 3.00, FALSE),
  (103, 'Mariam Diallo',   0.80, TRUE);

ENGINE=InnoDB ist in MariaDB die Standard-Speicherengine. Wichtig ist sie hier, weil nur InnoDB Transaktionen und Fremdschlüssel unterstützt; die ältere Engine MyISAM kennt beides nicht.

Was ein DBMS mehr kann als speichern

Ein Datenbanksystem (DBS) besteht aus der eigentlichen Datenbank (den gespeicherten Daten) und dem Datenbankmanagementsystem (DBMS), also der Software, die alle Zugriffe auf die Daten verwaltet (zum Beispiel MariaDB, PostgreSQL oder Microsoft SQL Server). Anwendungen greifen nie direkt auf die Dateien zu, sondern schicken SQL-Anweisungen an das DBMS. Edgar F. Codd hat 1982 zusammengestellt, was ein DBMS dabei leisten soll. Frei zusammengefasst:

Dazu kommen in der Praxis die Optimierung von Abfragen (zum Beispiel mit Indizes) und die Möglichkeit, Programmlogik in der Datenbank abzulegen: Stored Procedures und Trigger.

Transaktionen: alles oder nichts

Warum eine Anweisung nicht reicht

Ayşe bucht 5,00 € von ihrer Karte auf die Karte ihres Bruders Jan um. In der Datenbank sind dafür drei Schritte nötig:

UPDATE karte SET guthaben = guthaben - 5.00 WHERE karte_id = 101;
UPDATE karte SET guthaben = guthaben + 5.00 WHERE karte_id = 102;
INSERT INTO buchung (karte_id, art, betrag) VALUES
  (101, 'umbuchung', -5.00),
  (102, 'umbuchung',  5.00);

Fällt der Server nach dem ersten UPDATE aus, sind 5,00 € von Ayşes Karte verschwunden, ohne bei Jan anzukommen. Läuft das INSERT auf einen Fehler, stimmen die Guthaben, aber die Buchungen fehlen. Beides darf nicht passieren.

Eine Transaktion bilden

Eine Transaktion ist eine Folge von Datenbankoperationen, die als logische, unteilbare Einheit behandelt wird: Entweder werden alle Änderungen dauerhaft übernommen oder keine. In MariaDB sieht das so aus:

START TRANSACTION;
UPDATE karte SET guthaben = guthaben - 5.00 WHERE karte_id = 101;
UPDATE karte SET guthaben = guthaben + 5.00 WHERE karte_id = 102;
INSERT INTO buchung (karte_id, art, betrag) VALUES
  (101, 'umbuchung', -5.00),
  (102, 'umbuchung',  5.00);
COMMIT;

• START TRANSACTION (gleichwertig: BEGIN) beginnt die Transaktion.

• COMMIT schließt sie ab: Alle Änderungen werden dauerhaft gespeichert und für andere Verbindungen sichtbar.

• ROLLBACK bricht sie ab: Alle Änderungen seit START TRANSACTION werden zurückgenommen, als hätte es sie nie gegeben.

Bis zum COMMIT sieht nur die eigene Verbindung die Änderungen (Ausnahme: Isolationsstufe READ UNCOMMITTED, siehe Isolationsstufen). Bricht die Verbindung ab oder stürzt der Server ab, bevor COMMIT ankommt, nimmt MariaDB die Transaktion beim Neustart automatisch zurück.

Autocommit: jede Anweisung ist eine eigene Transaktion

MariaDB arbeitet standardmäßig im Autocommit-Modus: Ohne START TRANSACTION ist jede einzelne Anweisung eine eigene Transaktion und wird sofort festgeschrieben. Das ist bequem, schützt aber nur die einzelne Anweisung. Bricht ein Programm nach der zweiten von drei Anweisungen ab, bleiben die ersten beiden gespeichert. Genau das passiert in vielen Fehlerbildern: Eine Bestellung ist angelegt, aber ohne Positionen; ein Lagerbestand ist verringert, aber die Bestellung fehlt.

Was bei einem Fehler in der Transaktion passiert

Ein häufiges Missverständnis: Schlägt in einer Transaktion eine Anweisung fehl (zum Beispiel wegen einer verletzten CHECK-Bedingung), nimmt MariaDB nur diese eine Anweisung zurück. Die Transaktion bleibt offen, die vorherigen Änderungen sind noch vorhanden. Die Anwendung muss den Fehler erkennen und ROLLBACK senden – in Java zum Beispiel im catch-Block, in einer Stored Procedure mit einem Fehler-Handler (siehe unten). Nur bei einem Deadlock oder wenn die Verbindung abbricht, nimmt MariaDB die ganze Transaktion selbst zurück.

START TRANSACTION;
UPDATE karte SET guthaben = guthaben - 5.00 WHERE karte_id = 102;  -- Jan hat nur 3,00 €
-- ERROR 4025 (23000): CONSTRAINT `chk_guthaben` failed for `mensa`.`karte`
ROLLBACK;

Was sich nicht zurücknehmen lässt

Anweisungen der DDL (CREATE TABLE, ALTER TABLE, DROP TABLE …) lösen in MariaDB ein implizites COMMIT aus: Sie schließen eine offene Transaktion ab und lassen sich selbst nicht mit ROLLBACK rückgängig machen. Strukturänderungen gehören deshalb nicht in eine Transaktion mit Datenänderungen; vorher wird eine Sicherung erstellt.

Mit SAVEPOINT name lassen sich innerhalb einer Transaktion Zwischenstände setzen, zu denen ROLLBACK TO SAVEPOINT name zurückkehrt, ohne die ganze Transaktion abzubrechen. Das ist eher Expertenwissen.

Die ACID-Eigenschaften

Ein DBMS, das Transaktionen zuverlässig verarbeitet, garantiert vier Eigenschaften, die nach ihren englischen Anfangsbuchstaben ACID heißen:

Zwei Hinweise, die in Erklärungen oft fehlen:

• Konsistenz ist eine gemeinsame Aufgabe. Das DBMS kann nur die Regeln prüfen, die ihm bekannt sind. Dass eine Umbuchung von 5,00 € auf beiden Karten genau 5,00 € bewegt, weiß nur die Anwendung. Deshalb ist die Transaktion so zu schreiben, dass sie korrekte Daten in korrekte Daten überführt.

• Dauerhaftigkeit ersetzt keine Datensicherung. Das Transaktionsprotokoll schützt vor Abstürzen, nicht vor einer defekten Festplatte, einem versehentlichen DELETE oder Ransomware. Dafür braucht es Backups (zum Beispiel mit mariadb-dump oder mariadb-backup) und ggf. Replikation.

ACID und verteilte Datenbanken: Werden Daten auf viele Server verteilt, lassen sich strenge Konsistenz und hohe Verfügbarkeit bei Netzwerkstörungen nicht gleichzeitig garantieren (CAP-Theorem). Viele NoSQL-Datenbanken verzichten deshalb auf einen Teil von ACID und arbeiten nach BASE (Basically Available, Soft State, Eventually Consistent): Sie sind immer erreichbar, alle Kopien stimmen aber erst nach kurzer Zeit wieder überein.

Mehrbenutzerbetrieb: wenn zwei gleichzeitig zugreifen

Das verlorene Update (Lost Update)

An der Mensa gibt es zwei Kassen. Ayşe bezahlt an Kasse 1 ein Menü (4,20 €), gleichzeitig zieht Kasse 2 einen Nachtisch (3,90 €) von derselben Karte ab, weil ihre Freundin die Karte ausgeliehen hat. Das Kassenprogramm liest zuerst das Guthaben, rechnet im Programm und schreibt dann den neuen Wert:

Richtig wäre 12,50 − 4,20 − 3,90 = 4,40 €. Kasse 2 hat mit einem veralteten Wert gerechnet und die Änderung von Kasse 1 überschrieben: ein verlorenes Update. Die Isolation ist verletzt, denn nacheinander ausgeführt wäre das nicht passiert. Dasselbe Problem entsteht auch bei Plätzen, Tickets oder Lagerbeständen: Zwei Personen buchen „den letzten freien Platz“.

Das Problem verschwindet nicht allein dadurch, dass jede Kasse ihre Anweisungen in eine Transaktion packt: In der Standard-Isolationsstufe von MariaDB liest ein einfaches SELECT einen Schnappschuss, ohne den Datensatz zu sperren.

Lösung 1: rechnen lassen, wo die Daten sind

Am einfachsten ist es, den neuen Wert nicht im Programm zu berechnen, sondern von der Datenbank – in einer Anweisung, die gleichzeitig die Bedingung prüft:

UPDATE karte
SET    guthaben = guthaben - 4.20
WHERE  karte_id = 101 AND guthaben >= 4.20 AND gesperrt = FALSE;

Ein UPDATE sperrt den Datensatz bis zum Ende der Transaktion und rechnet immer mit dem aktuellen, festgeschriebenen Wert. Die zweite Kasse wartet kurz und zieht dann von 8,30 € ab. Ob die Abbuchung geklappt hat, zeigt die Anzahl geänderter Zeilen (ROW_COUNT() in SQL, Rückgabewert von executeUpdate() in Java): 1 heißt erfolgreich, 0 heißt „Guthaben zu gering oder Karte gesperrt“.

Lösung 2: beim Lesen sperren

Muss das Programm erst lesen und dann entscheiden, sperrt es den Datensatz schon beim Lesen mit SELECT … FOR UPDATE:

START TRANSACTION;
SELECT guthaben, gesperrt FROM karte WHERE karte_id = 101 FOR UPDATE;
-- Programm prüft Guthaben und Sperre, dann:
UPDATE karte SET guthaben = guthaben - 4.20 WHERE karte_id = 101;
INSERT INTO buchung (karte_id, art, betrag) VALUES (101, 'zahlung', -4.20);
COMMIT;

Ein zweites SELECT … FOR UPDATE auf dieselbe Karte wartet, bis die erste Transaktion mit COMMIT oder ROLLBACK endet, und liest dann den neuen Wert. Man spricht von pessimistischem Sperren. Die Alternative, das optimistische Sperren, prüft erst beim Schreiben, ob sich der Datensatz inzwischen geändert hat (zum Beispiel über eine Versionsnummer in der WHERE-Bedingung), und wiederholt den Vorgang sonst.

Sperren gibt es in zwei Arten: Eine Lesesperre (shared lock, SELECT … LOCK IN SHARE MODE) erlaubt anderen das Lesen, aber nicht das Ändern; eine Schreibsperre (exclusive lock, FOR UPDATE, jedes UPDATE/DELETE) erlaubt anderen weder sperrendes Lesen noch Ändern. InnoDB sperrt einzelne Datensätze, nicht ganze Tabellen.

Deadlock: zwei warten aufeinander

Ayşe bucht 2,00 € auf Jans Karte um, gleichzeitig bucht Jan 1,00 € auf Ayşes Karte zurück. Beide Transaktionen sperren zuerst die Karte, von der abgebucht wird:

Keine der beiden kann weiterarbeiten: ein Deadlock (Verklemmung). InnoDB erkennt solche Zyklen sofort, bricht eine der Transaktionen ab und nimmt sie vollständig zurück (ERROR 1213: Deadlock found when trying to get lock; try restarting transaction). Die andere läuft weiter. Die abgebrochene Anwendung muss die Transaktion wiederholen. Vorbeugen lässt sich, indem alle Programme Datensätze in derselben Reihenfolge sperren (zum Beispiel immer zuerst die kleinere karte_id) und Transaktionen kurz halten.

Isolationsstufen

Wie stark sich Transaktionen gegenseitig abschirmen, legt die Isolationsstufe fest. Je höher die Stufe, desto weniger Anomalien, aber desto mehr Wartezeit:

• Dirty Read: Eine Transaktion liest Daten, die eine andere noch nicht festgeschrieben hat und vielleicht zurücknimmt.

• Non-Repeatable Read: Dieselbe Abfrage liefert innerhalb einer Transaktion zweimal einen anderen Wert, weil zwischendurch jemand den Datensatz geändert hat.

• Phantom: Eine wiederholte Abfrage findet plötzlich zusätzliche Zeilen, die eine andere Transaktion eingefügt hat.

Die Stufe wird mit SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; vor der nächsten Transaktion eingestellt. Ein verlorenes Update wie oben (lesen – im Programm rechnen – schreiben) verhindern in MariaDB 10.11 und 11.4 erst SERIALIZABLE oder ein sperrendes Lesen. Seit MariaDB 11.8 ist zusätzlich innodb_snapshot_isolation standardmäßig aktiv: Hat eine andere Transaktion den gelesenen Datensatz inzwischen geändert, bricht die zweite Transaktion beim Schreiben mit ERROR 1020 (Record has changed since last read) ab, wird zurückgenommen und muss wiederholt werden.

Integrität: die Daten bleiben widerspruchsfrei

Integrität heißt, dass die gespeicherten Daten korrekt, vollständig und widerspruchsfrei sind. Ein DBMS hilft dabei mit Integritätsbedingungen, die es bei jeder Änderung prüft. Man unterscheidet:

Wie die Constraints im Einzelnen notiert werden, zeigt der Artikel Constraints in der SQL-DDL. Hier geht es darum, was sie im Betrieb bewirken.

Referentielle Integrität im Betrieb

Zwischen karte (Elterntabelle, 1-Seite) und buchung (Kindtabelle, n-Seite, enthält den Fremdschlüssel) besteht eine 1:n-Beziehung mit referentieller Integrität. Das DBMS lässt deshalb keinen Zustand zu, in dem eine Buchung auf eine nicht vorhandene Karte verweist. Daraus folgen zwei Regeln:

1. Kein Kind ohne Eltern. Eine Buchung kann erst angelegt werden, wenn die Karte existiert:

INSERT INTO buchung (karte_id, art, betrag) VALUES (999, 'aufladung', 10.00);
-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
-- (`mensa`.`buchung`, CONSTRAINT `fk_buchung_karte` FOREIGN KEY (`karte_id`) REFERENCES `karte` (`karte_id`))

Die Ursache liegt also nicht in der Buchung selbst, sondern in der fehlenden Karte 999 (oder in einem Tippfehler bei der Kartennummer). Dasselbe gilt für ein UPDATE, das den Fremdschlüssel auf einen nicht vorhandenen Wert ändert.

2. Keine verwaisten Kinder. Eine Karte, zu der es noch Buchungen gibt, kann nicht einfach gelöscht werden:

DELETE FROM karte WHERE karte_id = 102;
-- ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
-- (`mensa`.`buchung`, CONSTRAINT `fk_buchung_karte` FOREIGN KEY (`karte_id`) REFERENCES `karte` (`karte_id`))

Die DELETE-Anweisung ist syntaktisch richtig. Sie wird abgewiesen, weil nach dem Löschen Buchungen auf eine Karte verweisen würden, die es nicht mehr gibt – die referentielle Integrität wäre verletzt. Die Fehlermeldung nennt die Kindtabelle (buchung) und den Constraint (fk_buchung_karte), der das Löschen verhindert. Ändern des Primärschlüssels der Karte wird aus demselben Grund abgewiesen.

Die richtige Löschreihenfolge

Sollen verknüpfte Daten gelöscht werden – zum Beispiel Testdaten nach einem Probelauf –, gilt bei RESTRICT (der Standardeinstellung): zuerst die Kinder, dann die Eltern, also zuerst die Datensätze der Tabelle mit dem Fremdschlüssel.

START TRANSACTION;
DELETE FROM buchung WHERE karte_id = 102;   -- 1. Kindtabelle
DELETE FROM karte   WHERE karte_id = 102;   -- 2. Elterntabelle
COMMIT;

Bei mehrstufigen Beziehungen arbeitet man sich von „unten“ (Tabellen, auf die niemand verweist) nach „oben“ vor. Beim Anlegen gilt die umgekehrte Reihenfolge: erst Eltern, dann Kinder. Wer die Reihenfolge nicht kennt, findet sie über die Fremdschlüssel im Relationenmodell oder mit SHOW CREATE TABLE.

Abkürzungen wie SET FOREIGN_KEY_CHECKS = 0 schalten die Prüfung ab und hinterlassen schnell verwaiste Datensätze. Sie sind nur beim Import vollständiger, bereits konsistenter Sicherungen sinnvoll.

Was beim Löschen passieren soll: ON DELETE

Die Fremdschlüsseldefinition legt fest, was mit den Kindern geschieht, wenn der Elterndatensatz gelöscht (oder sein Schlüssel geändert) wird:

Für die Mensa ist RESTRICT richtig: Buchungen sind Belege und dürfen nicht unbemerkt verschwinden. Eine nicht mehr benötigte Karte wird gesperrt statt gelöscht. Auch ein CASCADE ist übrigens referentielle Integrität: Die Datenbank bleibt widerspruchsfrei, nur auf andere Weise.

Stored Procedures: Abläufe in der Datenbank speichern

Eine Stored Procedure (gespeicherte Prozedur) ist eine benannte Folge von SQL-Anweisungen, die im DBMS gespeichert ist und von Clients mit einem einzigen Befehl (CALL) aufgerufen wird. Sie kann Parameter übernehmen und zurückgeben, Variablen, Verzweigungen und Schleifen enthalten und Transaktionen steuern. Das Bezahlen an der Mensakasse als Stored Procedure:

DELIMITER //
CREATE PROCEDURE bezahlen(IN p_karte_id INT UNSIGNED, IN p_betrag DECIMAL(6,2))
BEGIN
  DECLARE v_guthaben DECIMAL(6,2);

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;         -- bei jedem Fehler die ganze Transaktion zurücknehmen
    RESIGNAL;         -- und den Fehler an den Aufrufer weitergeben
  END;

  START TRANSACTION;
  SELECT guthaben INTO v_guthaben
  FROM   karte
  WHERE  karte_id = p_karte_id AND gesperrt = FALSE
  FOR UPDATE;

  IF v_guthaben IS NULL OR v_guthaben < p_betrag THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Karte unbekannt, gesperrt oder Guthaben zu gering';
  END IF;

  UPDATE karte SET guthaben = guthaben - p_betrag WHERE karte_id = p_karte_id;
  INSERT INTO buchung (karte_id, art, betrag) VALUES (p_karte_id, 'zahlung', -p_betrag);
  COMMIT;
END //
DELIMITER ;

Aufgerufen wird sie von der Kassensoftware oder in einem SQL-Client:

CALL bezahlen(101, 4.20);

Erläuterungen zum Aufbau:

• DELIMITER // ändert im Kommandozeilen-Client vorübergehend das Endezeichen, damit die Semikolons im Prozedurrumpf nicht schon als Ende der CREATE-Anweisung gelten. In grafischen Werkzeugen oder aus Java heraus ist das oft nicht nötig.

• Parameter haben eine Richtung: IN (Eingabe, Standard), OUT (Rückgabe an den Aufrufer, zum Beispiel in eine Sitzungsvariable @ergebnis), INOUT (beides).

• DECLARE legt lokale Variablen und Fehler-Handler an; SELECT … INTO schreibt ein Abfrageergebnis in Variablen. Findet die Abfrage keine Zeile, bleibt die Variable NULL.

• SIGNAL SQLSTATE '45000' löst einen selbst definierten Fehler aus. Der EXIT HANDLER fängt ihn wie jeden anderen SQL-Fehler, nimmt die Transaktion zurück und beendet die Prozedur.

Vorteile von Stored Procedures:

• Die Logik liegt an einer Stelle. Web-App, Kassensoftware und App für Mobilgeräte nutzen denselben Ablauf.

• Weniger Netzwerkverkehr: Statt vieler Einzelanweisungen geht ein Aufruf über das Netz.

• Sicherheit: Die Anwendung kann das Recht bekommen, die Prozedur auszuführen (GRANT EXECUTE ON PROCEDURE …), ohne direkt Tabellen ändern zu dürfen. Parameter werden nicht als SQL-Text zusammengesetzt, was SQL-Injection erschwert.

• Transaktionen und Prüfungen sind fest eingebaut und können nicht vergessen werden.

Nachteile: Die Prozedursprache ist herstellerspezifisch (MariaDB/MySQL, PL/pgSQL in PostgreSQL, T-SQL in SQL Server, PL/SQL in Oracle) und erschwert einen Wechsel des DBMS. Versionsverwaltung, Tests und Fehlersuche sind aufwendiger als im Anwendungscode, und die Last liegt auf dem Datenbankserver.

Verwandt sind Stored Functions (CREATE FUNCTION … RETURNS …): Sie liefern genau einen Wert zurück und lassen sich wie eingebaute Funktionen in Abfragen verwenden, zum Beispiel SELECT preis_brutto(netto_preis) FROM artikel;.

Trigger: automatisch reagieren

Ein Trigger (Auslöser) ist eine besondere Art einer gespeicherten Prozedur, die nicht aufgerufen wird, sondern automatisch ausgeführt wird, wenn ein bestimmtes Ereignis auf einer Tabelle eintritt: das Einfügen, Ändern oder Löschen eines Datensatzes. Ein Trigger gehört zu genau einer Tabelle und hat drei Merkmale:

• Zeitpunkt: BEFORE (vor der Änderung, kann Werte noch anpassen oder die Änderung mit einem Fehler verhindern) oder AFTER (nach der Änderung, zum Beispiel für Folgeänderungen in anderen Tabellen).

• Ereignis: INSERT, UPDATE oder DELETE.

• Zeilen-Bezug: In MariaDB läuft ein Trigger immer FOR EACH ROW, also einmal für jeden betroffenen Datensatz. Mit NEW.spalte greift er auf die neuen Werte (bei INSERT und UPDATE), mit OLD.spalte auf die alten Werte (bei UPDATE und DELETE) zu.

Beispiel 1: Regel prüfen. Eine gesperrte Karte darf nicht belastet werden. Ein CHECK-Constraint kann das nicht prüfen, weil die Sperre in einer anderen Tabelle steht:

DELIMITER //
CREATE TRIGGER trg_buchung_sperre
BEFORE INSERT ON buchung
FOR EACH ROW
BEGIN
  IF NEW.betrag < 0 AND
     (SELECT gesperrt FROM karte WHERE karte_id = NEW.karte_id) = TRUE THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Gesperrte Karten dürfen nicht belastet werden';
  END IF;
END //
DELIMITER ;

Beispiel 2: Änderungen protokollieren. Jede Änderung eines Guthabens soll mit altem und neuem Wert nachvollziehbar sein:

CREATE TABLE guthaben_protokoll (
  protokoll_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  karte_id     INT UNSIGNED NOT NULL,
  alt          DECIMAL(6,2) NOT NULL,
  neu          DECIMAL(6,2) NOT NULL,
  geaendert_am DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  benutzer     VARCHAR(100) NOT NULL
);

CREATE TRIGGER trg_karte_protokoll
AFTER UPDATE ON karte
FOR EACH ROW
  INSERT INTO guthaben_protokoll (karte_id, alt, neu, benutzer)
  VALUES (OLD.karte_id, OLD.guthaben, NEW.guthaben, USER());

Besteht der Rumpf aus nur einer Anweisung, sind BEGIN … END und DELIMITER nicht nötig. Mit UPDATE karte SET gesperrt = TRUE … würde dieser Trigger allerdings auch eine Zeile schreiben, obwohl sich das Guthaben nicht geändert hat. Besser ist die Bedingung IF NEW.guthaben <> OLD.guthaben THEN … END IF;.

Typische Einsatzzwecke von Triggern:

• fachliche Regeln über mehrere Tabellen erzwingen (benutzerdefinierte Integrität),

• abgeleitete Werte aktuell halten (Lagerbestand, Anzahl freier Plätze, Summen),

• Änderungen protokollieren (Audit),

• Werte vor dem Speichern vervollständigen oder vereinheitlichen (SET NEW.email = LOWER(NEW.email); in einem BEFORE-Trigger).

Was Du über Trigger wissen solltest:

• Ein Trigger läuft in derselben Transaktion wie die auslösende Anweisung. Bricht er mit einem Fehler ab, wird auch die auslösende Anweisung zurückgenommen. Er darf selbst kein COMMIT oder ROLLBACK ausführen.

• Ein Trigger darf die eigene Tabelle nicht per UPDATE ändern (ERROR 1442: Can't update table … in stored function/trigger because it is already used by statement which invoked this stored function/trigger). Werte des gerade geänderten Datensatzes setzt man stattdessen in einem BEFORE-Trigger mit SET NEW.spalte = ….

• Trigger wirken nur für künftige Ereignisse. Daten, die schon vor dem Anlegen gespeichert waren, prüft oder korrigiert ein Trigger nicht.

• Trigger sind „unsichtbar“: Wer nur die INSERT-Anweisung sieht, weiß nicht, dass dabei noch andere Tabellen geändert werden. Ketten von Triggern, die weitere Trigger auslösen, sind schwer zu überblicken. Deshalb sparsam einsetzen und dokumentieren (SHOW TRIGGERS;).

• Ausgelöst wird nur durch INSERT, UPDATE und DELETE auf der Tabelle selbst; Änderungen durch ON DELETE CASCADE lösen in MariaDB keine Trigger der Kindtabelle aus.

Index: schneller finden

Sucht MariaDB alle Buchungen eines Tages, muss es ohne Hilfsstruktur jede Zeile der Tabelle buchung lesen (full table scan). Bei einigen Hundert Zeilen fällt das nicht auf, bei Millionen Buchungen schon. Ein Index ist eine zusätzliche, sortierte Zugriffsstruktur über eine oder mehrere Spalten – wie das Stichwortverzeichnis eines Buchs: Statt das ganze Buch zu lesen, schlägst Du den Begriff nach und springst direkt zur Seite. Spalten werden also indiziert, um das Suchen, Verknüpfen und Sortieren von Datensätzen zu beschleunigen, vor allem bei großen Datenmengen.

CREATE INDEX idx_buchung_zeitpunkt ON buchung (zeitpunkt);

-- profitiert davon:
SELECT * FROM buchung WHERE zeitpunkt >= '2026-10-19' AND zeitpunkt < '2026-10-20';

• Syntax: CREATE [UNIQUE] INDEX indexname ON tabelle (spalte [, spalte …]); – gleichwertig ALTER TABLE tabelle ADD INDEX indexname (spalte);, entfernt mit DROP INDEX indexname ON tabelle;.

• Für Primärschlüssel und UNIQUE-Spalten legt MariaDB automatisch einen Index an, für Fremdschlüsselspalten ebenfalls (sofern noch keiner existiert).

• InnoDB speichert Indizes als B-Baum. Sinnvoll sind sie für Spalten, die häufig in WHERE, JOIN … ON oder ORDER BY vorkommen und viele verschiedene Werte haben.

Indizes haben ihren Preis: Jeder Index braucht Speicherplatz und muss bei jedem INSERT, UPDATE und DELETE mitgepflegt werden; Schreibvorgänge werden dadurch langsamer. Spalten mit wenigen verschiedenen Werten (zum Beispiel gesperrt) und kleine Tabellen profitieren kaum. Ob ein Index tatsächlich genutzt wird, zeigt EXPLAIN SELECT …. Mehr zu zusammengesetzten Indizes steht im Artikel Tabellen anlegen mit der SQL-DDL.

Was gehört wohin? Constraint, Trigger, Stored Procedure, Index im Vergleich

Als Faustregel gilt: So einfach wie möglich. Was ein Constraint prüfen kann, gehört in einen Constraint. Ein Trigger kommt hinzu, wenn eine Regel immer gelten muss, egal welche Anwendung die Daten ändert. Eine Stored Procedure bündelt einen Ablauf, den Anwendungen gezielt aufrufen.

Datenbanken betreiben: Leistung, Sicherheit und Sicherung

Bisher ging es darum, was ein DBMS mit den Daten macht. Wer eine Datenbank auswählt, einrichtet und betreut, muss außerdem entscheiden, welches System leistungsfähig genug ist, wie die Daten geschützt und gesichert werden und was zu tun ist, wenn der Server plötzlich langsam reagiert.

Leistungsmerkmale relationaler DBMS vergleichen

Relationale DBMS beherrschen alle SQL, unterscheiden sich aber in ihrer Leistungsfähigkeit. Zwei Merkmale tauchen bei jedem Vergleich auf:

• Indexierung: Welche Indexarten das System anbietet (B-Baum, Volltext, räumliche Indizes, Indizes über mehrere Spalten) und wie gut sein Abfrageoptimierer sie nutzt, entscheidet darüber, wie schnell Datensätze bei großen Tabellen gefunden, verknüpft und sortiert werden.

• Sperrmechanismen (Locking): Sperren verhindern Konflikte, wenn mehrere Benutzer*innen gleichzeitig dieselben Daten ändern (siehe Mehrbenutzerbetrieb). Wichtig ist die Granularität: Sperrt das System nur die betroffenen Zeilen (row-level locking, z. B. InnoDB), können viele Transaktionen parallel arbeiten. Sperrt es ganze Tabellen (table-level locking, z. B. MyISAM), müssen alle anderen warten, sobald eine Zeile geändert wird.

Weitere Merkmale sind Transaktionen und Isolationsstufen, Replikation und Clusterbetrieb, Benutzer- und Rechteverwaltung, Werkzeuge für Sicherung und Überwachung, Speicherengines sowie Lizenz und Support.

Sicherheitsanforderungen und passende Maßnahmen

Für eine Datenbank mit personenbezogenen oder geschäftskritischen Daten lassen sich typische Anforderungen direkt in Maßnahmen übersetzen:

Wenn die Datenbank langsam wird

Melden Anwender*innen, dass die Datenbank „hängt“, liegt die Ursache oft direkt beim Datenbankserver. Typische Gründe:

Datensicherung mit einem Dump

Ein Dump (Abzug) ist eine Sicherung der Datenbank als Textdatei mit SQL-Anweisungen, aus der sich die Datenbank vollständig wiederherstellen lässt. Er kann enthalten:

• die Struktur: CREATE TABLE mit Spalten, Datentypen, Schlüsseln, Constraints und Indizes,

• die Daten: INSERT-Anweisungen für alle Datensätze,

• Views, Trigger, Stored Procedures und Ereignisse,

• auf Wunsch die Benutzerkonten und Rechte.

Ein Dump der Datenbank mensa mit Prozeduren und Triggern, konsistent ohne Sperren, und das Wiederherstellen in eine leere Datenbank sehen so aus:

mariadb-dump --single-transaction --routines --triggers mensa > mensa_2026-10-24.sql
mariadb mensa < mensa_2026-10-24.sql

--single-transaction liest alle Tabellen in einer Transaktion. Der Dump ist dadurch in sich konsistent, ohne dass der Betrieb angehalten werden muss – ein Anwendungsfall der Isolation. Benutzerkonten sichert mariadb-dump --system=users. Ein Dump ist eine Momentaufnahme: Was danach geändert wird, fehlt bis zur nächsten Sicherung (oder wird aus dem Binärlog nachgespielt). Wie oft gesichert und wie lange aufbewahrt wird, legt das Datensicherungskonzept fest (siehe Datensicherung und Archivierung).

Vom Datenmodell zur verfügbaren Datenbank

Liegt ein Datenmodell in der 3. Normalform vor, sind bis zur nutzbaren Datenbank typischerweise diese Schritte nötig:

1. geeignetes DBMS auswählen und installieren (bzw. Datenbank bei einem Anbieter bereitstellen),

2. Datenbank mit Zeichensatz und Kollation anlegen (siehe unten),

3. Tabellen mit Datentypen, Schlüsseln, Constraints und Indizes per SQL-DDL erstellen,

4. Daten importieren bzw. einfügen (Altdaten bereinigen und migrieren),

5. Implementierung testen: Schlüssel, Fremdschlüsselbeziehungen, Constraints, typische Abfragen,

6. Views, Stored Procedures und Trigger anlegen, soweit benötigt,

7. Benutzer und Rollen anlegen, Rechte vergeben und die Rechte testen,

8. Datensicherung einrichten und eine Wiederherstellung testen,

9. Anwendung anbinden und Datenbank freigeben, Überwachung einrichten.

Groß- und Kleinschreibung: die Kollation

Ob MariaDB beim Vergleichen und Sortieren von Zeichenketten zwischen Groß- und Kleinbuchstaben unterscheidet, legt die Kollation (collation) der Spalte, Tabelle oder Datenbank fest. Ihr Name verrät das Verhalten:

• …_ci (case insensitive), z. B. utf8mb4_general_ci oder utf8mb4_unicode_ci: „3EF7“ und „3ef7“ gelten als gleich.

• …_cs (case sensitive) bzw. …_bin (binär, vergleicht die Bytes): „3EF7“ und „3ef7“ sind verschieden.

Ist eine Zeichenkette case sensitive, wird also zwischen Groß- und Kleinschreibung unterschieden. Das ist wichtig für Codes und Kennungen, bei denen die Schreibweise Bedeutung hat (Gutscheincodes, Tokens, manche RFID- oder Schlüsselcodes). Bei Namen oder E-Mail-Adressen ist dagegen meist gewollt, dass „Jan@Example.org“ und „jan@example.org“ gleich behandelt werden – auch für einen UNIQUE-Constraint.

SELECT '3EF7' = '3ef7';                          -- 1 (wahr) bei einer _ci-Kollation
SELECT '3EF7' = '3ef7' COLLATE utf8mb4_bin;      -- 0 (falsch)

CREATE TABLE gutschein (
  code   VARCHAR(12) COLLATE utf8mb4_bin NOT NULL PRIMARY KEY,
  wert   DECIMAL(6,2) NOT NULL
);

Typische Fehler

• Mehrere zusammengehörige Änderungen ohne Transaktion ausführen (Autocommit) und sich wundern, dass nach einem Fehler „halbe“ Vorgänge in der Datenbank stehen.

• In einer Transaktion nach einem Fehler kein ROLLBACK senden – die vorherigen Änderungen werden dann mit dem nächsten COMMIT doch festgeschrieben.

• Werte im Programm berechnen und als Konstante zurückschreiben (SET guthaben = 8.30) statt SET guthaben = guthaben - 4.20 – Ursache für verlorene Updates.

• ACID-Eigenschaften verwechseln: Isolation betrifft gleichzeitige Transaktionen, Atomarität den Abbruch einer einzelnen, Dauerhaftigkeit den Zustand nach dem COMMIT.

• Die Fehlermeldung „Cannot delete or update a parent row“ für einen Syntaxfehler halten. Die Anweisung ist korrekt; es gibt noch abhängige Datensätze in der genannten Kindtabelle.

• Testdaten in der falschen Reihenfolge löschen (erst Eltern, dann Kinder) oder die Prüfung mit FOREIGN_KEY_CHECKS = 0 abschalten.

• Im Trigger die eigene Tabelle mit UPDATE ändern, statt in einem BEFORE-Trigger NEW.spalte zu setzen.

• Jede Spalte indizieren, „damit es schneller wird“ – Schreibvorgänge werden dadurch langsamer.

• Einen Dump für eine vollständige Sicherung halten, ohne ihn je wiederhergestellt zu haben – oder vergessen, dass Stored Procedures und Benutzerkonten nur mit den passenden Optionen enthalten sind.

• Codes mit Bedeutung der Groß- und Kleinschreibung in einer _ci-Spalte speichern: „AB12“ und „ab12“ gelten dann als gleich.

Leitfragen

1. Welche Aufgaben übernimmt ein DBMS über das Speichern von Daten hinaus? Antwort

Neben Datenhaltung und Operationen verwaltet ein DBMS den Katalog (Data Dictionary), stellt Benutzersichten bereit, sichert die Integrität, kontrolliert den Zugriff über Benutzer, Rollen und Rechte, verarbeitet Transaktionen, synchronisiert den Mehrbenutzerbetrieb und sorgt für Datensicherung und Wiederherstellung. In der Praxis kommen Abfrageoptimierung (z. B. mit Indizes) und Programmlogik in der Datenbank dazu; siehe Abschnitt „Was ein DBMS mehr kann als speichern“.

2. Was ist eine Transaktion, wie wird sie in SQL begonnen, abgeschlossen und abgebrochen, und was bedeutet Autocommit? Antwort

Eine Transaktion ist eine Folge von Datenbankoperationen, die als logische, unteilbare Einheit ganz oder gar nicht ausgeführt wird. Du beginnst sie mit START TRANSACTION (oder BEGIN), schließt sie mit COMMIT ab und brichst sie mit ROLLBACK ab. Im Autocommit-Modus, der Standardeinstellung von MariaDB, ist jede einzelne Anweisung eine eigene Transaktion und wird sofort festgeschrieben; siehe Abschnitt „Eine Transaktion bilden“ und Abschnitt „Autocommit: jede Anweisung ist eine eigene Transaktion“.

3. Was bedeuten Atomicity, Consistency, Isolation und Durability, und wie setzt ein DBMS sie um? Antwort

Atomicity: Eine Transaktion wird ganz oder gar nicht ausgeführt (umgesetzt mit ROLLBACK über das Undo Log). Consistency: Sie überführt die Datenbank von einem konsistenten in einen konsistenten Zustand (Constraints, Trigger, Anwendungslogik). Isolation: Gleichzeitige Transaktionen beeinflussen sich nicht (Sperren, MVCC, Isolationsstufen). Durability: Nach dem COMMIT bleiben Änderungen auch bei Ausfällen erhalten (Redo Log); siehe Abschnitt „Die ACID-Eigenschaften“.

4. Wie entsteht ein verlorenes Update, und mit welchen zwei Techniken verhinderst Du es? Wie entsteht ein Deadlock? Antwort

Ein verlorenes Update entsteht, wenn zwei Programme denselben Wert lesen, jeweils im Programm rechnen und nacheinander zurückschreiben – die zweite Änderung überschreibt die erste. Du verhinderst es, indem die Datenbank in einer einzigen Anweisung rechnet (SET guthaben = guthaben - 4.20 WHERE … AND guthaben >= 4.20) oder indem Du schon beim Lesen mit SELECT … FOR UPDATE sperrst. Ein Deadlock entsteht, wenn zwei Transaktionen je einen Datensatz gesperrt haben und gegenseitig auf den Datensatz der anderen warten; siehe Abschnitt „Das verlorene Update (Lost Update)“ und Abschnitt „Deadlock: zwei warten aufeinander“.

5. Was bedeutet referentielle Integrität? Welche Ursache haben die Fehlermeldungen beim Löschen eines Elterndatensatzes und beim Einfügen eines Kinddatensatzes, und in welcher Reihenfolge löschst Du verknüpfte Daten? Antwort

Referentielle Integrität heißt, dass jeder Fremdschlüsselwert auf einen vorhandenen Primärschlüsselwert verweist. Beim Löschen eines Elterndatensatzes meldet das DBMS einen Fehler, weil noch Kinddatensätze in der genannten Kindtabelle auf ihn verweisen; beim Einfügen eines Kinddatensatzes, weil der referenzierte Elterndatensatz nicht existiert. Bei RESTRICT löschst Du deshalb zuerst die Kinder, dann die Eltern; siehe Abschnitt „Referentielle Integrität im Betrieb“ und Abschnitt „Die richtige Löschreihenfolge“.

6. Wann wählst Du RESTRICT, CASCADE oder SET NULL? Antwort

RESTRICT wählst Du, wenn die Kinder eigenständig wichtig sind oder aufbewahrt werden müssen (Buchungen, Rechnungen), CASCADE, wenn sie ohne den Elterndatensatz sinnlos sind (Positionen einer Bestellung), und SET NULL, wenn die Beziehung optional ist und die Kinder erhalten bleiben sollen; siehe Abschnitt „Was beim Löschen passieren soll: ON DELETE“.

7. Was ist eine Stored Procedure, was ist ein Trigger, und worin unterscheiden sie sich? Antwort

Eine Stored Procedure ist eine im DBMS gespeicherte, benannte Folge von SQL-Anweisungen mit Parametern, die ausdrücklich mit CALL aufgerufen wird. Ein Trigger ist eine besondere gespeicherte Prozedur, die zu genau einer Tabelle gehört und automatisch bei INSERT, UPDATE oder DELETE (BEFORE oder AFTER, für jede Zeile) ausgeführt wird. Der Trigger läuft in der Transaktion der auslösenden Anweisung und darf selbst kein COMMIT ausführen; siehe Abschnitt „Stored Procedures: Abläufe in der Datenbank speichern“ und Abschnitt „Trigger: automatisch reagieren“.

8. Wozu werden Spalten indiziert, wie legst Du einen Index an, und was kostet er? Antwort

Ein Index ist eine sortierte Zugriffsstruktur, die das Suchen, Verknüpfen und Sortieren vor allem bei großen Datenmengen beschleunigt, ähnlich dem Stichwortverzeichnis eines Buchs. Du legst ihn mit CREATE INDEX indexname ON tabelle (spalte); an. Er kostet Speicherplatz und muss bei jedem INSERT, UPDATE und DELETE mitgepflegt werden, sodass Schreibvorgänge langsamer werden; siehe Abschnitt „Index: schneller finden“.

9. Welche Gründe kann es haben, wenn ein Datenbankserver plötzlich verzögert reagiert, und wie gehst Du der Ursache nach? Antwort

Häufige Gründe sind ungünstige Abfragen ohne passenden Index, starkes Datenwachstum (Daten passen nicht mehr in den Arbeitsspeicher, Datenträger voll), zu viele Verbindungen oder Zugriffe, lange Transaktionen mit Sperren sowie ausgelastete Ressourcen, etwa durch eine Sicherung während der Arbeitszeit. Hinweise liefern das Slow-Query-Log, EXPLAIN, SHOW PROCESSLIST und die Überwachung von CPU, Speicher und Datenträgern, siehe Abschnitt „Wenn die Datenbank langsam wird“.

10. Was enthält ein Datenbank-Dump, und was bedeutet „case sensitive“ bei Zeichenketten? Antwort

Ein Dump enthält SQL-Anweisungen für die Struktur (Tabellen, Schlüssel, Indizes), die Daten (INSERT) und auf Wunsch Views, Trigger, Stored Procedures sowie Benutzer und Rechte; mit mariadb-dump --single-transaction entsteht er konsistent im laufenden Betrieb. „Case sensitive“ heißt, dass zwischen Groß- und Kleinbuchstaben unterschieden wird („3EF7“ ≠ „3ef7“); in MariaDB legt das die Kollation fest (_ci unterscheidet nicht, _cs/_bin unterscheiden), siehe Abschnitt „Datensicherung mit einem Dump“ und Abschnitt „Groß- und Kleinschreibung: die Kollation“.

Links und weitere Informationen

• Übungsaufgaben zu Datenbankkonzepten

• Datenbankmanagementsysteme

• Tabellen anlegen mit der SQL-DDL und Constraints in der SQL-DDL

• SQL-DML: Datensätze löschen

• als Nachschlagewerk: MariaDB Knowledge Base: Transactions und SET TRANSACTION

• als Nachschlagewerk: MariaDB Knowledge Base: Stored Procedures und Triggers

• als Nachschlagewerk: MariaDB Knowledge Base: Foreign Keys und CREATE INDEX

• als Nachschlagewerk: MariaDB Knowledge Base: mariadb-dump und Character Sets and Collations

• zur Vertiefung: Percona-Blog: MariaDB’s Snapshot Isolation (zu innodb_snapshot_isolation ab MariaDB 11.8)

Hinweis zur Nachnutzung als Open Educational Resource (OER)

Dieser Artikel und seine Texte, Bilder, Grafiken, Code und sonstiger Inhalt sind - sofern nicht anders angegeben - lizenziert unter CC BY 4.0. Nennung gemäß TULLU-Regel bitte wie folgt: “Datenbankkonzepte: Transaktionen, ACID, Integrität, Trigger, Stored Procedures und Index” von oer-informatik.de (H. Stein), Lizenz: CC BY 4.0. Der Artikel wurde unter https://oer-informatik.de/datenbankkonzepte veröffentlicht, die Quelltexte sind in weiterverarbeitbarer Form verfügbar im Repository unter https://gitlab.com/oer-informatik/db-design/datenbankentwurf. Erstellt mit KI-Assistenz; von mir umfassend redaktionell überarbeitet, geprüft und freigegeben. Stand: 27.10.2026.

[Kommentare zum Artikel lesen, schreiben] / [Artikel teilen] / [gitlab-Issue zum Artikel schreiben]