はじめに
データベースの基本的な挙動・仕組みをわかっているつもりでちゃんとわかっているのか微妙な部分があるのではないかという事で、実際に手を動かしてデッドロック・トランザクション分離レベルなどを確認してみた。
本記事はその際の備忘録です。
デッドロック
事前準備
CREATE TABLE `accounts` (
`id` int NOT NULL,
`balance` int NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 1000);
デッドロックを起こす
以下のようにトランザクションを張り、端末AでUPDATE文をid=1に対し実行。端末BでUPDATE文をid=2に対し実行。UPDATE文は排他ロックを取るのでそれぞれが行ロックされた状態に。
この状況において、端末Aでid=2にUPDATE文を実行＝ロックを取得しようとするとデッドロックになる（MySQLのInnoDBでは自動でロールバックする）。
| 端末A | 端末B |
|---|---|
 |
 |
ついでに理解
MVCC（Multi-Version Concurrency Control）（多版型同時実行制御）
行を版として複数世代持ち、読む人どうし・読む人と書く人がぶつからないようにする仕組み。
キーとなる考え方は以下。
- 行のバージョン管理
行ごとに “隠し列” で「作られた時点・消えた時点」のトランザクション ID を保持し、古い行も DB 内に残しておく - スナップショット読み
トランザクション開始時に その時点までコミット済み の行だけを自分用スナップショットとして覚え、以降の SELECT はロック待ちゼロ でその版を読む
上記の方法を取ると、以下のようにロックなしに更新（削除）・読み取りができる。
- 行を更新するとき
- 古い行をすぐ消さず、旧バージョンとして残す
- 新しい値は 新バージョンとして書き込む
- 読み取るとき
- 「自分が読み始めた瞬間」に存在していたバージョンだけを見る
→他人の更新は関係ない（更新の完了を待たなくてOK＝ブロック不要）
- 「自分が読み始めた瞬間」に存在していたバージョンだけを見る
トランザクション分離レベル
MVCCの仕組みが基礎としてある上で、ではどのバージョン（版）が見えるのか？を制御するのがトランザクション分離レベル。
各分離レベルは以下のように整理される。たとえ話は「オンライン書店の在庫管理」をイメージしている。全てトランザクションを開始しての操作。
登場人物
- 書店の在庫システム＝MySQL
- A さん／B さん＝同時に在庫を確認・更新するオペレータ（＝トランザクション）
- 在庫台帳＝
booksテーブル- 📦＝本の在庫数
| Isolation Level | 実際の DB 挙動 | 起こり得る問題 | オンライン書店の在庫管理 | 主な実装状況 |
|---|---|---|---|---|
| READ UNCOMMITTED (RU) | コミット前 の変更まで丸見え（コミット前の更新も読める） | Dirty Read（コミットされていないデータを読み込んでしまう） | A さんが「📦10 冊→7 冊」に打ち替えてまだ保存ボタンを押していない段階でも、B さんの画面には “7 冊” と表示。のちに A さんが取消しても B さんは “幻の在庫” を信じたまま。 | MySQL など一部のみ。PostgreSQL は非対応。 |
| READ COMMITTED (RC) | クエリ実行のたびに、その時点でコミット済みの版を読む | Non-Repeatable Read（以前読み込んだデータを再度読み込む際、以前のデータではなく新しいデータを得てしまう）。Phantom Read（ある行の集合を返す検索条件で問い合わせを再実行したとき、同じ検索条件で問い合わせを実行しても異なる結果を得てしまう）。 | A さんが在庫を問い合わせると 10 冊。B さんが在庫を 10→7 冊に 保存する。Aさんが再度在庫を問い合わせると 7 冊に変わる。 | PostgreSQL のデフォルト。MySQL で選択可。 |
| REPEATABLE READ (RR) | トランザクション開始時点のスナップショット（その版）を固定 | Phantom Read（※MySQL は Next-Key Lock で行追加をブロックできる） | A さんが在庫編集モードに入った瞬間（在庫＝10 冊）で “台帳のコピー” を保持。B さんが途中で 7 冊に更新しても、A さんの画面は完了するまで 10 冊のまま。 ※MySQL では Gap/Next-Key Lock が付くので “いつの間にか 11 冊目が挿入される” ファントムも起きない |
MySQL のデフォルト |
| SERIALIZABLE (SZ) | RR 相当のスナップショットに範囲ロックを加えて完全順序化（先に入った人が占有） | 在庫編集モードに入ると 在庫そのものが操作禁止に。A さんが終わるまで B さんは在庫を読んだり書いたりできない。 | 両 DB が実装。高整合性シーン向け |
まとめると以下のようになる。
- RU：ドラフト（保存前）まで見える。監査・デバッグ以外は使わない。
- RC：最新の状態が常に見える。一般的な業務 API はこれで十分。
- RR：作業を開始したときの状態がずっと見える。バッチ処理や帳票で「途中で値が変わらない安心感」が欲しいときに向く。
- SZ：自分だけが作業できる。銀行仕訳・財務など絶対にブレが許されない取引用。
実際にMySQLで実験してみると以下のようにそれぞれの動きを確認できる。
READ UNCOMMITTED (RU)
| TxA | TxB |
|---|---|
 |
 |
READ COMMITTED (RC)
| TxA | TxB |
|---|---|
 |
 |
REPEATABLE READ (RR)
| TxA | TxB |
|---|---|
 |
 |
MySQL特有のファントムリードが起きない様子も確認してみる。まずは、READ COMMITTED (RC)でファントムリードが起きる状況を確認してみる。
以下のように、TxBのコミット後にテーブル（集合）への追加要素が見えてしまっている。
| TxA | TxB |
|---|---|
 |
 |
ではREPEATABLE READ (RR)でどうなるかだが、以下のようにTxBのコミット後でも行集合は変化せず、ファントムリードは発生しないことがわかる。
※FOR UPDATEでロックを取得しないとファントムリードが防げないわけではないので注意。読み取りの時だけでもファントムリードは防げる＝更新処理がないならFOR UPDATEは不要。
| TxA | TxB |
|---|---|
 |
 |
最後にネクストキーロック（行ロック＋ギャップロック）1についてみてみる。
以下のように180の挿入がブロックされる。
| TxA | TxB |
|---|---|
 |
 |
状況を詳細に確認するために、以下のようにトランザクション状況を表示させると、0: len 4; hex 800000c8; ascがamount = 200 (16進数c8 = 10進数200)を意味し、1: len 4; hex 80000002; ascが主キーまたは行ID = 2を意味していること、X locks gap before recであることから、ネクストキーロック（(150, 200)のギャップロック）が確認できる。
mysql> SHOW ENGINE INNODB STATUS;
...
------------
TRANSACTIONS
------------
...
INSERT INTO invoices(amount) VALUES (180)
------- TRX HAS BEEN WAITING 29 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6 page no 5 n bits 72 index idx_amount of table `phantom_demo`.`invoices` trx id 2885 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 800000c8; asc ;;
1: len 4; hex 80000002; asc ;;
...
ロック
ロックに関しては、2つの概念がある。
- 1，いつロックするか（ロックの戦略）
- 悲観ロック
- 楽観ロック
- 2，どんなロックを使うか（ロックの種類）
- 排他（占有）ロック
- 共有ロック
結論としては、以下のように整理される。
悲観ロック戦略
├── 排他ロックを使用：更新処理で他を完全ブロック（※よく使われる組み合わせ）
├── 共有ロックを使用：読み取り処理で更新のみブロック
└── （その他のロック種類も可能）
楽観ロック戦略
└── ロック機能自体を使わない：バージョン番号やタイムスタンプで制御
※以下で詳細は見ていくが、悲観ロック×排他ロックと悲観ロック×共有ロックの違いは、排他ロックが読み取りもNGにするのに対し、共有ロックは読み取りのみOKという違い。
いつロックするか（ロックの戦略）
それぞれの戦略の特徴をまとめると次のようになる。楽観ロックはロックといいつつロックはとらず衝突検知をするだけ。
| 戦略 | 考え方の基本 | 衝突検知 | 代表 SQL/実装 | 適合ケース |
|---|---|---|---|---|
| 悲観 | データを取得した時点でロックをかける 「きっと誰かが同じデータを変更するだろう」という悲観的な考え |
衝突しない（最初にロックして待たせるので） | SELECT … FOR UPDATE |
衝突頻発／二重更新を厳禁 |
| 楽観 | 更新時にデータが変更されていないかチェック 「誰も同じデータを変更していないだろう」という楽観的な考え |
最後に WHERE version = ? が 0 行なら衝突 |
バージョン列・タイムスタンプ | 衝突が まれ／UI リトライ可 |
具体的な例として、悲観ロックの場合は以下のように処理を行う最初にロックを取得して、他の処理（トランザクション）が操作できないようにする。
BEGIN;
SELECT * FROM products WHERE id = 1 FOR UPDATE; -- この時点でロック
-- 在庫数をチェックして注文処理
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT; -- ここでロック解除
これに対して、楽観ロックの場合は以下のように更新処理の前に、自身が認識している更新前の状態と一致するか？を検証するイメージ。
-- 1. データ取得（ロックしない）
SELECT id, name, stock, version FROM products WHERE id = 1;
-- version = 5 だったとする
-- 2. 更新時にバージョンをチェック
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 5; -- バージョンが変わってたら更新失敗
-- 影響行数が0なら他の人が先に更新済み → 再試行
どんなロックを使うか（ロックの種類）
それぞれの種類の特徴をまとめると次のようになる。
| 種類 | 考え方 | 同時読込 | 同時書込 | 主な用途 |
|---|---|---|---|---|
| 排他 (X) | データを1つのトランザクションが独占して使用 他のトランザクションは読み取りも書き込みもできない |
✕ | ✕ | 行を更新・削除するとき |
| 共有 (S) | 複数のトランザクションが同時に読み取り可能 ただし書き込みはできない |
〇 | ✕ | 整合性を保ったまま読むだけ |
具体的な例として、排他ロックの場合は以下のように他の処理（トランザクション）に該当のデータを一切触らせない。実生活で例えるなら、「図書館で本を借りている間は、他の人はその本を読めない」・「ATMを使っている間は、他の人は同じATMを使えない」というイメージ。
BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1; -- 排他ロック取得
-- この間、他の処理はaccount id=1を触れない
COMMIT;
これに対して、共有ロックの場合は読み取りはできるが更新はできないようにする。実生活で例えるなら、「料理教室で先生が黒板に料理のレシピを書いているとして、それを料理教室の参加者は皆見られるが、先生はそのレシピの修正はできない（料理を作っている最中なので）」というイメージ。
SELECT balance FROM accounts WHERE id = 1; -- 共有ロック取得
-- 他の処理も同時にSELECTできるが、UPDATEはできない
ちなみに、SQLでは各操作で以下のように排他・共有ロックが取得される（基本的にトランザクションの宣言をしなくてもSQLの1つ1つは自動的にトランザクションとして扱われる。明示的にSTART TRANSACTION;を宣言すると、COMMIT;までをひとまとめのトランザクションにできる。）
| 文 | ロックの種類 |
|---|---|
UPDATE, DELETE, INSERT
|
排他ロック |
SELECT … FOR UPDATE |
排他ロック |
SELECT … LOCK IN SHARE MODE |
共有ロック |
参考文献等
-
A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record. cf.Next-Key Locks ↩