SQLトランザクションとWAL：database is lockedの3つの原因

シリーズ：SQL 講座 5/6（上級）。 クエリもインデックスも書けるようになり、データベースに同時に 2 つ以上のものが触る環境を扱い始めた方向けです。複数ワーカーの Web アプリ、API の横で動くバッチ、バックグラウンド同期のあるスマホアプリなどです。読み終えると、トランザクションが保証することとしないことが分かり、大量書き込みを 100 倍以上速くでき、出会う database is locked をすべて切り分けられるようになります。

トランザクションのバグの多くは、プロセスが 1 つしか DB を使わない開発環境では見えません。本番で「月に一度だけポイント残高が合わない」「ログに database is locked が出るが再現できない」という形で現れます。在庫の引当やポイント加算のように、2 つの更新を必ずセットで行う処理を書いている方には直接関係します。この講座では、2 つの sqlite3 プロセスと数行の Python でわざと再現し、正確なメッセージを示し、トランザクションが書き込み速度に与える影響を測ります。

実行環境： SQLite 3.51.0 の CLI と Python 3.9.6 の sqlite3 モジュール（同梱の SQLite も 3.51.0）、Apple Silicon Mac（M1 Pro）、macOS、APFS の SSD。MySQL と PostgreSQL は使えなかったので、それらについての記述には「ここでは実行していない」と明記します。

トランザクションとは何か、最初に出会うエラー

トランザクションは、DB が「全部まとめて反映するか、何も反映しないか」のどちらかで扱う文のまとまりです。BEGIN で開き、COMMIT で確定し、ROLLBACK で捨てます。BEGIN の外では、SQLite はすべての文をそれぞれ小さなトランザクションとして実行します。これが自動コミット（autocommit）モードです。

例は 2 つの口座間の振替で、残高は決してマイナスにならないという規則を付けます。

CREATE TABLE accounts (
  id      INTEGER PRIMARY KEY,
  owner   TEXT    NOT NULL,
  balance INTEGER NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts VALUES (1, 'alice', 100), (2, 'bob', 50);

誰もが最初の週に見る 2 つのエラーです。

sqlite> COMMIT;
Runtime error near line 1: cannot commit - no transaction is active
sqlite> BEGIN;
sqlite> BEGIN;
Runtime error near line 3: cannot start a transaction within a transaction

トランザクションは入れ子にできません。「トランザクションの中のトランザクション」が欲しいときは、名前付きの戻り地点であるセーブポイントを使います。

SAVEPOINT transfer;
UPDATE accounts SET balance = balance + 150 WHERE id = 2;
UPDATE accounts SET balance = balance - 150 WHERE id = 1;  -- CHECK fails
ROLLBACK TO transfer;   -- undoes the first UPDATE too
RELEASE transfer;

Runtime error near line 3: CHECK constraint failed: balance >= 0 (19)
1|alice|100
2|bob|50

よくある間違い：エラーが起きればロールバックされると思い込む

間違い。 alice の残高は 100 なのに 150 を送ろうとします。コードは 2 つの UPDATE を BEGIN … COMMIT で囲み、CHECK 制約が守ってくれると考えています。

BEGIN;
UPDATE accounts SET balance = balance + 150 WHERE id = 2;
UPDATE accounts SET balance = balance - 150 WHERE id = 1;
COMMIT;
SELECT * FROM accounts;
SELECT 'total', sum(balance) FROM accounts;

実際に起きること。 失敗した文だけが取り消され、トランザクションは開いたまま。COMMIT は成功した文を確定させます。

Runtime error near line 6: CHECK constraint failed: balance >= 0 (19)
1|alice|100
2|bob|200
total|300

合計 150 のお金が無から生まれました。（PostgreSQL は違い、エラー後は「current transaction is aborted」としてロールバックするまで以降の文をすべて拒否します。ここでは実行していません。）

直し方。 どれか 1 文でも失敗したら自分でロールバックします。スクリプトなら sqlite3 -bail が最初のエラーで止まり、トランザクションが開いたままプロセスが終わるので SQLite がロールバックします。

$ sqlite3 -bail bank.db < transfer.sql
Runtime error near line 3: CHECK constraint failed: balance >= 0 (19)
$ echo $?
1
$ sqlite3 bank.db "SELECT * FROM accounts"
1|alice|100
2|bob|50

アプリのコードでは言語のトランザクション用の仕組みを使います。Python の with con: は成功ならコミット、例外ならロールバックします。同じファイルで Python 特有の罠も見せます。

import sqlite3
print("SQLite", sqlite3.sqlite_version)

# Trap 1: Python 3.9's sqlite3 opens a transaction for you before INSERT/UPDATE/DELETE
con = sqlite3.connect("bank.db")
con.execute("UPDATE accounts SET balance = 999 WHERE id = 1")
print("in_transaction:", con.in_transaction)
con.close()                                   # no commit() -> rolled back
print("after close:", sqlite3.connect("bank.db").execute("SELECT balance FROM accounts WHERE id=1").fetchone())

# Fix: one transfer = one transaction; `with con:` commits, or rolls back on exception
def transfer(con, src, dst, amount):
    with con:
        con.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", (amount, dst))
        con.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", (amount, src))

con = sqlite3.connect("bank.db")
try:
    transfer(con, 1, 2, 150)
except sqlite3.IntegrityError as e:
    print("IntegrityError:", e)
print(con.execute("SELECT id, balance FROM accounts").fetchall())
transfer(con, 1, 2, 30)
print(con.execute("SELECT id, balance FROM accounts").fetchall())

SQLite 3.51.0
in_transaction: True
after close: (100,)
IntegrityError: CHECK constraint failed: balance >= 0
[(1, 100), (2, 50)]
[(1, 70), (2, 80)]

前半は逆方向の驚きです。Python が黙ってトランザクションを開き、誰も commit() を呼ばなかったので、更新が「消えました」。

実測：INSERT 1 万件、自動コミットと 1 トランザクション

同じ 10,000 件の INSERT 文（random.Random(42) で生成）を設定を変えて sqlite3 に流しました。各設定を新しい DB で 5 回実行しています。時間には sqlite3 プロセスの起動が含まれ、起動だけで 6ms でした。

教訓は 3 つです。1 トランザクションにまとめると約 195 倍速くなりました。同期を切っても（synchronous=OFF。停電時に壊れる危険あり）自動コミットは半分にしかなりません。DELETE モードではコミットごとに -journal ファイルを作って消すからです。そして、まとめられない場合（リクエストごとに 1 行コミットする Web アプリなど）は WAL + synchronous=NORMAL が最善でした。

1 行目のばらつきは本物です。マシンでは他の処理も動いており、ファイル作成はその影響をいちばん受けやすい操作です。

ロールバックジャーナルと WAL：誰が誰を待たせるか

SQLite がトランザクションを守る方式は 2 つあります。既定のロールバックジャーナル（journal_mode=DELETE）は元のページを -journal ファイルに写してから DB ファイル自体に書きます。WAL（write-ahead log、journal_mode=WAL）は変更を -wal ファイルに追記し、チェックポイントで書き戻すまで DB ファイルには触りません。

w.db 8192          <- after 10,000 inserts in WAL mode
w.db-wal 189552
w.db-shm 32768
(0, 0, 0)          <- PRAGMA wal_checkpoint(TRUNCATE)
w.db 188416
w.db-wal 0

実務での違いは同時実行です。次のテストでは、プロセス A が読み取りトランザクションを開いて 3 秒保持し、1 秒後にプロセス B が書き込もうとします。

( echo "BEGIN;"; echo "SELECT 'A sees', balance FROM accounts WHERE id = 1;"; sleep 3
  echo "SELECT 'A still sees', balance FROM accounts WHERE id = 1;"; echo "COMMIT;"
  echo "SELECT 'A after COMMIT', balance FROM accounts WHERE id = 1;" ) | sqlite3 bank.db &
sleep 1
printf "UPDATE accounts SET balance = 0 WHERE id = 1;\nSELECT 'B wrote', balance FROM accounts WHERE id = 1;\n" | sqlite3 bank.db
wait

--- delete mode
A sees|100
Runtime error near line 1: database is locked (5)
B wrote|100
A still sees|100
A after COMMIT|100
--- wal mode
A sees|100
B wrote|0
A still sees|100
A after COMMIT|0

DELETE モードでは読み取りが書き込みのコミットを妨げます。WAL モードでは両方とも進み、A は自分のトランザクションが終わるまで 100 を見続けます。A はスナップショットを読んでいるのです。スナップショット分離が 3 行で見えます。

「database is locked」の 3 つの原因

1. 別の接続が書き込み中で、待たなかった。 CLI の busy timeout は 0 なので、書き込み中の書き込みは即座に失敗します。

$ ( echo "BEGIN IMMEDIATE;"; echo "UPDATE accounts SET balance = balance - 10 WHERE id = 1;"; sleep 3; echo "COMMIT;" ) | sqlite3 bank.db &
$ sleep 1; sqlite3 bank.db "UPDATE accounts SET balance = balance + 10 WHERE id = 2;"; echo "exit=$?"
Error: stepping, database is locked (5)
exit=5

直し方：busy timeout。.timeout 5000 を付けると B は A のコミットを 2.05 秒待って成功しました。Python の sqlite3.connect() は最初から 5 秒を設定しており（PRAGMA busy_timeout は 5000 を返しました）、同じ処理が CLI では失敗し Python では通る理由はこれです。

2. ロールバックジャーナルでの長い読み取りが書き込みのコミットを妨げる。上で見たとおりです。直し方：WAL モードにするか、読み取りトランザクションを短くします。

3. WAL モードの「昇格」の罠。 timeout が効かないので、経験者でも驚きます。普通の BEGIN（遅延トランザクション）で始め、先に読み、他の接続がコミットした後に書こうとすると起きます。

( echo ".timeout 5000"; echo "BEGIN;"; echo "SELECT 'A read', balance FROM accounts WHERE id = 1;"; sleep 2
  echo ".timer on"; echo "UPDATE accounts SET balance = balance - 30 WHERE id = 1;"; echo "COMMIT;" ) | sqlite3 bank.db &
sleep 1
sqlite3 bank.db ".timeout 5000" "UPDATE accounts SET balance = balance - 50 WHERE id = 1;"
wait

A read|100
Runtime error near line 5: database is locked (5)
Run Time: real 0.000 user 0.000044 sys 0.000030
Run Time: real 0.000 user 0.000005 sys 0.000005

（2 行目の Run Time は COMMIT のもので、A の読み取りトランザクションを閉じただけです。）

5 秒の timeout があるのに A は 1ms 未満で失敗しました。A のスナップショットは B のコミットより古いので、A に書かせると見ていないデータの上に書くことになります。待っても解決しないので、SQLite は SQLITE_BUSY（拡張コードは SQLITE_BUSY_SNAPSHOT）を即座に返します。Python では同じものが sqlite3.OperationalError: database is locked になります。BEGIN IMMEDIATE で始めると A は最初に書き込みロックを取り、B は A を待ち、両方の更新が反映されました。最終残高は 20（100 − 30 − 50）です。

更新の消失：アプリのコードに潜む競合

ロックのエラーは一切出ないのに、お金が消えます。2 つのワーカーが残高を読み、アプリ側で計算し、書き戻します（Python、2 接続、順序は固定）。

import sqlite3
a = sqlite3.connect("bank.db", isolation_level=None)   # two app workers, autocommit mode
b = sqlite3.connect("bank.db", isolation_level=None)
q = "SELECT balance FROM accounts WHERE id = 1"
ba = a.execute(q).fetchone()[0]      # worker A reads 100
bb = b.execute(q).fetchone()[0]      # worker B reads 100
a.execute("UPDATE accounts SET balance = ? WHERE id = 1", (ba - 30,))
b.execute("UPDATE accounts SET balance = ? WHERE id = 1", (bb - 50,))
print("read-modify-write in app:", a.execute(q).fetchone()[0], "(expected 20)")

a.execute("UPDATE accounts SET balance = 100 WHERE id = 1")
a.execute("UPDATE accounts SET balance = balance - 30 WHERE id = 1")
b.execute("UPDATE accounts SET balance = balance - 50 WHERE id = 1")
print("atomic UPDATE          :", a.execute(q).fetchone()[0])

read-modify-write in app: 50 (expected 20)
atomic UPDATE          : 20

直し方（優先順）：計算を SQL の中で行う（SET balance = balance - ?）。どうしても先に読むなら、読みと書きを BEGIN IMMEDIATE … COMMIT の中で行う。他の DB なら SELECT … FOR UPDATE（SQLite にはこの句はありません）。在庫の引当で「残り 1 個を 2 人が買えた」も同じ構造です。

分離レベル：SQLite と他の DB

分離レベルとは、同時に動くトランザクションが互いの変更をどこまで見えるかの度合いです。SQL 標準は、許す異常現象によって 4 段階を定めています。SQLite は単純で、書き込みは常に 1 つだけなので、実質的に serializable です。WAL モードの読み取りは上で見たとおりスナップショットを読みます。（PRAGMA read_uncommitted は、ほとんど使われない共有キャッシュモードにしか効きません。）

他の DB の行は各ドキュメントに基づくもので、実行結果ではありません。実務上の結論：上の更新の消失はどの DB でも Read Committed で起きるので、アトミックな UPDATE という直し方はどこでも通用します。

どの状況でどう設定するか

練習問題

1. 夜間バッチが Python 3.9 で 1 行ずつ execute() して 5 万行を INSERT し、最後に commit() を呼びます。これは自動コミットで動いていますか？ 4 万行目でクラッシュしたらどうなりますか？
2. WAL モードのアプリが、busy_timeout が 5000 なのに数ミリ秒で database is locked になることがあります。原因として考えられるパターンと、ひと言で済む直し方は？

解答

1. いいえ。Python 3.9 の sqlite3（既定 isolation_level=""）は最初の INSERT の前にトランザクションを開くので、5 万行全体で 1 トランザクションです。速く、4 万行目でクラッシュすると開いたトランザクションはロールバックされ何も残りません。os._exit(1) で 4 万行目のクラッシュを再現したところ、in_transaction は True、その後の SELECT count(*) は 0 でした。たいていは望ましい動作ですが、多くの人の思い込みとは逆です。
2. 先に読んで後から書く遅延 BEGIN で、その間に別の接続がコミットした場合（上の原因 3）。BEGIN IMMEDIATE を使います。

正直な限界とバージョンの注意

• 実行したのは SQLite 3.51.0（CLI と Python 3.9.6 同梱ライブラリ）だけです。WAL モードは SQLite 3.7.0 から、セーブポイントは 3.6.8 からあります。
• この macOS のビルドは既定が synchronous=FULL で、コンパイルオプション（DEFAULT_WAL_SYNCHRONOUS=1）により WAL の DB では NORMAL になります。標準ビルドではどちらも FULL です。PRAGMA fullfsync は 0 だったので同期は通常の fsync() で、macOS ではドライブ自身のキャッシュまでは書き出させません。Linux や fullfsync=ON では自動コミットの数字は変わり、おそらく大きくなります。
• Python 3.12 で Connection.autocommit が追加され、トランザクションの制御方法が変わりました。実行したのは 3.9 だけです。
• WAL はプロセス間の共有メモリを使うので、ネットワークファイルシステム上では動きません。
• PostgreSQL・MySQL・SQL Server・Oracle の分離レベル表はドキュメントに基づくもので、実行していません。

WHERE のない UPDATE がなぜ危険かは 第 2 回 で、学習全体の道筋は プログラミング学習ロードマップ で確認できます。

前回： 第 4 回：インデックスと EXPLAIN QUERY PLAN · 次回： 第 6 回：ウィンドウ関数と CTE