MySQL Online-Übung zur Deadlock-Analyse

Vorwort

Ich glaube, dass jeder beim Erlernen von MySQL ein einfaches Verständnis des Sperrmechanismus von MySQL hat. Da es Sperren gibt, ist das Deadlock-Problem unvermeidlich. Tatsächlich treten bei MySQL in den meisten Szenarien keine Deadlock-Probleme auf (z. B. wenn die Parallelität nicht hoch ist und das SQL nicht zu schlecht geschrieben ist). In Geschäftsszenarien mit hoher Parallelität können jedoch Deadlocks auftreten, wenn Sie nicht vorsichtig sind. Diese Deadlocks sind schwieriger zu analysieren.

Als ich vor einiger Zeit ein Praktikum in einem Unternehmen machte, stieß ich auf eine ziemlich seltsame Sackgasse. Ich hatte vorher keine Zeit, sie richtig zu lösen. Vor Kurzem hatte ich etwas Zeit, sie zu reproduzieren, sodass ich etwas Erfahrung sammeln konnte.

Geschäftsszenario

Lassen Sie mich kurz auf den geschäftlichen Hintergrund eingehen. Das Unternehmen ist im Bereich E-Commerce-Live-Streaming tätig und ich bin für das Ankergeschäft verantwortlich. Dieser Deadlock tritt auf, wenn der Anker die Produktinformationen im Hintergrund aktualisiert.

Eines unserer Produkte hat zwei zugeordnete IDs. Es ist unmöglich, ein eindeutiges Produkt anhand einer der IDs zu identifizieren (d. h. die Beziehung zwischen der ID und dem Produkt ist eins-zu-viele). Nur durch die gleichzeitige Abfrage beider IDs kann ein Produkt identifiziert werden. Daher müssen Sie beim Aktualisieren von Produktinformationen gleichzeitig zwei IDs in der Where-Bedingung angeben. Nachfolgend sehen Sie die Struktur des Deadlock-SQL (anonym):

UPDATE test_table SET `name`="zhangsan" WHERE class_id = 10 AND teacher_id = 8;

Dieses SQL ist sehr einfach. Es aktualisiert ein Feld basierend auf zwei gleichen Bedingungen.

Ich frage mich, ob Sie verwirrt sein werden, wenn Sie dieses SQL sehen. Nach gesundem Menschenverstand kommt es wahrscheinlich nur dann zu einem Deadlock, wenn mehrere SQLs in einer Transaktion vorhanden sind. Wie könnte es bei diesem einen SQL zu einem Deadlock kommen?

Ja, ich hatte damals dieselben Zweifel und vermutete sogar, dass das Alarmsystem etwas Falsches meldete (was sich am Ende als falsch herausstellte...), ich war damals wirklich verwirrt. Und aufgrund der Datenbankberechtigungen konnte ich das Deadlock-Protokoll nicht sehen. Ich musste fast Feierabend machen und es wäre zu mühsam gewesen, den DBA zu finden, also habe ich einfach die Suchmaschine durchsucht ... (Schlüsselwörter: Update Deadlock Single SQL) und schließlich herausgefunden, dass es durch die Indexzusammenführungsoptimierung von MySQL, nämlich Index Merge, verursacht wurde. Im Folgenden wird das Deadlock-Szenario ausführlich erklärt und reproduziert.

Indexzusammenführung

Index Merge ist eine in MySQL 5.0 eingeführte Optimierungsfunktion, die hauptsächlich zur Optimierung der Situation verwendet wird, in der eine SQL-Anweisung mehrere Indizes verwendet.

Schauen wir uns jetzt das SQL an und gehen dabei davon aus, dass class_id und teacher_id zwei gemeinsame Indizes sind:

UPDATE test_table SET `name`="zhangsan" WHERE class_id = 10 AND teacher_id = 8;

Wenn keine Index Merge-Optimierung vorhanden ist, lauten die Schritte für MySQL zum Abfragen von Daten wie folgt:

• Basierend auf class_id oder teacher_id (der Optimierer bestimmt anhand der tatsächlichen Datensituation, welcher Index verwendet werden soll, und es wird davon ausgegangen, dass hier der class_id Index verwendet wird) wird die Primärschlüssel-ID der entsprechenden Daten im Sekundärindex abgefragt
• Führen Sie eine Back-Index-Abfrage (d. h. eine Abfrage des Clustered-Index) basierend auf der abgefragten Primärschlüssel-ID durch, um die entsprechende Datenzeile zu erhalten
• Holen Sie sich teacher_id aus der Datenzeile und prüfen Sie, ob es gleich 8 ist. Wenn es die Bedingung erfüllt, geben Sie zurück

Aus diesem Prozess ist nicht schwer zu erkennen, dass MySQL nur einen Index verwendet. Der Grund, warum nicht mehrere Indizes verwendet werden, ist einfach, dass sich mehrere Indizes auf mehreren Bäumen befinden und ihre Verwendung erzwungen wird, die Leistung beeinträchtigt.

Werfen wir einen Blick auf die Schritte der MySQL-Abfrage von Daten nach der Einführung der Index Merge-Optimierung:

• Fragen Sie den entsprechenden Primärschlüssel basierend auf class_id ab und fragen Sie dann die entsprechende Datenzeile basierend auf dem Primärschlüssel ab (aufgezeichnet als Ergebnissatz A).
• Fragen Sie den entsprechenden Primärschlüssel basierend auf teacher_id ab und fragen Sie dann die entsprechende Datenzeile basierend auf dem Primärschlüssel ab (aufgezeichnet als Ergebnissatz B).
• Führen Sie eine Schnittmengenoperation für Ergebnissatz A und Ergebnissatz B durch, um den endgültigen Ergebnissatz zu erhalten, der die Bedingungen erfüllt

Hier ist zu sehen, dass MySQL mit Index Merge eine SQL-Anweisung in zwei Abfrageschritte aufteilt, wobei jeweils zwei Indizes verwendet werden, und dann Schnittmengenoperationen verwendet, um die Leistung zu optimieren.

Deadlock-Analyse

Nachdem wir die Schritte der Indexzusammenführung analysiert haben, wollen wir noch einmal darüber nachdenken, warum es zu Deadlocks kommt.

Denken Sie daran, dass Index Merge eine SQL-Abfrage in zwei Schritte aufteilt? Hier entsteht das Problem. Wir wissen, dass UPDATE Anweisung eine exklusive Sperre auf Zeilenebene hinzufügt. Bevor wir die Sperrschritte analysieren, gehen wir davon aus, dass eine Datentabelle wie folgt vorliegt:

Die Daten in der obigen Tabelle erfüllen die am Anfang unseres Artikels genannten Merkmale. Es ist unmöglich, ein Datenelement anhand der einzelnen Felder class_id und teacher_id eindeutig zu identifizieren. Nur durch die Kombination der beiden Felder kann ein Datenelement identifiziert werden, und class_id und teacher_id werden jeweils als zwei gemeinsame Indizes festgelegt.

Angenommen, die folgenden beiden SQL-Anweisungen werden gleichzeitig ausgeführt und ihre Parameter sind völlig unterschiedlich. Die Intuition sagt uns, dass es nicht zu Deadlocks kommen sollte, aber die Intuition täuscht uns oft:

// Thread A führt UPDATE test_table SET `name`="zhangsan" WHERE class_id = 2 AND teacher_id = 1; aus.

// Thread B führt UPDATE test_table SET `name`="zhangsan" WHERE class_id = 1 AND teacher_id = 2; aus.

Wenn dann im Rahmen der Indexzusammenführungsoptimierung das obige SQL gleichzeitig ausgeführt wird, lauten die Sperrschritte von MySQL wie folgt:

Schließlich warten die beiden Transaktionen aufeinander, was zu einem Deadlock führt.

Lösung

Da dieser Deadlock im Wesentlichen durch die Index Merge-Optimierung verursacht wird, müssen Sie zur Lösung des Deadlock-Problems in diesem Szenario lediglich verhindern, dass MySQL die Index Merge-Optimierung durchführt.

Lösung 1

Teilen Sie eine SQL-Anweisung manuell in mehrere SQL-Anweisungen auf und führen Sie Schnittmengenoperationen auf der logischen Ebene durch, um das dumme Optimierungsverhalten von MySQL zu verhindern. Hier können wir beispielsweise zuerst den entsprechenden Primärschlüssel basierend auf class_id abfragen, dann den entsprechenden Primärschlüssel basierend auf teacher_id abfragen und schließlich die Daten basierend auf dem Primärschlüssel nach der Schnittmenge abfragen.

Lösung 2

Erstellen Sie einen gemeinsamen Index. Sie können beispielsweise einen gemeinsamen Index class_id und teacher_id erstellen. MySQL verwendet dann keine Indexzusammenführung.

Option 3

Um einen einzelnen Index zu erzwingen, fügen Sie nach dem Tabellennamen for index(class_id) hinzu, um anzugeben, dass die Anweisung nur den class_id-Index verwendet.

Option 4

Deaktivieren Sie die Index Merge-Optimierung:

• Zum dauerhaften Deaktivieren: SET [GLOBAL|SESSION] optimizer_switch='index_merge=off';
• Vorübergehende Schließung: UPDATE /*+ NO_INDEX_MERGE(test_table) */ test_table SET name ="zhangsan" WHERE class_id = 10 AND teacher_id = 8;

Szenenwiedergabe

Datenaufbereitung

Um das Testen zu erleichtern, finden Sie hier ein SQL-Skript, das mit Navicat importiert werden kann, um die erforderlichen Testdaten zu erhalten:

Download-Adresse: https://cdn.juzibiji.top/file/index_merge_student.sql

Nach dem Import erhalten wir 10.000 Testdaten im folgenden Format:

Testcode

Aus Platzgründen wird hier nur der Gist-Code-Link angegeben: https://gist.github.com/juzi214032/17c0f7a51bd8d1c0ab39fa203f930c60

Der obige Code startet hauptsächlich 100 Threads, um unsere SQL-Anweisungen zur Datenänderung auszuführen und Online-Parallelität zu simulieren. Nach einigen Sekunden wird der folgende Fehler angezeigt:

com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Beim Versuch, eine Sperre zu erhalten, wurde ein Deadlock festgestellt. Versuchen Sie, die Transaktion neu zu starten.

Dies bedeutet, dass eine Deadlock-Ausnahme aufgetreten ist.

Deadlock-Analyse

Wir haben mit dem obigen Code einen Deadlock erstellt. Als Nächstes rufen wir MySQL auf, um das Deadlock-Protokoll anzuzeigen. Führen Sie den folgenden Befehl in MySQL aus, um das Deadlock-Protokoll anzuzeigen:

ENGINE-INNODB-STATUS ANZEIGEN; 

Im Protokoll finden wir die Zeile LATEST DETECTED DEADLOCK , das ist der letzte Deadlock, den wir erzeugt haben. Als nächstes beginnen wir mit der Analyse.

Aus Zeile 29 können wir ersehen, dass das von Transaktion 1 ausgeführte SQL die Bedingungen class_id = 6 und teacher_id = 16 hat. Es hält derzeit eine Zeilensperre. Die Zeilen 34 bis 39 sind die Daten dieser Zeile. Zeile 34 ist die hexadezimale Darstellung des Primärschlüssels, der nach Umwandlung in Dezimalzahlen 1616 ergibt. Schauen Sie sich Zeile 45 an. Bei den Daten, die darauf warten, gesperrt zu werden, handelt es sich um die Daten mit der Primärschlüssel-ID 1517.

Als nächstes verwenden wir dieselbe Methode, um Transaktion 2 zu analysieren. Wir können sehen, dass Transaktion 2 drei Sperren hält, nämlich die Datenzeilen mit den Primärschlüssel-IDs 1317, 1417 und 1517, und auf 1616 wartet.

An diesem Punkt haben wir festgestellt, dass Transaktion 1 1616 hält und auf 1517 wartet, und Transaktion 2 1517 hält und auf 1616 wartet, sodass ein Deadlock entsteht. Zu diesem Zeitpunkt besteht die Verarbeitungsmethode von MySQL darin, die Transaktion mit den wenigsten Sperren zurückzusetzen, und JDBC löst die oben erwähnte Rollback-Ausnahme MySQLTransactionRollbackException aus.

Zusammenfassen

Dieser Deadlock ist tatsächlich sehr schwer zu beheben. Wenn Sie MySQLs Index Merge nicht kennen, wissen Sie bei der Fehlerbehebung nicht, was zu tun ist, da Sie nur eine sehr einfache SQL-Anweisung vor sich haben. Selbst wenn Sie sich das Deadlock-Protokoll ansehen, verstehen Sie es immer noch nicht.

Daher geht es bei der Lösung dieser Art von Problemen eher darum, Ihr Wissen und Ihre Erfahrung zu testen. Wenn Sie darauf stoßen, seien Sie beim Schreiben von SQL in Zukunft einfach aufmerksamer!

Dies ist das Ende dieses Artikels über die praktische Online-Deadlock-Analyse von MySQL. Weitere relevante Inhalte zur Online-Deadlock-Analyse von MySQL finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den verwandten Artikeln weiter unten. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!