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

データベースの復元

前回までに作成したデータベースを読み込むには，[File]→[Open DB]をクリックし， 保存した sqlite4.db を選択します． 復元したデータベースに【学生リスト】，【学部リスト】，【テニス部】，【サッカー部】，【商品リスト】，【八百屋】，【果物屋】があることを確認しましょう．

【学生リスト】

【学部リスト】

【テニス部】

【サッカー部】

【商品リスト】

【八百屋】

【果物屋】

トランザクション

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

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

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

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

トランザクションをスタートするには，BEGIN TRANSACTIONと入力します．

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

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

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

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

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

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

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

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

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

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

COMMIT TRANSACTION;

このように，トランザクションの処理は全て実行されるか，または， ロールバックで全て取り消されるか，どちらかの結果となります．

ビュー

テーブルに対するクエリの結果を ビュー として保存することができます． 保存された ビュー はテーブルと同じように扱うことができます． ここでは，【学生リスト】と【学部リスト】の自然結合を【大学リスト】という名前のビューとして保存します．

CREATE VIEW 大学リスト AS SELECT * FROM 学生リスト NATURAL JOIN 学部リスト;

SELECTで【大学リスト】のレコードを表示すると，【学生リスト】と【学部リスト】の自然結合が表示されます．

SELECT * from 大学リスト;

トリガー

テーブルに追加・削除・更新などのクエリが適用されたときに自動的に実行する処理を トリガー として登録することができます． ここでは，【売上商品】と【売上日時】のテーブルを作成し，【売上商品】にINSERTが発生したとき，同じタイミングで【売上日時】に時刻を記録する処理を【売上ログ】という名前のトリガーとして登録します．

CREATE TABLE 売上商品(
  ID INTEGER PRIMARY KEY AUTOINCREMENT,
  商品名 TEXT
);

CREATE TABLE 売上日時(
  ID INTEGER PRIMARY KEY AUTOINCREMENT,
  日時 TEXT
);

CREATE TRIGGER 売上ログ AFTER INSERT ON 売上商品
  BEGIN
    INSERT INTO 売上日時(日時) VALUES(datetime());
  END;

【売上商品】にパソコンをINSERTすると， 【売上日時】に2021-04-24 10:39:17が記録されていることがわかります．

INSERT INTO 売上商品(商品名) VALUES('パソコン');

データベースの保存

作成したデータベースを保存するには，[File]→[Save DB]をクリックします． 保存したファイルは sqlite5.db とファイル名を変更してから，提出してください．

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

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

• ITパスポート 平成27年秋期 問71
• ITパスポート 平成30年秋期 問63
• 基本情報技術者 平成24年春期 午前 問29
• 基本情報技術者 平成19年秋期 午前 問61
• 応用情報技術者 平成23年特別 午前 問32