この広告は30日以上更新がないブログに表示されております。
新規記事の投稿を行うことで、非表示にすることが可能です。

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のトランザクションとロックのおさらい

トランザクションは明示的、暗黙的の２種類です。
明示的トランザクションの開始は、以下の３つのコマンドです。確定（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の用途を超えた設計になっているかも。

WordPressの管理画面でウィジェットの内容を変更するために外観→ウィジェットで表示されるサイドバーウィジェットのテキスト、カスタムHTMLを展開しようとするとこんな感じでタイトルや内容の記載が表示できない状態になっていました。解決方法はこちらです。

WordPress管理画面でTypeError: undefined is not an objectが発生したらやるべき事

google page insightsやSearch Consoleの指摘でなんとか解決しなきゃとググって、見つけた設定方法でWordPressのテーマへ設定を反映しています。手早く解決できますよね。

これがいけませんでした。ちょっと考えてから適用しておけば防げました。

以下のような設定をfunctions.phpに記載している場合、管理画面、通常表示どちらでも有効になります。

/*wp-emoji-releaseの無効化*/
remove_action( 'wp_head', 'print_emoji_detection_script', 7 );
remove_action( 'wp_print_styles', 'print_emoji_styles' );    
/*link rel='shortlink'の無効化 */
remove_action('wp_head', 'wp_shortlink_wp_head');
/*link oEmbedの無効化 */
remove_action('wp_head','wp_oembed_add_discovery_links');
/*jqueryの無効化 */
wp_deregister_script('jquery');
/*プラグインの無効化*/
function dequeue_plugins_style() {
    wp_dequeue_style('wp-block-library');
    wp_dequeue_style('colorbox');wp_dequeue_style('colorbox');
    wp_dequeue_style('wordpress-popular-posts');
}
add_action( 'wp_enqueue_scripts', 'dequeue_plugins_style', 9999);

管理画面では有効にしたい場合、以下のように変更します。ワードプレス標準関数is_admin()で管理画面かどうかを判断することができます。

if( is_admin() ){ /* 管理画面 */ }
else{ /* 管理画面以外 */
/*wp-emoji-releaseの無効化*/
remove_action( 'wp_head', 'print_emoji_detection_script', 7 );
・
・
・
add_action( 'wp_enqueue_scripts', 'dequeue_plugins_style', 9999);
}/* else end */

管理画面では何もしない、それ以外の場合に処理するコード例です。管理画面がおかしい、そういえばコード修正したかもって方の参考になれば幸いです。

解決までの流れ Can't find variable: wp、TypeError: undefined is not an object (evaluating 'wp.i18n.setLocaleData')

本来の「テキストウィジェット」の表示

比較すると以下のようなことがわかりました。

1. タイトルのラベル、入力エリア(input type=text")がない
2. 「メディアを追加」ボタンが消えている
3. ビジュアル、テキストのタブが消えている
4. 入力エリア（textarea）がない
5. 削除、完了リンクはある
6. 「保存しました」ボタンが「Saved」ボタンとして未翻訳の状態である

本来表示されるべき内容が消えてしまっている？あります。通常のコンテンツにアクセスしてウィジェットのエリアは普通に表示されていました。

一体いつから消えていたのか？ウィジェット系の更新頻度は低いです。そのため、どれくらい前から消えていたのか判断できませんでした。

解決方法は？「Wordpress サイドバーウィジェット 不具合」等でググりました。該当しそうな情報が見つかりません。

原因となりそうなキーワードを集めるためにブラウザーのデバッガー（Safari：右クリック→要素の詳細を表示、Chrome：右クリック→検証）を起動したところ、エラーが多数発生していたことがわかりました。

1. 15箇所 ReferenceError: Can't find variable: wp
2. 1箇所 TypeError: undefined is not an object (evaluating 'window.wp.editor')
3. 1箇所 ReferenceError: Can't find variable: tinymce

このエラーを見て、ぴん！っと閃きました？

そういえば、通常サイト側の対応で、jqueryのURLをテーマ（header.php）に直書きしたことを思い出しました。
wordpressでのjquery呼び出しを削除するため、functions.phpに以下を追加しました。

・
・
wp_deregister_script('jquery');
・
・

よく良く考えると上記のような指定では管理画面、通常サイトを含めて全てjQueryが無効になってしまいますね。↓管理画面の場合は対象外！という処置をしました。

if( is_admin() ){ }else {
wp_deregister_script('jquery');
}

修正したらエラーの内容が変わっていました（アップロードして検証ツールのログを確認したところ）。

Can't find variable: wpエラーは解消しました。

1. 15箇所 ReferenceError: Can't find variable: wp
2. 1箇所 TypeError: undefined is not an object (evaluating 'window.wp.editor')
3. 7箇所 TypeError: undefined is not an object (evaluating 'wp.i18n.setLocaleData')
4. 1箇所 TypeError: undefined is not an object (evaluating 'wp.mediaWidgets.init')
5. 4箇所 TypeError: undefined is not an object (evaluating 'wp.mediaWidgets.modelConstructors')
6. 1箇所 TypeError: undefined is not an object (evaluating 'wp.textWidgets.idBases')
7. 1箇所 TypeError: undefined is not an object (evaluating 'wp.codeEditor.defaultSettings')
8. 1箇所 TypeError: undefined is not an object (evaluating 'wp.customHtmlWidgets.idBases')
9. ReferenceError: Can't find variable: tinymce

これらエラーは、jQueryを除去した方法と同じようなremove_action、wp_dequeue_style、wp_deregister_scriptなどを対策で追加した影響で発生しています。

同じようにis_admin()関数で判断することで対処しました。

エラーはなくなり、サイドバーウィジェットのテキストは展開されるようになりました！

まとめ

ワードプレスの管理画面 外観、サイドバーウィジェットの内容を編集できない！っていうところからの解決するまでの流れが分かったかと思います。ちょっとしたfunctions.phpへの修正でこんな影響が出るとは・・・ワードプレスの修正って、簡単だけど難しいって思いましたよ。

WordPress管理画面でTypeError: undefined is not an objectが発生したら、修正したテーマのfunctions.phpを見直すことでエラーをなくすことが分かったかと思います。

functions.phpへの修正はできる限り、影響範囲を抑えるため条件分岐タグis_adminを使った方がいいですね。

is_admin()

ダッシュボードまたは管理画面の表示中かどうか

これ以外にもたくさんありますが、管理画面かどうかで判断するのが一番確実だと思います。

初めはWordPressのバグかと疑ってフォーラムとか検索しまくりました。でも結局は自分で設定した変更による影響でがっくりきましたよ・・・


この記事の投稿時点のワードプレスのバージョンは「WordPress 5.6.2 」です。