Eine detaillierte Diskussion über MySQL-Deadlocks und -Logs

Vor kurzem sind mehrere Datenanomalien in MySQL online aufgetreten, die alle am frühen Morgen auftraten. Da es sich bei dem Geschäftsszenario um eine typische Data Warehouse-Anwendung handelt, ist der Druck tagsüber geringer und kann nicht reproduziert werden. Einige der Anomalien waren sogar ziemlich merkwürdig und die Analyse ihrer Grundursache erforderte einen großen Aufwand. Wie können wir also MySQL-Probleme schnell online lokalisieren und Ausnahmen im tatsächlichen Geschäftsbetrieb beheben? Nachfolgend werde ich anhand von zwei echten Fällen relevante Erfahrungen und Methoden weitergeben.

Fall 1: Teilweise Datenaktualisierung fehlgeschlagen

Eines Tages berichtete ein Kanalkollege, dass die Daten einiger Kanäle in einem bestimmten Bericht 0 waren, die Daten der meisten Kanäle jedoch normal waren. Diese Daten werden routinemäßig jeden Morgen von einem Statistikprogramm aktualisiert. Logischerweise ist entweder alles normal oder alles schlägt fehl. Was ist also der Grund für die Anomalie einzelner Datenpunkte?

Als Erstes fällt uns ein, das Statistikaufgabenprotokoll zu überprüfen. Nachdem wir das vom Statistikprogramm gedruckte Protokoll gelesen hatten, fanden wir jedoch keine abnormalen Beschreibungen wie SQL-Updatefehler. Was ist also zu diesem Zeitpunkt mit der Datenbank passiert? Bevor ich das MySQL-Serverprotokoll ansah, überprüfte ich regelmäßig den Datenbankstatus:

Ich habe gerade am frühen Morgen diesen Update-Deadlock gesehen:

Aus Platzgründen habe ich hier viel Kontext weggelassen. Aus diesem Protokoll können Sie ersehen, dass TRANSAKTION 1 und TRANSAKTION 2 jeweils eine bestimmte Anzahl von Zeilensperren halten und dann auf die Sperren der jeweils anderen warten. Schließlich erkennt MySQL den Deadlock und entscheidet sich, TRANSAKTION 1 zurückzusetzen: Innodbs aktuelle Methode zum Umgang mit Deadlocks besteht darin, die Transaktion zurückzusetzen, die die wenigsten exklusiven Sperren auf Zeilenebene hält.

Hier stellen sich drei Fragen:

1. Sperrt die Zeilensperre von InnoDB nicht nur eine Zeile?

Da es sich bei dieser Tabelle um eine InnoDB-Engine handelt, unterstützt InnoDB Zeilensperren und Tabellensperren. InnoDB-Zeilensperren werden implementiert, indem die Indexelemente im Index gesperrt werden. Dies unterscheidet sich von MySQL und Oracle, die dies implementieren, indem die entsprechenden Datenzeilen im Datenblock gesperrt werden. Die Zeilensperren-Implementierungsfunktion von InnoDB bedeutet, dass InnoDB Zeilensperren nur dann verwendet, wenn Daten über Indexbedingungen abgerufen werden. Andernfalls verwendet InnoDB Tabellensperren und sperrt alle gescannten Zeilen! In tatsächlichen Anwendungen sollte dieser Funktion der InnoDB-Zeilensperren besondere Aufmerksamkeit gewidmet werden, da sonst eine große Anzahl von Sperrkonflikten auftreten und somit die Parallelitätsleistung beeinträchtigt werden kann. Da MySQL-Zeilensperren für Indizes und nicht für Datensätze gesperrt sind, treten Sperrkonflikte auf, wenn auf Datensätze in unterschiedlichen Zeilen zugegriffen wird, aber derselbe Indexschlüssel verwendet wird. Wenn wir Daten unter Verwendung von Bereichsbedingungen statt Gleichheitsbedingungen abrufen und gemeinsame oder exklusive Sperren anfordern, sperrt InnoDB die Indexelemente vorhandener Datensätze, die die Bedingungen erfüllen; darüber hinaus sperren Lückensperren auch mehrere Zeilen. Neben der Verwendung von Lückensperren beim Sperren durch Bereichsbedingungen verwendet InnoDB auch Lückensperren, wenn eine Gleichheitsbedingung verwendet wird, um eine Sperre für einen nicht vorhandenen Datensatz anzufordern!

Nachdem wir das gesagt haben, schauen wir uns nun den Index unserer Geschäftstabelle an:

Es ist ersichtlich, dass der Index dieser Tabelle äußerst unvernünftig ist: Es gibt 3 Indizes, aber das Update nutzt die Indizes nicht vollständig, was dazu führt, dass das Update die Indizes nicht genau nutzt und mehrere Datenzeilen gesperrt werden müssen, was zu einem Deadlock führt.

Wenn wir das Prinzip kennen, können wir sorgfältig einen kombinierten Index mit vier Feldern erstellen, damit die Aktualisierung genau über den InnoDB-Index abläuft. Tatsächlich ist das Deadlock-Problem gelöst, nachdem wir den Index aktualisiert haben.

Hinweis: Innodb druckt nicht nur die Transaktionen und die von den Transaktionen gehaltenen und erwarteten Sperren aus, sondern auch die Datensätze selbst. Leider kann dies die von Innodb für die Ausgabeergebnisse reservierte Länge überschreiten (es kann nur 1 MB Inhalt gedruckt werden und nur die aktuellsten Deadlock-Informationen können beibehalten werden). Wenn Sie die vollständige Ausgabe nicht sehen können, können Sie die Tabelle innodb_monitor oder innodb_lock_monitor unter einer beliebigen Bibliothek erstellen, sodass die Innodb-Statusinformationen vollständig sind und alle 15 Sekunden im Fehlerprotokoll aufgezeichnet werden. Beispiel: create table innodb_monitor(a int)engine=innodb;. Wenn Sie das Fehlerprotokoll nicht aufzeichnen müssen, löschen Sie einfach die Tabelle.

2. Warum schlagen beim Rollback nur einige Update-Anweisungen fehl?

Warum schlagen beim Zurücksetzen nur einige Aktualisierungsanweisungen fehl, anstatt dass alle Aktualisierungen in der gesamten Transaktion fehlschlagen?

Dies liegt daran, dass unser InnoDB standardmäßig automatisch übermittelt wird:

Bei mehreren Update- oder Insert-Anweisungen führt InnoDB nach der Ausführung jeder SQL-Anweisung sofort ein Commit aus, um die Änderungen dauerhaft zu speichern, und gibt die Sperren frei. Aus diesem Grund schlagen in diesem Beispiel nur wenige Anweisungen fehl, nachdem der Deadlock die Transaktion zurückgesetzt hat.

Es ist zu beachten, dass es normalerweise eine andere Situation gibt, die ebenfalls zu einem Rollback einiger Anweisungen führen kann. Achten Sie daher besonders darauf. Es gibt einen Parameter in Innodb namens: innodb_rollback_on_timeout

Das offizielle Handbuch beschreibt es folgendermaßen:

In MySQL 5.1 setzt InnoDB bei einem Transaktionstimeout standardmäßig nur die letzte Anweisung zurück. Wenn –innodb_rollback_on_timeout angegeben ist, führt ein Transaktionstimeout dazu, dass InnoDB abbricht und die gesamte Transaktion zurücksetzt (dasselbe Verhalten wie in MySQL 4.1). Diese Variable wurde in MySQL 5.1.15 hinzugefügt.

Erklärung: Wenn dieser Parameter ausgeschaltet ist oder nicht existiert, wird bei einem Timeout nur die letzte Abfrage der Transaktion zurückgesetzt. Wenn er eingeschaltet ist, wird bei einem Timeout die gesamte Transaktion zurückgesetzt.

3. Wie kann die Wahrscheinlichkeit eines InnoDB-Deadlocks verringert werden?

In Zeilensperren- und Transaktionsszenarien lassen sich Deadlocks nur schwer vollständig vermeiden. Sperrkonflikte und Deadlocks lassen sich jedoch durch Tabellendesign und SQL-Anpassungen reduzieren. Dazu gehören:

Versuchen Sie, eine niedrigere Isolationsstufe zu verwenden. Wenn beispielsweise eine Lückensperre auftritt, können Sie die Transaktionsisolationsstufe der Sitzung oder Transaktion auf RC (Read Commited) ändern, um dies zu vermeiden. Sie müssen jedoch binlog_format auf Zeilen- oder gemischtes Format einstellen.

Entwerfen Sie Indizes sorgfältig und verwenden Sie Indizes möglichst für den Datenzugriff, um die Sperrung präziser zu gestalten und die Wahrscheinlichkeit von Sperrkonflikten zu verringern.

Wählen Sie eine angemessene Transaktionsgröße. Bei kleinen Transaktionen kommt es seltener zu Sperrkonflikten.

Beim expliziten Sperren eines Recordset empfiehlt es sich, eine ausreichende Anzahl an Sperren gleichzeitig anzufordern. Wenn Sie beispielsweise Daten ändern möchten, ist es am besten, direkt eine exklusive Sperre zu beantragen, anstatt zuerst eine gemeinsame Sperre zu beantragen und dann beim Ändern eine exklusive Sperre anzufordern, was zu Deadlocks führen kann.

Wenn verschiedene Programme auf eine Gruppe von Tabellen zugreifen, sollten sie versuchen, sich darauf zu einigen, in derselben Reihenfolge auf die Tabellen zuzugreifen. Versuchen Sie bei einer Tabelle, in einer festgelegten Reihenfolge auf die Zeilen zuzugreifen. Dadurch kann die Wahrscheinlichkeit eines Deadlocks erheblich verringert werden.

Versuchen Sie, unter gleichen Bedingungen auf die Daten zuzugreifen, um die Auswirkungen von Lückensperren auf gleichzeitige Einfügungen zu vermeiden.

Beantragen Sie keine höhere Sperrebene als tatsächlich nötig. Zeigen Sie bei Abfragen keine Sperren an, es sei denn, dies ist notwendig.

Für bestimmte Transaktionen können Tabellensperren verwendet werden, um die Verarbeitungsgeschwindigkeit zu erhöhen oder die Möglichkeit eines Deadlocks zu verringern.

Fall 2: Seltsame Wartezeit für Sperre

An mehreren aufeinanderfolgenden Tagen schlug eine Aufgabe um 6 Uhr bzw. 8 Uhr fehl. Beim Laden der lokalen Infile-Daten wurde eine Java SQL-Ausnahme „Wartezeit für Sperre überschritten. Versuchen Sie, die Transaktion innodb neu zu starten“ gemeldet. Nach der Kommunikation mit den Kollegen auf der Plattform erfuhren wir, dass dies daran lag, dass die Sperrzeit unserer eigenen Geschäftsdatenbank zu kurz war oder ein Sperrkonflikt auftrat. Aber wenn ich zurückdenke, sollte ich das nicht? War das nicht immer in Ordnung? Zudem handelt es sich grundsätzlich um Einzelaufgaben, bei denen es zu keinen Mehrpersonenkonflikten kommt.

Unabhängig davon, wessen Problem es ist, prüfen wir zunächst, ob ein Problem mit unserer eigenen Datenbank vorliegt:

Die Standard-Sperrzeitüberschreitung beträgt 50 Sekunden, was wirklich nicht kurz ist. Ich denke, es wäre sinnlos, sie anzupassen. Tatsächlich habe ich es als letzten Ausweg versucht und es hat nicht funktioniert. . .

Und dieses Mal zeigte SHOW ENGINE INNODB STATUS\G keine Deadlock-Informationen an. Dann richtete ich meine Aufmerksamkeit auf das MySQL-Serverprotokoll, in der Hoffnung, daraus zu entnehmen, welche Operationen die Daten vor und nach diesem Moment durchführten. Hier ist eine kurze Einführung in die Zusammensetzung des MySQL-Protokolldateisystems:

(a) Fehlerprotokoll: zeichnet Probleme auf, die beim Starten, Ausführen oder Stoppen von mysqld auftreten. Es ist standardmäßig aktiviert.
(b) allgemeines Protokoll: allgemeines Abfrageprotokoll, das alle Anweisungen und Anweisungen aufzeichnet. Die Aktivierung führt zu einem Leistungsverlust der Datenbank von etwa 5 %.
(c) Binlog: Es zeichnet im Binärformat alle Anweisungen auf, die Daten ändern, und wird hauptsächlich für die Slave-Replikation und Datenwiederherstellung verwendet.
(d) langsames Protokoll: zeichnet alle Abfragen auf, deren Ausführung länger als long_query_time Sekunden dauert oder die keine Indizes verwenden. Es ist standardmäßig deaktiviert.
(e) InnoDB-Protokoll: InnoDB-Redo-Protokoll, Undo-Protokoll, wird zum Wiederherstellen von Daten und Rückgängigmachen von Vorgängen verwendet.

Aus der obigen Einführung können wir ersehen, dass die Protokolle dieses Problems in d und b liegen können. Nach der Überprüfung haben wir festgestellt, dass in d kein Protokoll vorhanden ist, sodass wir nur b aktivieren können. Allerdings hat b einen gewissen Leistungsverlust bei der Datenbank. Da es sich um ein vollständiges Protokoll handelt, ist das Volumen sehr groß, sodass wir beim Aktivieren vorsichtig sein müssen:

Ich habe einfach jeden Tag eine halbe Stunde vor und nach dem Problem das vollständige Protokoll aktiviert und konnte keine MySQL-Client-Anfragen an unsere Geschäftsdatenbank finden! Das Protokollformat ist wie folgt und zeichnet alle Verbindungen und Befehle auf:

Das Problem wurde im Grunde bestätigt. Die obige Ausnahme wurde ausgelöst, bevor die Client-Anforderung uns erreichte. Nach wiederholter Kommunikation und Bestätigung mit der Plattform bestätigte die Plattform schließlich, dass dies daran lag, dass sie das SQL aus der SQL-Aufgabentabelle abrufen und den Aufgabenstatus aktualisieren mussten, bevor die Einfügung ausgeführt wurde. Infolgedessen hatte diese Tabelle zu jeder Stunde eine große Anzahl gleichzeitiger Einfügungen und Aktualisierungen, was dazu führte, dass einige SQLs beim Warten auf die Sperre eine Zeitüberschreitung erlitten. . .

MySQL-Protokollanalyseskript

Da der frühe Morgen die Hauptgeschäftszeit für Data Warehouses ist, treten zu dieser Zeit viele Probleme auf. Einige seltsame Probleme sind oft für immer verschwunden und können tagsüber nicht reproduziert werden. Es ist von größter Bedeutung, die für uns wichtigen Protokolle zu erfassen, um das Problem schnell zu lokalisieren. Hier habe ich ein kleines Skript für die Crontab-Bereitstellung geschrieben. Sie können einen Zeitraum für den Start auswählen und das Protokoll einmal pro Minute abfragen. Es ist zu beachten, dass das allgemeine Protokoll nicht einfach aktiviert werden sollte, da dies sonst zu einem großen Verlust der Datenbankleistung führt.

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, er wird für jedermanns Studium hilfreich sein. Ich hoffe auch, dass jeder 123WORDPRESS.COM unterstützen wird.