全部か無しか。境界は BEGIN と COMMIT で宣言する

リクエストがアプリケーションに届くまで

ブラウザURL入力
DNS名前→IP
ルータ経路選択
ロードバランサ振り分け
Nginx受け口
アプリ処理
DB保存今ここ

前回は、索引を足しても返る行の集合は変わらず、変わるのは道筋だけであることを見ました。今回は「この文とこの文は、一緒に残るか一緒に消えるべきだ」という約束を、どう宣言するかを見ます。

まず結論

トランザクションは複数の文を「全部か無しか」にまとめる仕組みです。その「全部」がどこからどこまでかは、データベースが推し量るのではなく、アプリが BEGIN と COMMIT で宣言します。

途中の文が重複キーのエラーになったとき、PostgreSQL は宣言した範囲ごと捨て、MySQL は失敗した文だけを戻して続きを許します。どちらも COMMIT した範囲を全部か無しかで扱っており、違うのはエラーの後にその範囲を続けられるかどうかです。

? 読む前に予測

PostgreSQL 18 で、主キー id の表に `BEGIN; INSERT (1,'alice'); INSERT (1,'bob'); INSERT (2,'carol'); COMMIT;` を流しました。2つ目の INSERT は主キー重複でエラーになります。最後に表には何行残るでしょうか。

当てることが目的ではありません。外した予測ほど記憶に残るので、直感で選んでください。

抽象原理

「全部」の範囲は、誰が決めるのか

口座から口座へお金を移すなら、「引く」と「足す」の2つの更新が要ります。片方だけ残ると困ります。PostgreSQL のチュートリアルは、トランザクションの要点をこう書きます。

The essential point of a transaction is that it bundles multiple steps into a single, all-or-nothing operation.

では、どの文とどの文が同じまとまりなのか。データベースは文の中身からそれを知ることができません。アプリが BEGIN で開き、COMMIT(確定)か ROLLBACK(取り消し)で閉じて宣言します。

宣言しなければどうなるか。同じチュートリアルは If you do not issue a BEGIN command, then each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped around it. と書きます。1文ずつが、それぞれ別の「全部」になります。 MySQL も既定で同じ動き(autocommit)です。

全部か無しかの範囲は、アプリが送る2つの印で決まる

BEGINここからが一まとまり

アプリが送る文

INSERT alice

INSERT bob

INSERT carol

アプリが範囲を閉じる

COMMITまとまりを確定する

ROLLBACKまとまりを無かったことにする

・BEGIN を送らなければ、既定では1文ずつが別のトランザクションになる
・同じ性質は ACID の A(Atomicity)と呼ばれる。C・I・D はこの先の回で扱う

名前の由来にも一言。Gray 1981 は性質を Consistency Atomicity Durability の3つで挙げ、原子性を it either happens or it does not と書きました。この論文に ACID の語はありません。Haerder と Reuter 1983 は isolation を含む4つを並べ、(ACID) と書いています。

具体実物

同じ5文を PostgreSQL に流す

主キー id を持つ表 accounts に、次の5文と、確認の SELECT を流します。2つ目の INSERT は id が重複します。

BEGIN;
INSERT INTO accounts VALUES (1, 'alice');
INSERT INTO accounts VALUES (1, 'bob');
INSERT INTO accounts VALUES (2, 'carol');
COMMIT;
SELECT id, owner FROM accounts;

PostgreSQL 18.6 / psql -e(stderr を合流、2回実行して同一。見出し行のみ省略)コマンド出力

BEGIN;
BEGIN
INSERT INTO accounts VALUES (1, 'alice');
INSERT 0 1
INSERT INTO accounts VALUES (1, 'bob');
ERROR:  duplicate key value violates unique constraint "accounts_pkey"
DETAIL:  Key (id)=(1) already exists.
INSERT INTO accounts VALUES (2, 'carol');
ERROR:  current transaction is aborted, commands ignored until end of transaction block
COMMIT;
ROLLBACK
SELECT id, owner FROM accounts;
id | owner 
----+-------
(0 rows)
 
psql exit=0

重複のエラーの後、carol の INSERT は current transaction is aborted で無視されました。COMMIT に対するサーバの応答は ROLLBACK で、表は0行です。alice も残っていません。宣言した範囲の一部が失敗したので、範囲ごと捨てられました。

psql 自体は最後まで流し、終了コードは0です。スクリプトが成功したかを終了コードで見ても、この取り消しには気づけません。

✓ ここまでの確認

PostgreSQL で `COMMIT;` を送ったのに、応答が `ROLLBACK` でした。何が起きたと読むのが正しいですか。

抽象原理

失敗の後、範囲を続けられるかは DB が決める

PostgreSQL の用語集は原子性を either all its operations complete as a single unit or none do と定めます。では「失敗した文があれば必ず範囲ごと捨てる」のが原子性か、というとそうではありません。MySQL の公式文書はこう書きます。

InnoDB sometimes rolls back only the statement that failed, other times it rolls back the entire transaction.

重複キーについては A duplicate-key error rolls back the SQL statement, if you have not specified the IGNORE option in your statement. 戻るのは失敗した文だけで、トランザクションは開いたまま続きます。

そこでアプリが何を送るかで結果が決まります。ROLLBACK rolls back the current transaction, canceling its changes.(同じく公式文書)なので、ROLLBACK を送れば範囲全体が消えます。COMMIT を送れば、その時点で残っている変更全体がまとめて確定します。 COMMIT の場合に確定するのは、範囲に残っている変更の全体です。

具体実物

同じ5文を MySQL に流す

同じファイルを MySQL 8.4 へ流します。mysql クライアントは既定で最初のエラーで止まるので、まず --force(Continue even if an SQL error occurs.)を付けて最後まで送ります。エラーの後に ROLLBACK ではなく COMMIT を送った場合です。

MySQL 8.4.11 / mysql -t -v --force(2回実行して同一。区切り線と空行を省略)コマンド出力

BEGIN
INSERT INTO accounts VALUES (1, 'alice')
INSERT INTO accounts VALUES (1, 'bob')
ERROR 1062 (23000) at line 3: Duplicate entry '1' for key 'accounts.PRIMARY'
INSERT INTO accounts VALUES (2, 'carol')
COMMIT
SELECT id, owner FROM accounts
+----+-------+
| id | owner |
+----+-------+
|  1 | alice |
|  2 | carol |
+----+-------+
mysql exit=0

bob の文だけが戻り、alice と carol が確定しました。--force を付けないと結果が変わります。

MySQL 8.4.11 / --force なし、後で別の接続から数える(2回実行して同一。区切り線と空行を省略)コマンド出力

BEGIN
INSERT INTO accounts VALUES (1, 'alice')
INSERT INTO accounts VALUES (1, 'bob')
ERROR 1062 (23000) at line 3: Duplicate entry '1' for key 'accounts.PRIMARY'
mysql exit=1
--- new session:
SELECT COUNT(*) AS n FROM accounts
+---+
| n |
+---+
| 0 |
+---+
mysql exit=0

クライアントが止まって COMMIT は送られず、0行でした。公式文書が書くのは autocommit を無効にしたセッションの場合(If a session that has autocommit disabled ends without explicitly committing the final transaction, MySQL rolls back that transaction.)で、BEGIN で始めた今回の場合を直接書いた文は見つかっていません。0行は実測です。

✓ ここまでの確認

MySQL 8.4 で同じ5文を `--force` 付きで流し、エラーの後も COMMIT を送ったところ alice と carol が残りました。この結果の読み方として適切なのはどれですか。

⇄ 2つを見比べる

具体例 A

データベースのトランザクション

境界の印は、アプリが送る BEGIN と COMMIT です。途中でエラーが出たとき範囲をどう扱うかは DB によって違いますが、確定は COMMIT が受け付けられたときだけです。

今回の実測表

場面                         結果
PG: 重複キー後に COMMIT       応答は ROLLBACK、0行
MySQL: 重複キー後に COMMIT    失敗文だけ戻り、2行確定
MySQL: COMMIT 前に切断        0行

具体例 B

ext4 のジャーナル(別の図解で扱う)

ファイルシステムは複数のブロック更新を1つのトランザクションにまとめてジャーナルに書き、最後にコミットブロックを書きます(既定の data=ordered でジャーナルを通るのはメタデータのみ)。再起動時、コミットブロックがあれば本体へ再適用し、無ければ捨てます。

図解『ジャーナリング』の要約表

場面                         結果
コミットブロックが届いた      再起動時に本体へ再適用
コミットブロックが届く前に停止  以降を破棄

どちらも「最後の印」で全部か無しかを決めています。では、どこまでが一まとまりかを知っているのは、それぞれ誰でしょうか。

どのブロック更新を一まとまりにするかを決めるのは誰か。alice と carol の登録が1つの用事かを知っているのは誰か。

実務の場面エージェントが書いた『トランザクションで囲んだので安全です』というコードを見る

生成されたコードが、BEGIN の後に3つの INSERT を送り、例外を捕まえてログに書き、そのまま COMMIT へ進んでいたとします。報告には「トランザクションで囲んだので、途中で失敗しても不整合は起きません」とあります。

囲んだだけでは足りません。失敗の後に何を送るかが、アプリの側に残っています。 MySQL で重複キーなら、失敗した文だけが戻り、残りが確定します。PostgreSQL なら COMMIT への応答が ROLLBACK になり、何も残りません。同じコードが DB によって別の結果を出します。

確かめるのは2つ。例外の後に送っているのは ROLLBACK か。COMMIT の応答を確かめているか。PostgreSQL のチュートリアルは Some client libraries issue BEGIN and COMMIT commands automatically とも書くので、ライブラリが何を送るかも文書で確かめます。

この章のまとめ

トランザクションは複数の文を 全部か無しか にまとめる。範囲は アプリが BEGIN と COMMIT で宣言する。BEGIN を送らなければ1文ずつが別の範囲になる。
PostgreSQL は途中のエラーで範囲が中断状態になり、COMMIT への応答が ROLLBACK になる。表は0行。
MySQL は重複キーで 失敗した文だけ を戻す。ROLLBACK ではなく COMMIT を送った場合、残りが確定する。ROLLBACK を送れば全部が消える。
mysql クライアントは既定で最初のエラーで止まる。COMMIT が送られず、実測では0行。
Gray 1981 の性質は3つで ACID の語は無い。4つを並べて (ACID) と書いたのは Haerder と Reuter 1983。

自分の現場で確かめる

読んだだけでは身につきません。手元の環境で実際に見てみてください。

自分のコードでトランザクションを開始している箇所を探し、途中の例外を捕まえたあと COMMIT に進む経路が無いか確かめる
使っている DB が PostgreSQL か MySQL かを確かめ、途中でエラーが出たあと COMMIT を送ると何が残るかを、今回の2つの型のどちらになるかで書き出す
使っているライブラリが BEGIN と COMMIT を自動で送るかを、そのライブラリの文書で確かめる

理解度チェック

読んだ直後は「分かった」と感じやすいものです。実際に解いて確かめてください。

問題 1 / 3正解: 0

ACID という語について、原典で確かめられる記述はどれですか。

用語集(4語)

トランザクション: 複数の文を1つのまとまりとして扱う単位。PostgreSQL のチュートリアルは `a single, all-or-nothing operation` と書く。
BEGIN / COMMIT / ROLLBACK: まとまりの開始、確定、取り消し。BEGIN を送らなければ、既定では1文ずつが別のトランザクションになる。
原子性 (Atomicity): トランザクションの操作が全部完了するか、どれも完了しないかのどちらかになる性質。ACID の A。
ACID: Atomicity・Consistency・Isolation・Durability の頭文字。この4つを並べて (ACID) と書いた論文に Haerder と Reuter 1983 がある。