Detaillierte Erklärung, wie MySQL (InnoDB) mit Deadlocks umgeht

1. Was ist ein Deadlock?

Die offizielle Definition lautet wie folgt: Zwei Transaktionen halten von der anderen Partei benötigte Sperren und warten darauf, dass die andere Partei sie freigibt, und keine der Parteien gibt ihre eigenen Sperren frei.

Das ist so, als ob Sie und die andere Partei eine Geisel hätten und Sie beide über den Austausch von Personen verhandeln würden. Sie bitten die andere Seite, den Player freizugeben, und die andere Seite bittet Sie, den Player freizugeben.

2. Warum kommt es zu einem Deadlock?

Angesichts dessen haben Sie möglicherweise eine solche Frage. Transaktionen unterscheiden sich von Verhandlungen. Warum können Transaktionen die Sperre nicht sofort nach ihrer Verwendung freigeben? Müssen Sie die Sperre nach Abschluss des Vorgangs gedrückt halten? Hierbei handelt es sich um die Parallelitätskontrolle von MySQL.

Es gibt zwei Möglichkeiten der Parallelitätskontrolle in MySQL, eine ist MVCC und die andere ist das Zwei-Phasen-Sperrprotokoll. Warum brauchen wir also eine Parallelitätskontrolle? Dies liegt daran, dass bei der gleichzeitigen Ausführung von MySQL durch mehrere Benutzer zur Verbesserung der Parallelitätsleistung die Anforderungen mehrerer Benutzer seriell ausgeführt werden müssen (serialisierbare Planung). Auf die konkrete Parallelitätskontrolle wird hier nicht näher eingegangen. Lassen Sie uns das Zweiphasen-Sperrprotokoll weiter ausführlich besprechen.

Zwei-Phasen-Verriegelungsprotokoll (2PL)

Offizielle Definition:

Das zweiphasige Sperrprotokoll bedeutet, dass alle Transaktionen Daten in zwei Phasen sperren und entsperren müssen. Bevor die Transaktion Daten liest oder schreibt, muss sie zunächst eine Sperre für die Daten erhalten. Nach dem Aufheben einer Sperre kann die Transaktion keine weiteren Sperren mehr beantragen oder erhalten.

Entsprechend MySQL ist es in zwei Phasen unterteilt:

1. Erweiterungsphase (nach Beginn der Transaktion und vor dem Commit): Sperren erwerben
2. Kontraktionsphase (nach dem Commit): Sperren freigeben

Das heißt, nur durch Befolgen des zweistufigen Sperrprotokolls kann eine serialisierbare Planung erreicht werden.

Das Zwei-Phasen-Sperrprotokoll erfordert jedoch nicht, dass eine Transaktion alle Daten, die sie benötigt, gleichzeitig sperrt, und es gibt keine Reihenfolgeanforderung in der Sperrphase, sodass diese Methode der Parallelitätskontrolle zu einem Deadlock führt.

3. Wie geht MySQL mit Deadlocks um?

MySQL verfügt über zwei Methoden zur Behandlung von Deadlocks:

1. Warten Sie, bis das Timeout abgelaufen ist (innodb_lock_wait_timeout=50s).
2. Starten Sie die Deadlock-Erkennung, setzen Sie eine Transaktion aktiv zurück und lassen Sie die weitere Ausführung anderer Transaktionen zu (innodb_deadlock_detect=on).

Aus Leistungsgründen wird zur Behandlung von Deadlocks im Allgemeinen die Deadlock-Erkennung verwendet.

Deadlock-Erkennung

Das Prinzip der Deadlock-Erkennung besteht darin, einen gerichteten Graphen mit Transaktionen als Knoten und Sperren als Kanten zu konstruieren und zu bestimmen, ob im gerichteten Graphen ein Zyklus vorliegt. Wenn ein Zyklus vorliegt, liegt ein Deadlock vor.

Rollback

Nachdem ein Deadlock erkannt wurde, wird basierend auf dem Feld trx_weight in der Tabelle INFORMATION_SCHEMA.INNODB_TRX die Transaktion mit der geringsten Anzahl eingefügter, aktualisierter oder gelöschter Zeilen zum Rollback ausgewählt.

4. So vermeiden Sie Deadlocks

Sammeln Sie Deadlock-Informationen:

1. Verwenden Sie den Befehl SHOW ENGINE INNODB STATUS, um die Ursache des Deadlocks anzuzeigen.
2. Während der Debugphase wird innodb_print_all_deadlocks aktiviert, um alle Deadlock-Protokolle zu sammeln.

Reduzieren Sie Deadlocks:

1. Verwenden Sie Transaktionen, sperren Sie keine Tabellen.
2. Stellen Sie sicher, dass keine langen Transaktionen stattfinden.
3. Führen Sie Transaktionen sofort nach dem Vorgang aus, insbesondere in der interaktiven Befehlszeile.
4. Wenn Sie (SELECT ... FOR UPDATE oder SELECT ... LOCK IN SHARE MODE) verwenden, versuchen Sie, die Isolationsstufe zu verringern.
5. Achten Sie beim Ändern mehrerer Tabellen oder Zeilen auf die gleiche Reihenfolge.
6. Durch das Erstellen eines Indexes können weniger Sperren entstehen.
7. Verwenden Sie am besten nicht (SELECT … FOR UPDATE oder SELECT … LOCK IN SHARE MODE).
8. Wenn das Problem durch die oben genannten Schritte nicht gelöst werden kann, versuchen Sie, mehrere Tabellen mit den Sperrtabellen t1, t2 und t3 zu sperren.

Oben finden Sie eine ausführliche Erklärung, wie MySQL (InnoDB) mit Deadlocks umgeht. Ich hoffe, dass es Ihnen weiterhilft. Wenn Sie Fragen haben, hinterlassen Sie mir bitte eine Nachricht und ich werde Ihnen rechtzeitig antworten. Ich möchte auch allen für ihre Unterstützung der Website 123WORDPRESS.COM danken!