「みんなのお仕事相談所」では、ユーザーさまのご依頼の相場や製作期間、
契約書やお金に関する悩みを気軽に相談できるQ&Aコミュニティです。
違反案件についてはよくある質問の「【共通】違反のお仕事とは」をご覧ください。
また、違反報告についてはよくある質問の「【共通】違反報告とは」をご確認ください。

発注者からの相談

システム管理・更新・保守の見積もり・相場に関する相談

業務用Webアプリ（RoR/sqlite3）の保守・運用に関する相談

解決済

回答数: 4

閲覧回数: 11779

気になる

websuzuki

下記の要件で、入出荷管理を行う業務用Webアプリを
今年7月より稼働しています。

・フレームワーク：Ruby on Rails 3
・DB：sqlite3
・Webサーバ：Apache

最近、データ更新時に
下記のデータベースロックが頻繁に出るようになっています。

SQLite3::BusyException: database is locked: commit transaction

ひとまず緊急対応として、下記の策を講じました。

------------------------------------------------------------------------
①model各ファイル：明示的にトランザクションを入れる
http://d.hatena.ne.jp/kamip/20121021

②database.yml：sqliteのtimeout値を180秒に変更

③Apache再起動
------------------------------------------------------------------------

Q.上記①～③以外に考えられる必要な対応はありそうでしょうか？
有識者の方にアドバイスを頂きたく、ご教授頂きたいです。
何卒、宜しくお願い致します。

※ 今のところ上記以外に大きなトラブルは発生しておりませんが、
今後のことも考えて、トラブル時の対応を含む本システムの保守・運用を
近日中に、どなたかにお願いしたいと考えております。
対応が発生する度に、有償でトラブルを解決して頂くようなイメージです。
こちらに関心がある方は、その旨を別途お知らせください。

2013年09月06日 01:23

ベストアンサーに選ばれた回答

asip2k25 この人に依頼する

asip2k25さんからの回答

SQLite3の排他制御は(DB)ファイルのロックという形で行われます。
行ロックやテーブル・ロックではないため、DB内の異なるテーブル・
異なるレコードに対する更新であっても排他制御をしていれば、
DB全体がロックされます。

このため、業務用Webアプリを利用者がブラウザで利用し、データの更新を
掛けている(ロックによる排他制御が行われている)最中に別の利用者がデータの更新を
掛けようとすると排他的ロックが既に行われているため、必ず例外が発生します。
(データの更新が秒単位で重なることはほぼないと思うので、御社のケースでは運用上
問題にはならないと思いますが。。。)

現状の御社の業務用Webアプリの利用スタイルの範疇ではコストを掛けてまでDB移行を
行う必要はないか(コストに見合うDB移行のメリットはないか)と思います。
今後、業務用Webアプリの利用スタイルの変化により、複数ユーザからの同時データ更新
が発生するようになった場合に行ロックを行える本格的なDBへの移行を検討されても
遅くはないと思います。もちろん、今後の利用スタイルの変化を見越して本格的なDBへの
移行に先行投資されるのであればそれが一番無難ではあると思います。

また、ないとは思いますが、業務用Webアプリとは別にスタンドアローンのツール等を
使ってSQLite3のDBファイルの中身を直接編集されることはありますでしょうか？
ツールの実装にもよりますが、テーブルのレコード編集中はずっと排他制御が
行われている可能性があります。うっかりツールをレコードの編集モードのまま
放置していると意図しない排他制御でDBがロックされたままとなり、何故DBが
ロックされているのかわからないということもおこりえます。

>現象の回避・対策の案としては、先述の３点以外に
>・500エラー発生時にシステム管理者にメールが配信されるようにする
捕捉されていない例外が発生した場合にメールを送信するexception_notification
というRailsプラグインが存在します、導入を検討されては如何でしょうか？
https://github.com/smartinez87/exception_notification
>・sqliteロック解除ツールを作成して、客先PCに配置→ダブルクリックするだけでロックが解除される
スタンドアローンのsqliteロック解除ツールの問題点はロックを解除するsqliteのDBファイルが
客先のネットワークから参照・更新できる場所にある必要があるという点です。クラウド・ストレージ
・サービス？を利用すれば可能になるかとは思いますが、セキュリティの観点からお勧めできません。
sqliteロック解除ツールを作成されるなら、スタンドアローンのツールとしてではなく、業務用Webアプリ
の一機能として作成することをお勧めします。

以上、参考になるかはわかりませんが、よろしくお願いします。

参考(エンジニア向け)：
http://blog.livedoor.jp/nizoraul/archives/3409614.html
http://akkunchoi.github.io/rails3-active-record-query-interface.html

2013年09月10日 21:22

websuzuki

相談者からのお礼コメント

一般論だけでなく、コスト面からのシステム可用性を含むアドバイスを頂き、
非常にありがたいアドバイスでしたので、ベストアンサーとさせて頂きます。
ご回答ありがとうございました。

2013年09月12日 18:00

すべての回答

おごちゃんこの人に依頼する

おごちゃんさんからの回答

同時利用者数はどれくらいでしょうか？

あくまでも感覚でしかありませんが、同時アクセスが複数あるアプリでsqliteを使うこと自体に問題があるように思います。

RoRなので、他のデータベースに移行する手間は、それ程大きくはないでしょう。
sqliteで頑張るよりは、工数少なくて出来ると思います。

2013年09月06日 12:45

websuzuki

相談者コメント

おごちゃん様

有益なアドバイスを頂き、ありがとうございます。

>同時利用者数はどれくらいでしょうか？
１台のみです。
複数端末から更新を行うということは、現在の運用ではありません。

・データ登録→社内
・データ閲覧→社内or社外

運用上、社外の商談時にデータを閲覧することがあるため、
Webアプリの形態をとっています。

では、なぜ１台のみの運用でデータベースロックがかかるのか
という点が気になるかと思いますが、
下名分析では以下のような場合に発生しているのではないかと想定しています。

▼通常
１．画面上よりデータを更新する（このタイミングでsqliteにロックがかかる）
２．更新
３．データ更新完了（このタイミングでsqliteにロックが解除される）

▼エラー発生
１．画面上よりデータを更新する（このタイミングでsqliteにロックがかかる）
２．更新→更新時間がサーバーのtimeout値のLimitを超える
３．「500 Internal Server Error」が発生する（sqliteはロック状態）

現象の回避・対策の案としては、先述の３点以外に
・500エラー発生時にシステム管理者にメールが配信されるようにする
・sqliteロック解除ツールを作成して、客先PCに配置→ダブルクリックするだけでロックが解除される
・sqlite -> MySQLへの移行
等が考えられるところです。

おごちゃん様の仰る通り、DBの変更というのは
検討の余地がある有力な方法の１つだと思っています。

他の皆様も、有用なご意見・ご提案等がありましたら
どうぞお寄せください。

2013年09月07日 13:53

JIT この人に依頼する

JITさんからの回答

答え：sqllite3の使用を、 mysql2に使用を進言します。
理由：
１）容量が増える場合に備えて、 mysql2の方を進めます。
２）システム的にいうと mysql2の方がいいです。
３）multiple connections を　Mysql は、サポートする事が出来るというのが第二の理由です。
４） SQLiteは、実際には、同時アクセスをサポートしますが、たまにサポートされないのでエラー発生の発生がある
　　それが、SQLlite　liteを使用しない方が良い理由です。

*以下、次の理由があります。
　SQLiteを使用しない理由。
５）製品使用には、”向かないと強く進言します”と書いています。
６）平行アクセスはサポートしない。
７）大きいファイルでは、問題がある。、例えば、スクロールのクエリーの場合　。
８）そして、他の多くの問題がある。

2013年09月07日 16:35

websuzuki

相談者コメント

JIT様

MySQLを使うべきということで
ご提案誠にありがとうございます。

下記調査の通りsqliteのパフォーマンスは捨てがたいところですが
安定的に動かないと話になりませんので、
マイグレーションは積極的に検討させて頂きます。

http://d.hatena.ne.jp/cypher256/20121013/p1

他の皆様も、ソース・設定レベルで妙案がありましたら
是非お知らせください。

2013年09月07日 17:01

asip2k25 この人に依頼する

asip2k25さんからの回答

2013年09月10日 21:22

websuzuki

相談者コメント

asip2k25様

有用な情報を多くお寄せ頂き、ありがとうございます。
ご回答内容、大変参考になりました。

>現状の御社の業務用Webアプリの利用スタイルの範疇ではコストを掛けてまでDB移行を
>行う必要はないか(コストに見合うDB移行のメリットはないか)と思います。

確かに一般論では「業務用アプリなら行ロックを行えるDBに移行すべき」
となるのですが、

asip2k25様のご指摘の通り、コストの要素を加味して考えると
運用状況を踏まえて移行タイミングを見計らうのが
今のシステムにおいては最も理想的であるように思います。

>うっかりツールをレコードの編集モードのまま
>放置していると意図しない排他制御でDBがロックされたままとなり、何故DBが
>ロックされているのかわからないということもおこりえます。

なるほど・・・現在直接編集するツールは利用していませんが、
仮に利用していた場合、確かにこのケースはあり得ますね。
他のDBに慣れていると、うっかりはまりそうな落とし穴だと感じました。

>捕捉されていない例外が発生した場合にメールを送信するexception_notification
>というRailsプラグインが存在します、導入を検討されては如何でしょうか？
>https://github.com/smartinez87/exception_notification

まさに導入を検討していたGEMでした。
DB移行の是非に関わらず、様子を見ながら搭載していく予定です。
http://d.hatena.ne.jp/the_yokochi/20110517/1305641153
http://d.hatena.ne.jp/jiikko/20130308/1362727260
http://blog.twiwt.org/e/340979

>sqliteロック解除ツールを作成されるなら、スタンドアローンのツールとしてではなく、業務用Webアプリ
>の一機能として作成することをお勧めします。
確かに、仰る通りです。
常識的に考えて、サーバーにDBを置くという前提からして
DB操作の機能はクライアントではなく、
当然サーバー側（＝Webアプリ）に持たせますよね。。。

先述の通り、本件のシステムは運用は始まったばかりのシステムですので、
今後機能追加等の要望が見込まれます。

本件および他の案件でお仕事が発生しましたら
豊富なご経験をお持ちのasip2k25様に優先的にご案内申し上げたく存じます。
もしタイミング等が合うようでしたら、奮ってご応募頂ければ幸いです。

今後とも何卒宜しくお願い致します。

2013年09月11日 23:05

websuzuki

相談者コメント

ご回答くださいました皆様、
貴重なお時間を割いて丁寧にご教授いただき、
誠にありがとうございました。

2013年09月11日 23:05

asip2k25 この人に依頼する

asip2k25さんからの回答

websuzuki様

業務用Webアプリ with SQLite において気になる点があったので、
ご相談終了のようですが、書かせていただきます。

運用が始まったばかりということですので、DBのバックアップは
現在、どのように行っておられますでしょうか？

SQLiteはiOS(iPhone・iPad)やAndroid等にも採用されている定評の
ある(組込み)DBではありますが、１ファイル１DBということもあり、
PostgreSQL・MySQL等の本格的なDBと較べると壊れる可能性の
大きいDBです。

また、壊れなかったにしても、業務用Webアプリに内在するバグ等に
より、データの整合性が損なわれる可能性もあります。

DBのバックアップをできれば一日一回、リムーバブルメディア等に
保存する形で行われることをお勧めします。

SQLiteには標準でバックアップ・リストア用のコマンドライン・ツール
が付属しています。また、SQLiteのGUIクライアント・ツールの中には
GUIでバックアップ・リストアを行えるモノもあると思います。

コマンドラインからのバックアップ・リストアについては次のURLをご参照
ください。http://www.dbonline.jp/sqlite/manage/index6.html

壊れたSQLite DBファイルの修復に関しては、壊れたファイルに対して
バックアップを実行、新規に作成したDBファイルに対して作成した
バックアップのリストアを実行し、そのDBファイルの中身を確認し、
問題がないかをチェックする、という些か心許ない方法しか存在しない
のが現状のようです。

この点からみましても、DBの定期的なバックアップは必須であるように
思います。

既にDBのバックアップを運用フローの中に折込み済みであれば、すみません。

それでは、よろしくお願いします。

参考(エンジニア向け)：
SQLiteが database disk image is malformed エラーを吐いた話
http://taichino.com/engineer-life/database/4123

2013年09月12日 12:07

websuzuki

相談者コメント

asip2k25様

>運用が始まったばかりということですので、DBのバックアップは
>現在、どのように行っておられますでしょうか？
sqliteファイルをhttp経由で取得するツールを作成し、
客先PCのタスクスケジューラに登録して、
毎日1回ツール起動->客先PCのローカルに保存しております。

バックアップ周りは運用フローの中には折込み済みなので、
問題ありません。

重要なご指摘であり、本相談をご覧になった方には大変参考になるかと思います。
有用な情報をお寄せ頂き、ありがとうございました。

2013年09月12日 17:58

業務用Webアプリ（RoR/sqlite3）の保守・運用に関する相談

ベストアンサーに選ばれた回答

すべての回答

回答受付中！

人気の相談

解決済みの相談