【MySQL】行ロックのまとめ
背景
最近、業務案件において排他制御を適切に実装できておらず、データの整合性に問題が生じてしまいとても痛い目に遭いました。
戒めと理解度向上のため、MySQL(InnoDB)の行ロックについてざっくりとまとめていきます。
バージョンはMySQL8.0です。
ロックの種類
特に重要なロックの種類について解説します。
共有ロック（Shared Lock）
共有ロックは、複数のトランザクションが同じデータを読み取ることを許可しますが、そのデータの更新はロックが解除されるまで行えません。
主な用途: データの整合性を保ちながら、複数の読み取り操作を許容する場合。
START TRANSACTION;
SELECT * FROM users WHERE id = 1 FOR SHARE;
-- 他のトランザクションも同じ行を共有ロックで読み取れるが、更新はできない。
COMMIT;
排他ロック（Exclusive Lock）
排他ロックは、データに対して読み取りも更新も他のトランザクションから行えないようにするロックです。
排他制御で主に使うのはこれかなと思います。
主な用途: データを更新中に他のトランザクションがそのデータを操作しないようにしたい場合。
START TRANSACTION;
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- ロックした行を更新
UPDATE users SET email = 'new_email@example.com' WHERE id = 1;
COMMIT;
ロックの仕組み
MySQLのロックの動作を理解する上で、InnoDBストレージエンジンの特性が重要になります。以下に解説していきます。
1. 行ロックの基本動作とスコープ範囲
InnoDBは、インデックスを利用してロックを実現します。インデックスが存在しない場合、テーブル全体のロック（意図しない広範囲ロック）が発生する可能性があります。
インデックスありの場合: 特定の行だけがロックされる。
インデックスなしの場合: テーブル全体がロックされる（注意が必要）。
例：
START TRANSACTION;
SELECT * FROM users WHERE name = 'John' FOR UPDATE;
-- name列にインデックスがない場合、テーブル全体がロックされる。
COMMIT;
対策：
インデックスを追加することで、意図しない広範囲ロックを防ぎます。
CREATE INDEX idx_name ON users(name);
また、行ロックはトランザクション内で明示的に設定され、トランザクションが終了する（COMMIT または ROLLBACK）まで保持されます。
2. デッドロックの可能性
複数のトランザクションが互いに必要なロックを待つことでデッドロックが発生します。MySQLはデッドロックを検出し、いずれかのトランザクションを強制的に終了させます。
例：
-- トランザクション1
START TRANSACTION;
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- 同時にトランザクション2
START TRANSACTION;
SELECT * FROM users WHERE id = 2 FOR UPDATE;
-- その後、それぞれがもう一方の行を更新しようとする
-- トランザクション1: UPDATE users SET name = 'Alice' WHERE id = 2;
-- トランザクション2: UPDATE users SET name = 'Bob' WHERE id = 1;
-- → デッドロック発生
対策：
トランザクション内でロックの取得順序を統一します。例えば、アプリケーション側のロジックでid の小さい順にロックを取得するポリシーを設定します。
-- トランザクション1
START TRANSACTION;
SELECT * FROM users WHERE id = 1 FOR UPDATE;
SELECT * FROM users WHERE id = 2 FOR UPDATE;
-- トランザクション2も同じ順序で処理する
START TRANSACTION;
SELECT * FROM users WHERE id = 1 FOR UPDATE;
SELECT * FROM users WHERE id = 2 FOR UPDATE;
3. 行ロックの確認方法
performance_schema.data_locksテーブルを利用してInnoDBトランザクションのロック状態を詳細に確認することができます。以下の項目が含まれています。
| カラム名 | 説明 |
|---|---|
| ENGINE | ロックが適用されているストレージエンジン（通常はInnoDB）。 |
| ENGINE_LOCK_ID | ストレージエンジン固有のロックID。 |
| LOCK_TYPE | ロックの種類（例: RECORD、TABLE、GAP）。 |
| LOCK_MODE | ロックモード（例: S（共有ロック）、X（排他ロック）など）。 |
| LOCK_STATUS | ロックの状態（GRANTED または WAITING）。 |
| OBJECT_SCHEMA | ロック対象のデータベース名。 |
| OBJECT_NAME | ロック対象のテーブル名。 |
| OBJECT_INSTANCE_BEGIN | ロック対象オブジェクトのメモリアドレス。 |
| INDEX_NAME | ロックがかかっているインデックス名。 |
| LOCK_DATA | ロック対象のデータ（主キーや行情報など）。 |
行ロックの確認方法については、こちらの記事がとても参考になりました。
4. ギャップロック
ギャップロックは、範囲条件のクエリで発生し、指定された範囲に含まれる行やその間の「ギャップ」に対してもロックをかける仕組みです。
主に REPEATABLE READ トランザクション分離レベル で発生し、ファントムリード（新規行の挿入などによるデータの不整合）を防止します。
例：
下記をテストデータとして解説します。
INSERT INTO users (id, name, email) VALUES
(1, 'Alice', 'alice@example.com'),
(3, 'Bob', 'bob@example.com'),
(5, 'Charlie', 'charlie@example.com');
トランザクション1で以下のクエリを実行して、idが3以上5以下の範囲にギャップロックをかけます。
-- トランザクション1
START TRANSACTION;
SELECT * FROM users WHERE id > 2 AND id < 5 FOR UPDATE;
別のセッション（トランザクション2）で、次のクエリを実行します。
-- トランザクション2
START TRANSACTION;
INSERT INTO users (id, name, email) VALUES (4, 'David', 'david@example.com');
この操作は、トランザクション1のギャップロックによってブロックされます。
トランザクション1が完了（コミットまたはロールバック）しない限り、id = 4 の挿入は待機状態になります。
注意点：
-
不要な範囲ロックの拡大
ギャップロックは条件に該当しない行間にも適用されるため、意図せず範囲が広がり、デッドロックのリスクが高まることがあります。 -
パフォーマンスへの影響
ロック範囲が広がると、他のトランザクションの操作を遅らせる可能性があります。
ギャップロックについては下記記事がとても参考になりました。
まとめ
MySQL（InnoDB）の行ロックについてざっくりとまとめてみました。記載した以外にもロックの種類は存在するようなので、引き続きキャッチアップしていきたいです。
今まで私の参画した案件では新規開発が多く、納品と同時に案件が異動になることから、本番運用上で発生する排他制御などの問題に出くわすことがあまりありませんでした。開発スケジュールがパツパツすぎて負荷テストやらないケースも多く…。
その意味ではかなり良い経験をできた（本当に大変でしたが…😭）と思っています。新規開発も楽しいのですが、大規模データに触る機会を得たい気持ちがあり、今後は運用開発の経験をもっと積んでいきたいなーと考えている今日この頃です。
Discussion