MySQLは難易度が高いのでSQLiteで構築

質問

お世話になります。
現在Web上で動作する商品管理のツールをPHP＋pdo＋MySQLでデータベースを構築しています。
MySQLでの構築は初めてで、いろいろ手間がかかっています。

今まではPHP＋pdo＋SQLiteでデータベースを構築していました。
しかし同時書き込みでデータベースがロックしてしまうSQLiteの仕様では複数人数での運用ができません。

そこでMySQLに変更しようと思ったのですが、いろいろ覚えることが多くて時間がかかってしまっています。
やはりSQLiteの構築のしやすさは魅力的です。
そこで、MySQLでの構築を一旦中止し、SQLiteで構築しようかと思っています。

PHP側で工夫してSQLiteのデータベースへの同時書き込みを無くせば運用的には問題なさそうでしょうか？
セキュリティ的に不安ですがデータベースはルートより上に置きます。

助言があればお願い致します。

回答No.3

hanabutako · ベストアンサー

正直、書いてあることからどういうデザインなのか今ひとつピンときません。
普通、ロックによる性能劣化の解消方法はロックの粒度を細粒度にするか、ロックフリーアルゴリズムの採用だと思いますが、提案されている方法はどちらでもないように思います。

> DBに書き込み前にロック用ファイルをファイルロックをし、書き込みが終わったらロックを解除する、という感じです。

というデザインにしたことでどうして現状より良くなるんでしょうか？
これはSQLiteがロックしてくれていたところを自分でやっただけで、本質的に現状と変わらないと思います。むしろ、自分の実装ミスによってロックがされず、にデータの消失や不整合を招くリスクのほうが高いのではないでしょうか。

いや、RESERVEDやPENDINGといったSQLite3のロック状態を使えない分だけ、それをちゃんと使って書いたコードよりも、ロックの粒度は粗くなっているので、トータルでの性能は落ちそうです。
質問者が言うところのロックファイルを使う実装というのは、要はすべてEXCLUSIVEにするということですよね？

ちなみに、 http://www.sqlite.org/lockingv3.html によると、SQLite3からはPENDING状態があることでリードの多発によってEXCLUSIVEロックが取れない状態というのは解消されたそうです。

> その他に、メインのDBに書き込みできるのはマスターのPHPだけにして、ユーザーは一切書き込みできないようにする、という方法です。

ロックを取る話と今ひとつつながって見えませんが、普通に考えていくつも問題が思い浮かびます。
1. DBの内容の不整合
2. スレーブ？によって新規作成されたDBファイル名の競合
3. スレーブ？によって新規作成されたDBが書き込み中であったことによるマスターの待ち
4. スレーブ？によって新規作成されたDBファイルによるディスク領域の圧迫

1についてですが、例えば、持っている商品の数に整合性がないわけですから、ヘタしたら自分が持っている数以上に商品を出してしまいますよね。商品数が1個で、ユーザーAもユーザーBもそれを使えると思って確保したら、マスターが突き合わせをした時に不足しているとわかると。
あるいは、新規商品の追加だけしかしないとしたら、同一商品の追加があった時に微妙に内容が違っていたことでエントリーが複数できたりしませんか？また、登録したものがマスターが処理を終えるまで見られないという状況になるとも思います。

2について、被らないようにファイル名をつけないと、先の入力を後の入力で上書きしそうです。あるいは、マスターによるDBの削除でデータがあるファイルを削除しそうです。

3について、1分ごとにマスターのデータベースを止めてひたすらアップデートをするので、スレーブ？が作ったファイルが著しく多かったり、スレーブ？が自分が書いているDBのロックを取ったまま帰らないことが多かったりすると、その間マスターを使えないということになりませんか？

4について、それぞれのスレーブがDBファイルを作るとなると、書き込みが増えれば増えるほどファイルのオーバーヘッドの部分によるディスク領域の圧迫もありそうです。

> しかし同時書き込みでデータベースがロックしてしまうSQLiteの仕様では複数人数での運用ができません。

一応確認ですが、書き込みにはちゃんとトランザクションを使っていますよね。使うのと使わないのとでは雲泥の性能差がSQLite2のころからあるようですが。
http://www.sqlite.org/speed.html

あと、釈迦に説法だと思うのですが、インデックスをちゃんと設定してquery planも確認してますよね。
http://www.sqlite.org/eqp.html
http://www.sqlite.org/queryplanner.html

それでだめならMySQLの採用もやむなしだと思うのですが。

自分だったらSQLite3でちゃんと性能が出るように実装してみて、ダメだったらもっと細かい粒度でロックが取れるRDBMSを採用するというアプローチを取るでしょう。 #1さんと同じく、ロック機構を自分で実装するというのは、リスクが高すぎてやりたくないです。

お礼

アドバイス頂きましてありがとうございます。

確かに自前でロックファイルを作るのはやはり無理ですよね…。ちょっと安易に考えていたようです。

あと、「メインのDBに書き込みできるのはマスターのPHPだけにして…」というのはロックファイルの件とは別の方法ということです。なのでロックを取る話とは関係ないです。

1についてですが、マスターが処理を終えるまでメインのデータベースから商品を参照できないのは許せる範囲と思っています。
ユーザーAとBにはそれぞれ専用のデータベースを作る予定です。
同一商品の追加があったときに内容が微妙に違うのは織り込み済みで、エントリーは複数できないようにします。

2は問題ないと思っています。「先の入力を後の入力で上書きしそう」というのもそれは上書きして問題ありません。

3ですが、一度に登録できる商品数に制限を入れているので問題ない範囲かと思っています。

4ですが、スレーブが作るDBのファイル数にも上限を入れようと思っています。

＞一応確認ですが、書き込みにはちゃんとトランザクションを使っていますよね。

SQLite3ですが、自分の環境で複数プロセスで同時書き込みを行ったところ、データベースファイルがロックされたままの状態になってしまい、以後そのデータベースファイルが使えなくなってしまった経験があります。トランザクションは使用していました。
ネットで調べたところ、このデータベースファイルをコピーしたものを再利用することでロックから解放されるとありましたのでそのようにして対処しました。

トランザクションのやり方が間違っていたのか、もう一度確認してみようと思います。

回答No.2

mitoneko

No.1です。
　まぁ、一番単純な形での実装ですね。
　ロックの手法としても一番単純な形ですから、マルチスレッドにおけるロックの実装を体験するには良い題材かもしれません。
　もし、マルチスレッドでの共有資源のロックを組むのが初めてでしたら、「言うは簡単、するは・・・」であることは覚悟しておいてください。
　もし、マルチスレッドのロックを作成したことがあるなら、以下は、無視してもらって結構です。

　注意ポイントはひとつです。「ロックファイルの存在を確認する」「ロックファイルを生成する」という一連の作業は、アトミックな（分離不可分な）処理ではありません。この処理中に他のプロセスは任意のタイミングで割り込む可能性があります。
　どういう意味かというと、単純な例を挙げれば、

　プロセスＡ：ロックファイルが無いことを確認した。
　プロセスＢ：ロックファイルが無いことを確認した。
　プロセスＡ：ロックファイルを作成した。
　プロセスＢ：ロックファイルを作成した（踏みつぶした？？？）
　プロセスＡ及びプロセスＢ：共有リソースの使用で喧嘩をしながら処理を続行・・・・

　なんてことが絶対に発生しないようにロジックを組む必要があります。（この単純なパターンであれば、プロセスＢがロックファイルを踏みつぶす時点でエラーとすることも出来るかもしれません。）
　「ロックファイルの存在確認」も、「ロックファイルの生成」も、両方ともアトミックな処理では無いことにも注意が必要です。ロックファイルの生成中にも、ロックファイルの存在確認が発生する可能性があると言うことです。

　また、ＤＢへのアクセスが完全にシリアライズされますから、効率は・・・・まぁ、言及する必要は無いですよね。

　最初に一番単純と書きましたが、結構難しいですから、頑張ってください。
　ポイントは、自分の書いている一連の手順同士が割り込みあったら何が起きるかを徹底的に考えることです。一つの行で書いた処理が（ないしは一つの単語で書いた処理が）、中断されない処理であることを当てにしてはいけません。（極端な話、「変数Ａをインクリメントする」処理でさえアトミックではないのです。インクリメントしている途中に・・・という事態は現実に発生します。）
　一般的には、クリティカルセクションを特定し、セマフォやスピンロックなどで実装するのが普通です。このあたりの用語で調べてみると良いでしょう。

お礼

再度のアドバイス、ありがとうございます。

ロックファイルはファイルに書き込みをするときに必ず使っているので使用経験はあります。
ただ、自分が使っているロック方法ではロック用ファイルは最初から存在した状態です。

確かに単純にロックファイルというのもいろいろ課題があるのは承知です。
いろいろロックに関するサイトを読んでいて頭が痛くなる話が多いです。

そもそもSQLiteでもトランザクションはあるので、本来は自前でロックする必要はないはずですがそれをやらないといけないのはファイルベースのSQLiteの限界ということで、これを自分で完璧に代用するのは無理な気もします。

そうすると最初のお礼で書いたように、メインのデータベースへ書き込みするのはマスター側のPHPにして、ユーザーは書き込めないようにする、という方法のほうが安全かもしれません。
もちろんリアルタイムにデータベースに書き込みはできないですが、運用上は問題ないレベルです。

回答No.1

mitoneko

あっさりと回答してしまうなら、私なら、たとえＭｙＳＱＬがどんなに面倒でも、意地でもＭｙＳＱＬを学習しますね。

　マルチスレッドにおけるリソースの衝突管理は難しいです。ましてや、トランザクションを白紙から全部自分で構築する・・・なんてなったら、気が遠くなります。
　これって、実際に作るのも恐ろしく難易度は高いんですが、作るだけなら何とかなります。でも、デバッグは出来ません。なぜなら、全てのパターンを論理的に網羅してというのが、不可能だからです。スレッドのタイミングなんてランダムですから、実験が出来ません。一日や２日程度、ちょっと何人かで走らしてみて問題が無かったら・・・なんて甘いテストなら出来ますけどね。問題が発生した時、どこが問題なのかを探すのも極めて難しい分野です。テストして問題があることが解っても、どこが問題なのかを見つける有効なツールはありません。（ちなみに、マーフィーの法則という証明はされていない真理によると、このような甘いテストをしていると、あなたが、一番トラブルが起こって欲しくないタイミングで、バグが顕在化するそうです（笑））

　こんな作業するくらいなら、全ての構築作業を中断してMySQLのマニュアルを熟読してマスターした方がよっぽど楽です。

お礼

ありがとうございます。
いずれMySQLに移行できるようにPDOで作成しております。

Webツールを作る時間があまりないので今回はSQLiteで行こうかと思っていますが、同時アクセスを回避する方法は、ファイルロックと同じ要領で良いかなと考えているのですがどうでしょうか？

DBに書き込み前にロック用ファイルをファイルロックをし、書き込みが終わったらロックを解除する、という感じです。
他のプロセスがDBに書き込みするときはロックファイルを監視し、ロック中ならループして解除されるまで待ちます。

その他に、メインのDBに書き込みできるのはマスターのPHPだけにして、ユーザーは一切書き込みできないようにする、という方法です。
ユーザーが何かデータを作ったら、そのデータを新規DBとして登録します。他のユーザーも何かデータを作ったら新規DBを作ります。
どんどんDBが出来上がっていきますが、マスターのPHPがその作られたDB群をメインのDBにコピーします。コピーが終わればコピー元のDBを削除します。
マスターのPHPは1分置きにcronで実行します。

こんな感じを検討しています。
もちろんMySQLにすれば良いのでしょうが、私個人で独学でやっていくしかなく、時間もあまりないためにSQLiteを検討しています。もちろんいずれはMySQLに移行したいと考えています。

再度助言があればよろしくお願い致します。