Warum wurde eine Abfrage an meine Amazon RDS für MySQL-DB-Instance blockiert, obwohl es keine andere aktive Sitzung gibt?

Lesedauer: 4 Minute
0

Wenn ich auf meiner Amazon Relational Database Service (Amazon RDS) für MySQL-DB-Instance eine Abfrage ausführe, wird die Abfrage blockiert. Es werden keine anderen Abfragen ausgeführt. Ich möchte dieses Problem beheben.

Lösung

Blockierte Abfragen treten auf, weil eine Transaktion in InnoDB darauf wartet, dass eine andere Transaktion eine Sperre aufhebt. Unbestätigte Transaktionen können auch Abfragen blockieren. Diese Transaktionen werden möglicherweise als NULL angezeigt.

Die INNODB_TRX-Tabelle verwenden, um unbestätigte Transaktionen zu identifizieren

Gehe wie folgt vor, um die Abfrage oder Sitzung zu identifizieren, die die Abfrage blockiert:

  1. Führe die folgende Abfrage für die INNODB_TRX-Tabelle aus, um die Transaktionen anzuzeigen, die aktuell ausgeführt werden:

    select * from information_schema.innodb_trx\G
  2. Um die ausstehenden Transaktionen und die Transaktionen, die sie blockieren, anzuzeigen, führe eine der folgenden Abfragen aus, die auf deiner MySQL-Version basieren.
    MySQL 5.7 und früher:

    SELECT   
    r.trx_id waiting_trx_id,   
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
    FROM information_schema.innodb_lock_waits w
    INNER JOIN information_schema.innodb_trx b
    ON b.trx_id = w.blocking_trx_id
    INNER JOIN information_schema.innodb_trx r
    ON r.trx_id = w.requesting_trx_id;
    

    MySQL 8.0:

    SELECT   
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
    FROM performance_schema.data_lock_waits w
    INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_engine_transaction_id
    INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_engine_transaction_id;
    

    Hinweis: Die blockierte Transaktion kann erst fortgesetzt werden, wenn für die andere Transaktion einen Commit oder Rollback ausgeführt wird. Wenn die Sitzung, die die Abfrage ausgegeben hat, inaktiv ist, wird ein NULL-Wert für die blockierende Abfrage gemeldet. Verwende in diesem Fall die vorhergehende Abfrage, um die blocking_thread PROCESSLIST_ID zu finden.

  3. Führe für MySQL 5.7 oder höher die folgende Abfrage aus, um die PROCESSLIST_ID der blockierenden Transaktion durch die blockierende Ersatz-THREAD_ID zu ermitteln:

    SELECT PROCESSLIST_ID FROM performance_schema.threads where THREAD_ID = blocking;
  4. Führe die folgende Abfrage für die Tabelle events_statements_current mit der THREAD_ID aus, um die letzte Abfrage zu ermitteln, die der Thread ausgeführt hat:

    SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_current WHERE THREAD_ID = 1;

    Hinweis: Ersetze THREAD_ID durch den Wert, den du in Schritt 3 erhalten hast.

  5. Führe den folgenden Befehl aus, um die Transaktion zu stoppen:

    CALL mysql.rds_kill(PROCESSLIST_ID);

    Hinweis: Das Stoppen oder Zurücksetzen einer Transaktion mit langer Ausführungszeit ist zeitaufwändig und E/A-intensiv.

XA-Blockierungstransaktionen identifizieren

Wenn der blocking_thread 0 ist und CALL mysql.rds_kill(PROCESSLIST_ID); nicht funktioniert, blockiert dich möglicherweise eine XA-Transaktion.

Führe den folgenden Befehl aus, um die XA-Transaktion zu identifizieren, die die Blockierung verursacht:

XA RECOVER;

Der vorhergehende Befehl gibt eine Ausgabe für XA-Transaktionen zurück, die sich im Status Prepared (Vorbereitet) befinden. Um den Block zu beenden, verwende die Datenspalte, um für die Transaktion einen Commit oder Rollback auszuführen:

XA RECOVER;

+----------+--------------+--------------+--------------------+
| formatID | gtrid_length | bqual_length | data               |
+----------+--------------+--------------+--------------------+
|   1      |           10 |            0 | RePostTest         |
+----------+--------------+--------------+--------------------+
1 row in set (0.00 sec)

XA COMMIT 'RePostTest';
XA ROLLBACK 'RePostTest';

Wenn du beim Commit oder Rollback die folgende Fehlermeldung erhältst, konvertiere die XID in eine Hexadezimalzahl und setze die Transaktion zurück:

„ERROR 1397 (XAE04): XAER_NOTA: Unknown XID“

Um die Transaktion wiederherzustellen, führe den folgenden Befehl aus, um gtrid_length und bqual_length abzurufen, um die erforderlichen Teile des Datenfeldes zu extrahieren:

mysql> xa recover convert xid;
+----------+--------------+--------------+----------------------------------------+
| formatID | gtrid_length | bqual_length | data                                   |
+----------+--------------+--------------+----------------------------------------+
|   1      |           10 |            0 | 0x5265506F737454657374                 |
+----------+--------------+--------------+----------------------------------------+
1 row in set (0.00 sec)

mysql> XA ROLLBACK X'5265506F737454657374';
Query OK, 0 rows affected (0.01 sec)

Weitere Informationen zur Konvertierung von XID-Werten findest du unter XA transaction SQL statements (XA-Transaktions-SQL-Anweisungen) auf der MySQL-Website.

Ähnliche Informationen

Beenden einer Sitzung oder Abfrage bei RDS für MySQL

Optionen für MySQL-DB-Instances

Die INFORMATION_SCHEMA_INNODB_TRX-Tabelle auf der MySQL-Website

Identifying blocking transactions (Identifizierung blockierender Transaktionen) auf der MySQL-Website

Allgemeine DBA-Aufgaben für MySQL-DB-Instances