Der Fall ist wie folgt:
Bei der Verwendung von Show innodb status zur Überprüfung des Engine-Status wurde ein Deadlock-Problem entdeckt:
*** (1) TRANSACTION:
TRANSACTION 0 677833455, ACTIVE 0 sec, Process No 11393, OS Thread ID 278546 Starting Index Read
Verwendete MySQL-Tabellen 1, gesperrt 1
LOCK WAIT 3 Sperrstruktur(en), Heap-Größe 320
MySQL-Thread-ID 83, Abfrage-ID 162348740 dcnet03 dcnet Zeilen werden nach Aktualisierung durchsucht.
Update TSK_TASK setzt STATUS_ID=1064,UPDATE_TIME=now (), wobei STATUS_ID=1061 und MON_TIME
*** (1) WARTEN AUF DIE GEWÄHRUNG DIESER SPERRE:
RECORD SPERRT Space-ID 0 Seiten-Nr. 849384 n Bits 208 Index „PRIMARY“ der Tabelle „dcnet_db/TSK_TASK“ TRX-ID 0 677833455
lock_mode , Heap-Nr. 92 PHYSISCHER RECORD: n_fields 0
0: len 8; asc b ;; asc (f ;; 2: len 7; hex 00000 110; @ ;; len 8; hex 8000000000502a; f ;; 7: len hex 75706c6f6164666972652e636f6d2f6 8616e642e706870;; asc 8; hex 8000000000042b;; aufsteigend N$;;
** * (2) TRANSAKTION:
TRANSAKTION 0 677833454, AKTIV 0 Sek., Prozess-Nr. 11397, Betriebssystem-Thread-ID 344086 wird aktualisiert oder gelöscht, Thread in InnoDB deklariert 499
MySQL-Tabellen werden verwendet 1, gesperrt 1
3 Sperrstruktur(en), Heap-Größe 320, Protokolleinträge rückgängig machen 1
MySQL-Thread-ID 84, Abfrage-ID 162348739 dcnet03 dcnet Update
TSK_TASK wird aktualisiert, STATUS_ID=1067,UPDATE_TIME=now () gesetzt, wobei ID in (9921180)
*** (2) HÄLT DIE SPERRE:
RECORD SPERRT Space-ID 0 Seitennummer 849384 n Bits 208 Index „PRIMARY“ der Tabelle „dcnet_db/TSK_TASK“ trx id 0
677833454
lock_mode
8; hex 800000000097629c; asc 1: len 6; asc 2: len 7; hex 8000000000050b2; asc P ;; 4: len 8 ;hex 80000000000502a; asc 8;; len 23; 0; asc uploadfire.com/hand.php;; 8: hex 800000000000042b; 8; hex 8000000000004e24; asc N$ ;;
(2) WARTEN AUF DIE GEWÄHRUNG DIESER SPERRE:
RECORD LOCKS Leerzeichen-ID 0 Seitennummer 843102 n Bits 600 Index „KEY_TSKTASK_MONTIME2“ der Tabelle „dcnet_db/TSK_TASK“ trx-ID 0 6778334 54
lock_mode 629c;
aufsteigend
b ;;
*** WE ROLL BACK TRANSACTION (1)
Dieses Deadlock-Problem betrifft die TSK_TASK-Tabelle, die zum Speichern von Systemüberwachungsaufgaben verwendet wird:
ID: Primärschlüssel;
STATUS_ID
: Aufgabenstatus;
Index: KEY_TSKTASK_MONTIME2 (STATUS_ID, MON_TIME).
Die Analyse zeigt, dass die beiden beteiligten Anweisungen nicht denselben TSK_TASK-Datensatz betreffen sollten. Warum kommt es also zu einem Deadlock?
Nachdem ich die Dokumentation der offiziellen MySQL-Website abgefragt hatte, stellte ich fest, dass dies mit dem Indizierungsmechanismus von MySQL zusammenhängt. Die InnoDB-Engine von MySQL verwendet Sperren auf Zeilenebene, aber das ist eigentlich nicht der Fall.
Die wichtigsten Punkte sind wie folgt:
Anstatt Datensätze zu sperren, wird der Index
während UPDATE- und DELETE-Vorgängen gesperrt. MySQL sperrt nicht nur alle von der WHERE-Bedingung gescannten Indexdatensätze, sondern auch benachbarte Schlüsselwerte, den sogenannten nächsten Schlüssel Sperren;
zum Beispiel sperrt die Anweisung UPDATE TSK_TASK SET UPDATE_TIME = NOW() WHERE ID > 10000 alle Datensätze mit einem Primärschlüssel größer oder gleich 1000. Bevor die Anweisung abgeschlossen ist, können Sie keine Operationen mit Datensätzen durchführen, deren Primärschlüssel gleich ist auf 10000;
wenn der Nicht-Cluster-Index (Wenn ein Nicht-Cluster-Indexdatensatz gesperrt ist, muss auch der zugehörige Cluster-Indexdatensatz gesperrt werden, um den entsprechenden Vorgang abzuschließen.
Bei der Analyse der beiden SQL-Anweisungen, bei denen das Problem aufgetreten ist, ist es nicht schwierig, das Problem zu finden:
Wenn „Update TSK_TASK STATUS_ID=1064,UPDATE_TIME=now () setzt, wobei STATUS_ID=1061 und MON_TIME
Unter der Annahme, dass „update TSK_TASK set STATUS_ID=1067,UPDATE_TIME=now () where ID in (9921180)“ fast gleichzeitig ausgeführt wird, sperrt diese Anweisung zunächst den Clusterindex (Primärschlüssel), da der Wert von STATUS_ID aktualisiert werden muss Es ist auch erforderlich, einen bestimmten Teil von KEY_TSKTASK_MONTIME2 einiger Indexdatensätze zu sperren.
Auf diese Weise sperrt die erste Anweisung den Datensatz von KEY_TSKTASK_MONTIME2 und wartet auf den Primärschlüsselindex, während die zweite Anweisung den Datensatz des Primärschlüsselindex sperrt und auf den Datensatz von KEY_TSKTASK_MONTIME2 wartet. In diesem Fall tritt ein Deadlock auf.
Der Autor löste das Deadlock-Problem, indem er die erste Anweisung aufteilte:
Finden Sie zuerst die qualifizierte ID: Wählen Sie die ID aus TSK_TASK aus, wobei STATUS_ID=1061 und MON_TIME < date_sub(now(), INTERVAL 30 Minute); und aktualisieren Sie dann den Status: update TSK_TASK set STATUS_ID= 1064 mit ID in (….)
An diesem Punkt ist das Deadlock-Problem vollständig gelöst.