他にアクティブなセッションがないときに、Amazon RDS MySQL DB インスタンスに対するクエリがブロックされたのはなぜですか?

簡単な説明

InnoDB のトランザクションが別のトランザクションによるロックの解放を待っているため、クエリがブロックされる可能性があります。トランザクションがコミットされていないためにクエリがブロックされる可能性もあります。これらのトランザクションは NULL として表示されることがあります。クエリをブロックしている可能性があるクエリまたはセッションを特定するには、次のステップを実行します。

解決方法

コミットされていないトランザクションを特定

1.    INNODB_TRX テーブルに対してこのクエリを実行することで、現在実行中のトランザクションを表示します。

select * from information_schema.innodb_trx\G

2.    このクエリを実行して、待機しているトランザクションとクエリをブロックしているトランザクションを確認します。

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;

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;

注: ブロックされたトランザクションは、他のトランザクションがコミットまたはロールバックされるまで続行できません。

ブロックしているトランザクションを識別するときに、クエリを発行したセッションがアイドル状態になった場合、ブロックしているクエリに対して NULL 値が報告されます。この場合、ステップ 2 のクエリを使用して blocking_thread プロセスリスト ID を検索します。

3.    MySQL 5.7 以降の場合、このクエリを実行して、ブロックしているトランザクションの THREAD_ID を blocking_thread プロセスリスト ID に置き換えて判別します。

SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = blocking_thread;

4.    THREAD_ID を使用して、パフォーマンススキーマの events_statements_current テーブルをクエリします。これは、スレッドによって実行された最後のクエリを決定します。

注: THREAD_ID は、手順 3 で返された値に置き換えてください。

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

5.    ブロックしているセッションまたはスレッド ID を特定したら、この手順を実行してトランザクションを停止します。

注: この手順を実行する前に、トランザクションが必要かどうか、または安全に停止できるかどうかを評価してください。

CALL mysql.rds_kill(thread-ID);

注: 長時間実行されている操作を停止またはロールバックすると、時間がかかり、I/O 負荷が高くなります。

関連情報

セッションやクエリの終了

MySQL DB インスタンスのオプション

MySQL ウェブサイトの INFORMATION_SCHEMA_INNODB_TRX テーブル

MySQL ウェブサイトのブロックしているトランザクションの識別 

MySQL DB インスタンスの一般的な DBA タスク