Analyse von MySQL-Lock-Wait- und Deadlock-Problemen

mysql> anzeigen, erstellen, Tabelle test_tb\G
*************************** 1. Reihe ***************************
       Tabelle: test_tb
Tabelle erstellen: CREATE TABLE `test_tb` (
  `id` int(11) NICHT NULL AUTO_INCREMENT,
  `col1` varchar(50) NICHT NULL STANDARD '',
  `col2` int(11) NICHT NULL STANDARD '1',
  `col3` varchar(20) NICHT NULL STANDARD '',
  Primärschlüssel (`id`),
  SCHLÜSSEL `idx_col1` (`col1`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 Zeile im Satz (0,00 Sek.)

mysql> wähle * aus test_tb;
+----+------+------+------+
| ID | Spalte1 | Spalte2 | Spalte3 |
+----+------+------+------+
| 1 | fdg | 1 | abc |
| 2 | ein | 2 | fg |
| 3 | ghrv | 2 | rhdv |
+----+------+------+------+
3 Zeilen im Satz (0,00 Sek.)

# Transaktion 1 wird zuerst ausgeführt mysql> begin;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

mysql> wähle * aus test_tb, wobei col1 = „a“ für Update;
+----+------+------+------+
| ID | Spalte1 | Spalte2 | Spalte3 |
+----+------+------+------+
| 2 | ein | 2 | fg |
+----+------+------+------+
1 Zeile im Satz (0,00 Sek.)

# Transaktion 2 und dann mysql ausführen> begin;
Abfrage OK, 0 Zeilen betroffen (0,01 Sek.)

mysql> aktualisiere test_tb, setze col2 = 1, wobei col1 = „a“;
FEHLER 1205 (HY000): Wartezeit für Sperre überschritten; versuchen Sie, die Transaktion neu zu starten.

# Wenn eine Sperrwartezeit auftritt, können Sie alle Transaktionen in der Tabelle innodb_trx anzeigen.
 # Wenn der trx_state-Wert LOCK WAIT ist, bedeutet dies, dass sich die Transaktion im Wartezustand befindet. mysql> select * from information_schema.innodb_trx\G
*************************** 1. Reihe ***************************
                    trx_id: 38511
                 trx_state: SPERRE WARTEN
               trx_started: 2021-03-24 17:20:43
     trx_requested_lock_id: 38511:156:4:2
          trx_wait_started: 24.03.2021 17:20:43
                trx_gewicht: 2
       trx_mysql_thread_id: 1668447
                 trx_query: Aktualisiere test_tb, setze col2 = 1, wobei col1 = „a“
       trx_operation_state: Indexlesen wird gestartet
         trx_tables_in_use: 1
         trx_tables_locked: 1
          Anzahl der Lockstrukturen: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: WIEDERHOLBARES LESEN
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_ist_schreibgeschützt: 0
trx_autocommit_non_locking: 0
*************************** 2. Reihe ***************************
                    trx_id: 38510
                 trx_state: LÄUFT
               trx_started: 2021-03-24 17:18:54
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_gewicht: 4
       trx_mysql_thread_id: 1667530
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          Anzahl der Lockstrukturen: 4
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 3
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: WIEDERHOLBARES LESEN
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_ist_schreibgeschützt: 0
trx_autocommit_non_locking: 0
2 Zeilen im Satz (0,00 Sek.)

# Bedeutung des Feldwerts innodb_trx trx_id: Transaktions-ID.
trx_state: Transaktionsstatus, der die folgenden Zustände umfasst: RUNNING, LOCK WAIT, ROLLING BACK und COMMITTING.
trx_started: Startzeit der Transaktion.
trx_requested_lock_id: Die ID der Transaktion, die derzeit auf die Sperre wartet. Sie können mit der Tabelle INNODB_LOCKS JOIN durchführen, um detailliertere Informationen zu erhalten.
trx_wait_started: Die Zeit, zu der die Transaktion mit dem Warten beginnt.
trx_weight: Das Gewicht der Transaktion.
trx_mysql_thread_id: Transaktions-Thread-ID, die mit der PROCESSLIST-Tabelle verknüpft werden kann.
trx_query: Die von der Transaktion ausgeführte SQL-Anweisung.
trx_operation_state: aktueller Betriebsstatus der Transaktion.
trx_tables_in_use: Die Anzahl der Tabellen, die im von der aktuellen Transaktion ausgeführten SQL verwendet werden.
trx_tables_locked: Die Anzahl der Zeilensperren, die aktuell SQL ausführen.
trx_lock_structs: Die Anzahl der von der Transaktion beibehaltenen Sperren.
trx_isolation_level: Die Isolationsstufe der aktuellen Transaktion.

# Die Ansicht sys.innodb_lock_waits zeigt auch den Wartestatus der Transaktion an und gibt das SQL zum Beenden der Verbindung aus
mysql> wähle * aus sys.innodb_lock_waits\G
*************************** 1. Reihe ***************************
                wait_started: 24.03.2021 17:20:43
                    Wartezeit: 00:00:22
               Wartezeit in Sekunden: 22
                gesperrte_Tabelle: `testdb`.`test_tb`
                gesperrter_index: idx_col1
                 gesperrter Typ: AUFZEICHNUNG
              wartende_trx_id: 38511
         waiting_trx_started: 24.03.2021 17:20:43
             Wartezeit: 00:00:22
     waiting_trx_rows_locked: 1
   waiting_trx_rows_modified: 0
                 warte_pid: 1668447
               waiting_query: Aktualisiere test_tb, setze col2 = 1, wobei col1 = „a“
             Wartesperren-ID: 38511:156:4:2
           Wartesperrmodus: X
             Blockierungs-TRX-ID: 38510
                Blockierungs-PID: 1667530
              Blockierungsabfrage: NULL
            Sperrsperr-ID: 38510:156:4:2
          Blockierungssperrmodus: X
        blocking_trx_started: 24.03.2021 17:18:54
            Blockierung_TRX_Alter: 00:02:11
    Blockierung_TRX_Zeilen_gesperrt: 3
  Blockieren von TRX-Zeilen geändert: 0
     sql_kill_blocking_query: KILL-ABFRAGE 1667530
sql_kill_blocking_connection: KILL 1667530

# Zwei Transaktionen öffnen # Transaktion 1 wird ausgeführt mysql> update test_tb set col2 = 1 where col1 = 'a';
Abfrage OK, 1 Zeile betroffen (0,00 Sek.)
Übereinstimmende Zeilen: 1 Geändert: 1 Warnungen: 0

# Transaktion 2 mysql> update test_tb set col2 = 1 wobei id = 3;
Abfrage OK, 1 Zeile betroffen (0,00 Sek.)
Übereinstimmende Zeilen: 1 Geändert: 1 Warnungen: 0

# Kehren Sie zu Transaktion 1 zurück und drücken Sie die Eingabetaste. Diese Anweisung befindet sich im Sperrwartezustand. mysql> update test_tb set col1 = 'abcd' where id = 3;
Abfrage OK, 1 Zeile betroffen (5,71 Sek.)
Übereinstimmende Zeilen: 1 Geändert: 1 Warnungen: 0

# Kehren Sie zu Transaktion 2 zurück und führen Sie sie erneut aus. Zu diesem Zeitpunkt warten die beiden aufeinander und es tritt ein Deadlock auf. mysql> update test_tb set col3 = 'gddx' where col1 = 'a';
FEHLER 1213 (40001): Beim Versuch, eine Sperre zu erhalten, wurde ein Deadlock festgestellt. Versuchen Sie, die Transaktion neu zu starten.