Teilen Sie eine Methode zur Lösung des MySQL-Deadlock-Problems

Teilen Sie eine Methode zur Lösung des MySQL-Deadlock-Problems

1. Umgebung

• CentOS, MySQL 5.6.21-70, JPA

• Problemszenario: Das System verfügt über einen geplanten Stapelaktualisierungsvorgang für den Datenstatus, bei dem jedes Mal Tausende von Datensätzen aktualisiert werden. Die Gesamtzahl der Datensätze in der Tabelle beträgt etwa 5 Millionen.

2. Fehlerprotokoll

2017-2-25 17:38:41 org.hibernate.util.JDBCExceptionReporter logExceptions
严重: Lock wait timeout exceeded; try restarting transaction
2017-2-25 17:39:05 org.hibernate.util.JDBCExceptionReporter logExceptions
警告: SQL Error: 1213, SQLState: 40001
2017-2-25 17:39:05 org.hibernate.util.JDBCExceptionReporter logExceptions
严重: Deadlock found when trying to get lock; try restarting transaction

3. Fehlerbehebung

Check InnoDB status for locks
mysql> SHOW ENGINE InnoDB STATUS;

Check MySQL open tables
mysql> SHOW OPEN TABLES WHERE In_use > 0;

Check pending InnoDB transactions
mysql> SELECT * FROM `information_schema`.`innodb_trx` ORDER BY `trx_started`; 

Check lock dependency - what blocks what
mysql> SELECT * FROM `information_schema`.`innodb_locks`;

Nach der Fehlerbehebung wurde festgestellt, dass bei der Ausführung von Anweisungen wie Dies:

update t_task_tel set state='iok', update_date='2017-02-27 11:03:02' where tel_id=66042 and task_id=350199;

4. Analyse

Nach der Suche nach relevanten Informationen wurde festgestellt, dass MySQL InnoDB ist nicht unbedingt alles. Es handelt sich um eine Sperre auf Zeilenebene.

Relevante Referenzmaterialfragmente sind wie folgt:

Auswahl sperren
1) Wenn die Aktualisierungsbedingung den Index nicht durchläuft, führen Sie beispielsweise „Update von t1 set v2=0“ aus wobei v2=5;", Zu diesem Zeitpunkt wird ein vollständiger Tabellenscan durchgeführt. Beim Scannen der Tabelle müssen alle anderen Aktualisierungsvorgänge verhindert werden, daher wird sie auf eine Tabellensperre aktualisiert.
2) Wenn es sich bei der Aktualisierungsbedingung um ein Indexfeld handelt, es sich jedoch nicht um einen eindeutigen Index (einschließlich des Primärschlüsselindex) handelt, wenn Sie beispielsweise „Update from t1 set v2=0 where v1=9;“ ausführen 🎜>

dann verwendet das Update die Next-Key-Sperre. Gründe für die Verwendung der Next-Key-Sperre:

a) Stellen Sie zunächst sicher, dass den Datensätzen, die die Bedingungen erfüllen, eine exklusive Sperre hinzugefügt wird, die den Wert des aktuellen nicht eindeutigen Index und des entsprechenden Primärschlüsselindex b) und außerdem ist gewährleistet, dass keine neuen Daten in das gesperrte Intervall eingefügt werden können.
3) Wenn die Aktualisierungsbedingung ein eindeutiger Index ist, verwenden Sie die Datensatzsperre.

InnoDB findet den entsprechenden Datensatz basierend auf dem eindeutigen Index und fügt die Datensatzsperre zum Primärschlüsselindexwert und zum eindeutigen Indexwert hinzu. Verwenden Sie jedoch nicht Gap Lock (Lückensperre).
Da InnoDB standardmäßig die Zeilensperre verwendet (sperrt nur die ausgewählten Daten), führt MySQL ansonsten eine Tabellensperre aus (sperrt die gesamten Daten). Formular ist gesperrt).


Basierend auf der Schlussfolgerung der Analyse wird vermutet, dass der Grund darin liegt, dass tel_id und task_id beim Aktualisieren der Tabelle _task_tel nicht UNIQUE (eindeutiger Index) eingerichtet haben 5. Lösung

Versuchen Sie basierend auf dieser Analyse, das Problem zu lösen, indem Sie über die beiden Felder tel_id und task_id einen UNIQUE (eindeutigen Index) einrichten. (Sie können es auch zuerst abfragen und dann basierend auf der Primärschlüssel-ID aktualisieren, sodass die große Datenmenge in der Tabelle das Online-Geschäft nicht beeinträchtigt.)

Nach der Lösung dieses Problems ist das Problem nicht erneut aufgetreten.

Wenn Ihr Problem dem ähnelt, auf das ich gestoßen bin, können Sie versuchen, es entsprechend zu lösen.