PHPでSQLiteのロックとファイルロックの挙動をおさらい

「WordPressの投稿データをsqliteへエクスポートする」で書いたとおり、MySQLからSQLiteへのデータ移行自体は簡単なのですが、実際運用する場合、SQLiteは行単位のロックが出来ないため、統計情報の記録などでパフォーマンス上の問題が発生します。

ほら、例えばブログシステムを作りたい場合、記事ごとに閲覧数のカウンターを持っていたりするじゃないですか。SQLiteはそういった同時書き込みが大量発生するケースに弱いので、そこはログファイル使おうか、っていう話です。

SQLiteの場合、INSERTだけでもDB全体がロックされる

<?php
$conn = new PDO('sqlite:test.db');
//統計用のテーブルがなければ作成
$conn->query('CREATE TABLE IF NOT EXISTS log (access_date datetime)');
//統計用のテーブルへ1行挿入
$conn->query("BEGIN EXCLUSIVE TRANSACTION");
$conn->query("INSERT INTO log VALUES (CURRENT_TIMESTAMP)");
sleep(10);
$conn->query("COMMIT");
?>

INSERT文1つでは処理が早すぎてテストにならないので、このサンプルではBEGIN～COMMITを使ってわざと10秒間DB全体をロックしています。

そのため、このPHPを2つ用意し、同時に実行した場合、logテーブルの中身は
access_date
2019-04-26 13:00:00
2019-04-26 13:00:10

となり、2行目がきっちり10秒遅れで記録されます。

まぁ実際は1行の挿入なんて一瞬で終わるので気にならないかも知れませんが、これがWebサイトのアクセスログなんかだと1日に数千、数万、あるいは数十万PVのアクセスのたびにロックが発生するわけで、ちょっと無視できないボトルネックになってくるかも知れません。

ログみたいな追記型のデータはファイルを使う

データを一箇所で管理したい気持ちはわかりますし、実際WordPressだと記事の閲覧数をテーブルに溜め込んでいるわけですが、だから重いというのもありますし、こういった追記型のログは普通のテキストファイルに書き込んで、後で集計するほうがサーバーには優しいでしょう。

というわけで、ファイルのロックと追記についてのおさらい。

ファイルを排他ロックするサンプル

$fp = fopen("test.txt", "w");
if (flock($fp, LOCK_EX)) {
	fwrite($fp, date("Y/m/d H:i:s")."\n");
	sleep(5);
} else {
	echo "ロック失敗";
}
fclose($fp);

解説

test.txtを "w" （書き込み）モードで開き、ファイルをロックし、日時を入れた行を1行目を書き込み、5秒待ってから閉じています。

このPHPを2連続で実行した場合、1回目のPHPの終了を待ってから、2回目のPHPが実行され、最後の日時だけが書き込まれます。まぁ排他ロックなので当然ですね。

一応、flockの返り値を見ていますが、LOCK_EXモードだと開放されるまで待ち続けるため、PHPスクリプトのタイムアウト値まで待ってしまうハズ。

ファイルを追記モードで開き、排他ロックした場合の挙動

サンプルA

$fp = fopen("test.txt", "a");
if (flock($fp, LOCK_EX)) {
	fwrite($fp, date("Y/m/d H:i:s")." A-1 \n");
	sleep(5);
	fwrite($fp, date("Y/m/d H:i:s")." A-2 \n");
} else {
	echo "ロック失敗";
}
fclose($fp);

サンプルB

$fp = fopen("test.txt", "a");
if (flock($fp, LOCK_EX)) {
	fwrite($fp, date("Y/m/d H:i:s")." B-1 \n");
	sleep(5);
	fwrite($fp, date("Y/m/d H:i:s")." B-2 \n");
} else {
	echo "ロック失敗";
}
fclose($fp);

解説

今度はサンプルAで排他ロックを5秒間行い、そのロック中にサンプルBで同じファイルに追記した例です。

結果、test.txtはこんな感じになります。

2019/04/26 13:00:00 A-1
2019/04/26 13:00:10 A-2
2019/04/26 13:00:10 B-1
2019/04/26 13:00:15 B-2

サンプルAが実行されるとtest.txtは排他ロックがかかるため、Aの処理が全部終わるまでBは動かないわけですね。

"w"モードだろうが、"a"モードだろうが、flockかけたら動作は同じだよー、という確認です。

ロックせずにファイルを追記した場合の挙動

サンプルA

$fp = fopen("test.txt", "a");
fwrite($fp, date("Y/m/d H:i:s")." A-1 \n");
sleep(5);
fwrite($fp, date("Y/m/d H:i:s")." A-2 \n");
fclose($fp);

サンプルB

$fp = fopen("test.txt", "a");
fwrite($fp, date("Y/m/d H:i:s")." B-1 \n");
sleep(5);
fwrite($fp, date("Y/m/d H:i:s")." B-2 \n");
fclose($fp);

解説

今度はロックを使わず、単に処理の長い追記書き込みをした場合です。サンプルA→サンプルBと両方即座に実行した場合、test.txtの中身はこのようになります。

2019/04/26 13:00:00 A-1
2019/04/26 13:00:01 B-1
2019/04/26 13:00:10 A-2
2019/04/26 13:00:11 B-2

Aの1行目が書き込まれた後、Bの1行目も即座に書き込まれ、それぞれsleep(5)で5秒待った後、またA→Bの順で書き込まれています。

まぁ当たり前の挙動ですが、追記動作のおさらいということで。

まとめ

• SQLiteでは1行INSERTするだけでもDB全体にロックがかかってしまう
• ファイルを"a"（追記）モードで開いたとしてもflockを使うとSQLiteと同じくファイル全体にロックがかかる

というわけで、記事の閲覧数をカウントアップするような処理はSQLiteやファイルのロックで行わず、追記モードで開いてログのように記録するほうが良いよー、という話でした。

そして実際のカウントアップ自体は1時間ごとや1日ごとにcronジョブか何かで集計して書き込めばコト足りるかな、と。

また、その集計をする際にログファイルをロックしてから古いログの切り詰めをしても良いですが、ぼくならファイル名自体に日付や時間を付けて別ファイルにするかなぁ。

要するにApacheのLogと同じですね。