MariaDB-Kurs / Isolation, Sperren und Deadlocks mit zwei Verbindungen vergleichen
Isolation bestimmt, welche gleichzeitigen Änderungen eine Transaktion lesen kann; Sperren koordinieren konkurrierende Operationen. Vergleichen Sie mit zwei getrennten Verbindungen und einer kontrollierten Reihenfolge REPEATABLE READ, READ COMMITTED, eine abgelaufene Sperrwartezeit und einen Deadlock als unterschiedliche Vorgänge.
Zwei unabhängige Verbindungen A und B vorbereiten
Öffnen Sie zwei Kommandozeilenclients zum selben Lernserver und zur Datenbank shopcourse. Die Anleitung zum MariaDB-Client erklärt die Verbindung. Prüfen Sie unterschiedliche Kennungen mit CONNECTION_ID. Beide Seiten in einen Client einzufügen, erzeugt keine Nebenläufigkeit. Verwenden Sie nur Übungsdaten und lesen Sie die Grenzen von START TRANSACTION, COMMIT und ROLLBACK.
Führen Sie den folgenden Block in A und B jeweils separat aus. Er sichert Isolation und Wartezeit jeder Sitzung zur späteren Wiederherstellung. Prüfen Sie den Server außerdem mit SELECT VERSION();. Die Änderungen gelten für SESSION, nicht GLOBAL.
SELECT CONNECTION_ID(),DATABASE(),@@SESSION.tx_isolation;
SET @saved_isolation=@@SESSION.tx_isolation;
SET @saved_wait=@@SESSION.innodb_lock_wait_timeout;
Erstellen Sie die folgende Tabelle nur in A. Prüfen Sie vorher, ob accounts_36_lab fehlt. Untersuchen Sie vorhandene Inhalte und sichern Sie benötigte Daten, bevor Sie die Tabelle als entbehrliches Übungsobjekt behandeln.
USE shopcourse;
CREATE TABLE accounts_36_lab (
account_id INT PRIMARY KEY,balance INT NOT NULL
) ENGINE=InnoDB;
INSERT INTO accounts_36_lab VALUES(1,1000),(2,2000);
| Fall | Beobachtete Regel | Abgrenzung |
|---|---|---|
| Gewöhnliches SELECT bei REPEATABLE READ | Konsistente Sicht auf einen Snapshot innerhalb der Transaktion | Unterscheidet sich vom sperrenden Lesen |
| Gewöhnliches SELECT bei READ COMMITTED | Sichtbare bestätigte Version für jede Anweisung | Wiederholte Leseergebnisse können sich ändern |
| Sperrwartezeit | Warten auf das Ende einer inkompatiblen Operation | Zeitüberschreitung bedeutet keinen Deadlock |
REPEATABLE READ in der Folge A → B → A vergleichen
Beginnen Sie mit dem vorbereiteten Saldo 1000 für Kennung 1. Beachten Sie die Verbindungsangaben: B handelt erst nach der ersten abgeschlossenen Leseanweisung von A. Ein Snapshot ist die konsistente Datensicht für die Lesezugriffe der Transaktion.
A-1: Snapshot herstellen
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT balance FROM accounts_36_lab WHERE account_id=1;
B-1: Änderung bestätigen
START TRANSACTION;
UPDATE accounts_36_lab SET balance=1100 WHERE account_id=1;
COMMIT;
A-2: Innerhalb derselben Transaktion erneut lesen
SELECT balance FROM accounts_36_lab WHERE account_id=1;
A-1 und A-2 liefern 1000. Das zweite gewöhnliche SELECT von A erhält seinen Snapshot auch nach der Bestätigung von 1100 durch B. Verglichen wird nicht sperrendes SELECT. FOR UPDATE an seiner Stelle wäre ein anderer Versuch.
A-3: Transaktion beenden und erneut lesen
ROLLBACK;
SELECT balance FROM accounts_36_lab WHERE account_id=1;
Die Abfrage nach ROLLBACK liefert 1100. Die offizielle Referenz zu SET TRANSACTION erläutert Geltungsbereiche der Isolation und den Unterschied zwischen gewöhnlichen und sperrenden Zugriffen.
Nach dem Zurücksetzen mit READ COMMITTED wiederholen
B-0: Saldo des vorherigen Versuchs zurücksetzen
ROLLBACK;
START TRANSACTION;
UPDATE accounts_36_lab SET balance=1000 WHERE account_id=1;
COMMIT;
A-1: Erstes Lesen bei READ COMMITTED
ROLLBACK;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT balance FROM accounts_36_lab WHERE account_id=1;
B-1: Saldo 1100 bestätigen
START TRANSACTION;
UPDATE accounts_36_lab SET balance=1100 WHERE account_id=1;
COMMIT;
A-2: Zweites Lesen und Abschluss
SELECT balance FROM accounts_36_lab WHERE account_id=1;
ROLLBACK;
A liefert zuerst 1000 und danach 1100. Jedes gewöhnliche SELECT verwendet die für diese Anweisung sichtbaren bestätigten Daten. Das allein entscheidet nicht über die Isolation einer Anwendung. Prüfen Sie auch deren Schreiboperationen und erforderliche Konsistenzregeln.
Gehaltene Sperre und Wartezeit unterscheiden
A-1: Sperre auf Zeile 1 halten
START TRANSACTION;
SELECT account_id,balance FROM accounts_36_lab
WHERE account_id=1 FOR UPDATE;
B-1: Dieselbe Zeile ändern wollen
SET SESSION innodb_lock_wait_timeout=3;
START TRANSACTION;
UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=1;
Lassen Sie die Transaktion von A offen. B soll nach ungefähr drei Sekunden einen Sperrwartezeitfehler erhalten, üblicherweise 1205. Der Wert begrenzt die Wartezeit dieser Sitzung und verspricht keine exakte Laufzeit. Nehmen Sie nicht an, dass der Fehler die gesamte Transaktion von B beendet hat.
B-2: Versuch zurücknehmen und Wartezeit wiederherstellen
ROLLBACK;
SET SESSION innodb_lock_wait_timeout=@saved_wait;
A-2: Sperre freigeben
ROLLBACK;
Die Sperre entstand innerhalb einer ausdrücklichen Transaktion. Erhalten Sie diese Grenze für den Versuch. Die offizielle Referenz zu FOR UPDATE erklärt die Transaktionsbedingungen bei InnoDB. Das Warten auf eine einzelne Abhängigkeit unterscheidet sich vom Kreis im folgenden Fall.
Einen Deadlock mit vier verschränkten Schritten erzeugen
Bei einem Deadlock warten Transaktionen in einem Kreis aufeinander. Prüfen Sie, dass beide vorherigen Transaktionen beendet sind. Wechseln Sie in der Reihenfolge A-1 → B-1 → A-2 → B-2 zwischen den Fenstern. Führen Sie die Schritte nicht als ein sequenzielles Skript aus.
| Schritt | Verbindung | Aktion |
|---|---|---|
| 1 | A | Zeile 1 ändern und sperren |
| 2 | B | Zeile 2 ändern und sperren |
| 3 | A | Zeile 2 anfordern und warten |
| 4 | B | Zeile 1 anfordern und Kreis schließen |
A-1: Nur die erste Zeile sperren
START TRANSACTION;
UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=1;
B-1: Die andere Zeile sperren
START TRANSACTION;
UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=2;
A-2: Die von B gehaltene Zeile anfordern
UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=2;
A wartet. Wechseln Sie zu B und führen Sie die folgende Anweisung aus, ohne den Abschluss von A abzuwarten.
B-2: Die von A gehaltene Zeile anfordern
UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=1;
Bei aktiver InnoDB-Deadlock-Erkennung kann eine Transaktion Deadlock found erhalten, üblicherweise Fehler 1213. Die ausgewählte Opferverbindung steht nicht fest. Die andere Operation kann nach Freigabe der Sperren fortfahren. Führen Sie ROLLBACK; in beiden Fenstern separat aus, um den Versuch ohne bleibende Erhöhungen zu beenden.
Eine Anwendung muss diesen Fall von einer Zeitüberschreitung unterscheiden und begrenzte Wiederholungen der gesamten Transaktion mit Schutz vor doppelten Operationen vorsehen. Nur eine Anweisung zu wiederholen oder unbegrenzt erneut zu versuchen, definiert keine korrekte Wiederherstellung. Die offizielle Referenz zu Fehler 1213 beschreibt Deadlocks und den Neustart der Transaktion.
Sitzungen wiederherstellen und einheitliche Sperrreihenfolge verwenden
Mehrere Zeilen in einheitlicher Schlüsselreihenfolge zu sperren und Transaktionen kurz zu halten, verringert kreisförmiges Warten. Breite Bedingungen oder fehlende brauchbare Indizes können den Sperrbereich vergrößern. Untersuchen Sie Abfragen, Protokolle und Operationsfolge. Ein Deadlock ist von einem allgemeinen Verbindungsfehler zu unterscheiden.
ROLLBACK;
SET SESSION tx_isolation=@saved_isolation;
SET SESSION innodb_lock_wait_timeout=@saved_wait;
SELECT @@SESSION.tx_isolation,@@SESSION.innodb_lock_wait_timeout;
Führen Sie diesen Block in A und B aus und vergleichen Sie mit den jeweils anfangs gesicherten Werten. Benutzervariablen verschwinden beim Schließen einer Verbindung. Prüfen Sie bei einer neuen Verbindung die Einstellungen erneut, statt vorhandene alte Variablen anzunehmen.
Entfernen Sie nach dem Ende beider Transaktionen die folgende Tabelle nur, wenn sie weiterhin ein entbehrliches Übungsobjekt ist. DROP TABLE entfernt Struktur und Datensätze und wird durch ein gewöhnliches ROLLBACK nicht rückgängig gemacht.
DROP TABLE shopcourse.accounts_36_lab;




