SQLite3で同時書き込みしたい

SQLite3で同時書き込みしたい、同時接続、同時読み込みをサポートしているから当然同時書き込みもできると思いがちですが、SQLite3は同時書き込みできない仕様になっています。

SQLite supports multiple simultaneous read transactions coming from separate database connections, possibly in separate threads or processes, but only one simultaneous write transaction.

SQLiteは、場合によっては別々のスレッドまたはプロセスで、別々のデータベース接続からの複数の同時読み取りトランザクションをサポートしますが、同時書き込みトランザクションは1つだけです。
sqlite.org 2.1. Read transactions versus write transactions

SQLite3のトランザクションとロックのおさらい
同時書き込みトランザクションは1つだけの範囲は？テーブル？全体？検証してみました
同時書き込みトランザクションは1つだけのSQLite3で同時書き込み
1. できないのではなく、できるようにする考え方
まとめ

SQLite3のトランザクションとロックのおさらい

トランザクションは明示的、暗黙的の２種類です。
明示的トランザクションの開始は、以下の３つのコマンドです。確定（COMMIT）、元に戻す（ROLLBACK）制御がでいます。
明示的 BEGIN/BEGIN DEFERRED 最初のR/I/U/Dアクセスで読み込み・書き込みトランザクションを開始
明示的 BEGIN IMMEDIATE 宣言で書き込みトランザクションを開始、他からリードできる
明示的 BEGIN DEFERRED 宣言で書き込みトランザクションを開始、他からリードできない

暗黙的トランザクションは、自動的に開始されたトランザクションです。BEGINやCOMMITを使わなくてもトランザクションが働く仕組みになっています。BEGIN命令は自動的コミットを停止する役割になっています。

トランザクション開始、コミット等で「SQLITE_BUSY：別のプロセスで使用されているデータベース」エラーが発生します。sqlite.orgのトランザクションドキュメントを読むとこのあたりこのことが明確になります。

ロックと並行性（ttps://sqlite.org/lockingv3.html）も合わせて熟読すると内部構造がなんとなく理解できます。

トランザクションの開始でファイルがロックされる仕組みではないことがわかります。

SQLite3のロックは？SQLite3がファイルロックするタイミングは極力少なくするように設計されています。ただロックの失敗はSQLITE_BUSYエラーに集約されます。

ロックの状態は５種類あります。

UNLOCKED	デフォルトの状態。オープン直後またはアクセスがない状態
SHARED	SELECT、BEGINコマンド後のSELECTで取得、この状態は複数プロセスを許可
RESERVED	最初のINSERT、UPDATE、またはDELETEの実行で取得。この状態は唯一、獲得可能は１プロセス
PENDING	自分以外のSHAREDがある状態、EXCLUSIVE待ち。獲得可能は１プロセス
EXCLUSIVE	排他ロック。書き込み中。獲得可能は１プロセス

書き込みが発生する暗黙的トランザクションも同様のロックが発生します。

ロック中の読み取りは、EXCLUSIVEロック中だけ他プロセスもリードアクセスができなくなります。これ以外のUNLOCKED 、SHARED 、RESERVED中はリードアクセス可能です。
PENDINGはSHAREDロック所有プロセスはリードアクセス可能です。新たなSHAREDロックは獲得できません。
同時読み込みをサポートする仕組みであることがわかります。ただEXCLUSIVEロック中は読み込みも失敗することに注意が必要です。

プログラム(PHP SQLIte3)に落とし込んで考えると以下のようなことがわかりました。

querySingle(“select”)、query(“select”)、prepare(“select”)等でSHAREDロックが発生する
exec(“insert”)等でSHARED、RESERVED、EXCLUSIVEロックが発生する
exec(“begin”)発行では、ロックは発生しない。
exec(“begin IMMEDIATE”)はRESERVEDロックを獲得しようとする
exec(“begin EXCLUSIVE”)はEXCLUSIVEロックを獲得しようとする
sqlite3内部のロック状態は発行の失敗で判断するしかない

同時書き込みトランザクションは1つだけの範囲は？テーブル？全体？検証してみました

検証の結果、同時書き込みトランザクションは1つだけの範囲は、データベース全体です。
同一テーブルに対しての同時書き込みは、１００％「Unable to execute statement: database is locked」が発生します。
異なるデーブルに対しての同時書き込みは、１００％「Unable to execute statement: database is locked」が発生します。

添付のPHPソースコードで検証しています。2つのプロセスから同時に1000件書き込む単純なコードです。
sqlite3のバージョンは「3.32.3 2020-06-18 14:16:19」です。macOSで実行しています。
同一テーブル検証ソース(test1-w.php)

<?php
class test {
    public function run($table, $name){
        $db = new SQLite3(__DIR__ . "/test.db");
        $db->enableExceptions(true);
        try{
            $db->exec('begin');
            $stmt = $db->prepare("insert into {$table} (time, value ) values (:time,:value)");
            for( $i = 0; $i < 1000; $i++ ){
                $stmt->bindValue(':time', time() );
                $stmt->bindValue(':value', $name ."-". $i );
                $stmt->execute();
            }
            $db->exec('commit');

        }catch(Exception $e){
            $db->exec('rollback');
            print_r($e);echo "{$table}:{$name}:{$i}:".PHP_EOL;
        }
    }
}
if( isset($argv) && isset($argv[0]) && isset($argv[1]) == false ){
    if( file_exists(__DIR__ . "/log.log") ){unlink(__DIR__ . "/log.log");}
    if( file_exists(__DIR__ . "/test.db") ){unlink(__DIR__ . "/test.db");}
    $db = new SQLite3(__DIR__ . "/test.db");
    $db->exec('create table tbl (pk INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, time, value )');
    $db->close();
    unset($db);
    $cmdlist = [];
    for( $i=0; $i< 2; $i++ ){
        $cmdlist[] = "php test1-w.php tbl name{$i} > ./log.log 2>&1 &";
    }
    exec( implode( '', $cmdlist ) );
}
else{
    $test = new test();
    $test->run( $argv[1], $argv[2] );
}

○ test1-w.phpとして保存してください。
○ 実行：php test1-w.php
○ database is locked等が発生するとすぐに終了します。
○ test.dbファイルがテストに利用したSQLiteデータベースファイルです。
○ log.logファイルに実行結果が残っています。cat、less等で内容を確認できます。

異なるデーブル検証ソース(test2-w.php)

<?php
class test {
    public function run($table, $name){
        $db = new SQLite3(__DIR__ . "/test.db");
        $db->enableExceptions(true);
        try{
            $db->exec('begin');
            $stmt = $db->prepare("insert into {$table} (time, value ) values (:time,:value)");
            for( $i = 0; $i < 1000; $i++ ){
                $stmt->bindValue(':time', time() );
                $stmt->bindValue(':value', $name ."-". $i );
                $stmt->execute();
            }
            $db->exec('commit');

        }catch(Exception $e){
            $db->exec('rollback');
            print_r($e);echo "{$table}:{$name}:{$i}:".PHP_EOL;
        }
    }
}
if( isset($argv) && isset($argv[0]) && isset($argv[1]) == false ){
    if( file_exists(__DIR__ . "/log.log") ){unlink(__DIR__ . "/log.log");}
    if( file_exists(__DIR__ . "/test.db") ){unlink(__DIR__ . "/test.db");}
    $db = new SQLite3(__DIR__ . "/test.db");
    $cmdlist = [];
    for( $i=0; $i< 2; $i++ ){
        $db->exec('create table tbl'.$i.' (pk INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, time, value )');
        $cmdlist[] = "php test2-w.php tbl{$i} name{$i} > ./log.log 2>&1 &";
    }
    $db->close(); unset($db);
    exec( implode( '', $cmdlist ) );
}
else{
    $test = new test();
    $test->run( $argv[1], $argv[2] );
}

○ test2-w.phpとして保存してください。
○ 実行：php test2-w.php
○ database is locked等が発生するとすぐに終了します。
○ test.dbファイルがテストに利用したSQLiteデータベースファイルです。
○ log.logファイルに実行結果が残っています。cat、less等で内容を確認できます。

同時書き込みトランザクションは1つだけのSQLite3で同時書き込み

こちらで説明した通り、SQLite3は同時書き込みをサポートしていません。これはファイル型データベースの宿命かもしれませんね。Access(mdb)でも同じような感じだったと記憶しています。

同時書き込みをサポートしているSQLServer、Oracle、MySQLといったサーバー方式のデータベースと同じような概念では対応できないってことですね。

SQLite3で同時書き込みをサポートすることはSQLite3をSQLServer、Oracle、MySQLに昇格させるような困難が待ち受けている可能性があります。同時書き込みをさせない設計にしておくことが良いかと思います。

できないのではなく、できるようにする考え方

ここではレンタルサーバー利用で良くあるWebのコメント欄や掲示板にSQLite3を利用した場合を想定し、同時書き込みするにはどうすればいいのかを考えています。複数のユーザーがブラウザでアクセス、書き込み等することでほぼ同時の書き込みが発生する可能性があります。
SQLite3では同時書き込みはサポートされていませんが、複数の要求をシーケンシャルに順次書き込みすることで問題は発生しないはずです。

不確実なことがあり得るリトライ方式

Exceptionを検出して、リトライする方式です。Exceptionやエラーが検出できる言語なら、少量のソースコードの変更で対応できる方法です。
書き込みの順番性は失われることがあります。
リトライの上限を決めておくのも大切です。リトライ満了で失敗した場合を考慮する必要があります。
環境によっては同時書き込みの影響でデータベースが壊れてしまう可能性を秘めています（ファルロックシステムコールが正しく動作するストレージをお使いください）。

Mutexやセマフォを利用することで上記のようなエラーを抑止することができます（Mutexやセマフォが使える環境の場合）。

登録する順番をあまり考慮していませんが、以下のような方法があります。こちらのトライロックを実現する方法を利用した同期処理を利用します。

１）try-lock 獲得できない false/NG 
１−１）dbに対してinsert/update/deleteする内容をユニークなファイル名で所定ディレクトリに保存します。
２）try-lock 獲得できた true/OK 獲得できても稀に競合が発生します。
２−１）dbに対してbegin;create table;commitを発行します。稀に発生する競合はここで除外します。
　例外が発生した場合は、１ー１）と同じようにファイルに保存します。
　ここで除外できないパターンもあるかもしれません。
　　２−２）、２−３）でもcatchし、ファイル化するロジック組み込んでおくのが安心です。
２−２）所定ディレクトリをスキャンし、ファイルがある場合はinsert/update/delete処理を行います。
　処理できた場合は、対象ファイルを削除します。
２−３）通常処理

・所定ディレクトリは設計で定めてください。
・dbはenableExceptions(true)で例外発生を許可しておきます。
・try catch構文でdatabase is lockが発生した際、catch可能です。
・ディレクトリからファイルを探す処理になるため、ファイル名をうまく設計しておかないと順番制が担保できません。
・try-lockの方法で、競合発生するはずないでしょ。って思うかもしれません。とてもシンプルで頑強に思います。事実、稀に発生します。どうして発生するのか想像もつかないぐらいナイスなタイミングが発生しているんだと思います。競合をなくすことはかなり難しいので発生に備えた方が簡単だと思っています。

後からまとめ書き込み方式

この方法は、要求で即時に書き込む代わりに、要求を所定のディレクトリ等にファイル化します。
スケジュール（クーロン等）でディレクトリをスキャンし、まとめて書き込む方式です。

順序性は秒レベルです。工夫することでミリ秒レベルまで落とし込むことが可能です。

リクエストしたデータの反映は、スケジュール間隔に依存します。リアルタイム性がありません。
スケジュールの最小実行間隔は通常1分です。これ以下での間隔での処理はできません。

定期実行されるプログラムでは、所定ディレクトリをスキャンし、古い順（ファイル更新時刻等で並び替え）に処理していきます。処理済みの要求ファイルを削除します。

EXCLUSIVEロック中は読み取りアクセスも失敗します。わずかなタイミングですがこの隙間に読み取りアクセスが発生しない保証がないのでオンライン用データベースとオフライン用データベース（更新用）、２つのデータベースを持つことも検討してみてください。

オフライン用データベースがマスターデータベースです。オフライン用に書き込み処理を実施し、
完了後オフライン用データベースをオンライン用データベースに上書きコピーします。

「後からまとめ書き込み方式」は、システムが出来上がると安定した運用が可能です。

上書きによるエラー（オンラインで参照している側）は記憶の限りありません。
ボトルネックの少ない、SSD採用のレンタルサーバーを利用しています。

メッセージキュー方式

レンタルサーバーでの利用は厳しいかもしれない方法です。VPS等ご利用中の方が使える方法です。お使いの環境でメッセージキュー関数が使えるかどうかを事前に確認する必要があります。

要求の順序性を保ちつつ、ほぼリアルタイムに処理できる方式です。
AJAX等でコールされたプログラムからメッセージキューへ送信します。
メッセージキューを受信する専用のプロセスが必要です。このプロセスは、SQLite3書き込み用プロセスで順次くる要求に従ってデータベースを更新します。

キューを利用することで並列でくる要求をシーケンシャルにすることが可能です。

メッセージキューに格納できるデータサイズの上限等の制限（環境依存）もあり得ます。この場合中間ファイル等を利用することで回避できます。

同時書き込み単位にファイルを分割

まとめ

SQLite3で同時書き込みすると「database is locked」エラーが発生します。同時読み込みをサポートしているから当然同時書き込みもできると思いがちですが、SQLite3は同時書き込みできないことが分かったかと思います。

SQLite3で同時書き込みを防ぎつつ、システム利用するのにはどうすべきかをご紹介してきました。

SQLite3の更新系は「同期」が必須になります。そのための設計が必要ってことですね。

今回ご紹介したように同期をプログラムからサポートする方法もあります。
ただこれがいいってわけではありません。

同時書き込みする場合のおすすめはデータベースサーバーです。

「database is locked」エラーで悩むのは、SQLite3の用途を超えた設計になっているかも。

将来のSQLite3では同時書き込みがサポートされる可能性があります(2023/8)　→ https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begin_concurrent.md