Die normale Methode der MySQL-Deadlock-Prüfungsverarbeitung

#Schritt 1: Fenster 1mysql> Transaktion starten;
mysql> update aa set name='aaa', wobei id = 1;
 
#Schritt 2: Fenster 2mysql> Transaktion starten;
mysql> update bb set name='bbb', wobei id = 1;
 
#Schritt 3: Fenster 1mysql> update bb set name='bbb';

#Schritt 4: Fenster 3#Gibt es eine automatische Übermittlung? mysql> Variablen wie ‚autocommit‘ anzeigen;
+---------------+-------+
| Variablenname | Wert |
+---------------+-------+
| Autocommit | EIN |
+---------------+-------+
 
#Aktuelle Verbindung anzeigenmysql> show processlist;
mysql> vollständige Prozessliste anzeigen;
mysql> AUSWÄHLEN * AUS INFORMATION_SCHEMA.PROCESSLIST;
+----+------+--------------+------+---------+------+---------+----------+-----------------+
| ID | Benutzer | Host | db | Befehl | Zeit | Status | Info |
+----+------+--------------+------+---------+------+---------+----------+-----------------+
| 4 | root | localhost | test | Ruhezustand | 244 | | NULL |
| 5 | root | localhost | test | Ruhezustand | 111 | | NULL |
| 6 | root | localhost | NULL | Abfrage | 0 | init | Prozessliste anzeigen |
+----+------+--------------+------+---------+------+---------+----------+-----------------+
 
 
#Zeigen Sie die aktuell gesperrte Transaktion an (kann nicht gefunden werden, nachdem die Sperranforderung abgelaufen ist)
mysql> AUSWÄHLEN * AUS INFORMATION_SCHEMA.INNODB_LOCKS;
+------------------+----------+----------+-----------+-------------+-----------------+------------+-----------+-----------+----------------+
| Sperr-ID | Sperr-TRX-ID | Sperrmodus | Sperrtyp | Sperrtabelle | Sperrindex | Sperrbereich | Sperrseite | Sperraufzeichnung | Sperrdaten |
+------------------+----------+----------+-----------+-------------+-----------------+------------+-----------+-----------+----------------+
| 130718495:65:3:4 | 130718495 | X | AUFZEICHNUNG | `test`.`bb` | GEN_CLUST_INDEX | 65 | 3 | 4 | 0x000000000300 |
| 130718496:65:3:4 | 130718496 | X | AUFZEICHNUNG | `test`.`bb` | GEN_CLUST_INDEX | 65 | 3 | 4 | 0x000000000300 |
+------------------+----------+----------+-----------+-------------+-----------------+------------+-----------+-----------+----------------+
 
#Zeigen Sie die Transaktionen an, die derzeit auf Sperren warten (können nicht gefunden werden, nachdem die Sperranforderung abgelaufen ist)
mysql> AUSWÄHLEN * AUS INFORMATION_SCHEMA.INNODB_LOCK_WAITS; 
+---------------------------------+----------------------+-----------------+------------------+
| anfordernde_TRX-ID | angeforderte_Sperr-ID | blockierende_TRX-ID | blockierende_Sperr-ID |
+---------------------------------+----------------------+-----------------+------------------+
| 130718499 | 130718499:65:3:4 | 130718500 | 130718500:65:3:4 |
+---------------------------------+----------------------+-----------------+------------------+
 
 
#Zeigen Sie die aktuelle nicht festgeschriebene Transaktion an (wenn die Wartezeit für den Deadlock abläuft, ist die Transaktion möglicherweise noch nicht abgeschlossen)
mysql> AUSWÄHLEN * AUS INFORMATION_SCHEMA.INNODB_TRX;
+-----------------------------------------------------------------------------------------------------+
| trx_id | trx_status | trx_gestartet | trx_angeforderte_Sperr-ID | trx_wait_gestartet | trx_gewicht |
+-----------+--------------+---------------------+-----------------------+---------------------+------------+
| 130718500 | LÄUFT | 12.03.2018 09:28:10 | NULL | NULL | 3 |
| 130718499 | SPERRE WARTEN | 12.03.2018 09:27:59 | 130718499:65:3:4 | 12.03.2018 09:32:48 | 5 |
==========================================================================================================
| trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use |
+---------------------+-----------------------------------------+---------------------+---------------------------------+
| 4 | NULL | NULL | 0 |
| 5 | Update bb Set Name='bbb' | Startindex lesen | 1 |
=========================================================================================================
| trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified |
+-------------------+------------------+-------------------------+-------------------+-------------------+
| 0 | 2 | 360 | 3 | 1 |
| 1 | 4 | 1184 | 4 | 1 |
=========================================================================================================================
| trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error |
+-------------------------+---------------------+-------------------+------------------------+-------------------------+
| 0 | WIEDERHOLBARES LESEN | 1 | 1 | NULL |
| 0 | WIEDERHOLBARES LESEN | 1 | 1 | NULL |
=========================================================================================================================
| trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking |
+-----------------------------+------------------------------+------------------+----------------------------+
| 0 | 10000 | 0 | 0 |
| 0 | 10000 | 0 | 0 |
+-----------------------------+------------------------------+------------------+----------------------------+
 
 
#Zugriffstabellen anzeigen mysql> show OPEN TABLES where In_use > 0;
+----------+----------+--------+----------+
| Datenbank | Tabelle | In_Verwendung | Name_gesperrt |
+----------+----------+--------+----------+
| Prüfung | bb | 1 | 0 |
+----------+----------+--------+----------+

#Schritt 3: Fenster 1 (Wenn die Sperranforderung in Schritt 3 zu lange dauert, wird die Sperre abgebrochen und die Ausführung wird beendet)
mysql> BB-Set-Name aktualisieren = "bbb";
FEHLER 1205 (HY000): Wartezeit für Sperre überschritten; versuchen Sie, die Transaktion neu zu starten.
 
 
#Bevor die Sperranforderung von „Fenster eins“ abläuft, führen Sie Schritt 5 aus, um einen Deadlock zu verursachen. Dann wird die Ausführung der Verbindung „Fenster zwei“ beendet und „Fenster eins“ erfolgreich ausgeführt. #Schritt 5: Fenster zweimysql> update aa set name='aa';
FEHLER 1213 (40001): Beim Versuch, eine Sperre zu erhalten, wurde ein Deadlock festgestellt. Versuchen Sie, die Transaktion neu zu starten.

#Letzte Deadlock-Situation anzeigenmysql> SHOW ENGINE INNODB STATUS\G;
...............
------------------------
Zuletzt erkannter Deadlock
------------------------
2018-03-12 11:01:06 7ffb4993a700 #Auftrittszeitpunkt*** (1) TRANSAKTION: #Transaktion 1
TRANSAKTION 130718515, AKTIV 19 Sek. Start des Indexlesens
MySQL-Tabellen in Verwendung 1, gesperrt 1 #Auf Tabelle wird zugegriffen LOCK WAIT 4 Sperrstruktur(en), Heap-Größe 1184, 4 Zeilensperre(n), Undo-Log-Einträge 1 #Anzahl der betroffenen Zeilen MySQL-Thread-ID 5, OS-Thread-Handle 0x7ffb498f8700, Abfrage-ID 205 Localhost-Stamm wird aktualisiert #Thread/Verbindungshost/Benutzeraktualisierung bb set name='bb' #Anforderungsanweisung*** (1) WARTEN AUF DIE GEWÄHRUNG DIESER SPERRE: #Warten auf folgende Ressourcen (Sperrposition und Sperrmodus)
RECORD LOCKS Bereichs-ID 65 Seitennummer 3 n Bits 72 Index `GEN_CLUST_INDEX` der Tabelle `test`.`bb` TRX-ID 130718515 Sperrmodus X wartend
Datensatzsperre, Heap Nr. 5 PHYSIKALISCHER DATENSATZ: n_Felder 5; kompaktes Format; Infobits 0
 0: Länge 6; Hex 000000000300; aufsteigend ;;
 1: Länge 6; Hex 000007ca9b34; aufsteigend 4;;
 2: Länge 7; Hex 1f000002092075; asc u;;
 3: Länge 4; Hex 80000001; aufsteigend ;;
 4: Länge 2; Hex 6262; aufsteigend bb;;
 
*** (2) TRANSAKTION: #Transaktion 2
TRANSAKTION 130718516, AKTIV 14 Sek. Start des Indexlesens
MySQL-Tabellen in Verwendung 1, gesperrt 1
4 Sperrstruktur(en), Heapgröße 1184, 4 Zeilensperre(n), Undo-Logeinträge 1
MySQL-Thread-ID 4, OS-Thread-Handle 0x7ffb4993a700, Abfrage-ID 206, Localhost-Root-Aktualisierung
update aa set name='aa' #Anforderungsanweisung*** (2) HÄLT DIE SPERRE(N): #Halten der Sperrressourcen DATENSATZSPERREN Bereichs-ID 65 Seitennummer 3 n Bits 72 Index `GEN_CLUST_INDEX` der Tabelle `test`.`bb` TRX-ID 130718516 Sperrmodus X
Datensatzsperre, Heap Nr. 1 PHYSIKALISCHER DATENSATZ: n_Felder 1; kompaktes Format; Infobits 0
 0: Länge 8; Hex 73757072656d756d; Asc Supremum;;
 
Datensatzsperre, Heap Nr. 3 PHYSIKALISCHER DATENSATZ: n_Felder 5; kompaktes Format; Infobits 0
 0: Länge 6; Hex 000000000301; aufsteigend ;;
 1: Länge 6; Hex 000007ca9b17; aufsteigend ;;
 2: Länge 7; Hex 9000000144011e; aufsteigend D ;;
 3: Länge 4; Hex 80000002; aufsteigend ;;
 4: Länge 2; Hex 6262; aufsteigend bb;;
 
Datensatzsperre, Heap Nr. 5 PHYSIKALISCHER DATENSATZ: n_Felder 5; kompaktes Format; Infobits 0
 0: Länge 6; Hex 000000000300; aufsteigend ;;
 1: Länge 6; Hex 000007ca9b34; aufsteigend 4;;
 2: Länge 7; Hex 1f000002092075; asc u;;
 3: Länge 4; Hex 80000001; aufsteigend ;;
 4: Länge 2; Hex 6262; aufsteigend bb;;
 
*** (2) WARTEN AUF DIE GEWÄHRUNG DIESER SPERRE:
RECORD LOCKS Bereichs-ID 64 Seitennummer 3 n Bits 80 Index `GEN_CLUST_INDEX` der Tabelle `test`.`aa` TRX-ID 130718516 Sperrmodus X wartend
Datensatzsperre, Heap Nr. 7 PHYSIKALISCHER DATENSATZ: n_Felder 5; kompaktes Format; Infobits 0
 0: Länge 6; Hex 000000000200; aufsteigend ;;
 1: Länge 6; Hex 000007ca9b33; aufsteigend 3;;
 2: Länge 7; Hex 1e000001d53057; aufsteigend 0W;;
 3: Länge 4; Hex 80000001; aufsteigend ;;
 4: Länge 2; Hex 6161; aufsteigend aa;;
 
*** WIR MACHEN DIE TRANSAKTION ZURÜCK (2)
...............

#Deadlock-Aufzeichnungen zeichnen nur die aktuellsten Deadlock-Informationen auf. Um alle Deadlock-Informationen im Fehlerprotokoll zu speichern, aktivieren Sie die folgenden Parameter:
mysql> Variablen wie „innodb_print_all_deadlocks“ anzeigen;
+----------------------------+----------+
| Variablenname | Wert |
+----------------------------+----------+
| innodb_print_all_deadlocks | AUS |
+----------------------------+----------+
 
 
#Wenn die Ressource in [Schritt 3: Fenster 1] oben nicht angefordert werden kann, tritt das Timeout für die Sperre nach der Standardeinstellung von 50 Sekunden ein.
mysql> Variablen wie „innodb_lock_wait_timeout“ anzeigen; 
+--------------------------+----------+
| Variablenname | Wert |
+--------------------------+----------+
| innodb_lock_wait_timeout | 50 |
+--------------------------+----------+
 
FEHLER 1205 (HY000): Wartezeit für Sperre überschritten; versuchen Sie, die Transaktion neu zu starten.
 
 
#Setzen Sie das Wartetimeout für die globale Variablensperre auf 60 Sekunden (neue Verbindung wird wirksam)
#mysql> Sitzung festlegen innodb_lock_wait_timeout=50; 
mysql> setze global innodb_lock_wait_timeout=60; 
 
 
#Wenn im obigen Test für eine Anweisung in einer Transaktion das Zeitlimit abläuft, wird nur die Anweisung zurückgesetzt und die Integrität der Transaktion zerstört. Um diese Transaktion rückgängig zu machen, aktivieren Sie die folgenden Parameter:
mysql> Variablen wie „innodb_rollback_on_timeout“ anzeigen;
+----------------------------+----------+
| Variablenname | Wert |
+----------------------------+----------+
| innodb_rollback_on_timeout | AUS |
+----------------------------+----------+

[mysqld]
log-error=/var/log/mysqld3306.log
innodb_lock_wait_timeout=60 #Timeout der Sperranforderung (Sekunden)
innodb_rollback_on_timeout = 1 #Wenn bei einer Sperranforderung einer Anweisung in einer Transaktion ein Timeout auftritt, wird die gesamte Transaktion zurückgesetzt. innodb_print_all_deadlocks = 1 #Alle Deadlocks werden im Fehlerprotokoll gespeichert.

#Wenn Sie die blockierte Sitzung manuell löschen, löschen Sie die mit Command='Sleep', ohne Status, ohne Informationen und mit dem kleinsten trx_weight.
Prozessliste anzeigen;
Wählen Sie trx_mysql_thread_id, trx_state, trx_started, trx_weight aus INFORMATION_SCHEMA.INNODB_TRX;