単純ですが、意外と穴？なデータベースリソースの鮮度

質問

よろしくお願い致します。
環境PHP5.2.5 / SQLite3

/*----------------------------------------
*　インサート
-----------------------------------------*/

$sql_1 = 'INSERTなんたら～(id = 25 を作成）';

$db_pass = 'db_hoge.sqlite';
$db_res = sqlite_open($db_pass);

//★タイム（１）
$result = sqlite_query($db_res,$sql_1);
　　　　　：
　　～　INSERT処理($sql_1)をしてます　～
　　　　　：
//sqlite_close($db_res)　ここではクローズせず
　　　　　：
　　　　　：
/*----------------------------------------
*　デリート
-----------------------------------------*/
$sql_2 = 'DELETEなんたら～(id = 25 を削除）';

//省略しちゃうわけです。$db_pass = 'db_hoge.sqlite';
//省略しちゃうわけです。$db_res = sqlite_open($db_pass);

//★タイム（２）
$result = sqlite_query($db_res,$sql_2);
　　　　　：
　　～　DELETE処理($sql_2)をしてます　～
　　　　　：
/*----------------------------------------
*　/処理完了
-----------------------------------------*/

//ここで、ようやく、クローズ
sqlite_close($db_res);

//==============================================

何が疑問かと言いますと、
★タイム（１）と、★タイム（２）の時点では、
$db_res　の中身の鮮度が違うのでは？と思うわけです。

つまり、
★タイム（１）の時点では、id=25のデータは作成されていません。
★タイム（２）の時点では、id=25のデータは作成済みです。

id=25が作成されていない状態のDBリソースを、
★タイム（２）の所で、使用しています。

言い換えると、

id=25が存在していないDBのリソースを、
タイム（２）の時点で使用し、
そのリソースをもとに、

id=25のデータを消そう（DELETEしよう)としているわけです。

「//省略しちゃうわけです。」
という所で、省略する場合と、しない場合とでは、
結果はやはり異なるのでしょうか？

検査対象がリソースだけに、調べにくいなと思い、
質問させて頂きました。

どなたか、この辺りの正式な作法も含めて、
教えて頂けてませんでしょうか。

上記の「//省略しちゃうんです。」の方法で、
問題ないですよ、
ということであれば、今後も、省略しちゃう方向で、
コーディングしていこうと思います。笑

回答No.2

INTLINSIDE · ベストアンサー

＞後者の方法だと、リソースが新鮮に保たれないため、
＞インサート処理の結果が影響するデータについて処理を行う場合（例では、インサートされたデータをすぐデリートしている）、
＞正常に処理できないことになりますよね。

同じ $db_res を使っている限り、正常に処理できると思います。
明示的にトランザクションを開始した場合、コミットしない限り本物のDBには反映されませんが、
そのトランザクションを開始した同じ$db_resを使っていれば、insertしたものをdeleteしてもエラーにはならないと思います。

それどころか、SQLiteはトランザクションが常に１つしかないと思われるので、常に最新であるように思います。
SQLiteの場合、トランザクションを使うメリットは、高速に動かしたい場合と、排他というか他の人のアクセスを拒否したい場合のようです。

これはPHPによるウェブアプリケーションの話ですよね。
ということは、１サイクル中（クライアントが1ページ表示している間）の話ですよね。
INSERTしてDELETEするのも1サイクル中の話ということになるかと思います。
であれば、鮮度は考えなくても問題ないと思うのですが、駄目なんでしょうか。
1サイクル中であれば、同じ $db_res で操作する限り、常に最新の状態で操作しているのと変わらないと思います。

トランザクションで保護された情報はコミットされない限り、本物のDBには反映されないので
別なサイクル（他の人がアクセスして来た時とか）では、古いままですが、SQLiteは、トランザクションを開始すると、テーブル全体がロックされて他からのアクセスを拒否するようです。（エラーになります）

http://blog.zunbe.com/archives/2009/03/sqlite.html

ので、他の人も常に最新の状態でしかアクセスできないという事になると思います。

アクセス過多によるロック
http://oshiete1.goo.ne.jp/qa4310712.html

テーブルがロックされている間、ページの表示を待機させるような仕組みも実装できると思いますが、どうすればいいか、今の私には思いつきません。
ロックの状態を検査できる関数とかあれば、それを使ってループで回すとか、PHPには例外ハンドラがあったと思うので、エラーがでたらループするとか。
（操作の量にもよると思いますが、待たされるのは一瞬だと思います。なので利用者は待たされてるとは感じないと思います。アクセスが集中した場合には、別ですが、そういう状況になるようなら既にSQLiteでは、さばけなくなっていると思います。）
「アクセスが集中して混み合っています」とか「他の人が処理中です」のようにページに表示するようにする事もできますが。

削除のエラー処理に例外ハンドラの例となるような部分が有ります
http://study.rakuto.net/exec/bbs/browse/146/

ここまで踏まえて最初の質問のソースを見ると
open - insert - delete - close　と
open - insert - close - open - delete - close
の違いは、途中がcloseとopenがあるか無いかだけの違いのように思います。
トランザクションを明示的に開始しなければ自動で開始されるので、クエリーを発行した直後、本物のDBにも繁栄され、常に最新です。

＞コミットしなくても、インサート処理自体はDBに反映されているのでしょうかね。
本物のDBには反映されないですが、同じ $db_res を使ったクエリーは、それまでのクエリーの累積した結果が反映されていると思います。
そうじゃないとテンポラリテーブルを使ったデータ処理
BEGIN TRANSACTION;
CREATE TEMPORARY TABLE temp();
INSERT INTO temp SELECT * FROM xxx;
DROP TABLE temp;
COMMIT;
みたいな事ができないですよね。

補足

＞1サイクル中であれば、同じ $db_res で操作する限り、常に最新の状態で操作しているのと変わらないと思います。
＞トランザクションで保護された情報はコミットされない限り、本物のDBには反映されないので
＞本物のDBには反映されないですが、同じ $db_res を使ったクエリーは、それまでのクエリーの累積した結果が反映されていると思います。

なるほど、なるほど。
私を混乱させていた「トランザクション」について、分かるようになってきました。
頂いた回答の中に何度か登場した「本物のDB」という言葉が印象的でした。
イメージ的には、
SQLiteのDBにおける、
ビギン→コミットの間の１サイクル中においては、
ここで言う$db_resの中身には、
最新状態のDBリソースが常に格納されていて、
この状態は、
ビギンされたDB（DBリソース）がコミットされるまでの間、
仮の（メモリ上の？）スペースに一時的に保管されているような、
そんな状態なのでしょうかね。

で、コミットされるまでの間、
その一時保管されているDBリソース（常に新鮮）に対して、
インサートしたりデリートしたりするので、
正常にDB処理ができ、
最終的にコミットされると、その一時保管されていたデータの、
最終状態が「本物のDB」へと書き込まれる、
といった感じになるのでしょうね。

イメージ的には、そういうことをおっしゃられているのだと思います。

また、紹介頂いたURLも参考にさせて頂きました。
（昨夜の段階では、「教えて!goo」によるURLチェックが入っていたため、
その間は、こちらの回答(NO.2)を見ることはできませんでしたが、
最終的には読むことができるようになりました。
どなたかのブログのURLがまずかったのでしょうかね。）

で、話を戻し…

トランザクションを利用する場合、
処理速度を速める以外に、「ロック（排他）」も行われるということが
よく分かりました。
処理速度を速めることについては、「良い」としか思いませんが、
ロックについては、もちろん、これも「良い」のですが、ただ多少、弊害もありそうだなという印象です。

その弊害とはつまり、
ロックされている間、他のアクセスを待機させるということを指しています。
弊害でもあり、有り難い処理でもあり、といった
痛し痒しな処理だなと思っています。

ただ、余談ですが、こちらについては正直、私のサイト規模では、
今のところ、問題なさそうです。
しかし、頭の片隅には入れておきます。重要な事だと思いますので。

以上を踏まえ、私にとってベストな解を導き、
それをもとにサイトの設計に当たりたいと思います。

この度は私のためにご尽力頂き、ありがとうございました。
また分からないことがありましたら、色々教えて下さいね。

回答No.3

hogehoge78

http://www.techscore.com/tech/sql/11_01.html
トランザクションとは、一連の作業単位を意味するので、
INSERT⇒DELETEという一連の作業が成功したらCOMMITするということを考えれば、トランザクション内でINSERTしたidを同一トランザクション内でDELETEの条件として加えてやれば、存在しているとされ削除が行われると考えるのは自然と思えます。

実際、疑問に思ったのであれば、一度試してみてはいかがでしょうか。
http://reddog.s35.xrea.com/wiki/TkSQLite.html
こちらにWindowsで動作する、Sqliteを操作するGUIアプリケーションもありますので。
実際に、
-------------------------
BEGIN;
INSERT INTO hoge (id, name) VALUES (1, 'hogemoge');
UPDATE hoge SET name = 'momoge' WHERE id = 1;
COMMIT;
-------------------------
このSQL文を投げたら、id=1でname=momogeというデータが反映されてます。
-------------------------
BEGIN;
INSERT INTO hoge (id, name) VALUES (2, 'hogemoge');
DELETE FROM hoge WHERE id = 2;
COMMIT;
-------------------------
こちらも、INSERTの後にDELETEしてるので、正常に何もINSERTされませんでした。（これは、どちらにしても、DELETE文の構文自体正しいので、単純にid=2の条件に適合するものが無ければ処理なしとして反映されるのでINSERTがされてようがされてまいが、処理は一緒ですけど）

お礼

いつもお世話になっております。

＞トランザクションとは、一連の作業単位を意味するので、（～中略～）と考えるのは自然と思えます。

私はトランザクションというものをよく理解していなかったのだと思います。
そのことがよく分かりました。（もはや、PHPの話ではなくなっている。笑）

＞実際、疑問に思ったのであれば、一度試してみてはいかがでしょうか。

このようなソフトもあるのですね～。便利ですね～。
（と言いつつ、DLはしたものの、上手く使いこなせなかった私。苦笑）

なにはともあれ、
NO.2の補足でも既に書いたことですが、
ビギン→コミット　の間で行われる処理は、
ちゃんと更新されているんですよね。
（そう考えるのが自然ということですよね。）
それがよく分かりました。
今回もどうもありがとうございました。

P.S.
教えて頂いたSQLiteのアプリケーションは、
頑張って使えるようになりたいと思います。
マスターしたら便利そうなので。笑

回答No.1

INTLINSIDE

SQLiteとは疎遠なので、あんまりよく分かってないんですが。

http://net-newbie.com/sqlite/lang.html#transaction

・SQLiteはネストしたトランザクションを許していません
・トランザクション中を除いてはデータベースに対する変更は出来ません
・データベースを変更するコマンドを発行するとき、自動的にトランザクションが開始されます。
・自動的に開始されたトランザクションは、そのコマンドの終了を持ってコミットされます

とあります。
BEGIN でトランザクションを明示的に開始していない場合は
INSERTやDELETE毎に自動的に開始され、コマンドが終了すると、コミットされるのでしょう。

ということは、INSERTしたID=25のレコードは、sqlopenしたままでも、直後にコミットされるので、DELETE する時には既にID=25のレコードは存在しているという事になると思います。

より明確にしたい場合には、トランザクションを明示したほうがよいでしょう。
以下に使い方が有ります。
http://cgi.crystal-creation.com/web-appli/technical-information/sqlite/php.htm

SQLite以外の話ですが
DBの中には、トランザクションのデフォルトの動作を指定できる場合が有ります。そういったものの初期値がロールバックになっている場合、コミットもロールバックもせずにデータベースを閉じると、自動的にロールバックされて、INSERTしても反映されないというような不具合で悩む事もあるかもしれないので、そういった事も記憶の片隅にでも入れておくと、良いのではないかなと思います。

お礼

読みやすく、分かりやすい回答をありがとうございます。
とてもよく分かりました。

＞BEGIN でトランザクションを明示的に開始していない場合は
＞INSERTやDELETE毎に自動的に開始され、コマンドが終了すると、
＞コミットされるのでしょう。

ということですので、
タイム（１）とタイム（２）、それぞれの時点における$db_resには、
その時点における最新のDBリソースが格納されているということになりますね。
（疑問だったことの核心部分ですね）

よって、
「//省略しちゃうわけです。」の所における、省略の有無は、
動作に影響を及ぼさないということになりそうですね。

ただ、ソースの保守性などの観点から、
これらを省略すべきかどうか、今一度考えてみたいと思います。

また、意識的にBEGIN,COMMITするべきかどうかも併せて考えてみようと思います。
SQLiteの場合、毎回BEGIN,COMMITを繰り返すようだと、動作が極端に遅くなるようなので、この辺りのことも含め、再検討してみます。

補足

（「お礼」を書いた後に、「補足」を書いています。読む順番に気をつけて下さい。）

「お礼」の最後の所が、ちょっと変な内容になってしまっている気がするので少々修正を。

質問で挙げたサンプルは、下記の例（変数の代入式などは省略してあります）
と等価であると思います。

※自動的に行われるトランザクション処理を明示的に書き加えた点が、質問のサンプルと異なります。他は同じ。

/*----------------------------------------
*　インサート
-----------------------------------------*/

//★タイム（１）
sqlite_query("BEGIN;",$db_res);//明示的に
$result = sqlite_query($db_res,$sql_1);
sqlite_query("COMMIT;",$db_res);//明示的に

//sqlite_close($db_res)　ここではクローズせず

/*----------------------------------------
*　デリート
-----------------------------------------*/

//★タイム（２）
sqlite_query("BEGIN;",$db_res);//明示的に
$result = sqlite_query($db_res,$sql_2);
sqlite_query("COMMIT;",$db_res);//明示的に

/*----------------------------------------
*　/処理完了
-----------------------------------------*/

で、上とは別に、また別のパターンとして、

/*----------------------------------------
*　インサート
-----------------------------------------*/

//★タイム（１）
sqlite_query("BEGIN;",$db_res);//明示的に
$result = sqlite_query($db_res,$sql_1);
//ここではコミットしない！

//sqlite_close($db_res)　ここではクローズせず

/*----------------------------------------
*　デリート
-----------------------------------------*/

//★タイム（２）

$result = sqlite_query($db_res,$sql_2);
sqlite_query("COMMIT;",$db_res);//ようやくここでコミット。（余談ですが、省略可能なのかな。

/*----------------------------------------
*　/処理完了
-----------------------------------------*/

という書き方も考えられます。

後者の場合、
コミットされるのが、タイム（２）の後になりますから、
$db_resの鮮度が常に新鮮に保たれているわけではないですよね。

さて、ここまで考えて、
思っていた以上に奥深い問題なのかな、と思い始めました…。

そう思った根拠を書きますと、

後者の方法では、コミットをまとめて最後に行うため、
前者の方法に比べ、処理が速くなるはずですが
（2回くらいのDB処理では、さほど変わらないでしょうが）、

後者の方法だと、リソースが新鮮に保たれないため、
インサート処理の結果が影響するデータについて処理を行う場合（例では、インサートされたデータをすぐデリートしている）、
正常に処理できないことになりますよね。
（id=25がコミットされていないのに、id=25のデータをデリートできやしない、という意味で。）

よって、まとめると、

前者フローだと、処理時間は多少要すが、正確な処理が常に可能。

後者は、処理時間短縮は期待できるが、正確に行える処理が限定的となる。

ということになりますでしょうか。

と、ここまで書いて、
コミットはあくまで確定的なものにする処理であって、
コミットしなくても、インサート処理自体はDBに反映されているのでしょうかね。

とすれば、後者の方法で問題ないことになりますね。
（ごちゃごちゃと、すみません。苦笑）