MySQLの行ロックで発生するギャップロックとネクストキーロックの解決策


 MySQLでテーブルのレコードを更新するために、SELECT ~ FOR UPDATE を実行して、対象レコードを行ロックしました。このとき、対象レコードの次のレコードも同時に行ロックされていることがわかりました。

 私は Oracle データベース出身者であったため、SELECT ~ FOR UPDATE すれば、対象レコードのみを行ロックできると考えていました。

 なぜだろう?と思い、いろいろと調べて試してみた結果、MySQLには、ネクストキーロックという概念があり、その事象であることがわかりました。



 では、なぜ、ネクストキーロックがあるのでしょうか?


 まずは、MySQL における行ロックの種類を理解する必要があります。
  1. レコードロック
  2. ギャップロック
  3. ネクストキーロック
3つのロックについては、以下のサイトの説明がわかりやすいです。
 InnoDBにおけるロックの種類 - Sojiro’s Blog

 上記のサイトで説明されている通り、ネクストキーロックが発生する原因は、 SELECT ~ FOR UPDATE の WHERE 句で範囲をしていたためでした。


 よって、行ロックの根本的な解決策は以下の通り。

  • WHERE 句では、ユニークインデックスで使用するカラムを指定する。
  • WHERE 句では、範囲の指定を行わない。
  • 存在しないレコードを行ロックしない


 ひとまず、こんな解決策となりました。


 しかしながら、なぜ、MySQL はこのようなロックの種類を用意しているのでしょうか。


 MySQL のトランザクション分離レベルに起因するようです。Oracle と MySQL のデータベースでは、トランザクション分離レベルのデフォルトが異なります。

データベーストランザクション分離レベルのデフォルト
OracleREAD-COMMITED
MySQLREPEATABLE-READ

トランザクション分離レベルの説明については、以下のサイトが分かりやすいです。
MySQLでトランザクションの4つの分離レベルを試す


 ギャップロックとネクストキーロックは、トランザクション分離レベルの「REPEATABLE-READ」を実現するため(ファジーリードとファントムリードを解決するため)に MySQL が行っているというのが私の認識です。この部分については勉強不足のため、補足すべき内容がたくさんありそうです。


 そこで、トランザクション分離レベルを「READ-COMMITED」に変更した結果、行ロック時に WHERE 句で範囲を指定しても、ネクストキーロックが発生しませんでした。

 データベース設計、アプリケーション設計によって、解決策が異なるかと思います。


以上

最新

【PHP】スーパーグローバル変数 $_GET $_POST $_REQUEST

スーパーグローバル変数 $_REQUESTは、HTTPメソッドがGETとPOSTのいずれの場合もデータを取得することができる。 $_REQUEST['xxx'] $_GET['xxx'] $_POST['xxx'] ...