トランザクション分離レベル — 同じSELECTが2回目で変わる

トランザクション分離レベル。二台の端末と共有データベース、アクセスを制御する南京錠。

本線の回を書き終えたあと、私はしばらく分かった気になっていました。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 を使うと、待たずに「他の人が持っていない行」だけを取れること。キューをデータベースだけで作れること

この記事でやること

  1. 端末を2つ開き、練習用の表(d_tx_ で始まる4つ)を作る
  2. READ UNCOMMITTED にして、コミット前の値が見えるところ(ダーティリード)を再現する
  3. READ COMMITTED で、同じ SELECT の答えが変わるところ(ノンリピータブルリード)を再現し、REPEATABLE READ では変わらないことを確かめる
  4. SELECT ... FOR UPDATE でもう一方を待たせ、innodb_lock_wait_timeout で失敗させ、performance_schema.data_locks で中身を見る
  5. 索引の無い列で UPDATE して、関係ない行まで止まるところを実演し、索引を張って直す
  6. デッドロックをわざと起こして ERROR 1213 を出し、SHOW ENGINE INNODB STATUS の記録を読む
  7. NOWAIT と SKIP LOCKED でキューを作り、最後に両方の端末を片づけて設定を戻す

先に知る言葉

この記事で初めて出てくる言葉です。 基本的な言葉は用語集にもまとめてあります。

言葉意味
分離レベル他人がまだ確定させていない(あるいは確定させたばかりの)変更が、自分にどこまで見えるかの設定。 接続ごとに変えられる
ダーティリード他人がまだ COMMIT していない値を読んでしまうこと。相手が ROLLBACK したら、読んだ値は存在しなかったことになる
ノンリピータブルリード同じ行を2回読んだら、値が変わっていたこと。間に他人が UPDATE して COMMIT した
ファントムリード同じ条件で2回読んだら、行の数が変わっていたこと。間に他人が INSERT した。幻(phantom)の行
スナップショットある瞬間のデータベース全体の見え方を、そのまま固定したもの。 InnoDB は履歴を持っていて、過去の姿を再現できる
共有ロック「私も読んでいるので、書き換えないでください」という印。複数人が同時に持てる
排他ロック「私が書き換えます」という印。1人しか持てない。 他の人は待たされる
ギャップロック行そのものではなく、行と行の「すきま」に掛けるロック。 そこへの INSERT を止める。ファントムを防ぐための仕組み
デッドロックAがBの持っているものを待ち、BがAの持っているものを待っている状態。 放置すると永遠に終わらない
InnoDBMySQL の標準のストレージエンジン。ロックやトランザクションを実際に担当している部分

① 準備 — 端末を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

bash
$ sudo mysql

端末B

bash
$ sudo mysql

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
PostgreSQLREAD COMMITTED
OracleREAD COMMITTED
SQL ServerREAD 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つずつ再現する

ここからが本番です。

やり方の型はどれも同じです。

  1. 端末B の分離レベルを変える(見る側がBです)
  2. 端末B でトランザクションを開始して、1回読む
  3. 端末A で、何かをする
  4. 端末B で、もう一度同じものを読む
  5. 答えが変わったかどうかを見る

「見る側が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
1START TRANSACTION;
2SELECT balance FROM d_tx_account WHERE id = 1; → 10000
3START TRANSACTION;
4UPDATE d_tx_account SET balance = 1 WHERE id = 1; (COMMITしない)
5SELECT balance FROM d_tx_account WHERE id = 1; → 1
6ROLLBACK;
7SELECT balance FROM d_tx_account WHERE id = 1; → 10000
8COMMIT;

実際の画面です。

端末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
1START TRANSACTION;
2SELECT balance FROM d_tx_account WHERE id = 1; → 10000
3UPDATE d_tx_account SET balance = 7777 WHERE id = 1; (autocommitで即確定)
4SELECT balance FROM d_tx_account WHERE id = 1; → 7777
5COMMIT;

端末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
1START TRANSACTION;
2SELECT balance FROM d_tx_account WHERE id = 1; → 7777
3UPDATE d_tx_account SET balance = 3333 WHERE id = 1;
4SELECT balance FROM d_tx_account WHERE id = 1; → 7777(変わらない)
5COMMIT;
6SELECT 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
1START TRANSACTION; (まだ何も読まない)
2UPDATE d_tx_account SET balance = 5555 WHERE id = 1;
3SELECT balance FROM d_tx_account WHERE id = 1; → 5555
4COMMIT;

端末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
1START TRANSACTION;
2SELECT COUNT(*) FROM d_tx_item WHERE shop_id = 10; → 2
3INSERT INTO d_tx_item VALUES (4, 'ふせん', 3, 10);
4SELECT COUNT(*) FROM d_tx_item WHERE shop_id = 10; → 2(変わらない)
5SELECT COUNT(*) FROM d_tx_item WHERE shop_id = 10 FOR UPDATE; → 3
6COMMIT;

端末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
1START TRANSACTION;
2SELECT * FROM d_tx_account WHERE id = 1 FOR UPDATE;
3START TRANSACTION;
4SELECT * FROM d_tx_account WHERE id = 1 FOR UPDATE; → 止まる
5COMMIT;
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
1START TRANSACTION;
2SELECT * FROM d_tx_account WHERE id = 1 FOR UPDATE;
3START TRANSACTION;
4SELECT * FROM d_tx_account WHERE id = 1 FOR UPDATE; → 5秒後にエラー
5COMMIT;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(見るだけの端末です)

bash
$ sudo mysql
PROMPT C> 

では、Aにロックを取らせて、Bを待たせて、Cから覗きます。

順番端末A端末B端末C
1START TRANSACTION;
2SELECT * FROM d_tx_account WHERE id = 1 FOR UPDATE;
3SET SESSION innodb_lock_wait_timeout = 60;
4START TRANSACTION;
5SELECT * FROM d_tx_account WHERE id = 1 FOR UPDATE; → 待ち
6ここで覗く
7COMMIT;
8COMMIT;

端末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
1START TRANSACTION;
2UPDATE d_tx_memo SET note = 'A' WHERE memo_no = 3;
3START TRANSACTION;
4UPDATE d_tx_memo SET note = 'B' WHERE memo_no = 7; → 止まる
5ROLLBACK;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
1START TRANSACTION;
2UPDATE d_tx_memo SET note = 'A' WHERE memo_no = 3;
3START TRANSACTION;
4UPDATE d_tx_memo SET note = 'B' WHERE memo_no = 7; → 通る
5ROLLBACK;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
1START TRANSACTION;
2UPDATE d_tx_account SET balance = balance - 1 WHERE id = 1;
3START TRANSACTION;
4UPDATE d_tx_account SET balance = balance - 1 WHERE id = 2;
5UPDATE d_tx_account SET balance = balance + 1 WHERE id = 2; → 待ち
6UPDATE 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
1START TRANSACTION;
2SELECT * FROM d_tx_account WHERE id = 1 FOR SHARE;
3START TRANSACTION;
4SELECT * FROM d_tx_account WHERE id = 1 FOR SHARE; → 通る
5UPDATE d_tx_account SET balance = 1 WHERE id = 1; → 止まる
6ROLLBACK;
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
1START TRANSACTION;
2SELECT * FROM d_tx_account WHERE id = 1 FOR UPDATE;
3START TRANSACTION;
4SELECT * FROM d_tx_account WHERE id = 1 FOR UPDATE NOWAIT; → 即エラー
5ROLLBACK;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
1START TRANSACTION;
2SELECT * FROM d_tx_job WHERE state='waiting' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED; → 1番
3START TRANSACTION;
4同じSQL → 2番(待たない)
5UPDATE d_tx_job SET state='done', worker='A' WHERE id=1;
6UPDATE d_tx_job SET state='done', worker='B' WHERE id=2;
7COMMIT;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_isolationSET 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行にします。

  1. トランザクションの意味は、端末を2つ開くまで分からない。 1人で試すと、START TRANSACTION は書いても書かなくても同じ結果になる
  2. 分離レベルは「他人の途中経過がどこまで見えるか」の設定。 上げるほど安全になるが、上げるほど待たされる。 つまみを回して解決する種類の問題ではない
  3. MySQL は REPEATABLE READ、PostgreSQL は READ COMMITTED。 ここが違うので、移植すると同じコードの挙動が変わる
  4. REPEATABLE READ の景色は、最初に SELECT を打った瞬間に固まる。 START TRANSACTION の瞬間ではない。先に開いておけば安全、は間違い
  5. 読み方は2種類ある。 普通の SELECT は過去の景色、FOR UPDATE / UPDATE / DELETE はいまの最新。同じトランザクションの中で、答えが違って当然
  6. ロックは行ではなく索引に掛かる。 索引が無い列で WHERE すると、1行のつもりが全行ロックになる。索引は速度だけの話ではない
  7. デッドロックは正常な検出であって、バグではない。 SHOW ENGINE INNODB STATUS に、誰が何を持って何を待ったかが全部残っている。 直すのは触る順番、それでも起きるのでリトライ

この7つは、1つのことの言い換えです。

データベースは「同時に触られること」を前提に作られていて、その挙動は全部見える。

見えるのに、私は見ていませんでした。 分離レベルの表を10回読むより、端末を2つ並べて ERROR 1213 を1回自分で出したほうが、確実に身に付きます。

そして、ここで見た「有限の資源を、待っている間ずっと握らない」という考え方は、データベースの外でもそのまま使えます。 それが最後の節で書いたことです。

参考リンク


次に読む

この記事は 【深掘り】SQL の 13 / 17 本目です。

記事
← 前の記事ビューとストアドプロシージャ — 列を消したら壊れた
→ 次の記事インデックスとEXPLAIN — 張ったのに使われない

上から順に読めば積み上がるように並べています。 飛ばしても構いませんが、詰まったら前の回に戻ってください。

全体の順路は はじめに にあります。

roadmap.sh のどこをやっているか

Backend›SQL — Transactions
  • 分離レベル4つと3つの現象を再現する
  • ロックとデッドロック

この記事は、上の項目を実際に手を動かして確かめる回です。

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

コメント

コメントする

目次