SQL(5) トランザクション

テーブルのレストア

前回までに作成したテーブルを .restore でレストアしましょう． また，.tableで【学生リスト】，【学部リスト】，【テニス部】，【サッカー部】，【商品リスト】，【八百屋】，【果物屋】があることを確認しましょう．

sqlite> .restore db4.sqlite
sqlite> .table
サッカー部  八百屋        学生リスト  果物屋
テニス部     商品リスト  学部リスト

【学生リスト】

【学部リスト】

【テニス部】

【サッカー部】

【商品リスト】

【八百屋】

【果物屋】

トランザクション

先ずは，トランザクションについて見ていきましょう． トランザクションとは，データベースに対する分割不可能な一連の処理のことを意味しています． 例えば，学籍番号が$A002$の学生が$人文学部$に転学部したとします． このとき，下記の二つの処理が必要です．

• 【学生リスト】から学籍番号が$A002$のレコードを検索し，学部を$人文学部$に変更
• 【テニス部】から学籍番号が$A002$のレコードを検索し，学部を$人文学部$に変更

もしこの処理が分割されて実行された場合， 【学生リスト】では$人文学部$であるのに，【テニス部】では$工学部$のままとなり矛盾が生じてしまいます． このように矛盾が生じないように，データベースに対する複数の処理を， トランザクションとしてまとめて実行することができます．

また，トランザクションが満たすべき性質として ACID と呼ばれる概念が存在します．

原子性

まずは，原子性に関して確認していきましょう． トランザクションをスタートするには，BEGIN TRANSACTIONと入力します．

/* トランザクションのスタート */
BEGIN TRANSACTION;

トランザクションをスタートすると，データベースがロックされ，他者は書き込みが出来なくなります （正確にはデータベースへの最初のアクセスがあった時点でロックされる）． それでは，学籍番号が$A002$の学生が，$人文学部$に転学部したことを想定し， 【学生リスト】と【テニス部】のレコードをUPDATE文で更新してみましょう．

/* 学生リストの更新 */
UPDATE 学生リスト SET 学部="人文学部" WHERE 学籍番号="A002";

/* テニス部の更新 */
UPDATE テニス部 SET 学部="人文学部" WHERE 学籍番号="A002";

sqlite> select * from 学生リスト;
学籍番号|氏名|学年|学部|よみがな
A001|岩城隼人|2|工学部|いわきはやと
A002|岩村優|1|人文学部|いわむらゆう
B003|杉江弘子|3|人文学部|すぎえひろこ
C004|仙波あすか|1|国際関係学部|せんばあすか
D006|山下隆|4|経営情報学部|やましたたかし

sqlite> select * from テニス部;
学籍番号|氏名|学年|学部|よみがな
A002|岩村優|1|人文学部|いわむらゆう
B003|杉江弘子|3|人文学部|すぎえひろこ
C004|仙波あすか|1|国際関係学部|せんばあすか

【学生リスト】と【テニス部】を確認すると， 確かに学籍番号が$A002$の所属が$人文学部$に変更されていることが確認できます． しかし，実はこの時点では，更新は確定されておらず，更新前の状態に戻すことが可能です． 前の状態に戻すことを ロールバック と言います． ロールバックするには，ROLLBACK TRANSACTIONと入力します．

/* トランザクションのロールバック */
ROLLBACK TRANSACTION;

ロールバックすると，【学部リスト】と【テニス部】は更新前の状態に戻っていることが確認できます．

sqlite> select * from 学生リスト;
学籍番号|氏名|学年|学部|よみがな
A001|岩城隼人|2|工学部|いわきはやと
A002|岩村優|1|工学部|いわむらゆう
B003|杉江弘子|3|人文学部|すぎえひろこ
C004|仙波あすか|1|国際関係学部|せんばあすか
D006|山下隆|4|経営情報学部|やましたたかし

sqlite> select * from テニス部;
学籍番号|氏名|学年|学部|よみがな
A002|岩村優|1|工学部|いわむらゆう
B003|杉江弘子|3|人文学部|すぎえひろこ
C004|仙波あすか|1|国際関係学部|せんばあすか

それでは，もう一度，トランザクションをスタートして， 先程と同じように【学部リスト】と【テニス部】を更新しましょう．

BEGIN TRANSACTION;
UPDATE 学生リスト SET 学部="人文学部" WHERE 学籍番号="A002";
UPDATE テニス部 SET 学部="人文学部" WHERE 学籍番号="A002";

今度は，更新を確定しましょう． 確定するには，COMMIT TRANSACTIONと入力します． これで，更新は確定され，データベースに対するロックも解除されます．

COMMIT TRANSACTION;

このように，トランザクションの処理は全て実行されるか，または， ロールバックで全て取り消されるか，どちらかの結果となります． ここで，一旦，作成したテーブルをファイルにバックアップしましょう． バックアップするファイル名は db5.sqlite としましょう．

sqlite> .backup db5.sqlite

独立性

次に，独立性に関して確認していきましょう． 独立性を確認するためには，複数のクライアントから，データベースに接続する必要があります． そこで，2つのクライアント(便宜的に クライアントA と クライアントB と呼ぶ)を起動し， それぞれから.openを利用して，先程バックアップしたファイル（ db5.sqlite ）に接続します． ファイルへの接続は.databaseで確認することができます． これで，クライアントAとクライアントBは同じデータベースを共有していることになります．

/* クライアントAとクライアントBの両方で実行 */
sqlite> .open db5.sqlite
sqlite> .database
main: db5.sqlite

それでは，クライアントAで，トランザクションをスタートし， 学籍番号が$A002$の学部を$工学部$に戻してみましょう．

/* クライアントAで実行 */
BEGIN TRANSACTION;
UPDATE 学生リスト SET 学部="工学部" WHERE 学籍番号="A002";
UPDATE テニス部 SET 学部="工学部" WHERE 学籍番号="A002";

クライアントAで更新を確定していない状態で， クライアントBから【学生リスト】を確認してみます． 学部は更新されておらず$人文学部$のままであることがわかります．

/* クライアントBの【学生リスト】 */
sqlite> select * from 学生リスト;
A001|岩城隼人|2|工学部|いわきはやと
A002|岩村優|1|人文学部|いわむらゆう
B003|杉江弘子|3|人文学部|すぎえひろこ
C004|仙波あすか|1|国際関係学部|せんばあすか
D006|山下隆|4|経営情報学部|やましたたかし

そこで，クライアントAで更新を確定します．

/* クライアントAで実行 */
COMMIT TRANSACTION;

再度，クライアントBから【学生リスト】を確認すると， 学部が$工学部$に更新されていることがわかります． このように，トランザクションの途中経過は， 他のクライアントには隠蔽され，確定した段階で観測できるようになります．

/* クライアントBの【学生リスト】 */
sqlite> select * from 学生リスト;
A001|岩城隼人|2|工学部|いわきはやと
A002|岩村優|1|工学部|いわむらゆう
B003|杉江弘子|3|人文学部|すぎえひろこ
C004|仙波あすか|1|国際関係学部|せんばあすか
D006|山下隆|4|経営情報学部|やましたたかし

テーブルのバックアップ

作成したテーブルをファイルにバックアップするには，.backupを用います． バックアップするファイル名は db5.sqlite としましょう．

sqlite> .backup db5.sqlite

情報処理技術者試験・過去問

下記リンクは ITパスポート試験ドットコム， 基本情報技術者試験ドットコム， 応用情報技術者試験ドットコムに掲載されている問題です．

• 基本情報技術者 平成24年春期 午前 問30
• 基本情報技術者 平成24年春期 午前 問31
• 基本情報技術者 平成27年春期 午前 問28

参考書籍