9. トランザクション

DBMSの障害対策：トランザクション処理 ＡＣＩＤ特性 障害対策 ロールバックとロールフォワード について演習を行う。

キーワード： トランザクション処理 ＡＣＩＤ特性 ロールバックとロールフォワード

（参考資料）

>> ＩＴパスポート試験学習教材

>>テクノロジ系対策コース

>>第9章 データベース

9.5 トランザクション処理（１）

銀行口座をハッキング可能か？ 思考実験する。

9.6 トランザクション処理（２）

ゲームのセーブポイントと、ロールバックロールフォワードを比較してみよう。

>>新基本情報技術者試験学習教材

>>テクノロジ系対策コース２

>>第3章データベース

3.18 トランザクション処理（１）

分散データベースのコミットメント処理

２相コミット プロトコル （欠点あり。処理がブロックされる。）http://www.ogis-ri.co.jp/otc/hiroba/technical/DTP/step2/

３相コミット プロトコル （２相コミットの修正版。調停者の障害、参加者のタイムアウトに対応。http://ossforum.jp/book/export/html/837）

3.19 トランザクション処理（２）

基本情報処理の過去問から 基本情報技術者 平成20年春期 午前問60

3.20 トランザクション処理（３）

ACID特性 と インデックスについて （関連項目：データ検索アルゴリズム）

（関連記事）

http://www.infiniteloop.co.jp/blog/2014/06/newcommerslide/

新入社員のための大規模ゲーム開発入門 サーバサイド編 今回の講義との関連： スライド５５~７４ 排他制御 と インデックス設定の部分

食事する哲学者の問題 Wikipedia

■演習１（ビューとインデックス）

XAMPPでデータベースの実習を行う。

今まで自分が使用していたPC（出席登録参照）でWebサーバとSQLサーバを起動し、管理画面から演習を行う。

ビュー

VIEW = 仮想表

１つあるいは幾つかのテーブルを組み合わせてＳＥＬＥＣＴ文で作成した一時的な表のことをビューと呼ぶ。

ビューに名前を与え、SQLでビューをテーブルとして利用することが出来る。

データベースで実際にレコードを記録するテーブルを実表と呼ぶ。

ビューは実表からselect文で作成された仮想表である。

上記は、

2つの実表を、 商品番号フィールドで結合して、１つの表にした

例である。

ビューの作成方法：

・データベース Accessでは、新規クエリの作成で、クエリビルダを利用し、 選択クエリー を作成する。 出来た 選択クエリー を実行して表示される 表 が ビュー に相当する。

Ａｃｃｅｓｓでは、選択クエリーに付けた名前がビューの名前になり、他のクエリーからその名前で参照できるようになる。

Q．Accessによる 履修登録データベース のクエリ―デザイン画面の例

どれが、テーブル（実表） で どれが ビュー（仮想表） か考えてみよう。

（クリックで拡大表示）

・MyＳＱＬでビューに名前を付けて保存しておくには、以下の構文を用いる。

ＣＲＥＡＴＥ ＶＩＥＷ ビューの名前 (表示項目,....) AS SELECT文

ＡＳ以下には今回まで解説してきた、SELECT 文を書くことが出来る。

（）の部分は、ＡＳ以下のSELECTの実行の結果、得られる表に（）内で指定したフィールド名を付け直して、新たなフィールド名を与える為の項目である。

ビューを他のSQLコマンドから利用する方法について：

select や delete や update で、ＦＲＯＭ の次に、表名の代わりに、ビュー名を指定できる。

例）

students 表の firstname と lastname を結合し id と name と age のフィールドを持つ myview としてビューを作成

create view myview (id, name , age ) as select id , firstname & " " & lastname , age from students

ビューの必要性

データベースの利用時に実際に表示したり印刷する表は、主にビューである。

では、なぜ、ビューをテーブル（実表）として最初からＤＢＭＳに用意しないのだろうか？

ビューは次のような場合に必要となる。

・テーブルの正規化により分割されたテーブルを元のテーブルに結合して戻す

・テーブルを利用者の用途に応じて不要な部分を除き、必要な部分だけにして表示する

もし、データベースマネジメントシステム（ＤＢＭＳ）中に、予め必要となるテーブルを実表として全て用意しておくと、データの重複を生じ無駄であるばかりでなくデータの追加・更新・削除においてもコストがかさむ。

そこで、ＤＢＭＳ中には必要最小限の実表だけを用意し、実表以外のテーブルが必要な場合はビューとしてその都度クエリーで実表から組み立てなおして表示する方が良い解決法となる。

このように、利用時とデータの格納時で別々にデータを扱うために考案されたのが、３層スキーマと呼ばれるＤＢＭＳの構造である。

３層スキーマ構造：

ＤＢＭＳを設計する際の基本的概念。ＤＢＭＳを運用する際には、各スキーマ間で独立性が保たれていることが望ましい。

MySQLの管理画面から、以下を行う。

XAMPPを利用して、データベースで演習を行う。

今日の演習は、主に SQL コマンド だけを利用して進めて行く。 SQLの実行画面を開いて、コマンドを入力、 実行ボタンで実行。

新しいコマンドを実行するには、入力済みのコマンドを削除し、再入力する。

■演習１の実行手順

    1. コンピュータ → D： ドライブ → xampp → xampp control
    2. Apache Webサーバと MySQL データベースサーバ を start ボタンで起動する。
    1. MySQL の Admin をクリックする。
      1. SQL タブから、コマンド入力フォームを表示して、以下を入力し実行ボタンを押す。
      2. create database db3113???
      3. 新しくデータベース db3113??? が作成されていることを phpMyAdminの画面で確認する。
      4. コマンドを以下のものに入れ替えて実行。
      5. ・phpMyAdminの画面からデータベース db3113??? をクリック
      6. ・SQLのアイコンをクリック
      7. コマンド入力画面の上部が、 「サーバ 'localhost' 上でクエリを実行する」 から 「データベース db3113??? 上でクエリを実行する」 になったことを確認する。
      1. (補足)上記の操作は、SQLコマンドでは use db学籍番号 となるが、phpMyAdminでは機能しない。
    1. 学籍簿を作成する。主キーの設定は後ほど行う。
    2. create table STUDENT ( ID integer , NAME char(12) )
    3. 番号１、名前１は自分のものを利用する。名前はアルファベットを利用する（漢字の使用には文字コードの指定が必要）
      1. insert into STUDENT values ( 番号１, '名前１' )
      2. ※テーブルの文字コードの指定（漢字の名前を入力する場合）：
      3. phpMyAdminの画面で、 テーブル STUDENT をクリックし 構造タブ をクリック。
      4. NAME フィールドの 鉛筆アイコン（編集） をクリック。
      5. 照合順序 をutf8_genera_ci に修正
      6. もう一名、適当にデータを追加しておく
      7. insert into STUDENT values ( 番号２, '名前２' )
      8. 科目表を作成する
      9. create table CLASS ( CLASS_ID integer primary key, NAME char(12) )
      10. insert into CLASS values ( 1, 'DataBase' )
    4. 履修表を作成する
    5. create table STUDIES ( ID integer, CLASS_ID integer)
    6. 自分の番号で、科目番号１の科目の履修を記録する
    7. insert into STUDIES values ( 番号, 1 )
      • ※補足※
      • 学籍番号などを重複して登録してしまった場合は、
      • delete from STUDENT where id = 削除対象の学籍番号
      • で、一括して重複学籍番号を削除するか、
      • delete from STUDENT where name = '削除対象の名前'
      • で、指定した名前のレコードを削除できます。
    8. ビューを作成する（名前と科目名の表）コンマやピリオドの打ち間違いに注意する。
      1. ※途中に入力ミスがあった場合は、create view の部分からやり直すこと。
      2. ※２０１７年： ↓のSQLを phpMyadminに入力するとエラーが表示されるが、構わず実行する。
              1. create view STUDY_LIST (name , class) as
              2. select student.name, class.name
              3. from student, studies, class
              4. where student.id=studies.id and studies.class_id=class.class_id
      1. ↓ 実表とビューの関係図(各テーブルのレコードは上の演習内容よりも多めに記録してある)
              1. 実表
              2. ビュー
              1. STUDENT
              1. CLASS
              1. STUDIES
                      1. ID
                      2. 1122333
                      3. 1122444
                      4. 1122555
                      1. NAME
                      2. kobashi
                      3. hasegawa
                      4. yokota
                      1. CLASS_ID
                      2. 1
                      3. 2
                      1. NAME
                      2. DataBase
                      3. Program
                      1. ID
                      2. 1122333
                      3. 1122333
                      4. 1122444
                      5. 1122555
                      1. CLASS_ID
                      2. 1
                      3. 2
                      4. 2
                      5. 1
              1. STUDY_LIST
                      1. NAME
                      2. kobashi
                      3. kobashi
                      4. hasegawa
                      5. yokota
                      1. CLASS
                      2. DataBase
                      3. Program
                      4. Program
                      5. DataBase
      1. 作成したビューを表示する。
      2. select * from STUDY_LIST
      3. (STUDY_LISTを間違えて登録した場合は、 drop view STUDY_LIST でビューを破棄して作成しなおす)
      4. あと１件、科目を登録する（データは適当でよい。コマンドの項目も自分で考える）
      5. ヒント： insert into の構文確認
      6. insert into ????? ?????? (? , '???????? ')
      7. 学生の履修科目を増やす。（STUDIESに登録する。コマンドの項目も自分で考える）
      8. insert into ??????? ?????? (? , ??????? )
      9. 先程登録したビューSTUDY_LISTを表示して、履修状況を確認する。
      10. ヒント： ビューの内容を表示するコマンドは少し上のほうで実行したものと同じ
      11. select ? ???? ??????????
      12. ビューをupdateで修正する。
      13. update study_list set class = 'database2' where class = 'database'
      14. 修正結果がclassテーブルにも反映されることを確認する。
      15. study_list ビューの表示と classテーブルの内容確認。
      16. select * from study_list
      17. と
      18. select * from class
      19. でテーブルのレコードを表示する。

上記演習の実行結果：

テーブルが３つ、ビューが１つ作成されている。

■演習２（インデックス）

インデックスとは、テーブルのフィールドに管理用の見出し（目次）を作成し、データの検索効率など改善する仕組みのこと。

このインデックスはDBMSがデータ管理の為に利用するもので、SQLでインデックス番号を指定して並べ替えや検索などは行えない。

データベースには、大量のデータを効率よく処理するために、データの検索を高速で行う仕組みが必要である。

データ検索の高度なアルゴリズムを利用できるようにあらかじめDBMSにはインデックスと呼ばれる機能が用意されている。

データベースの設計者は、このアルゴリズムそのものを理解していなくても、処理の効率化を図るためにインデックスを利用することができる。

テーブルのフィールドに対してインデックスを設定すると、検索などの効率を上げることができる。

データ検索アルゴリズムについて

解説ページを用意したので時間があるときに参照しておくこと。

SQLサーバに接続し、インデックスを設定した場合の効果を確認する。

インデックス利用上の方針：

・ 単なるデータ記録用のテーブルには、データベースはインデックスを必要としない。

データの検索や並べ替えに利用しないフィールドについて、インデックスを作成しても無駄（インデックスの作成と構造の再編の負荷が増すだけ）である。

・大規模なテーブルの、頻繁に参照されるフィールドに対してインデックスを設定すると処理の効率が改善される。

■演習２の実行手順

  • インデックスの追加１
    • ※２０１７年： ↓のSQLを phpMyadminに入力するとエラーが表示されるが、構わず実行する。
      1. create index STUDENT_INDEX on STUDENT ( ID )

STUDENT表のIDフィールドに index を設定することで、IDを利用したデータベースの処理効率（検索、データ追加、削除、修正）が改善される。

※効果の確認： 数万件のデータ登録後にテーブルを操作するとパフォーマンス向上を体感できるかもしれない。

■演習３（トランザクションとロールバック）

トランザクションとは、データベースでデータを処理する為の一連の処理手続きのことです。

例えば、これまでMySQLで実行したSQL文、

insert into SHIILE values ( '2003/12', 'ABC', 10)

update ZAIKO set KAZU=KAZU+10 where NAME = 'ABC'

select * from ZAIKO

などもトランザクションです。

これまでの演習では、このSQLコマンドは、

１つのSQL文 ＝ １つの処理 ＝ １つのトランザクション

として処理されていました。

つまり、上記のSQLの例では、 insert と update と select の各処理は、

一つの命令の処理が完了 → データベースが更新される → 次の命令を処理

というように動作します。

この様に、データベースの更新を確定することを、コミット（Commit）といいます。

MySQLでは、デフォルトで自動コミットモードが有効で、SQLのコマンドを1つ処理するごとに、データベースの更新が確定します。

例）

insert into SHIILE values ( '2003/12', 'ABC', 10)

を実行→コミット。 テーブルにレコードが記録される。

update ZAIKO set KAZU=KAZU+10 where NAME = 'ABC'

を実行→コミット。 レコードが修正される。

select * from ZAIKO

を実行→コミット。 テーブルの内容が表示される。

しかし、これでは問題が生じる場合があります。例えば、insert で品物の入庫数を記録後にデータベースや通信回線に異常が生じて update が実行時エラーになったとします。

すると、入庫数は記録されているのに、在庫数の値が増えていないというデータ不整合が生じてしまいます。

そこで、上記の処理では複数のSQL文を、

複数のSQL文 ＝ 1連の処理 ＝ １つのトランザクション

としてデータベースで一括して処理する方法が必要になります。

要点：

■トランザクションの実行中にエラーが生じると、トランザクションの処理全体が失敗したことになり、データベースはトランザクションの実行前の状態の元に戻ります。

■トランザクションの実行中は、他のトランザクションが割り込んで処理されることはありません。

■トランザクションの実行中に、エラー処理をしてデータベースを処理前の状態に戻す（ロールバック）ことができます。

トランザクション処理とROLLBACK・COMMIT

マルチユーザ環境でのデータベースの問題点とその対処法について説明する。

  • 復旧処理
    • コミット： データベースに送ったトランザクションの処理を完了させる。
    • ロールバック： トランザクション実行時に障害が発生してトランザクションが完了しなかった場合などに、以前にコミットした時点か、以前にトランザクションをセーブした時点までデータベースの状態を戻すこと
    • ロールフォワード： 処理をコミットした時点からもう一度トランザクションを実行しなおしてデータベースを復旧する。
    • トランザクションログ：データベース復旧用の記録。ロールバックに必要。
    • トランザクション処理の原子性（Atomicity）： データベースに対する更新などの処理が、”完了”か”失敗”かのどちらかになることが保障されること。
    • 例） テーブルのレコードを変更する際に、
    • update ITEM set cost = 10 where cost<10
    • として処理する場合、
    • ”全ての１０未満のcostのデータが変更される”
    • もしくは
    • ”一つもデータが変更されないか”（エラーが起きた場合など）
    • のどちらかになることが保障される。 万が一、更新処理の途中でエラーが起きた場合でも、一部の１０未満のcostのレコードだけが変更されるという事態が発生しないようにする。

上記の他にも、データベースのトランザクション処理では次の用語の理解が求められる。

ACID属性 ２相コミットメント デッドロック

MySQLでトランザクション処理を行う為のコマンド

  • START TRANSACTION
    • トランザクション処理の開始宣言
  • ROLLBACK
    • トランザクション処理を破棄し、データベースを処理前（トランザクション開始前）の状態に戻す
  • COMMIT
    • トランザクション処理を完了し、データベースに結果を反映させる

例）

START TRANSACTION

SQL文

SQL文

SQL文

COMMIT

例）

START TRANSACTION

SQL文

SQL文

SQL文

エラーが発生したら、ROLLBACK

SQL文

SQL文

COMMIT

■演習３の実行手順

トランザクション処理の確認：

      1. db??????? データベースを選択（演習１で作成したデータベース）
      2. ???????は自分の学籍番号を指定。
    1. CLIENT表を作成し、IDフィールドを主キーに設定する。
    2. 以下のSQLコマンドをMySQLで実行し、テーブルを作成する。
    3. (注意：下線の部分、char()ではカッコとｒの間に空白を入れてはならない)
      1. create table CLIENT (ID integer primary key, NAME char(12) not null, TEL char(11));
      2. データベースの種別を InnoDB に変更する（ MyISAM はトランザクション処理に対応しない）
      3. 既に、InnoDBに設定されている場合は、次の修正は必要ない。
      4. 修正方法：
      5. client テーブルから、操作 → テーブルオプション → ストレージエンジン → InnoDB
      6. 上記が正常に処理されれば、以下のようなテーブルがＤＢＭＳに作成される。
      7. 各フィールドの設定内容をよく確認すること。
              1. ＩＤ
              2. 登録番号
              3. 主キー
              1. NAME
              2. 名前
              3. 12文字までの'文字'データ 入力必須
              1. TEL
              2. 電話番号
              3. 11文字までの'文字'データ
    1. 名前の部分は12文字以内、電話番号の部分は11文字以内で各自で適当にデータを考えて指定すること. IDの値は変更しないように。
    2. insert into CLIENT values ( 1, 'Test1', '0901231111');
    3. select * from CLIENT;
      1. データを２つ追加する。わざと、主キーを衝突させてエラーを起こしてみる。
      2. insert into CLIENT values ( 2, 'Test2', '0901232222');
      3. insert into CLIENT values ( 1, 'Test3', '0901231111');
      4. insert into CLIENT values ( 3, 'Test4', '0901233333');
      5. 上記の３つのinsert文を、SQLとして１つづつトランザクション処理する。
    4. ２つ目のデータの追加でエラーが起きたので、CLIENT表はどうなったかを確認
    5. select * from CLIENT;
      1. SQLが一行単位で処理されているので、最初に追加したID=2のレコードは追加され、ID=3のレコードは追加されていない。
      2. つまり、エラーで処理が中断して、３つのinsert文のうち、一部分だけが実行された状態になっており、問題のある処理となっている。
      3. 以下のトランザクション処理で、途中でエラーが起きた場合は、トランザクション処理全体が失敗して、データベースが更新されないことを確認する。
      4. エラー時に、更新前の状態にデータベースを戻すことで、中途半端にデータベースが更新されて不整合が発生することを回避できる。
    6. start transaction;
      1. insert into CLIENT values ( 4, 'Test4', '0901234444');
      2. insert into CLIENT values ( 1, 'Test1', '0901231111');
      3. ２つ目のinsert文で、トランザクション全体が失敗して、ID番号4 は登録されていないことを確認
      4. select * from CLIENT;
      5. 以下のトランザクション処理で、ロールバックした場合、データベースが復旧することを確認
    7. start transaction;
      1. insert into CLIENT values ( 5, 'Test5', '0901235555');
      2. rollback;
      3. insert into CLIENT values ( 6, 'Test6', '0901236666');
      4. ロールバックコマンドで トランザクション開始前の状態に復帰 するので、５番の顧客は登録キャンセルされている
    8. rollbackにより、ID番号5は登録されていないことを確認
    9. select * from CLIENT;

■演習４（セキュリティ）

データベースのユーザに、データベースの利用権限を設定するコマンドを紹介する。

SQLコマンド：

    • GRANT 権限 ON テーブル名 TO ユーザー
    • ユーザにデータベースの利用権限を認める
    • REVOKE 権限 ON テーブル名 FROM ユーザー
    • ユーザーからデータベースの利用権限を取り上げる

権限の部分で指定可能なキーワード：

ALL（全て）・SELECT・INSERT・UPDATE・DELETE・REFERENCESなど

ユーザー：

データベースを利用する際のユーザー名で権限を設定するユーザを指定する。

PUBLIC というキーワードでユーザを指定すると、全データベースユーザに対して権限を設定できる。

さらに、権限は以下の様な段階で細かく設定可能である。

・データベースサーバ全体

・データベース単位

・テーブル単位

・フィールド単位

■演習４の実行手順

データベースでの権限の設定について、以下の様に進め確認する。

GRANT コマンド や REVOKE コマンドによる権限設定の代わりに、次の手順でMySQLの管理画面からユーザの作成と権限の設定を行う。

    • ユーザを以下の手順で２件追加する。
    • 追加したユーザの片方の権限を制限し、権限設定が機能していることを確認する。
      1. XAMPPのホームアイコンをクリック をクリックし、データベースサーバの設定画面を表示
      2. ユーザ をクリック
  • ※[ 2015/6/10 注 講義中では、mysql データベース の user テーブルを直接編集して ユーザを２名追加しましたが、
  • この方法では、以下の作業で、問題が発生します。 追加した ユーザーを削除して、 以下の手順通り、phpMyAdminのメニューから作成してください。 ]
    1. ユーザを追加する のリンクから、
      1. ↑※２０１７年 User accounts
      2. 自分の学籍番号をユーザ名とし、適当なパスワードで 新規ユーザーを登録する
      3. ※ホストの指定は、ローカル を選ぶこと
      4. ※グローバル特権 として、「全てをチェックする」 を選び、「実行」 （全コマンドの使用権限を与える）
      2. データベース Mysql を開き、
      3. userテーブル に新規アカウントが追加されていることを確認する。
      4. もう1件、同様の手順（以下に示す）で実験用ユーザを登録する。
    1. XAMPPのホームアイコンをクリック をクリックし、データベースサーバの設定画面を表示
      1. ユーザ をクリック
    2. ユーザを追加する から、
      1. 他人の学籍番号（架空でも可）をユーザ名とし、適当なパスワードで 新規ユーザーを登録する
      2. ※ホストの指定は、ローカル を選ぶこと
      3. ※グローバル特権 は与えない
    3. 先ほど作成した実験用の他人（または架空）学籍番号のIDの特権の編集ボタンをクリック
      1. ※↑２０１７年 Edit privileges
    1. 他人の学籍番号のIDにデータベースの特権を追加する。
    2. 【データベース】 をクリックし、データベースに固有の特権 で db学籍番号 のデータベースを選択 して実行
      1. テーブル固有の特権 で、テーブルとして client を選択
      2. 【テーブル】 をクリックし、
      1. SELECTコマンドの部分で ID NAME TEL の中から、NAMEだけを選択して、実行ボタンを押す。
      1. ここまでて、他人の学籍番号のIDは、 db学籍番号のデータベースの clientテーブルの NAMEフィールドを SELECT で表示する権利だけが与えられたことになる。
      2. 新たに作成したユーザIDの権限を、以下の手順で確認する。
      3. ブラウザの MySQL Adminツールは、管理者権限でデータベース全体の調整を行うツールなので、ユーザの権限が機能しているか確認できない。
      4. そこで、Windowsのコマンドプロンプトから、手動で、MySQLサーバに接続し、コマンドを実行して確認を行う。
      5. スタート → 検索 → cmd と入力しEnter。
      6. 次のコマンドを入力し、 Enter 後、パスワードを入力して Enter を押す。
      7. D:\xampp\mysql\bin\mysql -u 学籍番号 -p
      8. 学籍番号の部分に、データベースのログインIDを記入する。
      9. ※ 個々で使用する学籍番号は、先ほど、ユーザ登録したＩＤの学籍番号。 データベース名、「db???????」のＩＤではない。
      10. ※ Enter Password: と表示されるので、パスワード入力。パスワードを無し、に設定している場合は、単にEnterを入力。
      1. use db???????; （??????? は学籍番号） 練習用データベースを開く。
      2. ※ユーザＩＤではなく、データベース名に付けた学籍番号を指定
    1. select * from CLIENT;
      1. 自分の学籍番号のユーザIDにはデータベースの全権限を設定したので、テーブルのレコードの全てのフィールドが表示される。
      2. データベースサーバから、ログアウト
      3. exit
      4. 権限に制限を加えたIDでMySQLにログインする。
      5. D:\xampp\mysql\bin\mysql -u 他人の学籍番号 -p
    2. use db???????;
      1. テーブルCLIENTのIDやTELのSELECTでの使用は、許可されていないので表示できない。（権限に関するエラーが起きる）
      2. select * from CLIENT;
      3. NAMEフィールドは、SELECTを許可されているので名前だけが表示される
      4. select NAME from CLIENT;
    1. exit

ここまでで、今回の演習は終了です。

■ 時間に余裕があるようなら・・・・

・第2のテスト用アカウントに、データベースへの書き込み権限と修正権限を与え、権限を確認する。

insert や update が利用できるか確認

・CLIENT以外のテーブルについても start transaction によるトランザクション処理や、 rollback の確認をする。

■ 提出方法

MySQLのサーバ内のデータベースをエクスポートしてファイルに書き出し、Webclassへ提出。

手順：

・ MySQLの管理画面の ホーム を開く

・エクスポート のタブを開く

・実行するをクリック

・ブラウザに、データベースのエクスポートファイルがダウンロードされる。

ファイル名 127_0_0_1.sql または localhost.sql

・ダウンロードしたファイルを、Webclassの 第９回課題 としてアップロードする。