やってみる

アウトプットすべく己を導くためのブログ。その試行錯誤すらたれ流す。

📌 ？
トップ > SQLite3 > SQLite3構文　begin,end,commit,rollback,savepoint,release（deferred,immediate,exclusive）

SQLite3構文　begin,end,commit,rollback,savepoint,release（deferred,immediate,exclusive）

SQLite3

　トランザクションとロック。

成果物

トランザクション

目的

　トランザクションの目的は、データの整合性を保つことである。

　たとえば膨大な量のデータを追加・更新・削除するとき。変更中に別のプロセスからデータを取得されると、タイミングによっては整合性が保てなくなる。

　あるプロセスP1があるDBDに対して、以下のような変更をしていたとする。

create table T(A int);
insert into T values(0);
update T set A=1;

　このときupdate前に、別のプロセスP2からDのデータを取得したとする。これでデータの整合性は壊れた。DBの値は1なのに、プロセスP2では0になってしまう。もしその値が主キーであれば、致命的なバグになりかねない。

　そこで整合性を保てるだけの更新処理のまとまりをトランザクションという単位にする。トランザクション中はDBファイルにロックをかけて読書できなくする。これにより他のプロセスからは読書できずにエラーとなる。

　もしもDBがプッシュ型サービスであれば、DB更新時にそれを参照しているアプリ側に対して取得データを更新してくれるだろう。これならタイミングによる整合性について悩まずに済む。プル型の設計思想は古いのかもしれない。

ロック方法

時期 概要
deferred 最初の書込SQL実行時にロックをかける（トランザクションの開始でなく）
immediate トランザクション開始時にロックをかける（他プロセスは書込不可）
exclusive トランザクション開始時にロックをかける（他プロセスは読書不可）
オプション ロック 読 書
deferred(最初が読込SQL) shared ○ ☓
deferred(最初が書込SQL) reserved ○ ☓
immediate reserved ○ ☓
exclusive exclusive ☓ ☓

　読,書はそのロックを取得したプロセス以外のプロセスに対する権限である。

ロック状態

ロック状態 読 書 他
unlocked ○ ○
shared ○ ☓
reserved ○ ☓ sharedロック取得可
pending ○ ☓ sharedロック取得不可
exclusive ☓ ☓ 他のロック取得不可

　読,書はそのロックを取得したプロセス以外のプロセスに対する権限である。

表記

　トランザクションを表記するためのキーワード。

開始 終了 概要
begin end(commit) / rollback トランザクション開始
savepoint release ... / rollback to ... トランザクションに名前をつける
  • begin直後のtransactionは省略可
  • endとcommitは同義
  • end(commit)直後のtransactionは省略可

確かめる最小コード

　end。

begin;
create table T(A int);
insert into T values(0);
end;
select * from T;

0

　commit。endと同じ。

begin;
create table T(A int);
insert into T values(0);
commit;
select * from T;

0

　rollback。

begin;
create table T(A int);
insert into T values(0);
rollback;
select * from T;

Error: near line 5: no such table: T

　savepoint/release。

savepoint SP1;
begin;
create table T(A int);
insert into T values(0);
end;
release SP1
select * from T;

0

　savepoint/rollback。

begin;
savepoint SP1;
create table T(A int);
insert into T values(0);
rollback transaction to savepoint SP1;
select * from T;

Error: near line 6: no such table: T

　savepointのネスト。

begin;
create table T(A int);
insert into T values(0);
savepoint SP1;
insert into T values(1);
rollback transaction to savepoint SP1;
release SP1;
insert into T values(2);
end;
select * from T;

Error: near line 6: no such table: T

　savepoint/releaseで苦労した。例によって公式がコードを載せていない。

やってみる

　トランザクション中は操作した内容を確認できる。だが、ロールバック後はトランザクション中に操作した内容を確認できない。

　ターミナルで以下コマンド実行。

sqlite3

　トランザクション開始。

begin transaction;

　テーブル作成。

create table T(A int);
select * from main.sqlite_master;

table|T|T|2|CREATE TABLE T(A int)

　トランザクションだからといって一時DBtempに入っているわけではない。

select * from temp.sqlite_master;





　レコード挿入。



insert into T values(0);
select * from T;







0





　以降、rollback/commitでの違いをみてみる。



rollback



　ロールバック。



rollback;





　テーブルは消えている。



select * from T;







Error: no such table: T





commit



　コミット。



commit;





　またはend;でも可。



end;





　テーブルとレコードが存在する。



select * from T;







0





ロック方法の違い



deferred（読込SQL時sharedロック）



　ターミナルのタブAにて以下コマンドを実行する。



sqlite3 a.db







begin deferred transaction;





　読込SQL実行。本当はこの読込中に時間をとめたい。



select * from sqlite_master;





　ターミナルの新しいタブBにて以下コマンドを実行する。開くファイルはタブAと同じものであること。



sqlite3 a.db





　書込できてしまう。読込中でなく読込完了してしまったから……。



begin deferred transaction;
create table T(A int);











　読込もできる。



select * from sqlite_master;







table|T|T|2|CREATE TABLE T(A int)





　もとに戻す。



rollback;





　begin deferred transaction;(読込)ではトランザクションを開始しても書込SQLを実行しないかぎり読書できてしまった。ただし読込の最中は書込不可のはず。それを確認したかったのだが、CLIで確かめる方法がわからず。



deferred（書込SQL時reservedロック）



　ターミナルのタブAにて以下コマンドを実行する。



sqlite3 a.db







begin deferred transaction;





　テーブル作成。



create table T(A int);





　ターミナルの新しいタブBにて以下コマンドを実行する。開くファイルはタブAと同じものであること。



sqlite3 a.db





　書込不可。ロックエラーとなる。



create table T(A int);







Error: database is locked





　読込はできる。ロックエラーが出ない。



select * from sqlite_master;











　もとに戻す。タブAでロールバックする。



rollback;





immediate



　ターミナルのタブAにて以下コマンドを実行する。



sqlite3 a.db







begin immediate transaction;





　ターミナルの新しいタブBにて以下コマンドを実行する。開くファイルはタブAと同じものであること。



sqlite3 a.db





　テーブル作成。



create table T(A int);







Error: database is locked





　すでにロックされている。immediateはトランザクションを開始した時点でロックをかける。（テーブル作成などSQL文を発行せずとも）



　読込はできる。ロックエラーは出ない。



select * from sqlite_master;











　まだ何もないので結果はなにもない。



　もとに戻す。タブAでロールバックする。



rollback;





exclusive



　ターミナルのタブAにて以下コマンドを実行する。



sqlite3 a.db







begin exclusive transaction;





　ターミナルの新しいタブBにて以下コマンドを実行する。開くファイルはタブAと同じものであること。



sqlite3 a.db





　テーブル作成。



create table T(A int);







Error: database is locked





　すでにロックされている。exclusiveはトランザクションを開始した時点でロックをかける。（テーブル作成などSQL文を発行せずとも）



　読込もできない。



select * from sqlite_master;







Error: database is locked





　もとに戻す。タブAでロールバックする。



rollback;





CLIでエラー時どうなる？



　
エラーがなければ何の問題もない。



begin transaction;
create table T(A int);
insert into T values(0);
commit;





　コマンド実行するなら以下。



sqlite3 :memory \
"begin transaction;" \
"create table T (A int);" \
"insert into T values(0);" \
"commit;" \
"select * from T;"







0








エラー時に自動ロールバックしない



　当然だが、トランザクションを使わないと、エラー時にロールバックしない。



rm a.db
sqlite3 a.db \
"create table T (A int unique);" \
"insert into T values(0);" \
"insert into T values(0);" \
"insert into T values(1);" \
"select * from T;"







Error: UNIQUE constraint failed: T.A





　テーブルとレコード0がある。つまりUNIQUE制約エラーが出る前までのSQL文は実行されてコミットされた。



sqlite3 a.db "select * from T;"







0





自動ロールバックする



　トランザクション内でエラーが発生したら自動でロールバックしてくれる。



UNIQUE制約エラー



auto_rollback_unique_error.sh



rm a.db
sqlite3 a.db \
"begin transaction;" \
"create table T (A int unique);" \
"insert into T values(0);" \
"insert into T values(0);" \
"insert into T values(1);" \
"commit;" \
"select * from T;"







Error: UNIQUE constraint failed: T.A





　テーブル自体存在しない。つまりトランザクションの内容がロールバックされた。



sqlite3 a.db "select * from T;"







Error: no such table: T





Syntaxエラー



auto_rollback_syntax_error.sh



rm -f a.db
sqlite3 a.db \
"begin transaction;" \
"create table T (A int);" \
"insert into T values(0);" \
"aaaaaaaaaaaaaaaaa" \
"insert into T values(1);" \
"commit;" \
"select * from T;"







Error: near "aaaaaaaaaaaaaaaaa": syntax error





　テーブル自体存在しない。つまりトランザクションの内容がロールバックされた。



sqlite3 a.db "select * from T;"







Error: no such table: T





savepoint内でUNIQUE制約エラー



auto_rollback_unique_error_in_savepoint.sh



rm -f a.db
sqlite3 a.db \
"begin transaction;" \
"create table T (A int unique);" \
"insert into T values(0);" \
"savepoint SP1;" \
"insert into T values(0);" \
"release SP1;" \
"insert into T values(1);" \
"commit;" \
"select * from T;"







Error: UNIQUE constraint failed: T.A





　テーブルをみてみる。



sqlite3 a.db "select * from T;"







Error: no such table: T





　テーブル自体存在しない。つまりトランザクション内の一部であるsavepoint内でエラーが発生したら、トランザクション全体がロールバックされる。



　savepointだけをロールバックして、それ以外をコミットするわけではない。だが、そうでないならsavepointを作る意味はあるのか？　ふつうにbegin/endだけしたのと同じではないか。



savepoint内でSyntaxエラー



auto_rollback_syntax_error_in_savepoint.sh



rm -f a.db
sqlite3 a.db \
"begin transaction;" \
"create table T (A int unique);" \
"insert into T values(0);" \
"savepoint SP1;" \
"aaaaaaaaaaaaaaaaa" \
"release SP1;" \
"insert into T values(1);" \
"commit;" \
"select * from T;"







Error: near "aaaaaaaaaaaaaaaaa": syntax error





　テーブルをみてみる。



sqlite3 a.db "select * from T;"







Error: no such table: T





　テーブル自体存在しない。つまりトランザクション内の一部であるsavepoint内でエラーが発生したら、トランザクション全体がロールバックされる。



　savepointだけをロールバックして、それ以外をコミットするわけではない。だが、そうでないならsavepointを作る意味はあるのか？　ふつうにbegin/endだけしたのと同じではないか。



savepoint/releaseはbegin/end内につくるもの



外側には作れない



savepoint_can_not_use_outside.sh



rm -f a.db
sqlite3 a.db \
"savepoint SP1;" \
"begin transaction;" \
"create table T (A int unique);" \
"insert into T values(0);" \
"aaaaaaaaaaaaaaaaa" \
"insert into T values(1);" \
"commit;" \
"release SP1;" \
"select * from T;"







Error: cannot start a transaction within a transaction







sqlite3 a.db "select * from T;"







Error: no such table: T





単独で使える



コミット



savepoint_can_use_alone.sh



rm -f a.db
sqlite3 a.db \
"savepoint SP1;" \
"create table T (A int unique);" \
"insert into T values(0);" \
"release SP1;" \
"select * from T;"







0





ロールバック



savepoint_can_use_alone_rollback.sh



rm -f a.db
sqlite3 a.db \
"savepoint SP1;" \
"create table T (A int unique);" \
"insert into T values(0);" \
"rollback to SP1;" \
"select * from T;"







Error: no such table: T







sqlite3 a.db "select * from T;"







Error: no such table: T





UNIQUE制約エラー



savepoint_can_use_alone_unique_error.sh



rm -f a.db
sqlite3 a.db \
"savepoint SP1;" \
"create table T (A int unique);" \
"insert into T values(0);" \
"insert into T values(0);" \
"rollback to SP1;" \
"select * from T;"







Error: UNIQUE constraint failed: T.A







sqlite3 a.db "select * from T;"







Error: no such table: T





Syntaxエラー



savepoint_can_use_alone_syntax_error.sh



rm -f a.db
sqlite3 a.db \
"savepoint SP1;" \
"create table T (A int unique);" \
"insert into T values(0);" \
"aaaaaaaaaaaaaaaaa" \
"rollback to SP1;" \
"select * from T;"







Error: near "aaaaaaaaaaaaaaaaa": syntax error







sqlite3 a.db "select * from T;"







Error: no such table: T





savepointの存在意義がわからない



savepointをreleaseしてもロールバックされる



rm -f a.db
sqlite3 a.db \
"create table T (A int unique);" \
"begin transaction;" \
"savepoint SP1;" \
"insert into T values(0);" \
"release SP1;" \
"savepoint SP2;" \
"insert into T values(1);" \
"release SP2;" \
"savepoint SP3;" \
"insert into T values(0);" \
"release SP3;" \
"commit;" \
"select * from T;"






sqlite3 a.db "select * from T;"











　begin内のsavepointはreleaseでコミットされる。だが、release後でもbegin内の他所でエラーが発生したらロールバックされる。



　期待していたのは以下。






　savepointの存在意義がわからない。



savepoint/rollback toで指定したsavepointへ戻る



　これこそsavepointの意義。だが、「エラーが発生したときは指定savepointへ戻る」という条件分けができなければ意味がない。



rm -f a.db
sqlite3 a.db \
"create table T (A int unique);" \
"begin transaction;" \
"savepoint SP1;" \
"insert into T values(0);" \
"release SP1;" \
"savepoint SP2;" \
"insert into T values(1);" \
"release SP2;" \
"savepoint SP3;" \
"insert into T values(0);" \
"rollback SP2;" \
"commit;" \
"select * from T;"







Error: UNIQUE constraint failed: T.A







sqlite3 a.db "select * from T;"











　何も出ない。エラーも出ないということは、テーブル作成は成功している。これはトランザクション外なのでコミットされたのだろう。だが、すべてのinsertはされていない。レコードが1件もでないので。



　期待値としては0と1が出てほしかった。エラーになると強制的に最も外側のトランザクションまでロールバックされてしまうのだろう。それはsavepoint/releaseなしのbegin/endと同じだろう。ならsavepoint/releaseには何の意味があるのか。



savepoint/rollback toで指定したsavepointへ戻る



　もしエラーがなければ、見出しの件が実現できる。



rm -f a.db
sqlite3 a.db \
"create table T (A int unique);" \
"begin transaction;" \
"savepoint SP1;" \
"insert into T values(0);" \
"release SP1;" \
"savepoint SP2;" \
"insert into T values(1);" \
"savepoint SP3;" \
"insert into T values(2);" \
"rollback to SP3;" \
"select * from T;"







0
1





　savepoint SP3;の直前までロールバックした。SP1はrelease(commit)したが、SP2はしていない。それでもSP3の前であるSP2の処理まで戻った。つまりreleaseは不要。



　release SPnは「ここまでくれば、SPnにロールバックする必要はないよ」というときに使うものなのだろう。上記コードのrollback toをSP3からSP1にしようとすると以下エラーがでる。releaseするとそうなる。



Error: no such savepoint: SP1





　ところで、そもそも「エラーでないがロールバックしたい」ときはあるのだろうか？　最初からその操作をしなければいいのでは？　何かを試したいなら:memory:でやればいいのでは？　エラー時を想定してロールバックしたがるのでは？



「エラーになったとき」をキャッチできないなら意味なくね？



　トランザクションはデータの整合性を保つSQL文のまとまり。エラーになったときはロールバックするからこそ、トランザクションを作ることに意味があるはず。



　だからエラーをキャッチしてロールバックできなければ、トランザクションする意味はない。エラーが自動でキャッチされ、自動でロールバックされるなら、rollback;文を書く機会は永久にない。rollback;文に存在意義はない。



　同じくsavepointの意味もない。エラー時には指定のsavepointへロールバックしたいのに、無視されて最初のトランザクション開始時点へ自動でロールバックされてしまうのだから。



書けないrollback



　ロールバックしたいときは「エラーになったとき」だろう。だが、SQL文ではエラーが起こった時をキャッチできない。正常時はcommit、異常時はrollbackというように条件分岐したコードが書けない。正常な場合のコードしか書けない。よって、rollback文を明記することなどふつうはありえないはず。



　だとするとrollback文に意味はあるのか？　プログラミング・インタフェースを使い、Try〜Catch文を書かねばならないのか？　それはつまりSQL文では表現できないということ。SQL文脈で使えないSQL文をSQL構文として定義しているとは、これいかに。



try:
    db.execute("begin;");
    db.execute("...");
    db.execute("end;"); # commit
except : db.execute("rollback;");
finally: db.close();





　上記のように、SQLとは別のプログラミング・インタフェースと組合せることで、はじめて有効な仕事をするrollback君。お前なんなの？　SQLならSQL内で有効な仕事しろよ。もうSQLじゃなくね？　仲間はずれ感パない。ロックンロールでアウトローを気取るrollbackは使えない子。



rollback＜「俺は冴えないSQLでなくプログラミング文脈でこそ輝くんだぜ☆　お前ら脇役とは違うんだよ」



うぜぇ。



戻せぬsavepoint



　おそらくsavepointも同じなのだろう。Catch文を条件分けしてrollback to SPnするように書かねばならないのだろう。



　たとえば以下のように。



try:
    db.execute("begin;");
    db.execute("...");
    db.execute("end;");
except SP1でUNIQUE制約エラーが発生したら: db.execute("rollback;");
except SP2でUNIQUE制約エラーが発生したら: db.execute("rollback to SP1;");
except SP3でUNIQUE制約エラーが発生したら: db.execute("rollback to SP2;");
except : db.execute("rollback;");
finally: db.close();





　「どこで」「何のエラーが」発生したのかをcatchできるものなのだろうか？　たぶんError: UNIQUE constraint failed: T.Aのようにエラーの種別しかわからず、どこのsavepointであるかはわからないのでは？　だとしたら上記のようにrollback to ...で戻るべきsavepointを指定できないことになる……。



　あと、仮にできたとしても、SQL文内でrollback to ...を定義できないせいで、どのsavepointがどのsavepointと戻り関係にあるか定義できない。「SQL文だけ見てもトランザクション設計がわからない」という状況である。ひどくないか？　そういうものなのか？　まだ知らない何かがあるのか？　それとも何か根本的に勘違いしている？



所感



　既存のすべてのDBMSについて調べたわけではないし、SQLite3についても勉強中だから、なんとも言えない。だけど、今回やったかぎりでは以下のような致命的な問題があるように思える。



想像している問題



    

  • DBMSがPush型でなくPull型である


      

    • 整合性を保つべくロックという手法を用いる


        

      • 非同期処理という無駄にむずかしい仕事が必要


          

        •  タイミング次第でバグる構造である
          • 

        

        • 

      

      • 

    

    • 

  • エラー時のロールバックをSQL文脈内だけで定義できない


      

    • どこで、どのエラーになった時、どこへ戻るか、を定義できない
      • 

    

    • 





　前者はファイル型DBであるSQLite3の特性なので仕方ない。プロセス間やマシン間で共有することは想定外であるゆえの仕様と言われればそれまで。



　だが、後者のロールバックの件は深刻に思える。想定外のトラブルが起こることは想定していて欲しい。



もっと学習せねば



　これらの問題について「本当に解決できないのか」「どういう手法があるか」「どう実装するか」を調べねばならない。SQL学習だけで完結できないのなら、もっと大きな問題ということになる。



　こういうのを包含した設計・実装ができる言語とかないの？



　クラウドDBでFirebaseとかあるらしい。詳しくは知らないけど、DBがServerにあるならPush型かもしれない。その点はクリアできるのかも？　ローカルで使うだけならPostglesQLなどを使えばプロセス間共有だけならできるだろう（Pull型だろうが）。



　そもそも、NoSQLを含めてDBMSというものを使うべきかどうかも疑わしく思えてきた。他のプログラミング言語を併用せずにデータ管理を定義できないの？



　もっと深く考えたいが、今は表層のSQLite3だけに集中しよう。それすら理解できていないのだから。



対象環境






$ uname -a
Linux raspberrypi 4.19.42-v7+ #1218 SMP Tue May 14 00:48:17 BST 2019 armv7l GNU/Linux





前回まで