行ロック待ちによるロック待機タイムアウトのトラブルシューティング - RDS - Alibaba Cloud -

問題の説明

行ロック待ちは、あるセッションが別のセッションによって保持されている排他的行ロックを待機している状態です。この待機時間がタイムアウトしきい値を超えると、MySQL は次のエラーを返します。

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

原因

通常、排他的行ロックを保持しているセッションはトランザクションを迅速に完了し、コミットまたはロールバックによってロックをリリースします。これにより、待機中のセッションはタイムアウト前にロックを取得して処理を続行できます。ロック待機タイムアウトは、セッションが innodb_lock_wait_timeout で許容される時間よりも長く行ロックを保持した場合に発生します。

一般的なパターンは以下のとおりです。

オープントランザクションを持つアイドル接続：アプリケーションがトランザクションを開始して一部の作業を実行しましたが、コミットもロールバックも行わず、ロックを無期限に保持したままになっています。
長時間実行されるバッチ DML：大規模な UPDATE または DELETE 操作が多数の行を処理している間、長時間にわたり行ロックを保持しています。
中断されたアプリケーションセッション：クライアントアプリケーションのデータベースセッションが切断されましたが、インスタンスがその切断を検出できなかったため、トランザクションとそのロックがリリースされていません。

ソリューション

以下の手順は、行ロック待ちが実際に発生している場合にのみ適用されます。RDS for MySQL のデフォルトの innodb_lock_wait_timeout は 50 秒であり、ライブ環境でのロック待ちの観察は困難です。テスト環境でこの問題を再現するには、innodb_lock_wait_timeout を大きな値に設定してください。ただし、本番環境ではこの設定を変更しないでください。

DMS を使用して RDS for MySQL インスタンスにログインします。

次のクエリを実行して、どのセッションがどのセッションをブロックしているか、ブロックされているクエリ、およびブロッカーが実行中である時間を確認します。

SELECT
  r.trx_mysql_thread_id    AS waiting_thread,
  r.trx_query              AS waiting_query,
  b.trx_mysql_thread_id    AS blocking_thread,
  b.trx_query              AS blocking_query,
  b.trx_started            AS blocking_started,
  TIMESTAMPDIFF(SECOND, b.trx_started, NOW()) AS blocking_duration_sec
FROM information_schema.INNODB_LOCK_WAITS w
JOIN information_schema.INNODB_TRX b ON w.blocking_trx_id  = b.trx_id
JOIN information_schema.INNODB_TRX r ON w.requesting_trx_id = r.trx_id;

結果には、待機中のセッションとブロッカーの両方について、スレッド ID と現在実行中のクエリが表示されます。blocking_duration_sec を使用して、ブロッカーがロックを保持している時間を把握できます。

より詳細な情報を確認するには、各テーブルを個別にクエリします。

-- アクティブなトランザクションすべて
SELECT * FROM information_schema.INNODB_TRX;

-- ロックを待機中のトランザクション
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- 現在保持されているロック
SELECT * FROM information_schema.INNODB_LOCKS;

ブロッカーとは、他のセッションでのデータ操作言語 (DML) 操作を妨げるロックを保持しているセッションです。ブロッカーのトランザクションロールバックが許容される場合は、ステップ 2 で取得したスレッド ID を使用して、次のようにしてセッションを終了します。
```
KILL <blocking_thread_id>;
```
これにより、ブロッカーのオープントランザクションがロールバックされ、ロックがリリースされるため、待機中のセッションが処理を続行できるようになります。
問題が継続する場合や根本原因を特定できない場合は、「ロックブロッキング」をご参照ください。ロックブロッキング統計ページを使用して、長期的なブロッカーを特定し、セッションの詳細を確認できます。

再発防止

ブロッキングセッションの終了は緊急時の対応策です。ロック待機タイムアウトの再発を防ぐには、根本原因に対処する必要があります。

トランザクションを速やかにコミットまたはロールバックする：トランザクションは短く保ちます。アプリケーションロジックやユーザー入力を待つ間にトランザクションをオープンしたままにしないでください。
大規模なバッチ操作を小さなチャンクに分割する：1 回の大規模な UPDATE または DELETE の代わりに、行を小さなバッチで処理し、各バッチ後にコミットしてロックを段階的にリリースします。
適切なインデックスを追加する：インデックスのない列に対するテーブルスキャンは、必要な以上の行ロックを取得します。DML ステートメントの WHERE 句で使用される列にインデックスを追加して、ロックされる行数を削減します。
アプリケーション層でアイドル接続を検出する：接続プールのタイムアウトを設定し、TCP キープアライブを有効にして、データベースが切断されたクライアントを検出し、そのロックを速やかにリリースできるようにします。

適用範囲

ApsaraDB RDS for MySQL