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

Image from Gyazo

テーブルのレストア

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

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

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

【学生リスト】

学部 キャンパス 教員数
工学部 春日井 30
人文学部 春日井 20
国際関係学部 名古屋 25

【学部リスト】

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

【テニス部】

学籍番号 氏名 学年 学部 よみがな
A001 岩城隼人 2 工学部 いわきはやと
B003 杉江弘子 3 人文学部 すぎえひろこ
A005 内田弘 3 工学部 うちだひろし

【サッカー部】

商品ID 商品名 仕入れ値 売値
C01 テレビ 50000 65000
C02 エアコン 60000 70000
C03 冷蔵庫 80000 55000
C04 電卓 3000 2000
C05 炊飯器 20000 25000

【商品リスト】

商品番号 商品名 価格
G1 ニンジン 100
G2 トマト 200
G3 スイカ 500

【八百屋】

商品番号 商品名 価格
G4 イチゴ 300
G5 ブドウ 200
G3 スイカ 500

【果物屋】

トランザクション

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

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

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

ACID

  • 原子性(Atomicity) トランザクションに含まれる処理は,全て実行されるか,全く実行されないことが保証される.
  • 独立性(Isolation) トランザクションの実行過程は隠蔽される(他者から観測できない).
  • 一貫性(Consistency) トランザクションの終了時に矛盾が発生する処理は実行されない(中断される).
  • 永続性(Durability) トランザクションが完了したら,その結果は失われない.

原子性

まずは,原子性に関して確認していきましょう. トランザクションをスタートするには,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|経営情報学部|やましたたかし

課題1

トランザクションを利用して【八百屋】と【果物屋】のスイカの価格を700円に変更しなさい. このとき,2つのクライアントを起動し,一方には途中経過が隠蔽されていることを確認しなさい. また,各クライアントの出力結果(確定前と確定後)を提出しなさい.

テーブルのバックアップ

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

sqlite> .backup db5.sqlite

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

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

参考書籍

愛知県名古屋市にある椙山女学園大学 文化情報学部 向研究室の公式サイトです. 専門は情報科学であり,人工知能やデータベースなどの技術要素を指導しています. この公式サイトでは,授業で使用している教材を公開すると共に, ベールに包まれた女子大教員のミステリアスな日常を4コマ漫画でお伝えしていきます. サイトに関するご意見やご質問はFacebookまたはTwitterでお問い合わせください.