テーブルの更新とトランザクション

【概要】

トランザクションは複数のデータベース操作を一つの処理として扱う機能である。BEGIN TRANSACTIONで開始され、これ以降の操作はCOMMITかROLLBACKまで一つの処理単位として扱われる。COMMITはトランザクション内のすべての操作を確定させ、データベースの変更を永続的に反映する。一方、ROLLBACKはトランザクション内のすべての操作を取り消し、データベースをトランザクション開始前の状態に戻す。この仕組みにより、データベース操作の信頼性と一貫性が保証される。トランザクション処理中の変更はジャーナルファイルに一時的に記録され、システム障害時の回復やROLLBACK時に活用される。

【目次】

【サイト内の関連情報】

SQLite 3 の使い方の説明がある。

教材の利用条件: クリエイティブコモンズ 表示-非営利-継承 4.0 国際ライセンス（CC BY-NC-SA 4.0）に基づき、著作者表示・非営利目的・同一ライセンスでの再配布を条件として自由に利用可能である。

演習に必要な機能と文法

SQLite Manager Tool で既存のデータベースを開く

すでに作成済みのデータベースを、下記の手順で開く。

  1. ツールバーの「DBオープン (Ctrl+O)」ボタンをクリックする。
  2. データベースファイルを開く

    Ubuntu での実行例（「/home/ubuntuuser/mydb」を開く場合）

    データベースファイル /home/ubuntuuser/mydb を選び、「開く」をクリックする。

    Windows での実行例（「C:\SQLite\mydb」を開く場合）

    データベースファイル C:\SQLite\mydb を選び、「開く」をクリックする。

  3. 「スキーマナビゲータ」にテーブルの一覧が表示されるので確認する。

SQL を用いたテーブル定義と一貫性制約の記述

SQL を用いて、E テーブルを定義し、一貫性制約を記述する。 

リレーショナル・スキーマ (relational schema): E(name, score, student_name, created_at, updated_at)
  1. E テーブルの定義

    次の SQL を入力し、「▶ SQL実行 (F5)」を押下する。 

    create table E (
    name          text     not null,
    score         integer  not null check ( score >= 0 AND score <= 100 ),
    student_name  text     not null,
    created_at    datetime not null,
    updated_at    datetime,
    unique (name, student_name) );
  2. 出力欄の確認

    「出力」欄にエラーメッセージが表示されていないことを確認する。

SQL を用いたテーブルへの行の挿入

テーブルの 'created_at' 属性には、行を挿入する日時を記録する。以下の手順で E テーブルへの行の挿入を行う。

  1. SQL プログラムの記述

    「insert into ...」は行の挿入である。ここでは 5つの SQL 文を「begin transaction」と「commit」で囲む。 

    begin transaction;
insert into E values( 'Database',    80, 'KK', datetime('now', 'localtime'), NULL );
insert into E values( 'Database',    95, 'AA', datetime('now', 'localtime'), NULL );
insert into E values( 'Database',    80, 'LL', datetime('now', 'localtime'), NULL );
insert into E values( 'Programming', 85, 'KK', datetime('now', 'localtime'), NULL );
insert into E values( 'Programming', 75, 'LL', datetime('now', 'localtime'), NULL );
commit;
  2. 複数の SQL 文の一括実行

    「SQLエディタ」に記述した複数の SQL 文をまとめて実行するには、「▶ SQL実行 (F5)」を押下する。

  3. 「出力」欄の確認

    エラーメッセージが出ていないことを確認する。

SQL 問い合わせの発行と評価結果の確認

SQL を用いた問い合わせの実行例を示す。

テーブルのすべての行の表示 

SELECT * FROM E;

条件を満たす行のみの表示 

SELECT * FROM E WHERE name = 'Database';

SELECT score FROM E WHERE name = 'Database';

SQLite Manager Tool を用いたデータのブラウズ

SQL 内の文法エラー

  1. まず、文法エラーを含むSQLプログラムの記述

    「insert into E values( HOGEHOGE );」の「HOGEHOGE」は誤りである（間違いを含むプログラムでの振る舞いを見るため、わざと間違えている）。 

    begin transaction;
insert into E values( HOGEHOGE );
commit;
  2. 複数の SQL 文の一括実行

    「▶ SQL実行 (F5)」を押下する。

  3. 「HOGEHOGE」が誤っているので、「出力」欄にエラーメッセージが表示される。

    エラーが発生した文以降（ここでは「commit;」）は実行されないため、「begin transaction;」で開始したトランザクションは、commit も ROLLBACK も行われないまま継続している。

  4. ここでは処理を続行したいので、「SQLエディタ」の内容を「commit;」のみに書き換えて、再度「▶ SQL実行 (F5)」を押下する。
  5. 「commit;」が実行され、トランザクションが終わる。
  6. 確認のため、テーブルのすべての行を表示する。テーブル E に変化はない。 
    SELECT * FROM E;

トランザクション開始後にトランザクションを開始できない

  1. 前の SQL プログラムをそのまま使う。 
    begin transaction;
insert into E values( HOGEHOGE );
commit;
  2. もう一度、複数の SQL 文の一括実行を行う。
  3. 「HOGEHOGE」が誤っているので、「出力」欄にエラーメッセージが表示される。

    今度は処理を中断したままにする。「insert into E values( HOGEHOGE );」の行以降（「commit;」を含む）は実行されないため、「begin transaction;」で開始したトランザクションは継続している（commit も ROLLBACK も行われていない状態である）。

  4. ロールバック (ROLLBACK) したいので、ツールバーの「ロールバック」ボタンを押下する。
  5. もしロールバックせずに、もう一度 SQL 文の実行を行ったとする。すると、「Cannot start a transaction within a transaction」（トランザクションの中でトランザクションを開始できない）というエラーメッセージが出る。

    これは、一度トランザクションを開始したのに、その終了をせずに再びトランザクションを開始しようとしているためである。故障ではない。一度 commit あるいは ROLLBACK でトランザクションを終了させること。

SQL を用いた更新

SQL を用いたデータの更新 (update)の実行例を示す。

「UPDATE <table-name> SET <attribute-name>=<expression> WHERE <expression>」の形をした SQL は、データの更新である。この SQL 文を「begin transaction」と「commit」で囲む。

  1. SQL プログラムの記述

    「UPDATE ... SET ...」は更新である。ここでは 1つの SQL 文を「begin transaction」と「commit」で囲む。 

    begin transaction;
UPDATE E SET score=90, updated_at=datetime('now', 'localtime')
WHERE name = 'Database' AND student_name = 'KK';
commit;
  2. 複数の SQL 文の一括実行

    「SQLエディタ」に記述した複数の SQL 文をまとめて実行するには、「▶ SQL実行 (F5)」を押下する。

  3. 「出力」欄の確認

    エラーメッセージが表示されていないことを確認する。

  4. E テーブルのブラウズを行う。
  5. SQL プログラムの記述

    続けて、次の更新を行う。 

    begin transaction;
UPDATE E SET score=80, updated_at=datetime('now', 'localtime')
WHERE name = 'Programming' AND student_name = 'LL';
commit;
  6. 複数の SQL 文の一括実行

    「▶ SQL実行 (F5)」を押下する。

  7. 「出力」欄の確認

    エラーメッセージが表示されていないことを確認する。

  8. E テーブルのブラウズを行う。

SQL を用いた行の削除

SQL を用いた行の削除の実行例を示す。

「DELETE FROM <table-name> WHERE <expression>;」の形をした SQL は行の削除である。この SQL 文を「begin transaction」と「commit」で囲む。

  1. SQL プログラムの記述 
    begin transaction;
DELETE FROM E
WHERE name = 'Database' AND student_name = 'AA';
commit;
  2. 複数の SQL 文の一括実行

    「▶ SQL実行 (F5)」を押下する。

  3. 「出力」欄の確認

    エラーメッセージが表示されていないことを確認する。

  4. E テーブルのブラウズを行う。

ジャーナルファイルの確認

  1. SQL プログラムの記述

    今度は commit を書かない。 

    begin transaction;
insert into E values( 'Database',    90, 'BB', datetime('now', 'localtime'), NULL );
insert into E values( 'Programming', 95, 'BB', datetime('now', 'localtime'), NULL );
  2. 複数の SQL 文の一括実行

    「▶ SQL実行 (F5)」を押下する。

  3. 「出力」欄の確認

    エラーメッセージが表示されていないことを確認する。

  4. ジャーナル・ファイルの確認

    Linux での実行例

    端末を開く。デスクトップに「端末」アイコンがある場合はそれをクリックし、ない場合はメニューで「アクセサリ」→「端末」と操作する。

    端末で次のように操作する。「cd SQLite」は SQLite 3 のデータベースファイルが置いてあるディレクトリに移動する操作なので、別のディレクトリにデータベースファイルを作った場合は適切に読み替えること。 

    cd
cd SQLite
ls

    「mydb-journal」というファイルができている。これがジャーナルファイルである。

    Windows での実行例

    C:\SQLite を開く。「mydb-journal」がジャーナル・ファイルである。

  5. 「SQLエディタ」での commit の発行 
    commit;
  6. 「出力」欄の確認

    エラーメッセージが表示されていないことを確認する。

  7. ジャーナル・ファイルの確認

    commit を発行し、データベースの修正が終わったため、ジャーナルファイルが消えている。

ROLLBACK の確認

  1. SQL プログラムの記述

    今度も commit を書かない。 

    begin transaction;
insert into E values( 'Database',    95, 'CC', datetime('now', 'localtime'), NULL );
insert into E values( 'Programming', 80, 'CC', datetime('now', 'localtime'), NULL );
  2. 複数の SQL 文の一括実行

    「▶ SQL実行 (F5)」を押下する。

  3. 「出力」欄の確認

    エラーメッセージが表示されていないことを確認する。

  4. ジャーナル・ファイルの確認

    「mydb-journal」がジャーナル・ファイルである。

  5. E テーブルのブラウズを行う。

    2件の insert の結果がデータベースに反映されているように見える。

  6. 「SQLエディタ」での ROLLBACK の発行

    今度は ROLLBACK を発行する。 

    ROLLBACK;
  7. 「出力」欄の確認

    エラーメッセージが表示されていないことを確認する。

  8. データベース・ファイルとジャーナル・ファイルの確認

    データベースファイルは一切修正されていない。

  9. E テーブルのブラウズを行う。

演習問題と解答例

次の問いに答えよ。その後、下記の解答例を確認せよ。

問い

  1. 次の PTABLE テーブルに関する問題 
    name    |  type   |  color
------------------------------
apple   |  fruit  |  red
apple   |  fruit  |  blue
rose    |  flower |  white
rose    |  flower |  red
rose    |  flower |  yellow
    1. PTABLE を次のように更新する SQL を書きなさい。 
      name    |  type   |  color
------------------------------
apple   |  fruit  |  red
apple   |  fruit  |  blue
rose    |  flower |  white
rose    |  flower |  red
rose    |  flower |  blue
    2. さらに、PTABLE から行を削除し、次のテーブルに変更する SQL を書きなさい。 
      name    |  type   |  color
------------------------------
apple   |  fruit  |  red
rose    |  flower |  white
rose    |  flower |  red
rose    |  flower |  blue
  2. commit, ROLLBACK に関する次の問いに解答せよ。
    1. SQLite を使い、下記の SQL を評価させる。「select * from GG;」の評価結果は何か。 
          create table GG (
        id integer primary key,
        name text );
    begin transaction;
    insert into GG values ( 1, 'X' );
    commit;
    select * from GG;
    2. その後、SQLite を使い、下記の SQL を評価させる。「select * from GG;」の評価結果は何か。 
          begin transaction;
    insert into GG values ( 2, 'Y' );
    ROLLBACK;
    select * from GG;

解答例

  1. 問1 
    1. UPDATE PTABLE SET color='blue' WHERE name='rose' AND type='flower' AND color='yellow';

    2. DELETE FROM PTABLE WHERE name='apple' AND type='fruit' AND color='blue';
  2. 問2

    2問とも、評価結果は次のとおりである。 

    id|name
1|X