MySQL トランザクションとロックの深掘り：隔離レベル・鎖機構・インデックス戦略

トランザクションの隔離レベル：動作原理と実務上の影響

SQL標準では4段階の隔離レベルが定義されており、それぞれがデータの一貫性と並行処理性能のトレードオフを表現しています。MySQL（InnoDB）では、デフォルトでREPEATABLE READが採用されていますが、その挙動は単純な「再読可能」を超えて、MVCCと特殊なロック機構によって拡張されています。

1. READ UNCOMMITTED — 最低限の制約

他のトランザクションがコミットしていない変更も可視となるため、「ダーティリード」が発生します。例えば、トランザクションAがUPDATE accounts SET balance = 5000 WHERE id = 101を実行した直後、トランザクションBがSELECT balance FROM accounts WHERE id = 101を実行すると、未確定の値が返されます。Aが後にロールバックすれば、Bは一時的に不正な状態を参照したことになります。

2. READ COMMITTED — コミット済みのみ可視

他トランザクションの変更は、そのコミット後にのみ可視化されます。このレベルではダーティリードは防げますが、「ノンリピータブルリード」が発生します。同一トランザクション内で2度同じSELECTを実行しても、中間で他トランザクションが更新した場合、結果が異なります。PostgreSQLやOracleではこのレベルがデフォルトですが、MySQLでは非推奨です。

3. REPEATABLE READ — InnoDBのデフォルト

トランザクション開始時のスナップショットに基づき、一貫した読み取りが保証されます。MVCCにより、各クエリはトランザクション開始時点のデータビューを参照します。ただし、範囲検索中に他トランザクションが新規レコードを挿入すると「ファントムリード」が発生する可能性があります。InnoDBでは、これをギャップロックとネクストキー・ロックで防止しています。

4. SERIALIZABLE — 完全な直列化

すべてのSELECT文が暗黙的にFOR UPDATE相当の排他ロックを取得します。これにより、読み取りも書き込みも完全に直列化され、すべての不整合が排除されますが、同時実行性は著しく低下します。実運用では極めて稀にしか使用されません。

隔離レベルの確認と設定

-- 現在のセッションの隔離レベルを確認
SELECT @@transaction_isolation;

-- グローバル設定（次回接続以降有効）
SET GLOBAL transaction_isolation = 'READ-COMMITTED';

-- セッション内での変更（即時適用）
SET SESSION transaction_isolation = 'REPEATABLE-READ';

MySQLにおけるロックの種類と応用

InnoDBのロック機構は、粒度と用途に応じて多層的に設計されています。理解の鍵は、「なぜこのロックが必要か？」ではなく、「このロックがどんな競合を回避しているか？」という視点です。

行ロック（Row-Level Lock）とインデックス依存性

InnoDBの行ロックは物理的な行ではなく、インデックスエントリに対して行われます。主キーまたはユニークインデックスによる等価検索では、対象行のみがロックされます。一方、非インデックスカラムや範囲条件（例：WHERE status = 'pending'）では、全行スキャンとなり、結果としてテーブル全体がロックされることがあります。

ギャップロック（Gap Lock）とネクストキー・ロック（Next-Key Lock）

これらは「ファントムリード」を防ぐために導入された概念です。
- ギャップロック：あるインデックス値と次の値の間の「隙間」をロック（例：(10, 20)）。INSERTをブロックしますが、既存レコードの更新は許可。
- ネクストキー・ロック：ギャップロック＋対象レコードの排他ロックの組み合わせ（例：(10, 20]）。InnoDBのデフォルト範囲ロック方式です。

-- age=24のレコードをロック → (18,24] と (24,30] の両方のギャップがロックされる
START TRANSACTION;
SELECT * FROM employees WHERE age = 24 FOR UPDATE;

-- この状態で、age=26 のINSERTはブロックされる
INSERT INTO employees (name, age) VALUES ('田中', 26); -- 待機

意図ロック（Intention Lock）：テーブルロックとの協調

行ロックとテーブルロックが干渉しないよう、InnoDBは自動的に意図ロックを付与します。
- INTENTION SHARED (IS)：行に対する共有ロック（LOCK IN SHARE MODE）を取る予定
- INTENTION EXCLUSIVE (IX)：行に対する排他ロック（FOR UPDATE, UPDATE, DELETE）を取る予定
これらのロックは互いにブロックせず、テーブルレベルのLOCK TABLES ... WRITEを試行する際に高速に衝突判定できます。

楽観的制御 vs 悲観的制御

楽観的制御は、競合が稀である前提で、更新時にバージョンチェックを行う方式です。以下は典型的な実装例です：

CREATE TABLE inventory (
  id BIGINT PRIMARY KEY,
  item_name VARCHAR(128),
  stock_count INT NOT NULL DEFAULT 0,
  version INT NOT NULL DEFAULT 0
);

-- 更新前：version=5 を確認
SELECT stock_count, version FROM inventory WHERE id = 123;

-- 更新（versionが一致する場合のみ成功）
UPDATE inventory 
SET stock_count = 99, version = version + 1 
WHERE id = 123 AND version = 5;

悲観的制御は、アクセス時点で明示的にロックを取得し、競合を事前に排除します。高頻度更新系の金融アプリケーションなどでは必須です：

START TRANSACTION;
-- 在庫レコードを排他ロック
SELECT stock_count FROM inventory WHERE id = 123 FOR UPDATE;

-- 在庫チェックと更新をアトミックに実行
UPDATE inventory SET stock_count = stock_count - 1 WHERE id = 123;
COMMIT;

B+ツリー vs ハッシュインデックス：選択の根拠

MySQL 8.0以降、ハッシュインデックスはメモリストレージエンジン（MEMORY）専用であり、InnoDBでは内部的にのみ使用されます。実務上はB+ツリーがデフォルトかつ最適な選択です。

B+ツリー：バランスの取れた木構造で、範囲検索（BETWEEN, >）、ソート（ORDER BY）、部分一致（LIKE 'abc%'）を効率的にサポート。すべての葉ノードがリンクで接続されているため、フルスキャンも高速です。
ハッシュインデックス：O(1)の等価検索が可能ですが、範囲・ソート・部分一致には一切対応しません。また、ハッシュ衝突時にチェインが発生し、パフォーマンスが劣化するリスクがあります。

したがって、WHERE user_id = ?のような単一キー検索だけならハッシュが有利ですが、現実のクエリは複雑な条件を含むことが多く、B+ツリーの柔軟性が不可欠です。

タグ: MySQL InnoDB Transaction isolation-level locking

6月27日 00:22 投稿

異端開発室