概要

MySQLのロックについてまとめる。 MySQLのバージョンは8系を想定する。

検証環境

検証に使う環境はdocker-composeで用意した。（1コンテナだけなのでcomposeを使わなくも良いのだが..）

docker-compose.yml

1_schema.sql

docker compose upでお手元にMySQL8系のコンテナが用意できる。

ロック

内部レベルロック

MySQLにおける排他制御の手法としては、行レベルロックとテーブルレベルロックがある。

cf. dev.mysql.com - 8.11.1 内部ロック方法

行レベルロック
- テーブル内の個々の行を対象としたロック
- ロック対象が狭いのでロックの競合、ロールバックする変更が少なくなる
- 1つの行を長時間ロック可能
テーブルレベルロック
- テーブルを対象としたロック
- 必要になるメモリーが比較的に少ない（行ロックはロックされた行また行のグループごとにメモリーが必要）
- 単一のロックだけが必要となるため、テーブルの大部分を対象に使用する場合は高速
- データの大部分を対象にGROUP BYを頻繁に実行する場合やテーブル全体を頻繁にスキャンする場合は高速

InnoDBロック

cf. dev.mysql.com - 15.7.1 InnoDB ロック

共有（READ）ロック

共有ロックは、データのREADは可能だが、WRITEはできないロック。Shared lock（IS）。

検証

TX1でトランザクションを開始、共有ロックをかける

TX2でトランザクションを開始、WRITEを行う

TX1がCOMMITするまでTX2の更新はロックされる。

占有（排他・WRITE）ロック

排他ロックは、データのREADもWRITEもできないロック。Exclusive lock（IX）。

検証

TX1にてトランザクションを開始、占有ロックをかける

TX2でトランザクションを開始、READ、WRITEを行う

TX1のロックが解放されるまでTX2ではREAD（単純なSELECT以外）やWRITEができないことが確認できる。

インテンションロック

トランザクションがテーブルの行に必要とするロックタイプ（共有または排他）を示すテーブルレベルのロック。行ロックとテーブルロックの共存をサポートするために用意されている。

インテンションロックには、

インテンション共有ロック
インテンション排他ロック

の2つがある。

検証

SQLで明示的に操作できるものではなく、基本的にはデータベース内部で管理されるものであるので、検証は割愛。

いくつか検証パターンがあるが、以下の記事で色々と検証されている。

cf. qiita.com - MySQLのロックについて公式ドキュメントを読みながら動作検証してみた〜行レベルロック: インテンションロック〜

レコードロック

インデックスレコードのロック。インデックスレコードとはクラスタインデックスとセカンダリインデックスのこと。スキャンしたインデックスに対してロックする。

検証

データベースの内部的な動作であるため割愛。

ギャップロック

インデックスレコード間のギャップのロック。または、インデックスレコードの前または後ろのギャップのロック。

検証

TX1でトランザクション開始、READを行う

TX2でトランザクション開始、WRITEを行う

行単位のロックかと思いきや、範囲でロックされているのが確認できる。

ネクストキーロック

インデックスレコードのレコードロックとインデックスレコードの前のギャップのギャップロックの組み合わせ。

検証

TX1でトランザクション開始、READを行う

TX2でトランザクション開始、WRITEを行う

idが5未満の行だけでなく、末尾のインデックス値を持つ行の後のギャップもロックされることが確認できる。

インテンションロックの挿入

行の挿入前のINSERTによって設定されるギャップロックのタイプ。INSERTのインテンションロック。

検証

データベースの内部的な動作であるため割愛。

こちらの記事で検証されているので参照。 cf. MySQLのロックについて公式ドキュメントを読みながら動作検証してみた〜レコードロック / ギャップロック / ネクストキーロック / 他〜

AUTO-INCロック

AUTO_INCREMENTカラムを含むテーブルに挿入されるトランザクションによって取得されるテーブルロック。 TX1でのトランザクションでINSERTするためにAUTO_INCREMENTの値を取得している間はTX2でのAUTO_INCREMENTの値を取得できないようするロック。

検証

内部的な動作である&再現方法が分からなかったので割愛。

空間インデックスの述語ロック

これはドキュメント参照。（空間インデックスに触りなれていないのものあってイマイチ分からなかった。。。）

cf. 空間インデックスの述語ロック

ロックの確認方法

ロックは以下のクエリで確認することができる。

デッドロックを確認するには、SHOW ENGINE INNODB STATUSを実行し、LATEST DETECTED DEADLOCKと記載されている部分を確認する。

まとめ

MySQLには明示的・暗黙的にロックされるパターンがある。

何が（行なのかテーブルなのか）対象なのか、範囲はどこまでなのかといったことにまずは目を向けると良さそう。