Warum wurde eine Abfrage für meine Amazon-RDS-DB-Instance mit MySQL blockiert, obwohl keine weitere aktive Sitzung vorhanden ist?

Kurzbeschreibung

Blockierte Abfragen können auftreten, wenn eine Transaktion in InnoDB darauf wartet, dass eine andere Transaktion eine Sperre aufhebt. Darüber hinaus können Abfragen durch Transaktionen blockiert werden, die noch nicht bestätigt wurden. Diese Transaktionen werden möglicherweise als NULL angezeigt. Gehen Sie wie folgt vor, um zu ermitteln, durch welche Abfrage oder Sitzung Ihre Abfrage möglicherweise blockiert wird:

Behebung

Identifizieren nicht bestätigter Transaktionen

1.Führen Sie die folgende Abfrage für die Tabelle „INNODB_TRX“ aus, um die aktuell ausgeführten Transaktionen anzuzeigen:

select * from information_schema.innodb_trx\G

2.Führen Sie die folgende Abfrage aus, um zu ermitteln, welche Transaktionen warten und durch welche Transaktionen sie blockiert werden:

Bis MySQL 5.7:

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;

Ab 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 die andere Transaktion bestätigt wird oder ein Rollback für sie ausgeführt wird.

Bei der Identifizierung blockierender Transaktionen wird ein NULL-Wert für die blockierende Abfrage gemeldet, wenn die Sitzung, von der die Abfrage stammt, inaktiv geworden ist. Verwenden Sie in diesem Fall die Abfrage in Schritt 2, um die Prozesslisten-ID von „blocking_thread“ zu ermitteln.

3.Führen Sie ab MySQL 5.7 die folgende Abfrage aus, um den THREAD_ID-Wert der blockierenden Transaktion anhand der ersetzenden Prozesslisten-ID von „blocking_thread“ zu ermitteln:

SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = blocking_thread;

4.Verwenden Sie den THREAD_ID-Wert, um die Tabelle events_statements_current des Leistungsschemas abzufragen. Dadurch wird die letzte Abfrage ermittelt, die durch den Thread ausgeführt wurde.

**Hinweis:**Stellen Sie sicher, dass Sie THREAD_ID durch den in Schritt 3 zurückgegebenen Wert ersetzen.

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

5.Nachdem Sie die blockierende Sitzung oder Thread-ID identifiziert haben, können Sie die Transaktion wie folgt beenden:

Hinweis: Prüfen Sie zunächst, ob Sie die Transaktion benötigen oder ob sie problemlos beendet werden kann, bevor Sie diese Schritte ausführen.

CALL mysql.rds_kill(thread-ID);

Hinweis: Das Beenden oder Zurücksetzen eines Vorgangs mit langer Laufzeit kann länger dauern und E/A-intensiv sein.

Verwandte Informationen

Ending a session or query

Options for MySQL DB instances

The INFORMATION_SCHEMA_INNODB_TRX table on the MySQL website

Identifying blocking transactions on the MySQL website 

Common DBA tasks for MySQL DB instances