SQLiteファイルへの同時アクセス過多によるロックについて

質問

PHP5.0+SQLite2.8でユーザー投稿型のサイトを個人運営しています。
1日数万ページビューほどの規模で、
データベースのテーブルのレコード数は数万件、
1レコードあたりのカラム数は数十件ほどあり、
そのテーブルに1日数百件の投稿があります。
それらのデータは随時更新・削除・検索されます。

データベースファイルへのINSERT、UPDATE、DELETE、SELECTなどが重なると
「database is locked」とのエラーメッセージが表示され、
それからもずっとユーザーによるデータベースへのアクセスが続くため、
データベースへのアクセスがしづらい、
もしくはまったくできない状態が延々と続いてしまいます。

SQLiteではトランザクション中にファイル全体がロックされるため
こうした現象が発生するようですが、
この状態を元に戻す方法はないのでしょうか？
ファイルをいったん削除してアップロードしなおすと直るのですが、
ファイルのアップロードには時間がかかるため、
他にいい応急措置の方法がないものかと悩んでいます。

それから、そもそもこの規模のサイトでデータベースを運用するには
SQLiteでは限界があるのでしょうか？
仮に他のデータベースに乗り換えるとしたら、
こうした頻繁に書き込み・更新・削除などが行われるサイトでは
どのデータベースを利用すべきでしょうか？
それともこの程度の規模であれば、
スクリプトを改善することでSQLiteでも対応可能なレベルでしょうか？

なにぶんデータベースに触れて間もないもので知識不足ですが、
どなたか詳しい方にご教授いただければ幸いです。

回答No.1

qaz_qwerty_me · ベストアンサー

そのような現象を確認したことがないので推測ですが・・・

たぶんSQLiteの限界と思います。
御自身で推測しているように「ファイル全体がロック・・・」は間違っていないと思います。 SQLiteの対極にあると思われるOracleが作業領域自体を複数のファイルで運用する理由のひとつが、このような現象を回避するためと推測できます。　SQLiteは、他のｄｂでは存在するｄｂエンジンと呼ばれるサービス( デーモン )が処理するのではなく、SQLite.exe自体が処理を行っているので複数のクライアントからのリクエストが重複した場合の処理が非力だと考えられます。

また、別の原因として考えられるのはWebサーバーのファイル・ロック機能との競合が考えられます。　これは私も経験している問題で、回避策はSQLiteからMySQLへのｄｂ変更でした。　使用しているWebサーバーでは、SQLiteのロック以外に、単純にファイルがロックされる現象が、時々発生し、Webサーバーの管理者からはロック回避手段はないとの回答が来たのでSQLite -> MySQL 以外に選択の余地が無かったのですが、MySQLに変更して以来 db関連のロックされる現象は発生していないので SQLite の限界と理解しています( ファイルのロックは依然として時々発生しているので )。
ただ、別な回避策として、Webサーバーが管理していない領域にｄｂを設定する方法があるのですが、管理ツールの関係で移動できないので、本当に回避策なるかは確認していないです。

　なお、PHPでコーディングされているなら「PDO」でｄｂを操作していますか？　もし、PDOで操作しているならｄｂへの接続文の１行を変更することでdbを変更できるので、開発はSQLite、実運用はMySQLという開発が可能なのでPDOによる記述をお勧めします。

お礼

ご回答ありがとうございます。

SQLiteへのリクエストを減らすようスクリプトを改善したところ、
ファイルがロックされてしまう現象はとりあえず回避できていますが、
やはりSQLiteは大規模サイトの運用には向いていないのかもしれませんね。
DB操作にはPDOを用いていますので、
MySQLの使用を検討してみたいと思います。ありがとうございました。