トランザクションとロック
MySQLのトランザクション分離レベルのデフォルトはREPEATABLE READです。
この分離レベルは同じ行を同時に更新しようとした場合に、ロックを取らないとロストアップデートと呼ばれる更新異常が起こる可能性があります。
サンプルデータ
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
pt INT NOT NULL
) ENGINE = InnoDB;
INSERT INTO users (id, name, pt) VALUES
(1, '佐藤', 10),
(2, '鈴木', 20),
(4, '田中', 40),
(5, '伊藤', 50);
| id | name | pt |
|---|---|---|
| 1 | 佐藤 | 10 |
| 2 | 鈴木 | 20 |
| 4 | 田中 | 40 |
| 5 | 伊藤 | 50 |
（id=3は欠番）
このようなユーザテーブルからptの値を読み取ってアプリケーション側でカウントアップしてDBに書き戻す、という場合を考えます。
今、2つのトランザクションAとBがほぼ同時にid=1のpt値を更新したとします。
ロストアップデートが起こる例
A > START TRANSACTION;
A > SELECT pt FROM users WHERE id = 1; -- Aがpt=10を読み取る
B > START TRANSACTION;
B > SELECT pt FROM users WHERE id = 1; -- Bがpt=10を読み取る
B > UPDATE users SET pt = 11 WHERE id = 1; -- Bがpt=11を書き戻す
B > COMMIT;
A > UPDATE users SET pt = 11 WHERE id = 1; -- Aがpt=11を書き戻す
A > COMMIT;
この場合、本来であればid=1のptは最終的に12になるべきところが、Aの更新がBによって上書きされたことによりptが11となってしまっています。これがロストアップデート（失われた更新）です。
ロストアップデートを防ぐ
ロストアップデートを防ぐにはFOR UPDATEで明示的にロックを取得します。
FOR UPDATEはそのトランザクションがCOMMITまたはROLLBACKされるまで行レベルの排他ロックをかけます。
A > START TRANSACTION;
A > SELECT pt FROM users WHERE id = 1 FOR UPDATE; -- Aがpt=10を読み取る
B > START TRANSACTION;
B > SELECT pt FROM users WHERE id = 1 FOR UPDATE; -- Bの読み取りはAのロックによってブロックされる
A > COMMIT; -- Aがコミットしたことでロックが解放されBがpt=11を読み取る
B > UPDATE users SET pt = 12 WHERE id = 1; -- Bがpt=12を書き戻す
B > COMMIT;
Aが先にid=1の行にロックをかけることでBのSELECTはAのトランザクションが終わるまで待ち状態となります。AがCOMMITしてロックを解放すると、BはAが更新した後の値である11を読み取るため、最終的なptの値は12となりロストアップデートが起きません。
分離レベルがSERIALIZABLEの場合
分離レベルがREPEATABLE READでは明示的なロックのない更新はロストアップデートを引き起こしましたが、SERIALIZABLEの場合はロックのないSELECTは暗黙的に共有ロックを取ります。そのためデッドロックが発生しやすくなります。
A > START TRANSACTION;
A > SELECT pt FROM users WHERE id = 1; -- Aがpt=10を読み取る（共有ロック）
B > START TRANSACTION;
B > SELECT pt FROM users WHERE id = 1; -- Bがpt=10を読み取る（共有ロック）
A > UPDATE users SET pt = 11 WHERE id = 1; -- Aの書き込みがBのロックによってブロック
B > UPDATE users SET pt = 11 WHERE id = 1; -- Bの書き込みがAのロックによってブロック
こうなるとお互いがお互いのロック解放を待つというすくみの状態に陥り、これ以上処理が進まなくなります。この状態をデッドロックといい、MySQLはデッドロックを検知すると一方のトランザクションを強制的にロールバックさせることで、もう一方の処理を先に進めます。
結果、ロストアップデートは起きませんが、ロールバックされた方のトランザクションの更新は失敗します。
FOR UPDATEのロック範囲
FOR UPDATEは行ロックを取りますが、ロックされる範囲はその行だけに留まらない場合があります。そのためロック範囲を見極めておかないと思わぬ性能劣化を引き起こすことがあります。
インデックスで解決できないカラムをWHEREに指定した場合
例えば、WHEREの指定をidではなくptにして別々の行を更新しようとした場合、
A > START TRANSACTION;
A > SELECT * FROM users WHERE pt = 10 FOR UPDATE; -- pt=10の行を読み取る
B > START TRANSACTION;
B > SELECT * FROM users WHERE pt = 20 FOR UPDATE; -- pt=20の行を読み取ろうとするがブロックされる
pt=10で取得できる行とpt=20で取得できる行はお互いに別々の行であるにも関わらず、Aはpt=10以外の行に対してもロックを取ります。結果、Bがブロックされることになります。
これは、行ロックが最終的にマッチした行ではなく、その過程でアクセスしたインデックスすべてにロックを取るからです。そのため、インデックスのないptを検索条件に使うとテーブルスキャンになり、全ての行を触ることになるので、例えpt=10の条件にマッチするのが１行だけであったとしてもその他すべての行もロックされることになります。
ギャップロックが発生する場合
A > START TRANSACTION;
A > SELECT * FROM users WHERE id <= 5 FOR UPDATE; -- idが5以下の行をロック
B > START TRANSACTION;
B > INSERT INTO users (id, name, pt) VALUES (3, 'Takashi', 30); -- 欠番のid=3に挿入しようとするがブロックされる
分離レベルがデフォルトのREPEATABLE READの場合、Aはid=5以下の行（1,2,4,5）に対して行ロックを取ると同時に2と4の間の行間（ギャップ）に対してもロックを取ります。この行間にかかるロックをギャップロックといい、このため行間にINSERTしようとした後発のBがブロックされます。
ギャップロックは範囲検索だけでなく等価検索の空振りでも起こります。
実行計画によってもロック範囲が変わる
A > EXPLAIN SELECT * FROM users WHERE id IN (2, 4);
-- +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
-- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-- +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
-- | 1 | SIMPLE | users | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where |
-- +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
A > START TRANSACTION;
A > SELECT * FROM users WHERE id IN (2, 4) FOR UPDATE; -- idが2と4の行をロック
B > START TRANSACTION;
B > INSERT INTO users (id, name, pt) VALUES (3, 'Takashi', 30); -- 挿入できる
A > EXPLAIN SELECT * FROM users WHERE id IN (1, 2, 4, 5);
-- +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
-- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-- +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
-- | 1 | SIMPLE | users | ALL | PRIMARY | NULL | NULL | NULL | 4 | Using where |
-- +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
A > START TRANSACTION;
A > SELECT * FROM users WHERE id IN (1, 2, 4, 5) FOR UPDATE; -- idが1,2,4,5の行をロック
B > START TRANSACTION;
B > INSERT INTO users (id, name, pt) VALUES (3, 'Takashi', 30); -- ブロックされる
上の例ではSELECTにインデックスが使用されているのでロックされるのは2と4の行ロックのみです。そのためid=3へのINSERTは競合することなく実行できます。ところが下の例ではインデックスが使われずテーブルスキャンになっています。MySQLはインデックスが使える状況であっても、テーブルスキャンした方が速いと判断した場合にはあえてインデックスを使わないという選択をする場合があります。この場合、2と4の間のギャップロックが取られることでid=3のINSERTがブロックされます。
まとめ
ロックの挙動と範囲を理解して快適なMySQLライフを