SQLiteのDatabase is lockedで悩む人に見て欲しいロックの話

SQLiteを使ったサイトをいくつか運営しているのですが、時折発生する↓このエラーに頭を悩ませていました。

SQLSTATE[HY000]: General error: 5 database is locked

急いでいる方のために先に答えを書いておきますが、このエラーはSQLiteのトランザクションの挙動を理解していないために発生しているエラーであり、BEGIN IMMEDIATEを適切に使うことで解決しました。

何も考えず、pdoのbeginTransactions()を使ったり、単に BEGIN だけで開始したり…。

PDOの接続オプションで PDO::ATTR_TIMEOUT に20を設定しておけば書き込みが衝突しても20秒待ってくれるだろ、と思い込んだり…。MySQLやPostgreSQL、SQLSeverやOracleなどの商用データベースに慣れている方ほどこの Database is locked の罠にハマりやすいと思います。

今回はその誤解を解きほぐしていきましょう。

Database is lockedを100％再現するコード
Database is lockedをWindows上で再現する方法
Database is lockedを再現した結果
解決策1：トランザクションを使わない
解決策2：BEGIN IMMEDIATEを使う
BEGIN IMMEDIATEとEXCLUSIVEの違い
まとめ

Database is lockedを100％再現するコード

Database is lockedについて書かれているサイトやブログは多くありますが、SQLiteは読み込みは複数プロセスに対応しているものの、書き込みトランザクションは1本だけなので、同時書き込みに弱いとか、PVの多い大規模サイトでは別のDBを選択すべきとか書かれていることが多く、もう少し具体例を出してもらえないものか、と悩んでいました。

特に、どうやったらDatabase is lockedが再現できるかが重要だとは思いませんか？　現象が再現できれば対応策も考えやすいというものです。

ぼくが運営するサイトでも1日に1回くらいの頻度でDatabase is lockedが発生していましたが、1日数万PVのうちの1アクセスに過ぎないし、検索エンジンが頻繁にアクセスしてくるからそんなエラーが発生しているのかな、なんて呑気に考えていました。…1年くらい。しかしちゃんと調べてみたら、そんな不確実な理由ではありませんでした。

こちらのPHPソースをご覧ください。

$db = new PDO('sqlite:test.db', '', '', array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_TIMEOUT => 20));
try {
	$db->query("BEGIN");
	putLog('SELECT');
	$db->query("SELECT * FROM test");
	putLog('INSERT開始');
	for ($i = 1; $i <= 5; $i++) {
		putLog('INSERT '.$i.'行目');
		$db->query("INSERT INTO test (data) VALUES ('test')");
	}
	putLog('COMMIT');
	$db->query("COMMIT");
} catch (PDOException $e) {
	putLog($e->getMessage());
}
putLog('END');

function putLog($msg)
{
	file_put_contents('test_lock.log', '['.$_SERVER['argv'][1].']('.date('H:i:s').") ".$msg."\n", FILE_APPEND | LOCK_EX);
}

このPHPスクリプトを使って現象を再現していきます。

接続するデータベース名は test.db、テーブル名はtestです。ここではテーブル構造は重要ではありませんが、サンプルで想定しているテーブルは↓こんな感じ。

CREATE TABLE test (seq INTEGER PRIMARY, data TEXT);

Database is lockedをWindows上で再現する方法

Linux上で開発してる方はごめんなさいね。ぼくはWindows上でPHPのコードを書き、CentOSでnginxあるいはLiteSpeedを使ったレンタルサーバー上にアップロードして使うスタイルです。

ローカルにLinux環境を作ったほうが良いとは思うのですが、Windowsひとつで済んだほうが個人的には楽なため、なるべくテストもWindowsで完結するようにしています。

ではWindows上にセットアップしたPHP7.4+nginxの環境で、どうやって複数ユーザーの同時アクセスを実現するか。頑張ってブラウザのタブを開く？　最初はそうやっていました。ChromeでもEdge Chromiumでも良いですが、ブックマークにフォルダを作成し、その中に複数のURLを登録すれば、マウスの中クリックもしくは右クリック＋「すべてを開く」を実行することでほとんど同時にアクセスするシミュレーションが可能です。

実際、自分の運営サイトにある複数のURLをお気に入りに登録し、いっぺんに開いてみたら Database is locked が発生し、ん？今まで再現できないと思っていた現象が簡単に再現できたぞ…と驚き、本格的に調査を始めたのです。

ですが、このテスト方法だと何十回、何百回と繰り返す作業に向かないため、コマンドラインで複数アクセスを実現することにしました。

del test_lock.log
start /B c:\php\php test_lock.php 1
start /B c:\php\php test_lock.php 2
start /B c:\php\php test_lock.php 3

こんなバッチファイルを用意するだけ。

PHPをローカルで動かすのは驚くほど簡単です。Webサーバーすら必要ありません。

下記のPHP公式サイトからWindows用のZIPをダウンロードしてCドライブのPHPディレクトリに解凍するだけ。

https://windows.php.net/download#php-7.4

php.iniは「extension=pdo_sqlite」だけ有効にすればOKです。

あとは先述の再現PHPスクリプトを test_lock.php という名前で保存して実行してあげればOK。

startコマンドは別プロセスで実行するという意味で、/Bオプションはそのときに新しいウィンドウを開かずに実行する、という意味です。

test_lock.phpの後ろに1、2、3の番号を付けているのはそれぞれのプロセスを識別するため。ログファイルにプロセス番号と実行したコマンドを記録することで、どんな順番で動いているのか確認するわけですね。

Database is lockedを再現した結果

■ログファイル(test_lock.log)

[1](19:54:42) SELECT
[1](19:54:42) INSERT開始
[1](19:54:42) INSERT 1行目
[2](19:54:42) SELECT
[3](19:54:42) SELECT
[1](19:54:42) INSERT 2行目
[1](19:54:42) INSERT 3行目
[2](19:54:42) INSERT開始
[3](19:54:42) INSERT開始
[1](19:54:42) INSERT 4行目
[2](19:54:42) INSERT 1行目
[3](19:54:42) INSERT 1行目
[1](19:54:42) INSERT 5行目
[2](19:54:42) SQLSTATE[HY000]: General error: 5 database is locked
[3](19:54:42) SQLSTATE[HY000]: General error: 5 database is locked
[1](19:54:42) COMMIT
[2](19:54:42) END
[3](19:54:42) END
[1](19:54:42) END

無事、General error: 5 database is lockedが再現できました。

ログだと見づらいかもなので、プロセス番号を横軸にして実行されたコマンドを順に見てみましょう。

1	2	3
SELECT	–	–
INSERT 1行目	–	–
–	SELECT	SELECT
INSERT 2行目	–	–
INSERT 3行目	–	–
INSERT 4行目	INSERTエラー	INSERTエラー
INSERT 5行目	–	–
COMMIT	–	–

はい、プロセス2番、3番は1行も書き込みに成功していませんね。ロックされているから。

SQLiteのデフォルトのトランザクションモードは BEGIN DEFERRED であり、DEFERREDを省略してもこのモードになりますし、何なら $db->beginTransactions(); を実行しても DEFERRED で動きます。

BEGIN DEFERRED が実行された時点ではロック処理は行われず、最初のクエリー、今回の例では “SELECT * FROM test” が実行された時点でロックがかかります。ロックがかかっていても他のプロセスからの読み込みは可能なのですが、書き込みは許可されないため、例外エラー Database is locked が発生。

PDO::ATTR_TIMEOUTで20秒を指定しているのになぜ待ってくれないの？と思うかもですが、このオプションでタイムアウト値まで自動で待ってくれるのは BEGIN IMMEDIATE か、BEGIN EXCLUSIVEでトランザクションを開始したときだけです。

だから例えば、よくあるログイン処理で、ユーザーテーブルを読み込み、ログイン成功ならログイン時間をセット(UPDATE/INSERT)などという処理をしていた場合、同時ログインが発生すると100％ database is lockedになります。

同時ログインなんて器用な真似する人、アタッカー以外おらんやろ、と考えるかも知れませんが、自動ログイン処理を実装していた場合は普通にありえます。

クッキーにトークンを保存しておき、自動ログインが成功するたびにトークンを更新するような仕組み。そのとき、ユーザーがお気に入りに複数登録していたり、あるいはブラウザのタブ復帰機能で同一サイトを同時に複数のタブで開いたり、同じユーザーが全く同一のタイミングでアクセスしてくることは普通にあるのです。実際、うちの運営サイトはこの状態でDatabase is lockedが発生していることがわかりました。

解決策1：トランザクションを使わない

乱暴なやり方ですが、更新順がどうでも良いのに雰囲気でトランザクションを使っているだけならこれが一番てっとり早いです。

$db = new PDO('sqlite:test.db', '', '', array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_TIMEOUT => 20));
try {
	putLog('SELECT');
	$db->query("SELECT * FROM test");
	putLog('INSERT開始');
	for ($i = 1; $i <= 5; $i++) {
		putLog('INSERT '.$i.'行目');
		$db->query("INSERT INTO test (data) VALUES ('test')");
	}
} catch (PDOException $e) {
	putLog($e->getMessage());
}
putLog('END');

function putLog($msg)
{
	file_put_contents('test_lock.log', '['.$_SERVER['argv'][1].']('.date('H:i:s').") ".$msg."\n", FILE_APPEND | LOCK_EX);
}

最初のサンプルからBEGIN～COMMITを失くしただけですが、これを実行するとこんな順番で記録されます。

1	2	3
SELECT	SELECT	–
–	INSERT 1行目	–
INSERT 1行目	–	SELECT
–	INSERT 2行目	–
–	INSERT 3行目	–
–	INSERT 4行目	–
–	INSERT 5行目	–
–	–	INSERT 1行目
INSERT 2行目	–	–
INSERT 3行目	–	–
INSERT 4行目	–	–
INSERT 5行目	–	–
–	–	INSERT 2行目
–	–	INSERT 3行目
–	–	INSERT 4行目
–	–	INSERT 5行目

これは一例であり、実行するたびに行挿入される順番は異なります。どのプロセスも5行挿入できているからOK、というシステムならアリ。

これはオートコミットで動いているので、INSERT文が発行されるたびに前後にBEGIN～COMMITが付与されているのと同じような動きのはずですが、プロセス数を100個くらいまで増やしてテストしてもDatabase is lockedは発生しなかったので、オートコミット時の書き込み衝突時は自動でロック解除を待ってくれるのかも。あんま自信ないけど。

解決策2：BEGIN IMMEDIATEを使う

たぶん、もっとも無難。

PHPソースのBEGIN部分をBEGIN IMMEDIATEにしただけなのでソースの再掲はしませんが、実行結果は下記のとおり。

[1](21:02:35) SELECT
[1](21:02:35) INSERT開始
[1](21:02:35) INSERT 1行目
[1](21:02:35) INSERT 2行目
[1](21:02:35) INSERT 3行目
[1](21:02:35) INSERT 4行目
[1](21:02:35) INSERT 5行目
[1](21:02:35) END
[3](21:02:35) SELECT
[3](21:02:35) INSERT開始
[3](21:02:35) INSERT 1行目
[3](21:02:35) INSERT 2行目
[3](21:02:35) INSERT 3行目
[3](21:02:35) INSERT 4行目
[3](21:02:35) INSERT 5行目
[3](21:02:35) END
[2](21:02:35) SELECT
[2](21:02:35) INSERT開始
[2](21:02:35) INSERT 1行目
[2](21:02:35) INSERT 2行目
[2](21:02:35) INSERT 3行目
[2](21:02:35) INSERT 4行目
[2](21:02:35) INSERT 5行目
[2](21:02:35) END

プロセスの順番自体は1→3→2の順になっていますが、いずれかのプロセスが1～5行目まで書き込み完了するのを待ってから次のプロセスが動作しています。プロセス数を100個にしてもこの動きは変わりませんでした。

BEGIN IMMEDIATEとEXCLUSIVEの違い

同じスクリプトをstartコマンドで複数同時実行しているのでわかりづらいですが、本来はトランザクションを使うスクリプトと使わないスクリプトが混在しているものですよね？

BEGIN IMMEDIATEを使うスクリプト同士が同時実行された場合は、片方の処理が終わるまで処理が止まりますが、トランザクションを使っていない、単なるSELECT文だけの処理は止まることなく動き続けます。

BEGIN IMMEDIATEの処理が完了していないのに、ほかのプロセスでINSERT/UPDATEなどの更新要求が発生した場合にはPDO::ATTR_TIMEOUTに設定された時間だけ待ち、その間にトランザクションが終了すればほかのプロセスの更新処理も引き続き動き出します。

一方で、BEGIN EXCLUSIVEの場合は他のプロセスに読み込みも許可しません。Webサイトに実装する場合ではEXCLUSIVEでのトランザクションはちょっと使いづらいのではないかなぁと思います。SQLiteはDBを分けるのも手軽ですから、EXCLUSIVEモードで更新する専用のdbを用意する等、活用方法はあると思いますけどね。

まとめ

実際にPHPで自動ログイン処理を実装したときにどんな問題が発生するのか、実例を交えて解説してみようと思っていたのですが、思いのほか記事が長くなったのでひとまず今回はここまでにしておきます。

重要ポイントをまとめると、

PDO::ATTR_TIMEOUTのタイムアウト値が参照されるのは BEGIN IMMEDIATE か、BEGIN EXCLUSIVE でトランザクションを開始したときだけ
BEGINのオプションを省略、もしくはPDOのbeginTransactions関数を使った場合は BEGIN DEFERREDでトランザクションが開始され、そのとき別プロセスの書き込みは全て例外エラー Database is locked となる
BEGIN EXCLUSIVEでは別プロセスの読み取り操作もロックされてしまうため、更新順が重要な処理においても基本的にはIMMEDIATEのほうを使ったほうが良いと思われる

と、こんなところでしょうか。

箇条書きにすると何か当たり前のことを書いている気がしますが、こんなことで1年近く悩んでいたので、もっと早く同時アクセスのサンプルコードを作ってテストしておけば良かったと反省しました。今はぼくの運営サイトでもDatabase is lockedが全く発生しなくなって快適そのものです。

あらためてSQLiteを見直しました。