x. トランザクションとロールバック演習2022

リソース共有の問題

リンク先ページで銀行口座の残高を同時に操作するときに発生する問題（残高の矛盾）を確認しておこう。

DBMSではこのような問題発生を防ぐために「排他制御」ができるようになっている。

トランザクション処理

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

SQLによるトランザクションは以下の様な複数のコマンドによる一連の処理になります。

例）仕入れ台帳に　”2003/12　商品ABC　10個”　と記録して、在庫管理表の商品ABCの在庫を10個増加させる。

※演習用のコードではない。

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 の各コマンドの処理は、

一つの命令の処理が完了　→　データベースが更新される（値確定。コミット）　→　次の命令の処理に移る

というように1つずつ順番に動作します。

データベースの処理を確定させることをコミット（Commit）といいます。

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

例）

※演習用のコードではない

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

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

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

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

select * from TANA1

を実行→コミット。 テーブルの内容（棚の商品在庫数）が表示される。

上記の例の様にSQLコマンドを１つづ実行してレコードを更新すると問題が生じる場合があります。

例えば、insert で品物の仕入れ数を記録成功後にデータベースや通信回線に異常が生じて棚の商品の在庫の update が実行時エラーになったとします。

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

そこで、上記の様な処理では「商品の仕入れ数の記録」と「在庫数の更新」という複数のSQL文は、

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

として一括処理する必要があります。

トランザクション処理の利点：

• トランザクションの実行中にエラーが生じると、トランザクションの処理全体が失敗したことになりデータベースはトランザクション実行前の元の状態に戻ります。
  つまりトランザクションでエラーが生じるとトランザクション中の処理は一切行われずデータベースに変更は生じない。

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

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

関連用語と記事

ACID属性

２相コミットメント

デッドロック

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

２相コミット プロトコル （欠点あり。処理がブロックされる）

http://www.ogis-ri.co.jp/otc/hiroba/technical/DTP/step2/

https://atmarkit.itmedia.co.jp/ait/articles/1703/01/news203_2.html

３相コミット プロトコル （２相コミットの修正版。調停者の障害、参加者のタイムアウトに対応。実装コストが高い）
https://qiita.com/behiron/items/54a10b6915479f891243

ロールバック・ロールフォワード・コールドスタート・ウォームスタート

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

ACID特性 とインデックスについて

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

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

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

用語解説：

排他制御
リソース（データ：情報、デバイス：装置）を同時に複数の利用者で利用しないように制御すること。

復旧処理

• • コミット： データベースに送ったトランザクションの処理を完了させる。

  • ロールバック： トランザクション実行時に障害が発生してトランザクションが完了しなかった場合などに、以前にコミットした時点か、以前にトランザクションをセーブした時点までデータベースの状態を戻すこと

  • ロールフォワード： 処理をコミットした時点からもう一度トランザクションを実行しなおしてデータベースを復旧する。

  • トランザクションログ：データベース復旧用のトランザクションの記録。ロールバックに必要。

  • トランザクション処理の原子性（Atomicity）： データベースに対する更新などの処理結果が”完了”か”失敗”かのどちらかになることが保証されること。

例） テーブルのレコードを変更する際に、

update ITEM set cost = 10 where cost<10

を実行した場合に

”全ての１０未満のcostのデータが変更される”

もしくは

”一つもデータが変更されない”（エラーが起きた場合など）

のどちらかになることを保証する。 更新処理の途中でエラーが起きた場合でも、一部の１０未満のcostのレコードだけが変更されるという事態が発生しないようにする。

演習

この演習は「x. ビュー演習2022」を完了してから取り組む必要がある。

以下にトランザクションとロールバックの確認用に銀行口座の送金（口座振替）処理を想定したSQLのトランザクションの演習を用意した。

トランザクション処理により口座のレコード更新時のデータ不整合の発生を防止する。

準備：

• • 1. スタート メニュー → xampp → xampp control

    2. Apache Webサーバと MySQL データベースサーバ を start ボタンで起動する。

    3. SQL タブからコマンド入力フォームを表示して以下を入力し実行ボタンを押す。

       1. use db3120888

手順：

• 1. CLIENT表を作成し、IDフィールドを主キーに設定する。

     1. 以下のSQLコマンドをMySQLで実行してテーブルを作成する。

     2. create table CLIENT (ID integer primary key, NAME char(12) not null, TEL char(11));

• 1. 名前の部分は12文字以内、電話番号の部分は11文字以内で各自で適当にデータを考えてCLIENTにレコードを追加する. IDの値は1のままでよい。

  2. insert into CLIENT values ( 1, 'Test1', '0901231111');

  3. select * from CLIENT;

  4. 顧客を3人一組で登録する。以下のSQLではトランザクション処理の確認のために主キーを既存のレコードと重複させてエラーを発生させている。
     以下の3行をまとめて（SQLタブに3行コピーして）実行する（実行は1回）
     
     

insert into CLIENT values ( 2, 'Test2', '0901232222');
insert into CLIENT values ( 1, 'Test3', '0901231111');
insert into CLIENT values ( 3, 'Test4', '0901233333');

• 5. ２つ目のデータの追加が原因でエラーが起きる。CLIENT表がどうなったかを確認する。

  6. select * from CLIENT;

SQLが一行単位で処理されているので、最初に追加したID=2のレコードは追加され、ID=3のレコードは追加されていない。

つまりエラーで処理が中断して、３つのinsert文のうち一部分だけが実行された状態になっておりトランザクション処理にはなっていない。

• 7. 次にトランザクション処理の途中でエラーを起こして、トランザクション処理全体が失敗してデータベースが更新されないことを確認する。
     この様に更新前の状態に戻るので中途半端にデータベースが更新されて不整合が発生することを回避できる。
     以下の3行をまとめて（SQLタブに3行コピーして）実行する（実行は1回）
     
     

start transaction;
insert into CLIENT values ( 4, 'Test4', '0901234444');
insert into CLIENT values ( 1, 'Test1', '0901231111');

• 8. ２つ目のinsert文で、トランザクション全体が失敗して、ID番号4 は登録されていないことを確認

  9. select * from CLIENT;

  10. 以下のトランザクション処理でロールバックした場合にデータベースが復旧することを確認
      
      

start transaction;
insert into CLIENT values ( 5, 'Test5', '0901235555');
rollback;
insert into CLIENT values ( 6, 'Test6', '0901236666');

• 11. ロールバックコマンドで トランザクション開始前の状態に復帰 するので、５番の顧客は登録キャンセルされる

  12. rollbackによりID番号5は登録されていないことを確認

  13. select * from CLIENT;

補足： MySQLでは、 start transaction から rollback までが１つのトランザクションとして扱われる。
rollback に続いた insert コマンドは、別の新たなトランザクションとして扱われるので、6番の学生は追加されてしまう。 

• 14. client テーブルの構造を修正する balance フィールドを追加（口座残高のデフォルト値は100万円なので全員100万円の残高となる）

  15. ALTER TABLE `client` ADD `balance` DECIMAL NOT NULL DEFAULT '1000000' AFTER `TEL`;

  16. client の ID 1 から ID 2 の口座へ 40万円送金するトランザクションを実行する。
      
      

start transaction;

set @amount = 400000;

update client set balance=balance - @amount where id = 1;

update client set balance=balance + @amount where id = 2;

commit

• 17. 口座残高がマイナスとなる送金を阻止送金用コード（ストアドプロシージャ：サーバ側に登録したサーバ側で処理するコード）を作成する。
      
      

DELIMITER //

CREATE PROCEDURE sendmoney( IN s INT, IN r INT, IN m INT)

BEGIN

start transaction;

IF (select balance from client where id = s) >= m THEN

  update client set balance=balance - m where id = s;

  update client set balance=balance + m where id = r;

END IF;

commit;

END

//

DELIMITER ;

※送金先の id が存在しない場合、上記のコードでは送金が入金されずに闇に消える。送信先idの存在を確認するコードに修正するとよい。

• 18. 送金を実行する。以下のSQLは上で定義したストアドプロシージャで、1 から 2 へ 400000 送金する例

  19. CALL sendmoney(1,2,400000)

  20. 残高以上に送金した場合は、ストアドプロシージャの送金元の残高チェックコードにより送金処理が実行されないことを確認する。

  21. CALL sendmoney(1,2,400000)

（別パターン）説明用のコード。実行しなくてもよい。

ROLLBACK コマンドの利用例。送金処理後に送金元の残高がマイナスになっていたら処理をキャンセルするコード。

DELIMITER //

CREATE PROCEDURE sendmoney2( IN s INT, IN r INT, IN m INT)

BEGIN

start transaction;

update client set balance=balance - m where id = s;

update client set balance=balance + m where id = r;

IF (select balance from client where id = s) < 0 THEN

  ROLLBACK;

END IF;

commit;

END

//

DELIMITER ;

課題提出

phpMyAdminの画面のスクリーンショットを撮る。

• データベース名（db3120888など）とプロシージャとCLIENTテーブルの内容が表示されていることが条件。

提出先はWebclass参照。

参考：

select for update、テーブルロック、行ロック、リピータブルリード、ノンリピータブルリード、ダーティリード、ダーティライト、ファントムリード、レコードロック、ギャップロック、ネクストキーロックの話題

なぜUsersテーブルの行でロックを取るのか？

MySQLで発生し得る思わぬデッドロックと対応方法

「トランザクション張っておけば大丈夫」と思ってませんか？ バグの温床になる、よくある実装パターン