ロック挙動を確認してみよう

前提

• MySQL 8.0 のロック挙動について、仕様と実際の動作を確認してみた

仕様

分離具合

分離レベル	ダーティリード	ファジーリード	ファントムリード	ロストアップデート
READ UNCOMMITTED	o	o	o	o
READ COMMITTED	x	o	o	o
REPEATABLE READ	x	x	x	o
SERIALIZABLE	x	x	x	x

• 分離レベル
  • ACID特性のうち分離性に関する概念であり、ANSI/ISO SQL標準で定められている仕様
    • トランザクションが互いにどれだけ独立しているかを示す
    • あくまで仕様なので、各ストレージエンジンで実装・動作が異なることがある
      • 例えば、InnoDB の REPEATABLE READ はファントムリードを防ぐ実装になっている
• 各種現象
  • ダーティリード
    • 他トランザクションの未コミットの変更が、自分のトランザクションから参照できる現象
  • ファジーリード
    • 他トランザクションによって更新/削除された結果が、自分のトランザクションから参照できる現象
  • ファントムリード
    • 他トランザクションによって挿入されたレコードが、自分のトランザクションから参照できる現象
  • ロストアップデート
    • 先行トランザクションの更新内容が、後続トランザクションの更新によって失われる現象

実現方法

• 上記の分離具合を実現するための仕組みとして、MVCC とロックがある

MVCC

• 正式名は、Multi-VersionConcurrency Control
• ある時点のスナップショットデータを使うことで、参照データの一貫性を実現する
  • 他トランザクションが更新したデータについて、スナップショット取得時のデータを参照できる
• 後述するロックと比較して、並列性を高められる
  • MVCC がない場合、過去バージョンを参照したい時にロックを取る必要があり、その分ロック解放待ちが増えてしまう

ロック

• リソースへのアクセスを制御するための概念
  • ロックを取得することでリソースにアクセスでき、他トランザクションはロック解放を待つ
• 前提として、テーブルは B+ 木 で表現されており、その仕組みの上でロックが行われている
  • 詳細については、インデックス構造を参照すること
• インデックスレコードに対してロックを実施する
  • レコードロックは存在しない
    • インデックスが無いテーブルであっても、内部的に作成したインデックスをロックしている
• 走査範囲をロックする
  • 検索条件に合致したレコード ではなく、インデックス上で走査したレコード に対してロックを取得する
    • 検索時にロックを取得した後、条件に合致するレコードを検索結果として返す
      • 例えば、複数条件 A,B で検索を行った場合、
        • 最終的に合致するのは、両条件を満たすレコードだが、
        • ロック対象となるのは、条件 A のみ満たすレコードも含まれる
          • オプティマイザが条件 A → B で検索することにした場合
    • 後述するように、インデックスの種類や検索方法、実行計画等によって、ロック範囲が異なる
• ロックの種類
  • インテンションロック
    • 対象トランザクションが必要とするロック種類（共有 or 排他）を示すテーブルロックのこと
      • インテンション共有ロック or インテンション排他ロック
    • 本ロックの目的は、既にロックしている or これからロックしようとしていること を示すこと
      • （正直、これが何で必要なのか理解できていない、、）
    • インテンションロック同士はロック競合しない
  • レコードロック
    • インデックスレコードのロックのこと
      • 具体的には、クラスタインデックスとセカンダリインデックス
  • ギャップロック
    • インデックスレコード間の範囲をロックすること
      • 先頭のインデックスレコードの前/末尾のインデックスレコードの後の範囲をロックすることも含む
    • ファントムリードを防ぐことが出来る
      • ロック範囲内に存在しない値の場合、他トランザクションはそれら値を挿入できなくなる
    • ギャップロック同士はロック競合が起きない
      • 他トランザクションがロック範囲の値を挿入できないことに変わりはないため
  • ネクストキーロック
    • レコードロックとその手前のギャップロックの組み合わせのこと
    • 末尾以降のファントムリードを防ぐ場合は、擬似レコードがネクストキーロックされる
  • その他
    • FOREIGN KEY 制約によるロック
      • 子レコードが挿入/更新/削除されると、親レコードに共有レコードロックが行われる
        • 制約条件をチェックするため
    • etc...

※ インデックス構造

インデックスはどのように構成されているのか

※ 引用元: MySQL with InnoDB のインデックスの基礎知識とありがちな間違い

• 前提
  • primary-key をクラスタインデックス、それ以外の index はセカンダリインデックスと呼ぶ
  • いずれも内部ノードは、インデックス値を持つ
  • クラスタインデックスの葉ノードは、実データ、他葉ノードへのポインタを持つ
  • セカンダリインデックスの葉ノードは primary-key 値、他葉ノードへのポインタを持つ
  • 葉ノードはポインタで繋がっているため、効率的に範囲検索を行える
• 検索の流れ
  • クラスタインデックスで検索する場合、クラスタインデックスを走査して実データを取得する
  • セカンダリインデックスで検索する場合、セカンダリインデックスを走査して primary-key を取得後、プライマリインデックスを走査する必要がある
  • 非インデックスで検索する場合、上記インデックス経由ではなく、実データを走査する必要がある

MVCC・ロック戦略

上記の仕組みがどのように活用されているのか

分離レベル	SELECT	FOR SHARE	FOR UPDATE・DML
READ COMMITTED	文単位のMVCC	共有レコードロック	排他レコードロック
REPEATABLE READ	トランザクション単位のMVCC	共有レコード/ギャップ/ネクストキーロック	排他レコード/ギャップ/ネクストキーロック
SERIALIZABLE	REPEATABLE READ の FOR SHARE に準拠	〃	〃

	共有ロック	排他ロック
共有ロック	o	x
排他ロック	x	x

REPEATABLE READ

• MySQLのデフォルト

ロック範囲

• プライマリインデックスに対する等価検索の場合は、レコードロックのみ
• それ以外の場合は、いくつかの条件によって、ロック範囲が変わる
  • 詳細は、MySQLのロックについてが分かりやすい
    • 基本的には、走査範囲に対してギャップロックやネクストキーロックが行われる
      • ファントムリードを防ぐため
  • 具体的な影響要素
    • インデックス
      • 有無
        • 非インデックスの場合、全レコードがロックされる
      • 種類
        • クラスタインデックス
        • セカンダリインデックス
    • 検索方法
      • 等価検索
      • IN検索
      • 範囲検索（BETWEEN）
    • その他
      • 空振り（検索条件に合致するレコードがない場合）
      • 実行計画（走査したレコードに対してロックを取得するため）
  • 走査したレコードのうち、検索条件に合致しないレコードのロックは、トランザクション完了まで維持される

MVCC

• トランザクションを開始して最初の非ロック参照クエリを発行したタイミングで、データベースのスナップショットを取る
  • 以降の非ロック参照クエリは、そのスナップショットに対して実行される
  • スナップショットが取得されるのは、トランザクション開始時 ではなく 最初の非ロック参照クエリ発行時 になる
    • （意図せず古いデータを取得しないように）
• ロック有無によって挙動が変わる
  • ロックなし参照の場合、上述の通り、一貫性参照になる
  • ロックありの場合、その時点の最新データを読み取る
    • 具体的には、ロッキングリード（FOR SHARE、FOR UPDATE）や DML
    • 他トランザクションで変更がコミットされている場合、変更後の最新データが取得される

READ COMMITTED

• その他 PostgreSQL、Oracle、SQL Server 等のデフォルト

ロック範囲

• ファントムリードを許容するため、基本的にギャップロックは取得されない
  • 一部例外はあるが省略
• 走査したレコードのうち、検索条件に合致しないレコードのロックは、SQL 完了時に解放される

MVCC

• クエリ発行毎にデータベースのスナップショットを取り直すため、他トランザクションの変更が参照される
  • ロック有無によって挙動が変わらない

参考

• 公式ドキュメント
• MySQLのロックについて
• MySQLのInnoDBのロック挙動調査

動作確認

• 上記の知識を踏まえて、実際の動作を確認してみる
  • 分離レベルは、READ COMMITTED と REPEATABLE READ
    • 利用頻度の多いと予想されるものを抜粋
• data_locks テーブルを確認する
  • 行ロックを保持している、または行ロックを取得しようと待機している状態を確認できる
  • 一部項目について補足
    • LOCK_MODE
      • ロックの種類・範囲を示す
        • 共有（S）、排他（X）
        • レコードロック（REC_NOT_GAP）、ギャップロック（GAP⁠）⁠、ネクストキーロック（なし）、
      • 例えば、
        • X: 排他ネクストキーロック
        • S,REC_NOT_GAP: 共有レコードロック
        • X,GAP: 排他ギャップロック
    • LOCK_STATUS
      • ロックの状態を示す
        • GRANTEDはロック保持、WAITINGはロック待機
    • LOCK_DATA
      • ロック取得した行の値を示す
        • プライマリキーの場合、プライマリキーの値
        • セカンダリキーの場合、セカンダリーキーの値とプライマリキーの値

実験内容

• 以下 tests テーブルの更新時のロック状況を確認する
  • 検索条件はセカンダリインデックス

desc tests;
+-------+-----------------+------+-----+---------+----------------+
| Field | Type            | Null | Key | Default | Extra          |
+-------+-----------------+------+-----+---------+----------------+
| id    | bigint unsigned | NO   | PRI | NULL    | auto_increment |
| id2   | int             | YES  | MUL | NULL    |                |
| str   | varchar(255)    | YES  |     | NULL    |                |
+-------+-----------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)



select * from tests;
+----+------+-----------+
| id | id2  | str       |
+----+------+-----------+
|  1 |    1 | test      |
|  2 |    3 | テスト    |
+----+------+-----------+
2 rows in set (0.00 sec)

REPEATABLE READ

SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;
+--------------------------------+-------------------------+
| @@GLOBAL.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ                | REPEATABLE-READ         |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)



begin;
Query OK, 0 rows affected (0.00 sec)



update tests set str = 'テスト2' where id2 = 3 ;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0



SELECT THREAD_ID, EVENT_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks\G
-- 4種類のロックを取得していることが分かる
-- 全て同一セッション、かつロック待ちはない
*************************** 1. row ***************************
  THREAD_ID: 181
   EVENT_ID: 119
OBJECT_NAME: tests
 INDEX_NAME: NULL
  LOCK_TYPE: TABLE
  LOCK_MODE: IX -- 排他インテンションロック
LOCK_STATUS: GRANTED
  LOCK_DATA: NULL
*************************** 2. row ***************************
  THREAD_ID: 181
   EVENT_ID: 119
OBJECT_NAME: tests
 INDEX_NAME: id2 -- id2インデックス
  LOCK_TYPE: RECORD
  LOCK_MODE: X -- 排他ネクストキーロック
LOCK_STATUS: GRANTED
  LOCK_DATA: supremum pseudo-record -- インデックスの最大値を超える疑似値
-- ※ 他トランザクションから「id2値が3~疑似値の行」が挿入されることを防ぐ
*************************** 3. row ***************************
  THREAD_ID: 181
   EVENT_ID: 119
OBJECT_NAME: tests
 INDEX_NAME: id2 -- id2インデックス
  LOCK_TYPE: RECORD
  LOCK_MODE: X -- 排他ネクストキーロック
LOCK_STATUS: GRANTED
  LOCK_DATA: 3, 2 -- id2値が3,id値が2
-- ※ 他トランザクションから「id2値が1~3の行」が挿入されることを防ぐ
*************************** 4. row ***************************
  THREAD_ID: 181
   EVENT_ID: 119
OBJECT_NAME: tests
 INDEX_NAME: PRIMARY -- idインデックス
  LOCK_TYPE: RECORD
  LOCK_MODE: X,REC_NOT_GAP -- 排他レコードロック
LOCK_STATUS: GRANTED
  LOCK_DATA: 2 -- id値が2
4 rows in set (0.00 sec)

• ファントムリードを防ぐため、セカンダリインデックスに対して、ネクストキーロックを取っている
• 上記でロック対象を特定後、対応するプライマリインデックスに対して、レコードロックを取っている

READ COMMITTED

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;


SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;
+--------------------------------+-------------------------+
| @@GLOBAL.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ                | READ-COMMITTED          |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)



begin;
Query OK, 0 rows affected (0.00 sec)



update tests set str = 'テスト2' where id2 = 3 ;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0



SELECT THREAD_ID, EVENT_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks\G
-- 3種類のロックを取得していることが分かる
-- 全て同一セッション、かつロック待ちはない
*************************** 1. row ***************************
  THREAD_ID: 180
   EVENT_ID: 127
OBJECT_NAME: tests
 INDEX_NAME: NULL
  LOCK_TYPE: TABLE
  LOCK_MODE: IX -- 排他インテンションロック
LOCK_STATUS: GRANTED
  LOCK_DATA: NULL
*************************** 2. row ***************************
  THREAD_ID: 180
   EVENT_ID: 127
OBJECT_NAME: tests
 INDEX_NAME: id2 -- id2インデックス
  LOCK_TYPE: RECORD
  LOCK_MODE: X,REC_NOT_GAP -- 排他レコードロック
LOCK_STATUS: GRANTED
  LOCK_DATA: 3, 2 -- id2値が3,id値が2
*************************** 3. row ***************************
  THREAD_ID: 180
   EVENT_ID: 127
OBJECT_NAME: tests
 INDEX_NAME: PRIMARY -- idインデックス
  LOCK_TYPE: RECORD
  LOCK_MODE: X,REC_NOT_GAP -- 排他レコードロック
LOCK_STATUS: GRANTED
  LOCK_DATA: 2 -- id値が2
3 rows in set (0.01 sec)

• ファントムリードを許容しているため、セカンダリインデックスに対して、レコードロックを取っている
• 上記でロック対象を特定後、対応するプライマリインデックスに対して、レコードロックを取っている

参考

• 公式ドキュメント
• InnoDBの行ロック状態を確認する

終わりに

本記事の知識をベースにして、次はロック競合時の調査記事を書く予定