本線の回を書き終えたあと、私はしばらく分かった気になっていました。START TRANSACTION と打って、COMMIT と打つ。失敗したら ROLLBACK。そこは理解した、と思っていました。
これは深掘りです。本線の順路ではありません。 読まなくても先へは進めます。 本線の全部やるか、1つもやらないか — トランザクションとACIDの回の続きです。あの回で
START TRANSACTION/COMMIT/ROLLBACKと、在庫1個を2人に売ってしまう事故と、SELECT ... FOR UPDATEまでをやりました。この記事はその先です。「分離レベル」と「ロック」を、端末を2つ並べて全部目で見ます。前回: ビューとストアドプロシージャ — 列を消したら壊れた
作業場所はVMの中(Ubuntu Server)です。MySQL 8.0 以降が動いていて、
appdbというデータベースがあれば、それ以外の準備は要りません。練習用の表はこの記事の中で作って、最後に全部消します。既存の表には一切触りません。
そのあと、こういうバグに当たりました。
管理画面に「今日の合計」と「内訳の一覧」を出すページがあります。合計と内訳が、たまに合いません。 1日に1回あるかどうかで、再現しません。ログを見ても、SQLは正しく実行されています。2つの SELECT は、どちらも正しい答えを返しています。
正しい答えを2回返したのに、足し算が合いませんでした。
原因は、2つの SELECT の間に、他の人が1件登録したことでした。1つ目の SELECT が走った瞬間の世界と、2つ目の SELECT が走った瞬間の世界が、違っていたのです。
ここで初めて分かったことがあります。
自分ひとりで端末を1つだけ開いて試している限り、トランザクションの意味は一生分かりません。 トランザクションが解いている問題は、「2人が同時に触ったとき」にしか現れないからです。
START TRANSACTION と打って COMMIT と打つだけなら、打たなくても結果は同じです。1人しかいないのだから、当たり前です。だから「作法として書いておくもの」に見えてしまいます。
なので、この記事では端末を2つ開きます。
左に 端末A、右に 端末B。この2つを人間2人だと思って、片方を途中で止めたまま、もう片方を動かします。 そうすると、教科書に「ダーティリード」「ノンリピータブルリード」「ファントムリード」と書いてある3つの現象が、その場で目の前に出ます。
出たものは、忘れません。
対象レベル: 中級〜上級 / 区分: 発展
この記事でわかること
- 分離レベルは「安全さのつまみ」ではなく、「他人の途中経過がどこまで見えるか」の設定だということ。上げれば安全、ではないこと
- MySQL の既定は
REPEATABLE READ、PostgreSQL の既定はREAD COMMITTEDで、同じコードが移すと挙動を変えること REPEATABLE READの「景色」が始まるのはSTART TRANSACTIONの瞬間ではなく、最初にSELECTを打った瞬間だということ- ロックは行に掛かるのではなく、索引の項目に掛かること。だから索引が無い列で
WHEREすると、1行のつもりが全行ロックになること - デッドロックはバグではなく、正常な検出だということ。
SHOW ENGINE INNODB STATUSに、誰と誰が何を待ったかが全部残っていること SKIP LOCKEDを使うと、待たずに「他の人が持っていない行」だけを取れること。キューをデータベースだけで作れること
この記事でやること
- 端末を2つ開き、練習用の表(
d_tx_で始まる4つ)を作る READ UNCOMMITTEDにして、コミット前の値が見えるところ(ダーティリード)を再現するREAD COMMITTEDで、同じSELECTの答えが変わるところ(ノンリピータブルリード)を再現し、REPEATABLE READでは変わらないことを確かめるSELECT ... FOR UPDATEでもう一方を待たせ、innodb_lock_wait_timeoutで失敗させ、performance_schema.data_locksで中身を見る- 索引の無い列で
UPDATEして、関係ない行まで止まるところを実演し、索引を張って直す - デッドロックをわざと起こして
ERROR 1213を出し、SHOW ENGINE INNODB STATUSの記録を読む NOWAITとSKIP LOCKEDでキューを作り、最後に両方の端末を片づけて設定を戻す
先に知る言葉
この記事で初めて出てくる言葉です。 基本的な言葉は用語集にもまとめてあります。
| 言葉 | 意味 |
|---|---|
| 分離レベル | 他人がまだ確定させていない(あるいは確定させたばかりの)変更が、自分にどこまで見えるかの設定。 接続ごとに変えられる |
| ダーティリード | 他人がまだ COMMIT していない値を読んでしまうこと。相手が ROLLBACK したら、読んだ値は存在しなかったことになる |
| ノンリピータブルリード | 同じ行を2回読んだら、値が変わっていたこと。間に他人が UPDATE して COMMIT した |
| ファントムリード | 同じ条件で2回読んだら、行の数が変わっていたこと。間に他人が INSERT した。幻(phantom)の行 |
| スナップショット | ある瞬間のデータベース全体の見え方を、そのまま固定したもの。 InnoDB は履歴を持っていて、過去の姿を再現できる |
| 共有ロック | 「私も読んでいるので、書き換えないでください」という印。複数人が同時に持てる |
| 排他ロック | 「私が書き換えます」という印。1人しか持てない。 他の人は待たされる |
| ギャップロック | 行そのものではなく、行と行の「すきま」に掛けるロック。 そこへの INSERT を止める。ファントムを防ぐための仕組み |
| デッドロック | AがBの持っているものを待ち、BがAの持っているものを待っている状態。 放置すると永遠に終わらない |
| InnoDB | MySQL の標準のストレージエンジン。ロックやトランザクションを実際に担当している部分 |
① 準備 — 端末を2つ開く
なぜ2つ要るのか
ここが、この記事のいちばん大事な仕掛けです。
MySQL の START TRANSACTION は、その接続の中だけの話です。同じ端末で START TRANSACTION を打って、COMMIT を打つまでの間に別のことをしても、それは全部「自分」の操作です。誰とも競合しません。
別の接続でないと、他人になれません。
端末を2つ開くというのは、「MySQLへの接続を2本作る」という意味です。sudo mysql を2回打てば、接続は2本になります。
端末を2つ用意する
VS Code から VM に繋いでいるなら、ターミナルを分割します。Windows Terminal で SSH しているなら、タブかペインを2つ開いてください。左右に並べて、両方が同時に見える状態にしてください。 これが大事です。片方を隠すと、何が起きているか分からなくなります。
[スクリーンショット: 01_端末2つ.png] 端末が左右に2つ並んでいて、両方に MySQL のプロンプト(mysql>)が出ている状態を撮ってください。この記事のあいだ、ずっとこの形で進みます。
端末A
端末B
sudo mysqlでパスワードなしに入れるのは、Ubuntu の MySQL がauth_socketという方式を使っているからです。本線のMySQLとSQLの基本の回で入れたままの状態なら、これで入れます。
どちらの端末か、いつでも分かるようにしておきます。 プロンプトに名前を出せます。
端末A
PROMPT A>
端末B
PROMPT B>
PROMPT set to 'B> '
これで、打ち間違いで「Aに打つべきものをBに打った」事故がほぼ無くなります。 私はこれをやる前に、2回やらかしました。
練習用の表を作る
端末A で作ります。Bでは打たないでください。
USE appdb;
この記事で作るのは、d_tx_ で始まる表だけです。 既存の tasks / users / sessions には触りません。
CREATE TABLE d_tx_account (
id INT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
balance INT NOT NULL
) ENGINE=InnoDB;
CREATE TABLE d_tx_item (
id INT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
stock INT NOT NULL,
shop_id INT NOT NULL
) ENGINE=InnoDB;
CREATE TABLE d_tx_job (
id INT PRIMARY KEY,
payload VARCHAR(40) NOT NULL,
state VARCHAR(10) NOT NULL,
worker VARCHAR(20) NULL
) ENGINE=InnoDB;
CREATE TABLE d_tx_memo (
memo_no INT NOT NULL,
note VARCHAR(40) NOT NULL
) ENGINE=InnoDB;
d_tx_memoにだけ主キーも索引も付いていません。 これは書き忘れではありません。⑤で「索引が無いとどうなるか」を実演するための表です。
データを入れます。端末A です。
INSERT INTO d_tx_account VALUES
(1, '佐藤', 10000),
(2, '鈴木', 5000),
(3, '高橋', 3000);
INSERT INTO d_tx_item VALUES
(1, 'マグカップ', 1, 10),
(2, 'ノート', 5, 10),
(3, 'ペン', 9, 20);
INSERT INTO d_tx_job VALUES
(1, 'send-mail-001', 'waiting', NULL),
(2, 'send-mail-002', 'waiting', NULL),
(3, 'send-mail-003', 'waiting', NULL),
(4, 'send-mail-004', 'waiting', NULL);
INSERT INTO d_tx_memo VALUES
(1, 'ひとつめ'), (2, 'ふたつめ'), (3, 'みっつめ'),
(4, 'よっつめ'), (5, 'いつつめ'), (6, 'むっつめ'),
(7, 'ななつめ'), (8, 'やっつめ');
端末B でも USE しておきます。
USE appdb;
SELECT COUNT(*) FROM d_tx_account;
+----------+
| COUNT(*) |
+----------+
| 3 |
+----------+
Bからも同じデータが見えていれば、準備完了です。 当たり前に見えますが、同じデータベースを別々の接続から見ているという状態を、ここで一度確認しておきます。
② 分離レベル4つと、防げる3つの現象
手を動かす前に、これから何を再現するのかを1枚の表にします。 先に地図を見ておかないと、途中で自分が何をやっているのか分からなくなります。
起きてほしくない3つの現象
| 現象 | 何が起きるか | いつ起きるか |
|---|---|---|
| ダーティリード | 他人がまだ確定させていない値を読む。 相手が ROLLBACK すると、読んだ値は幻になる | 相手が COMMIT する前 |
| ノンリピータブルリード | 同じ行を2回読むと、値が違う | 相手が UPDATE して COMMIT した後 |
| ファントムリード | 同じ条件で2回数えると、行数が違う | 相手が INSERT して COMMIT した後 |
下に行くほど「起きにくい」のではありません。 上から順に、防ぐのが難しくなっていきます。
- 値を1つ読むだけなら、相手の確定を待てば防げます(ダーティリード)
- 同じ値を2回読んで同じにするには、自分が読んだ瞬間の景色を覚えておく必要があります(ノンリピータブルリード)
- 行の数まで同じにするには、まだ存在しない行の「入る場所」まで押さえる必要があります(ファントムリード)
4つの分離レベル
そして、どこまで防ぐかの設定が4段階あります。
| 分離レベル | ダーティリード | ノンリピータブル | ファントム | ひとことで |
|---|---|---|---|---|
| READ UNCOMMITTED | 起きる | 起きる | 起きる | 何も防がない。 実務では使わない |
| READ COMMITTED | 防ぐ | 起きる | 起きる | 確定したものだけ見る。 PostgreSQL の既定 |
| REPEATABLE READ | 防ぐ | 防ぐ | 起きる(規格上) | 始めた時点の景色が変わらない。 MySQL の既定 |
| SERIALIZABLE | 防ぐ | 防ぐ | 防ぐ | 1つずつ順番に実行したのと同じ。 一番待たされる |
「REPEATABLE READ でファントムが起きる」は、規格上の話です。 InnoDB は、普通の
SELECTに関してはファントムも起きません。 スナップショットを使っているからです。 ただしSELECT ... FOR UPDATEのような「ロックを取る読み方」をすると、話が変わります。 これは④の最後で実際に見ます。ここが一番ややこしいところなので、表を覚えるのではなく、手で確かめてください。
既定値がデータベースごとに違う
ここは、移植のときに必ず刺さるところです。
| データベース | 既定の分離レベル |
|---|---|
| MySQL(InnoDB) | REPEATABLE READ |
| PostgreSQL | READ COMMITTED |
| Oracle | READ COMMITTED |
| SQL Server | READ COMMITTED |
MySQL だけが違います。
つまり、MySQL で動いていたコードを PostgreSQL に移すと、「同じ SELECT を2回打つと答えが変わる」ようになります。 本線のMySQLとPostgreSQLの違いは1割の回で「違いは1割」と書きましたが、この1割の中に、これが入っています。
逆向きも同じです。 PostgreSQL 前提で「2回読んだら変わるかもしれない」と気をつけて書いたコードを MySQL に持ってくると、気をつけた部分が動かないまま眠ります。 動くので気づきません。
③ いまの設定を確認する
端末A
SELECT @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ |
+-------------------------+
端末B でも同じものを打ってください。 同じ結果が出ます。
@@で始まるのは「システム変数」です。設定を読むための書き方で、SELECTの中にそのまま書けます。
3つの階層がある
変数には、どこまで効くかの範囲が3つあります。 ここを取り違えると「変えたはずなのに変わらない」になります。
| 書き方 | 効く範囲 | いつ消えるか |
|---|---|---|
SET SESSION ... | その接続だけ | EXIT したら消える |
SET GLOBAL ... | これから繋いでくる接続 | MySQL を再起動したら消える |
| 設定ファイルに書く | 全部 | 消えない |
この記事では SET SESSION しか使いません。 端末を閉じれば全部元に戻るので、後片づけの心配が要らないからです。
両方を一度に見ておきます。端末A
SELECT @@GLOBAL.transaction_isolation AS `全体`, @@SESSION.transaction_isolation AS `この接続`;
+-----------------+-----------------+
| 全体 | この接続 |
+-----------------+-----------------+
| REPEATABLE-READ | REPEATABLE-READ |
+-----------------+-----------------+
設定ファイルで恒久的に変える場合は、
/etc/mysql/mysql.conf.d/mysqld.cnfの[mysqld]にtransaction-isolation = READ-COMMITTEDのように書いて、MySQL を再起動します。 この記事ではやりません。 学習用のVMとはいえ、恒久設定を先に変えると、この記事の実験結果が全部ずれます。
④ 現象を、1つずつ再現する
ここからが本番です。
やり方の型はどれも同じです。
- 端末B の分離レベルを変える(見る側がBです)
- 端末B でトランザクションを開始して、1回読む
- 端末A で、何かをする
- 端末B で、もう一度同じものを読む
- 答えが変わったかどうかを見る
「見る側がB、動かす側がA」と決めておくと、最後まで混乱しません。
4-1. ダーティリード —— コミット前の値が見える
端末B の分離レベルを、一番ゆるい READ UNCOMMITTED にします。
端末B
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED |
+-------------------------+
ここから、手順表のとおりに打ちます。上から順に、1行ずつです。
| 順番 | 端末A | 端末B |
|---|---|---|
| 1 | START TRANSACTION; | |
| 2 | SELECT balance FROM d_tx_account WHERE id = 1; → 10000 | |
| 3 | START TRANSACTION; | |
| 4 | UPDATE d_tx_account SET balance = 1 WHERE id = 1; (COMMITしない) | |
| 5 | SELECT balance FROM d_tx_account WHERE id = 1; → 1 | |
| 6 | ROLLBACK; | |
| 7 | SELECT balance FROM d_tx_account WHERE id = 1; → 10000 | |
| 8 | COMMIT; |
実際の画面です。
端末B(手順1〜2)
START TRANSACTION;
SELECT balance FROM d_tx_account WHERE id = 1;
+---------+
| balance |
+---------+
| 10000 |
+---------+
端末A(手順3〜4)
START TRANSACTION;
UPDATE d_tx_account SET balance = 1 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
ここで COMMIT は打ちません。 Aは書き換えたつもりでいますが、まだ確定していません。
端末B(手順5)
SELECT balance FROM d_tx_account WHERE id = 1;
+---------+
| balance |
+---------+
| 1 |
+---------+
見えました。 Aがまだ COMMIT していない値が、Bから読めています。
これがダーティリードです。
そして、ここからが恐ろしいところです。端末A(手順6)
ROLLBACK;
端末B(手順7)
SELECT balance FROM d_tx_account WHERE id = 1;
+---------+
| balance |
+---------+
| 10000 |
+---------+
10000 に戻りました。
Bが読んだ「1」は、この世界のどの瞬間にも存在しなかった値です。 もしBが「残高が1円しかないので、この人は購入できません」と判断してメールを送っていたら、そのメールは、起きなかった出来事についてのお知らせになります。
READ UNCOMMITTEDを実務で使わないのは、これが理由です。 速くなるわけでもありません。
端末B(手順8)
COMMIT;
[スクリーンショット: 02_ダーティリード.png] 左が端末A(UPDATE したまま止まっている)、右が端末B(1 と表示されている)の状態を、2つ並べて1枚で撮ってください。この記事で一番大事な絵です。
4-2. ノンリピータブルリード —— 同じSELECTの答えが変わる
端末B を READ COMMITTED にします。
端末B
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED |
+-------------------------+
| 順番 | 端末A | 端末B |
|---|---|---|
| 1 | START TRANSACTION; | |
| 2 | SELECT balance FROM d_tx_account WHERE id = 1; → 10000 | |
| 3 | UPDATE d_tx_account SET balance = 7777 WHERE id = 1; (autocommitで即確定) | |
| 4 | SELECT balance FROM d_tx_account WHERE id = 1; → 7777 | |
| 5 | COMMIT; |
端末B(手順1〜2)
START TRANSACTION;
SELECT balance FROM d_tx_account WHERE id = 1;
+---------+
| balance |
+---------+
| 10000 |
+---------+
端末A(手順3)
UPDATE d_tx_account SET balance = 7777 WHERE id = 1;
START TRANSACTIONを打っていないので、このUPDATEはその場で確定しています。 MySQL は既定でautocommitが有効で、1文ごとに勝手にCOMMITされるからです。本線の回で扱ったところです。
端末B(手順4) —— Bは、まだ同じトランザクションの中にいます。
SELECT balance FROM d_tx_account WHERE id = 1;
+---------+
| balance |
+---------+
| 7777 |
+---------+
変わりました。
Bは1つのトランザクションの中で、まったく同じ SELECT を2回打っただけです。 それなのに、答えが 10000 から 7777 になりました。
これがノンリピータブルリードです。
これは「バグ」ではありません。
READ COMMITTEDは「確定したものは見せる」という約束なので、約束どおりに動いています。 困るのは、アプリ側が「トランザクションの中なら同じ答えが返る」と思い込んでいるときです。冒頭の「合計と内訳が合わない」は、まさにこれでした。
端末B(手順5)
COMMIT;
4-3. REPEATABLE READ にすると、変わらない
同じことを、MySQL の既定に戻して、もう一度やります。
端末B
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ |
+-------------------------+
| 順番 | 端末A | 端末B |
|---|---|---|
| 1 | START TRANSACTION; | |
| 2 | SELECT balance FROM d_tx_account WHERE id = 1; → 7777 | |
| 3 | UPDATE d_tx_account SET balance = 3333 WHERE id = 1; | |
| 4 | SELECT balance FROM d_tx_account WHERE id = 1; → 7777(変わらない) | |
| 5 | COMMIT; | |
| 6 | SELECT balance FROM d_tx_account WHERE id = 1; → 3333 |
端末B(手順4)
SELECT balance FROM d_tx_account WHERE id = 1;
+---------+
| balance |
+---------+
| 7777 |
+---------+
変わりません。 Aが確定させた 3333 は、Bのトランザクションが終わるまで見えません。
端末B(手順5〜6)
COMMIT;
SELECT balance FROM d_tx_account WHERE id = 1;
+---------+
| balance |
+---------+
| 3333 |
+---------+
トランザクションを閉じた瞬間に、見えました。
これが
REPEATABLE READです。 B は「自分が最初に読んだ瞬間の世界」を、COMMITするまで持ち続けます。 InnoDB は古い値の履歴を持っていて、Bにだけ古い姿を見せているからです。 Aは待たされていません。 AのUPDATEは普通に成功しています。「Bだけが過去を見ている」のであって、誰も止まっていません。ここが重要です。
4-4. 景色が始まるのは「START TRANSACTION」ではない
ここで、私が完全に誤解していたところを書きます。
REPEATABLE READ の「始めた時点の景色」は、START TRANSACTION と打った瞬間には作られません。 作られるのは、そのトランザクションで最初に SELECT を打った瞬間です。
確かめます。
| 順番 | 端末A | 端末B |
|---|---|---|
| 1 | START TRANSACTION; (まだ何も読まない) | |
| 2 | UPDATE d_tx_account SET balance = 5555 WHERE id = 1; | |
| 3 | SELECT balance FROM d_tx_account WHERE id = 1; → 5555 | |
| 4 | COMMIT; |
端末B(手順3)
SELECT balance FROM d_tx_account WHERE id = 1;
+---------+
| balance |
+---------+
| 5555 |
+---------+
Bは START TRANSACTION を先に打っていたのに、そのあとAが書いた値が見えています。
Bが最初に読んだ瞬間が、手順3だったからです。 その時点の世界には、もう 5555 が入っていました。
「トランザクションを開いた時刻」ではなく、「最初に読んだ時刻」で景色が固まる。 これを知らないと、「
START TRANSACTIONを先に打っておけば安全」という間違った安心をします。私はしていました。
開いた瞬間に固定したいときは、こう打ちます。
START TRANSACTION WITH CONSISTENT SNAPSHOT;
これは「開始と同時に景色を確定する」という意味です。REPEATABLE READ のときだけ効きます。
端末B(片づけ)
COMMIT;
4-5. ファントムリード —— 行の「数」が変わる
ここが、④で一番ややこしい場所です。
普通の SELECT では、InnoDB の REPEATABLE READ はファントムも防ぎます。 景色ごと固定しているので、行が増えても見えません。
ところが、FOR UPDATE を付けた瞬間に、見えます。
| 順番 | 端末A | 端末B |
|---|---|---|
| 1 | START TRANSACTION; | |
| 2 | SELECT COUNT(*) FROM d_tx_item WHERE shop_id = 10; → 2 | |
| 3 | INSERT INTO d_tx_item VALUES (4, 'ふせん', 3, 10); | |
| 4 | SELECT COUNT(*) FROM d_tx_item WHERE shop_id = 10; → 2(変わらない) | |
| 5 | SELECT COUNT(*) FROM d_tx_item WHERE shop_id = 10 FOR UPDATE; → 3 | |
| 6 | COMMIT; |
端末B(手順4)
SELECT COUNT(*) FROM d_tx_item WHERE shop_id = 10;
+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
端末B(手順5) —— 同じ条件に FOR UPDATE を付けるだけ
SELECT COUNT(*) FROM d_tx_item WHERE shop_id = 10 FOR UPDATE;
+----------+
| COUNT(*) |
+----------+
| 3 |
+----------+
同じトランザクションの中で、2 と 3 が両方出ました。
理由は、読み方が2種類あるからです。
| 読み方 | 何を見るか | 呼び名 |
|---|---|---|
普通の SELECT | 自分が最初に読んだ時点の景色(履歴から復元する) | 一貫読み取り |
SELECT ... FOR UPDATE / UPDATE / DELETE | いまこの瞬間の最新 | 現在読み取り |
ロックを掛けるということは、「これから書き換えるかもしれない」という宣言です。 過去の姿にロックを掛けても意味がありません。 だから、ロックを取る読み方は必ず最新を見ます。 本線の回で「
UPDATEは最新を見る」と書いたのは、この話です。FOR UPDATEも同じ側だと、ここで揃えて覚えてください。
端末B(手順6)
COMMIT;
入れた行を戻します。端末A
DELETE FROM d_tx_item WHERE id = 4;
⑤ ロックを、目で見る
ここまでは「見え方」の話でした。ここからは「待たされる」話です。
5-1. FOR UPDATE で、もう一方が止まる
| 順番 | 端末A | 端末B |
|---|---|---|
| 1 | START TRANSACTION; | |
| 2 | SELECT * FROM d_tx_account WHERE id = 1 FOR UPDATE; | |
| 3 | START TRANSACTION; | |
| 4 | SELECT * FROM d_tx_account WHERE id = 1 FOR UPDATE; → 止まる | |
| 5 | COMMIT; | |
| 6 | (動き出す) |
端末A(手順1〜2)
START TRANSACTION;
SELECT * FROM d_tx_account WHERE id = 1 FOR UPDATE;
+----+--------+---------+
| id | name | balance |
+----+--------+---------+
| 1 | 佐藤 | 5555 |
+----+--------+---------+
端末B(手順3〜4)
START TRANSACTION;
SELECT * FROM d_tx_account WHERE id = 1 FOR UPDATE;
何も返ってきません。 カーソルが点滅したまま止まります。
これが「待つ」です。
FOR UPDATEは排他ロックを取ります。 排他ロックは1人しか持てないので、Aが手放すまでBは進めません。 エラーではありません。 Bは正常に、待っています。
端末A(手順5)
COMMIT;
端末B の画面を見てください。 その瞬間に結果が出ます。
+----+--------+---------+
| id | name | balance |
+----+--------+---------+
| 1 | 佐藤 | 5555 |
+----+--------+---------+
1 row in set (12.43 sec)
12.43 sec の部分が、待たされた時間です。 私が COMMIT を打つまでにかかった時間が、そのまま出ています。
端末B
COMMIT;
5-2. 待ちきれずに失敗する —— innodb_lock_wait_timeout
永遠には待ちません。 上限があります。
端末B
SELECT @@innodb_lock_wait_timeout;
+----------------------------+
| @@innodb_lock_wait_timeout |
+----------------------------+
| 50 |
+----------------------------+
50秒です。 実験のために5秒に縮めます。
端末B
SET SESSION innodb_lock_wait_timeout = 5;
| 順番 | 端末A | 端末B |
|---|---|---|
| 1 | START TRANSACTION; | |
| 2 | SELECT * FROM d_tx_account WHERE id = 1 FOR UPDATE; | |
| 3 | START TRANSACTION; | |
| 4 | SELECT * FROM d_tx_account WHERE id = 1 FOR UPDATE; → 5秒後にエラー | |
| 5 | COMMIT; | ROLLBACK; |
端末B(手順4) —— 5秒待って、これが出ます。
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
[スクリーンショット: 03_ロック待ちタイムアウト.png] 端末B に ERROR 1205 が出ている状態を撮ってください。左の端末A が COMMIT せずに止まっているところも一緒に写ると、原因が分かって理想です。
ERROR 1205は「データベースが壊れた」ではありません。「誰かが長くロックを持ちすぎている」というお知らせです。 本番でこれが出たら、疑うのはSQLではなく「トランザクションを長く持っているコード」です。⑧で扱います。ERROR 1205とERROR 1213(デッドロック)は別物です。1205 は待ち時間切れ、1213 は互いに待ち合って詰んだ。混同すると対処を間違えます。
両方を片づけます。
端末A
COMMIT;
端末B
ROLLBACK;
5-3. 誰が何をロックしているか見る —— performance_schema.data_locks
「Bが止まっている」ところまでは分かりました。では、何を待っているのかを見ます。
ここで問題があります。 待たされている端末Bでは、何も打てません。 待っているからです。
そこで、3つ目の端末を開きます。
端末C(見るだけの端末です)
PROMPT C>
では、Aにロックを取らせて、Bを待たせて、Cから覗きます。
| 順番 | 端末A | 端末B | 端末C |
|---|---|---|---|
| 1 | START TRANSACTION; | ||
| 2 | SELECT * FROM d_tx_account WHERE id = 1 FOR UPDATE; | ||
| 3 | SET SESSION innodb_lock_wait_timeout = 60; | ||
| 4 | START TRANSACTION; | ||
| 5 | SELECT * FROM d_tx_account WHERE id = 1 FOR UPDATE; → 待ち | ||
| 6 | ここで覗く | ||
| 7 | COMMIT; | ||
| 8 | COMMIT; |
端末C(手順6)
SELECT
ENGINE_TRANSACTION_ID AS `トランザクション`,
OBJECT_NAME AS `表`,
INDEX_NAME AS `索引`,
LOCK_TYPE AS `種類`,
LOCK_MODE AS `モード`,
LOCK_STATUS AS `状態`,
LOCK_DATA AS `対象`
FROM performance_schema.data_locks
WHERE OBJECT_NAME LIKE 'd\_tx\_%';
+------------------+--------------+---------+--------+-----------+---------+------+
| トランザクション | 表 | 索引 | 種類 | モード | 状態 | 対象 |
+------------------+--------------+---------+--------+-----------+---------+------+
| 421965 | d_tx_account | NULL | TABLE | IX | GRANTED | NULL |
| 421965 | d_tx_account | PRIMARY | RECORD | X,REC_NOT_GAP | WAITING | 1 |
| 421964 | d_tx_account | NULL | TABLE | IX | GRANTED | NULL |
| 421964 | d_tx_account | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 1 |
+------------------+--------------+---------+--------+-----------+---------+------+
読み方です。
| 列 | 意味 |
|---|---|
種類 が TABLE | 表全体に対する「意思表示」。IX は「この表のどこかの行を書き換えるつもりです」という予告。行そのものは止めない |
種類 が RECORD | 本命。実際の行ロック |
モード が X | 排他(exclusive)。1人しか持てない |
モード が S | 共有(shared)。複数人が持てる |
REC_NOT_GAP | その行だけを押さえている。すきまは押さえていない |
状態 が GRANTED | 取れている |
状態 が WAITING | 待っている。これが止まっている本人 |
対象 | 押さえている行の、索引上の値。 ここでは主キーの 1 |
GRANTED の側がA、WAITING の側がB です。 番号(421964 と 421965)で区別できます。
「誰が誰を待っているか」だけを見る表もあります。端末C
SELECT * FROM performance_schema.data_lock_waits\G
*************************** 1. row ***************************
REQUESTING_ENGINE_TRANSACTION_ID: 421965
BLOCKING_ENGINE_TRANSACTION_ID: 421964
「421965 が、421964 に止められている」と読みます。
さらに、それが誰の接続なのかまで出せます。端末C
SELECT
r.trx_mysql_thread_id AS `待っている接続`,
b.trx_mysql_thread_id AS `止めている接続`,
b.trx_query AS `止めている側のSQL`,
TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS `待ち秒数`
FROM performance_schema.data_lock_waits w
JOIN information_schema.innodb_trx r ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID
JOIN information_schema.innodb_trx b ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID;
+----------------+----------------+-------------------+----------+
| 待っている接続 | 止めている接続 | 止めている側のSQL | 待ち秒数 |
+----------------+----------------+-------------------+----------+
| 27 | 26 | NULL | 18 |
+----------------+----------------+-------------------+----------+
止めている側のSQLがNULLなのは、正常です。 止めている側は、いま何もしていないからです。ロックを取ったまま、次の命令を待ってプロンプトの前で座っているだけです。 これが、本番で起きたときに一番厄介な形です。犯人は動いていません。動いていないからこそ、ロックを持ち続けています。 最後の手段はKILL 26;で接続を切ることです(切られた側はロールバックされます)。ただしそれは対症療法で、なぜ長く持っていたのかを直さないと、また起きます。
片づけます。
端末A
COMMIT;
端末B
COMMIT;
5-4. 索引が無いと、1行のつもりが全行ロックになる
ここが、この記事で一番「知らないと死ぬ」ところです。
思い出してください。 さっきの data_locks に、こういう列がありました。
| 索引 |
| PRIMARY |
ロックは「行」に掛かっているのではありません。「索引の項目」に掛かっています。
InnoDB は、行を索引を通してしか触りません。 だからロックも索引に掛かります。 索引が無い列で
WHEREすると、InnoDB は全部の行を読むことになり、読んだ行を全部ロックします。
d_tx_memo には、索引が1つもありません。 実演します。
まず、索引が無いことを確認します。端末A
SHOW INDEX FROM d_tx_memo;
Empty set (0.00 sec)
1本もありません。
| 順番 | 端末A | 端末B |
|---|---|---|
| 1 | START TRANSACTION; | |
| 2 | UPDATE d_tx_memo SET note = 'A' WHERE memo_no = 3; | |
| 3 | START TRANSACTION; | |
| 4 | UPDATE d_tx_memo SET note = 'B' WHERE memo_no = 7; → 止まる | |
| 5 | ROLLBACK; | ROLLBACK; |
端末A(手順1〜2)
START TRANSACTION;
UPDATE d_tx_memo SET note = 'A' WHERE memo_no = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
1 row affected です。 Aが触ったのは1行だけです。
端末B(手順3〜4) —— まったく別の行(7番)です。
START TRANSACTION;
UPDATE d_tx_memo SET note = 'B' WHERE memo_no = 7;
止まります。
Aが触ったのは3番、Bが触ろうとしたのは7番。関係ないはずなのに、止まりました。
端末C から覗きます。
SELECT INDEX_NAME AS `索引`, LOCK_TYPE AS `種類`, LOCK_MODE AS `モード`,
LOCK_STATUS AS `状態`, COUNT(*) AS `件数`
FROM performance_schema.data_locks
WHERE OBJECT_NAME = 'd_tx_memo'
GROUP BY INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS;
+-------------------+--------+--------+---------+------+
| 索引 | 種類 | モード | 状態 | 件数 |
+-------------------+--------+--------+---------+------+
| NULL | TABLE | IX | GRANTED | 2 |
| GEN_CLUST_INDEX | RECORD | X | GRANTED | 9 |
| GEN_CLUST_INDEX | RECORD | X | WAITING | 1 |
+-------------------+--------+--------+---------+------+
GRANTED が9件あります。 表には8行しかないのに、9件です(最後の1件は「表の終わり」を表す番人です)。
つまり、Aは表の全部をロックしていました。
WHERE memo_no = 3と書いたのに、8行全部が止まりました。 索引が無いので、InnoDB は「3番かどうか」を調べるために全行を読むしかなく、読んだ行を全部押さえたからです。GEN_CLUST_INDEXは、主キーを書かなかったときに InnoDB が勝手に作る内部の索引です。ここに掛かっています。
片づけて、直します。
端末A
ROLLBACK;
端末B
ROLLBACK;
索引を張ります。端末A
ALTER TABLE d_tx_memo ADD INDEX idx_memo_no (memo_no);
もう一度、まったく同じことをやります。
| 順番 | 端末A | 端末B |
|---|---|---|
| 1 | START TRANSACTION; | |
| 2 | UPDATE d_tx_memo SET note = 'A' WHERE memo_no = 3; | |
| 3 | START TRANSACTION; | |
| 4 | UPDATE d_tx_memo SET note = 'B' WHERE memo_no = 7; → 通る | |
| 5 | ROLLBACK; | ROLLBACK; |
端末B(手順4)
UPDATE d_tx_memo SET note = 'B' WHERE memo_no = 7;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
止まりませんでした。
端末C からロックの数を数え直します。
SELECT INDEX_NAME AS `索引`, LOCK_TYPE AS `種類`, COUNT(*) AS `件数`
FROM performance_schema.data_locks
WHERE OBJECT_NAME = 'd_tx_memo'
GROUP BY INDEX_NAME, LOCK_TYPE;
+-------------------+--------+------+
| 索引 | 種類 | 件数 |
+-------------------+--------+------+
| NULL | TABLE | 2 |
| GEN_CLUST_INDEX | RECORD | 2 |
| idx_memo_no | RECORD | 4 |
+-------------------+--------+------+
件数が、9件から数件に減りました。
片づけます。端末A と 端末B、両方です。
ROLLBACK;
前回の索引の回では、索引は「速くするもの」として扱いました。 ここで、もう1つの顔が出ました。索引は「ロックの範囲を絞るもの」でもあります。 索引の無い列で
UPDATEすると、たとえ1行しか変えなくても、表全体が止まります。 ユーザーが1人のうちは絶対に気づきません。同時に触られたときだけ、サイト全体が固まります。 「速くならないから索引は要らない」という判断は、この観点を落としています。
⑥ デッドロックを、わざと起こす
本線の回で1回やりましたが、あそこでは「起きた」で終わりました。 ここでは記録を読みます。
6-1. 起こす
AとBが、同じ2行を「逆の順番で」触ります。
| 順番 | 端末A | 端末B |
|---|---|---|
| 1 | START TRANSACTION; | |
| 2 | UPDATE d_tx_account SET balance = balance - 1 WHERE id = 1; | |
| 3 | START TRANSACTION; | |
| 4 | UPDATE d_tx_account SET balance = balance - 1 WHERE id = 2; | |
| 5 | UPDATE d_tx_account SET balance = balance + 1 WHERE id = 2; → 待ち | |
| 6 | UPDATE d_tx_account SET balance = balance + 1 WHERE id = 1; → ERROR 1213 |
手順6 で、片方に必ずこれが出ます。
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
もう片方(待っていたほう)は、その瞬間に動き出します。
[スクリーンショット: 04_デッドロック.png] ERROR 1213 が出た端末と、その隣で待ちから解放された端末を、2つ並べて撮ってください。
どちらが殺されるかは、決まっていません。 InnoDB は「巻き戻すのが安いほう」を選びます。 殺されたほうは、トランザクションまるごとロールバック済みです。途中まで書いた分は残っていません。
残っているほうを閉じます。両方の端末で打ってください。
ROLLBACK;
6-2. 記録を読む —— LATEST DETECTED DEADLOCK
「デッドロックが出た」で終わらせると、次も同じ場所で出ます。 どの2つがぶつかったのかは、MySQL が全部覚えています。
どの端末でも構いません。ここでは端末C
SHOW ENGINE INNODB STATUS\G
出力は長いです。 LATEST DETECTED DEADLOCK という見出しを探してください。そこだけ抜き出します。
------------------------
LATEST DETECTED DEADLOCK
------------------------
2026-09-20 14:22:31 0x7f2a1c0d5700
*** (1) TRANSACTION:
TRANSACTION 421983, ACTIVE 24 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 26, OS thread handle 139..., query id 512 localhost root updating
UPDATE d_tx_account SET balance = balance + 1 WHERE id = 2
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 112 page no 4 n bits 72 index PRIMARY of table `appdb`.`d_tx_account`
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; ... 0: len 4; hex 80000001; asc ;;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 112 page no 4 n bits 72 index PRIMARY of table `appdb`.`d_tx_account`
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; ... 0: len 4; hex 80000002; asc ;;
*** (2) TRANSACTION:
TRANSACTION 421984, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 27, OS thread handle 139..., query id 513 localhost root updating
UPDATE d_tx_account SET balance = balance + 1 WHERE id = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 112 page no 4 n bits 72 index PRIMARY of table `appdb`.`d_tx_account`
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; ... 0: len 4; hex 80000002; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 112 page no 4 n bits 72 index PRIMARY of table `appdb`.`d_tx_account`
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; ... 0: len 4; hex 80000001; asc ;;
*** WE ROLL BACK TRANSACTION (2)
------------
初めて見ると読む気が失せますが、見るところは5つだけです。
| 見るところ | 何が分かるか |
|---|---|
*** (1) TRANSACTION: の下の SQL | 片方が最後に打った文。 ここでは WHERE id = 2 |
*** (2) TRANSACTION: の下の SQL | もう片方が最後に打った文。 ここでは WHERE id = 1 |
HOLDS THE LOCK(S) | その人がすでに持っているもの |
WAITING FOR THIS LOCK TO BE GRANTED | その人が欲しがっているもの |
WE ROLL BACK TRANSACTION (2) | どちらが殺されたか |
hex 80000001 と hex 80000002 に注目してください。 これが押さえていた主キーの値です。80000001 は 1、80000002 は 2 を表しています(先頭の 8 は符号の印で、実質は下の桁です)。
この5点を並べると、事件がそのまま出ます。
- (1) は 1 を持って、2 を待っていた
- (2) は 2 を持って、1 を待っていた
- (2) が殺された
つまり「触る順番が逆だった」以外の情報は要りません。 犯人はここで確定します。
LATESTは「最新の1件」という意味です。 次のデッドロックが起きると、この記録は上書きされます。 本番で「昨日の夜デッドロックが出た」と言われても、この方法では読めません。 そのときはinnodb_print_all_deadlocksを有効にして、エラーログに全部残す設定に切り替えます(この記事ではやりません)。
6-3. 防ぎ方は3つ
① 更新の順番を、全員で揃える
さっきの事件は「Aが 1→2、Bが 2→1」だから起きました。 id の小さい順に触ると決めれば、この形は起きません。
// 送金でも、必ず id の小さいほうから押さえる
const ids = [fromId, toId].sort((a, b) => a - b);
for (const id of ids) {
await trx("d_tx_account").where({ id }).forUpdate().first();
}
「順番を揃える」は、コードの見た目としては地味です。 でもデッドロック対策の8割はこれです。
② トランザクションを短くする
持っている時間が短ければ、ぶつかる確率がそのまま下がります。 ⑧で扱います。
③ それでも起きるので、リトライする
順番を揃えても、索引の張り方や実行計画によっては起きます。ゼロにはできません。
async function withRetry(fn, times = 3) {
for (let i = 0; i < times; i++) {
try {
return await fn();
} catch (err) {
// 1213 = デッドロック / 1205 = ロック待ちタイムアウト
if ((err.errno === 1213 || err.errno === 1205) && i < times - 1) {
await new Promise((r) => setTimeout(r, 50 * (i + 1)));
continue;
}
throw err;
}
}
}
リトライしてよい理由は、「トランザクションが丸ごと巻き戻っているから」です。中途半端な状態が残っていないので、最初からやり直しても二重に処理されません。 これは、他のエラーでは成り立ちません。 「エラーが出たらとりあえず再試行」は危険で、巻き戻しが保証されているデッドロックだからこそ安全です。
1205(待ち時間切れ)も、既定では文だけが巻き戻ってトランザクションは生きています。 安全にやり直すなら、トランザクションごとROLLBACKしてから最初からやってください。
⑦ 共有ロックと、待たない読み方
7-1. FOR SHARE —— 「私も読んでいる」
FOR UPDATE は1人しか持てませんでした。 複数人が同時に持てるロックもあります。
| 順番 | 端末A | 端末B |
|---|---|---|
| 1 | START TRANSACTION; | |
| 2 | SELECT * FROM d_tx_account WHERE id = 1 FOR SHARE; | |
| 3 | START TRANSACTION; | |
| 4 | SELECT * FROM d_tx_account WHERE id = 1 FOR SHARE; → 通る | |
| 5 | UPDATE d_tx_account SET balance = 1 WHERE id = 1; → 止まる | |
| 6 | ROLLBACK; | |
| 7 | (動き出す) → ROLLBACK; |
手順4 は通ります。 共有ロックは何人でも持てるからです。
手順5 で止まります。 Aが「読んでいる最中だから変えないで」と言っているからです。
FOR SHAREの意味は、「この行が、私のトランザクションの間ずっと存在して、変わらないことを保証してほしい」です。 典型的な使い方は、外部キーの参照先を確かめるときです。「この部署が存在することを確かめてから、社員を登録する」。確かめた直後に部署が消されたら困るので、共有ロックを掛けます。LOCK IN SHARE MODEは、FOR SHAREの古い書き方です。MySQL 8.0 でFOR SHAREに改まりました。動きますが、新しく書くならFOR SHAREです。
-- 同じ意味。下は古い書き方
SELECT * FROM d_tx_account WHERE id = 1 FOR SHARE;
SELECT * FROM d_tx_account WHERE id = 1 LOCK IN SHARE MODE;
共有ロックは、デッドロックを作りやすいという顔もあります。 AとBが両方
FOR SHAREを持ったあと、両方がUPDATEしようとすると、互いに相手の共有ロックが消えるのを待ちます。 これも立派なデッドロックです。 後で書き換えると分かっているなら、最初からFOR UPDATEを取るほうが安全です。
7-2. NOWAIT —— 待たずに、すぐ諦める
「待つ」以外の選択肢が2つあります。
| 順番 | 端末A | 端末B |
|---|---|---|
| 1 | START TRANSACTION; | |
| 2 | SELECT * FROM d_tx_account WHERE id = 1 FOR UPDATE; | |
| 3 | START TRANSACTION; | |
| 4 | SELECT * FROM d_tx_account WHERE id = 1 FOR UPDATE NOWAIT; → 即エラー | |
| 5 | ROLLBACK; | ROLLBACK; |
端末B(手順4) —— 待ちません。その場で返ります。
ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.
NOWAITは「取れないなら、待たずに失敗させてくれ」という指定です。 画面の「編集」ボタンを押したときに使います。 他の人が編集中なら、30秒待たせてからエラーを出すより、すぐ「いま他の人が編集中です」と出したほうが親切です。
7-3. SKIP LOCKED —— 他人の分は飛ばして、自分の分を取る
これがキューを作るときの定石です。
d_tx_job に4件の仕事が入っています。 2つのワーカー(AとB)が、同じ仕事を二重に処理しないように取り合います。
| 順番 | 端末A | 端末B |
|---|---|---|
| 1 | START TRANSACTION; | |
| 2 | SELECT * FROM d_tx_job WHERE state='waiting' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED; → 1番 | |
| 3 | START TRANSACTION; | |
| 4 | 同じSQL → 2番(待たない) | |
| 5 | UPDATE d_tx_job SET state='done', worker='A' WHERE id=1; | |
| 6 | UPDATE d_tx_job SET state='done', worker='B' WHERE id=2; | |
| 7 | COMMIT; | COMMIT; |
端末A(手順2)
START TRANSACTION;
SELECT * FROM d_tx_job
WHERE state = 'waiting'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;
+----+---------------+---------+--------+
| id | payload | state | worker |
+----+---------------+---------+--------+
| 1 | send-mail-001 | waiting | NULL |
+----+---------------+---------+--------+
端末B(手順4) —— まったく同じSQLです。
START TRANSACTION;
SELECT * FROM d_tx_job
WHERE state = 'waiting'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;
+----+---------------+---------+--------+
| id | payload | state | worker |
+----+---------------+---------+--------+
| 2 | send-mail-002 | waiting | NULL |
+----+---------------+---------+--------+
Bは待ちませんでした。そして、1番ではなく2番を取りました。
SKIP LOCKED は「他人がロックしている行は、無かったことにして次へ行く」という指定です。
残りを片づけます。手順5〜7 を打ってください。
端末A
UPDATE d_tx_job SET state = 'done', worker = 'A' WHERE id = 1;
COMMIT;
端末B
UPDATE d_tx_job SET state = 'done', worker = 'B' WHERE id = 2;
COMMIT;
結果を見ます。端末A
SELECT * FROM d_tx_job ORDER BY id;
+----+---------------+---------+--------+
| id | payload | state | worker |
+----+---------------+---------+--------+
| 1 | send-mail-001 | done | A |
| 2 | send-mail-002 | done | B |
| 3 | send-mail-003 | waiting | NULL |
| 4 | send-mail-004 | waiting | NULL |
+----+---------------+---------+--------+
2人のワーカーが、1件も重複せず、1件も取りこぼさずに処理しました。
本線の待たせるAPIをやめる — メッセージキューとRabbitMQの回で、専用のキューを立てました。
SKIP LOCKEDは、「まだそこまで要らないとき」の答えです。すでに持っているデータベースだけで、二重処理しないキューが作れます。 RabbitMQ や Kafka を入れるのは、「1台では捌けない」「配送を保証したい」といった理由が出てからで間に合います。 部品が1つ減れば、障害の切り分けも1段簡単になります。
3つの読み方を並べます。
| 書き方 | ロックされていたら |
|---|---|
FOR UPDATE | 待つ(既定。innodb_lock_wait_timeout まで) |
FOR UPDATE NOWAIT | 即エラー(ERROR 3572) |
FOR UPDATE SKIP LOCKED | その行を飛ばして、次の行を返す |
⑧ トランザクションを、長く持たない
ここまでで、待たせる仕組みは全部見ました。最後は、それを踏まえた設計の話です。
⑤-3 で、こういうものを見ました。
| 止めている側のSQL |
| NULL |
止めている側は、何もしていませんでした。
これがアプリで起きるときの、典型的なコードがこれです。
// ⚠️ やってはいけない
await knex.transaction(async (trx) => {
const order = await trx("orders").where({ id }).forUpdate().first();
// ここで外部の決済APIを呼ぶ(3秒かかることもある)
const result = await fetch("https://payment.example.com/charge", { ... });
await trx("orders").where({ id }).update({ state: "paid" });
});
動きます。テストも通ります。1人で使っている限り、一生気づきません。
問題は、fetch が返るのを待っている3秒のあいだ、この行のロックを持ち続けていることです。
- 相手のAPIが遅ければ、その分だけ持ちます
- 相手が固まって応答を返さなければ、タイムアウトまで持ちます
- その間、同じ行を触ろうとした全員が
ERROR 1205になります
データベースの接続は、有限です。 同時に持てる数に上限があります。 トランザクションを長く持つコードは、接続も長く握ります。 全部の接続が握られたら、そのSQLと何の関係も無いページまで開かなくなります。 「DBが重い」と言われて調べたら、原因は外部APIだったというのは、この形です。
直し方は、順番を変えるだけです。
// 1. 外部APIを先に呼ぶ(トランザクションの外)
const result = await fetch("https://payment.example.com/charge", { ... });
// 2. 結果が出てから、短くDBを触る
await knex.transaction(async (trx) => {
const order = await trx("orders").where({ id }).forUpdate().first();
if (order.state !== "pending") return; // 二重実行を防ぐ
await trx("orders").where({ id }).update({ state: "paid", ref: result.id });
});
トランザクションの中に残ったのは、SELECT と UPDATE の2文だけです。 ミリ秒で終わります。
守るのは、この3つだけです。
| ルール | 理由 |
|---|---|
| トランザクションの中で、ネットワークを待たない | 相手の遅さが、そのまま自分のロック時間になる |
| トランザクションの中で、人間の入力を待たない | 「確認画面で30分放置」がそのまま30分のロックになる |
START TRANSACTION から COMMIT までを、目で追える長さにする | 長い=ぶつかる確率が上がる。 デッドロックも増える |
これは、本線の落ちても全部は止めない — サーキットブレーカと縮退の回と、まったく同じ話です。 あの回は「遅い相手に巻き込まれて、自分まで止まらない」でした。 この回は「遅い相手を待つあいだ、データベースのロックを握らない」。 握っている資源が「接続」から「ロック」に変わっただけです。 障害が広がるときは、いつも「有限の資源を、待っている間ずっと握っている」という形をしています。
つまずいたところ
1. 端末を1つしか開かずに、30分ためした
最初、私はタブを切り替えながら1つの端末でやろうとしました。 START TRANSACTION を打って、別のことをして、また戻る。何も起きません。
当たり前でした。同じ接続なら、自分の変更は自分に見えます。 競合するには、接続が2本要ります。
これに気づくまで、「本に書いてあることが再現しない」と思っていました。 再現しないのではなく、実験になっていなかったのです。
2. 端末A に打つべきものを、端末B に打った
何度もやりました。 特に④の手順表は、AとBが交互に出てくるので間違えます。
PROMPT A> を設定してから、一度も間違えなくなりました。 プロンプトが A> と B> になるので、打つ前に目に入ります。
3. ダーティリードが再現しなかった
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; を、端末A のほうに打っていました。
分離レベルは「見る側」の設定です。 書く側を変えても何も起きません。変えるのは、覗き見したい側(B)です。
4. START TRANSACTION を先に打ったのに、新しい値が見えた
④-4 のところです。 「REPEATABLE READ なら開始時点で固定される」と思い込んでいました。
固定されるのは、最初に SELECT を打った瞬間です。開いただけでは、まだ何も決まっていません。
開いた瞬間に固定したいなら START TRANSACTION WITH CONSISTENT SNAPSHOT; です。
5. performance_schema.data_locks が空だった
トランザクションを COMMIT した後に見ていました。
ロックは COMMIT か ROLLBACK で消えます。 見るなら、待っている最中です。だから3つ目の端末(C)が要ります。待っている端末自身では打てません。
6. d_tx_memo の実験で、Bが止まらなかった
1回目は、Bを READ COMMITTED にしたまま実験していました。
READ COMMITTED では、InnoDB は条件に合わなかった行のロックを、その場で外します(半一貫読み取りという仕組みです)。だから止まりませんでした。
「索引が無いと全行ロック」がはっきり出るのは、既定の REPEATABLE READ のときです。実験の前に、SELECT @@transaction_isolation; で確認してください。
7. SHOW ENGINE INNODB STATUS の出力が多すぎて諦めかけた
画面が全部流れます。 \G を付けても長いです。
見出しで探すのが正解でした。 LATEST DETECTED DEADLOCK という行を目で追い、そこから WE ROLL BACK までだけを読みます。 上のほうにあるバッファプールやI/Oの統計は、この目的には要りません。
8. ERROR 1205 と ERROR 1213 を同じものだと思っていた
別物です。
| 意味 | 相手 | |
|---|---|---|
| 1205 | 待ち時間切れ | 相手は普通に生きている。 ただ長く持っているだけ |
| 1213 | デッドロック | 互いに待ち合って詰んだ。 片方が強制的に殺された |
1205 が出たら「誰が長く持っているか」を探します(data_lock_waits)。 1213 が出たら「触る順番」を疑います(SHOW ENGINE INNODB STATUS)。探す先が違います。
後片づけ
端末が2つ(あるいは3つ)開いています。片づけの順番が大事です。
1. 全部の端末で、トランザクションを閉じる
開いたままのトランザクションが1つでもあると、次の DROP TABLE が止まります。
端末A / 端末B / 端末C、全部で打ってください。
ROLLBACK;
打っていないトランザクションがあっても、エラーにはなりません。 念のため全部で打つのが安全です。
2. 残っているトランザクションが無いか、確認する
端末A
SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id
FROM information_schema.innodb_trx;
Empty set (0.00 sec)
Empty set なら、誰もトランザクションを開いていません。 何か出たら、その trx_mysql_thread_id の端末で ROLLBACK; を打ってください。
3. 表を消す
端末A
SHOW TABLES LIKE 'd\_tx\_%';
+------------------------+
| Tables_in_appdb (d_tx_%) |
+------------------------+
| d_tx_account |
| d_tx_item |
| d_tx_job |
| d_tx_memo |
+------------------------+
d\_tx\_%の\は「_を1文字として扱う」ための印です。LIKEでは_が「任意の1文字」の意味になるので、これが無いと余計な表まで引っかかります。
DROP TABLE d_tx_account;
DROP TABLE d_tx_item;
DROP TABLE d_tx_job;
DROP TABLE d_tx_memo;
4. 本線の表が無事なことを確かめる
SHOW TABLES;
+-----------------+
| Tables_in_appdb |
+-----------------+
| sessions |
| tasks |
| users |
+-----------------+
d_tx_ で始まる表が1つも無く、tasks / users / sessions が残っていれば完了です。
5. 設定を戻す
この記事で変えたのは、次の2つだけです。
| 変えたもの | 戻し方 |
|---|---|
transaction_isolation | SET SESSION なので、EXIT すれば消える |
innodb_lock_wait_timeout | 同上 |
念のため、手で戻しておきます。端末B
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION innodb_lock_wait_timeout = 50;
SELECT @@transaction_isolation, @@innodb_lock_wait_timeout;
+-------------------------+----------------------------+
| @@transaction_isolation | @@innodb_lock_wait_timeout |
+-------------------------+----------------------------+
| REPEATABLE-READ | 50 |
+-------------------------+----------------------------+
全部の端末を閉じます。
EXIT;
SET GLOBALは一度も打っていないので、他の接続や再起動後には何も残りません。 設定ファイルも触っていません。 実験で恒久設定を変えなかったのは、そのためです。
まとめ — 理解できたこと
この記事で分かったことを、7行にします。
- トランザクションの意味は、端末を2つ開くまで分からない。 1人で試すと、
START TRANSACTIONは書いても書かなくても同じ結果になる - 分離レベルは「他人の途中経過がどこまで見えるか」の設定。 上げるほど安全になるが、上げるほど待たされる。 つまみを回して解決する種類の問題ではない
- MySQL は
REPEATABLE READ、PostgreSQL はREAD COMMITTED。 ここが違うので、移植すると同じコードの挙動が変わる REPEATABLE READの景色は、最初にSELECTを打った瞬間に固まる。START TRANSACTIONの瞬間ではない。先に開いておけば安全、は間違い- 読み方は2種類ある。 普通の
SELECTは過去の景色、FOR UPDATE/UPDATE/DELETEはいまの最新。同じトランザクションの中で、答えが違って当然 - ロックは行ではなく索引に掛かる。 索引が無い列で
WHEREすると、1行のつもりが全行ロックになる。索引は速度だけの話ではない - デッドロックは正常な検出であって、バグではない。
SHOW ENGINE INNODB STATUSに、誰が何を持って何を待ったかが全部残っている。 直すのは触る順番、それでも起きるのでリトライ
この7つは、1つのことの言い換えです。
データベースは「同時に触られること」を前提に作られていて、その挙動は全部見える。
見えるのに、私は見ていませんでした。 分離レベルの表を10回読むより、端末を2つ並べて ERROR 1213 を1回自分で出したほうが、確実に身に付きます。
そして、ここで見た「有限の資源を、待っている間ずっと握らない」という考え方は、データベースの外でもそのまま使えます。 それが最後の節で書いたことです。
参考リンク
- roadmap.sh — SQL
- roadmap.sh — Backend
- MySQL 公式ドキュメント
- PostgreSQL — トランザクションの分離
- PostgreSQL — 明示的なロック
- SQLite — トランザクション
- SQLite — ロックと並行性
次に読む
この記事は 【深掘り】SQL の 13 / 17 本目です。
| 記事 | |
|---|---|
| ← 前の記事 | ビューとストアドプロシージャ — 列を消したら壊れた |
| → 次の記事 | インデックスとEXPLAIN — 張ったのに使われない |
上から順に読めば積み上がるように並べています。 飛ばしても構いませんが、詰まったら前の回に戻ってください。
全体の順路は はじめに にあります。
roadmap.sh のどこをやっているか
- 分離レベル4つと3つの現象を再現する
- ロックとデッドロック
この記事は、上の項目を実際に手を動かして確かめる回です。

コメント