環境
- MySQL 8.0.32
症状
通常、selectクエリの実行中でも同じテーブルに対して別のselectクエリを実行することができる。ところが、selectクエリの実行中に同じテーブルに対してupdateまたはinsertクエリを実行しようとすると、それ以降そのテーブルに対してはselectクエリも実行することができなくなる。
以下の手順で再現できる。
- MyISAMのテーブルに対して時間のかかるselectクエリを実行する。
- 1のクエリが完了する前に、同じテーブルに対してupdateまたはinsertクエリを実行する。このクエリはテーブルロック待ちになり、1のクエリが完了するまで実行されない。
- 1のクエリが完了する前に、さらに同じテーブルに対してselectクエリを実行する。このクエリもテーブルロック待ちになり、2のクエリが完了するまで実行されない。
以降、このテーブルに対する全てのクエリがテーブルロック待ちになる。1のクエリが長時間終わらない場合、場合によってはDBの接続数が急増し、アプリがDBに接続できなくなる状態に陥る。
この挙動は以下に記載がある。
WRITE ロックは通常、更新ができるだけ早く処理されるように、READ ロックより高い優先度を持っています。 つまり、あるセッションが READ ロックを取得したあと、別のセッションが WRITE ロックを要求した場合は、WRITE ロックを要求したセッションがロックを取得して解放するまで、以降の READ ロック要求が待たされます。
解決方法
- MyISAMをやめてInnoDBに変換する。InnoDBでは行ロックになるため、このような問題は発生しなくなる。
- low_priority_updates 変数に1をセットする。writeロックよりもreadロックが優先されるようになる。
- max_write_lock_count 変数に低い値を設定する。待たされている insert, update クエリがここで指定した数を超えた場合、selectが実行されるようになる。
その他の解決方法については以下に記載がある。どうしてもMyISAMを使用する場合は更新のないテーブルに対してのみ使用すること。
