トランザクションとWALモード — SQLITE_BUSYが出る理由と対策

SQLiteを本格的に使い始めると、必ず一度は SQLITE_BUSY: database is locked というエラーに出会います。

このエラーは「SQLiteが壊れている」わけでも「性能が足りない」わけでもなく、SQLiteのロックの仕組みをそのまま反映したものです。仕組みを理解すれば、ほとんどのケースは設定2行と書き方の工夫で解決できます。

学習者

ローカルで動かしているときは何ともなかったのに、テストを並列で走らせたら急に database is locked が出るようになりました……。

先生

それはSQLiteが正しく動いている証拠でもあるんだ。まずロックの仕組みを見て、そのあとWALモードで大きく改善しよう。

トランザクションの基本

構文は標準SQLと同じです。

BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
COMMIT;

途中で問題が起きたら ROLLBACK で全体を取り消します。BEGIN を書かずに単独のSQLを実行した場合、SQLiteはそのSQL1つを自動的にトランザクションとして扱います。

-- 遅い: 10000回のディスク同期が発生する
INSERT INTO logs (message) VALUES ('a');
INSERT INTO logs (message) VALUES ('b');
-- ... 10000回
 
-- 速い: 同期は1回だけ
BEGIN;
INSERT INTO logs (message) VALUES ('a');
INSERT INTO logs (message) VALUES ('b');
-- ... 10000回
COMMIT;

この違いは、数千件規模でも体感で数十倍になります。バッチ処理やデータ投入では必ずトランザクションでまとめてください。

BEGIN の3つの種類

BEGIN には、いつロックを取るかを指定する3つの形があります。ここが後述の SQLITE_BUSY に直結します。

構文： BEGIN [DEFERRED | IMMEDIATE | EXCLUSIVE]

理由はこの章の後半で説明します。

ロールバックジャーナルとWALモード

SQLiteが変更を安全に記録する方法には、2つのモードがあります。

ロールバックジャーナル（既定） では、変更前の内容を mydb.db-journal という別ファイルに退避してから、本体ファイルを書き換えます。途中で電源が落ちても、次回起動時にジャーナルから元の状態へ戻せます。

WAL（Write-Ahead Logging）モードでは発想が逆になります。本体ファイルには触らず、変更内容を mydb.db-wal というファイルに追記していきます。読み手は本体ファイルとWALファイルを組み合わせて、自分に見えるべき状態を再構成します。

WALモードでの流れを図にすると次のようになります。書き手は本体ファイルに触れないため、読み手を待たせません。

この違いが生む最大の効果は、読み取りが書き込みを待たなくてよくなることです。

WALモードを有効にする

PRAGMA journal_mode = WAL;

wal

PRAGMA foreign_keys のように接続ごとに実行し直す必要はありません。マイグレーションの最初に1回実行しておけば十分です。

現在のモードは、値を書かずに実行すれば確認できます。

PRAGMA journal_mode;

SQLITE_BUSY が出る理由

SQLITE_BUSY は、必要なロックが他の接続に取られていて、待っても取れなかったことを示します。原因は大きく2つに分かれます。

原因1: 単純にロックの取り合いになっている

複数のプロセスやスレッドが同時に書き込もうとすると、1つを除いて待たされます。SQLiteの既定では待ち時間がゼロなので、その場で即エラーになります。

対策は、待ち時間を設定することです。

PRAGMA busy_timeout = 5000;

これで、ロックが取れるまで最大5秒間リトライするようになります。多くの場合、この1行で問題は解消します。

原因2: 読み取りから書き込みへの昇格に失敗している

こちらは busy_timeout を設定していても起きるため、原因が分かりにくいパターンです。

BEGIN（= BEGIN DEFERRED）でトランザクションを始め、最初に SELECT を実行すると、その時点では読み取りロックだけを取ります。その後 UPDATE を実行しようとした瞬間、書き込みロックへ昇格する必要が生じます。

このとき、自分が SELECT した後に別の接続が先にコミットしていると、SQLiteは昇格を許しません。待っても解決しない（自分が見ているデータがすでに古い）ため、busy_timeout を無視して即座に SQLITE_BUSY を返します。

-- 危険なパターン
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- 読み取りロック
-- ここで別の接続がコミットすると…
UPDATE accounts SET balance = 0 WHERE id = 1;  -- SQLITE_BUSY

対策は、最初から書き込みロックを取ることです。

BEGIN IMMEDIATE;
SELECT balance FROM accounts WHERE id = 1;
UPDATE accounts SET balance = 0 WHERE id = 1;
COMMIT;

学習者

BEGIN IMMEDIATE だと、最初からロックを取るぶん遅くなりませんか？

先生

待つ時間は増えるけど、その待ちは busy_timeout がちゃんと面倒を見てくれる。エラーで落ちて最初からやり直すより、ずっと速くて確実だよ。

実務での初期設定

ここまでを踏まえると、SQLiteを使うアプリの接続初期化は次のようになります。

-- 1回だけ実行すればよい（ファイルに保存される）
PRAGMA journal_mode = WAL;
 
-- 接続のたびに実行する
PRAGMA busy_timeout = 5000;
PRAGMA foreign_keys = ON;
PRAGMA synchronous = NORMAL;

synchronous は、コミット時にどこまでディスクへの同期を待つかの設定です。

Node.jsでの具体的な書き方は Node.jsでSQLiteを使う で扱います。

チェックポイント — WALファイルが肥大化するとき

WALファイルの内容は、チェックポイントという処理で本体ファイルへ反映されます。既定では、WALが約1000ページ（およそ4MB）を超えたタイミングで自動的に実行されます。

ところが、長時間実行され続ける読み取りトランザクションがあると、チェックポイントは進めません。古い状態を見ているリーダーがいる間、その情報をWALから消せないためです。

結果として、WALファイルが数百MB〜数GBまで膨らむことがあります。

-- 手動でチェックポイントを実行し、WALを切り詰める
PRAGMA wal_checkpoint(TRUNCATE);

よくあるハマりどころ

トランザクションを閉じ忘れる

BEGIN したまま COMMIT も ROLLBACK もしないコードパスがあると、そのロックは接続が閉じるまで残ります。他の書き込みはすべて SQLITE_BUSY になり、WALも肥大化します。

例外が起きたときも必ず閉じるよう、言語の機能（try/finally、defer、with など）で囲んでください。

1件ずつコミットしている

ORMやライブラリによっては、INSERT のたびに自動コミットが走ります。ループの中で1万件挿入すると、1万回のディスク同期が発生します。まとめてトランザクションで囲むだけで劇的に速くなります。

ネットワークファイルシステム上に置く

WALモードは共有メモリ（-shm ファイル）を使うため、NFSやSMB上では動作しません。データ破損だけでなく、そもそも正しく動きません。SQLiteのファイルは、必ずローカルディスクに置いてください。

複数のサーバーから同じファイルを触ろうとする

Webサーバーを複数台にスケールさせたい場合、SQLiteファイルを共有する構成は取れません。この要件が出てきたら、Turso・libSQL・Cloudflare D1 で扱うレプリケーションの仕組みを検討する段階です。

PRAGMA をトランザクションの中で実行する

journal_mode や foreign_keys の変更は、トランザクションの内側では効きません。接続直後、トランザクションを開始する前に実行してください。

ちゃんと使うためのポイント

• 明示的なトランザクションでまとめると、大量書き込みが劇的に速くなる
• 書き込みを含むトランザクションは BEGIN IMMEDIATE で始める
• PRAGMA journal_mode = WAL は1回実行すればファイルに保存される
• busy_timeout と foreign_keys は接続ごとに毎回設定する
• WALでも同時に書き込めるのは1つだけ。読み取りと書き込みの競合だけが解消される
• WALモードなら synchronous = NORMAL でも破損しない
• WALの肥大化は、閉じ忘れた読み取りトランザクションを疑う
• SQLiteのファイルは必ずローカルディスクに置く

次の章では、クエリを速くするためのインデックスと、SQLiteがどのインデックスを使ったかを確認する EXPLAIN QUERY PLAN の読み方を扱います。

参考リンク

• SQLite公式 — Write-Ahead Logging
• SQLite公式 — BEGIN TRANSACTION
• SQLite公式 — PRAGMA Statements