13. データベース操作，トランザクション，リカバリ，ロック，同時実行制御

13. データベース操作，トランザク ション，ロールバック，ロック，同 時実行制御 URL: https://www.kkaneko.jp/de/ds/index.html 金子邦彦 謝辞：この資料では「いらすとや」のイラストを使用しています 1

• https://www.kkaneko.jp/de/ds/index.html

全体まとめ ① SQL を用いたデータベース操作（挿入，削除，更新） 新しい行の挿入 条件に合致する行の削除 データの更新 INSERT INTO DELETE FROM WHERE UPDATE SET WHERE ②トランザクション データベースの処理単位 ③ ロールバック データベースをトランザクション開始時点に戻す ④ ロック，同時実行制御 複数が，同時にデータベース操作を行うことができる仕 掛け 2

アウトライン 番号 項目 13-1 13-2 13-3 13-4 13-5 13-6 データベース操作（挿入，削除，更新） データベース操作の演習 トランザクション ロールバック ロック，同時実行制御 データベースのバックアップ 各自、資料を読み返したり、課題に取り組んだりも行う 3

リレーショナルデータベースシステム データベースシステムの一種 コンピュータ 記憶 装置 リレーショナル リレーショナル データベース データベース 管理システム たくさんのテーブルが格納される あわせて リレーショナルデータベースシステム 4

SQL • SQL は、リレーショナルデータベースシステムの さまざまな機能を使える言語 問い合わせ（クエリ）、 テーブル定義、 その他の操作 5

SQL による問い合わせ（クエリ）の例 ① SELECT * FROM 商品; ② SELECT 名前, 単価 FROM 商品; ③ SELECT 名前, 単価 FROM 商品 WHERE 単価 > 80; ④ SELECT 受講者, COUNT(*) FROM 成績 GROUP BY 受講者; ⑤ SELECT * FROM 米国成人調査データ ORDER BY 年齢; ⑥ SELECT * FROM T, S; ⑦ SELECT * FROM T, S WHERE a = b; ⑧ SELECT * FROM 授業 WHERE 教室名 IN ('一階', '二階'); ⑨ SELECT DISTINCT 学生番号 FROM 成績 WHERE 科目名 IN (SELECT 科目名 FROM 成績 WHERE 学生番号 = 101); 6

13-1. データベース操作（挿入，削除， 更新） 7

挿入 テーブル T 名前 昼食 料金 空 テーブル T 名前 A B C D 昼食 そば カレーライス カレーライス うどん 料金 250 400 400 250 insert into T values('A', 'そば', 250); insert into T values('B', 'カレーライス', 400); insert into T values('C', 'カレーライス', 400); insert into T values('D', 'うどん', 250); 8

更新 テーブル T 名前 A B C D 昼食 そば カレーライス カレーライス うどん テーブル T 料金 250 400 400 250 名前 A B C D 昼食 そば カレーライス カレーライス うどん 料金 250 400 400 300 update T set 料金 = 300 where 昼食 = 'うどん'; 9

削除 テーブル T 名前 A B C D 昼食 そば カレーライス カレーライス うどん テーブル T 料金 250 400 400 300 名前 B C D 昼食 カレーライス カレーライス うどん 料金 400 400 300 delete from T where 名前 = 'A'; 10

SQL によるデータベース操作 操作の種類 SQL の例 新しい行の挿入 insert into T values('A', 'そば', 250); 条件に合致する行 delete from T where 名前 = 'A'; の削除 データの更新 update T set 料金 = 300 where 昼食 = 'うどん'; 11

13-2. データベース操作の演習 12

Paiza.IO の使い方 ① ウェブブラウザを起動する ② 次の URL を開く https://paiza.io/ ③ もし，表示が英語になっていたら，日本語に切り 替える

• https://paiza.io/

④ 「コード作成を試してみる」をクリック ⑤ 「MySQL」を選ぶ（左上のボタンをクリックするとメニューが出る）

プログラムの 編集画面 プログラムを 書き換えること ができる 実行ボタン 15

編集画面を確認する。 すでに、SQL が入っているが、使わないので消す。

Paiza.IO を用いた演習 ① URL は https://paiza.io/ja ② 「コード作成を試してみる」をクリック ③ 「MySQL」を選ぶ 17

• https://paiza.io/ja

挿入 テーブル T 名前 昼食 料金 空 テーブル T 名前 A B C D 昼食 そば カレーライス カレーライス うどん 料金 250 400 400 250 insert into T values('A', 'そば', 250); insert into T values('B', 'カレーライス', 400); insert into T values('C', 'カレーライス', 400); insert into T values('D', 'うどん', 250); 18

• 次の SQL を入れ，実行結果を確認 使用している Web ブラウザの設定等によっては，結果が表示されない場合 がある．そのときは，Webブラウザを別のものに変えて実行 19

更新 テーブル T 名前 A B C D 昼食 そば カレーライス カレーライス うどん テーブル T 料金 250 400 400 250 名前 A B C D 昼食 そば カレーライス カレーライス うどん 料金 250 400 400 300 update T set 料金 = 300 where 昼食 = 'うどん'; 20

• 次の SQL を書き加える．実行結果を確認 書き加える 結果が増える 21

削除 テーブル T 名前 A B C D 昼食 そば カレーライス カレーライス うどん テーブル T 料金 250 400 400 300 名前 B C D 昼食 カレーライス カレーライス うどん 料金 400 400 300 delete from T where 名前 = 'A'; 22

• 次の SQL を書き加える．実行結果を確認 書き加える 結果が増える 23

13-3. トランザクション 24

トランザクション • データベースを扱うプログラムは，普通のプログ ラムと違う ＝今から説明する特性 A, C, I, D • 特性 A, C, I, D を持つプログラムのことをトランザ クションと呼んだりする • 特性 A, C, I, D は，データベースシステムの側で保 証するのがふつうである 25

Ａ： 作業途中の結果をそのまま残さない ユーザ データベース管理システム 作業中に停電するかも 普通のプログラム：作業途中のデータが，ファイルで 残っている場合がある トランザクション：作業途中のデータは自動で消える 26

C: データベースの異状を防ぐ仕組み メニューから お選びください 枝豆はないんですか？ 普通のプログラム：参照整合性に反するデータが 入ってしまうかも トランザクション：システムに，参照整合性に反するデータ が入らないような仕掛けがある 27

Ｉ： データベースを複数人が同時使用できる コンピュータ 利用者Ａ ネットワーク 利用者Ｂ データベースシステム 利用者Ｃ 普通のプログラム：データがおかしくなる可能性もある トランザクション：システムに，同時使用できる仕掛けがある 28

Ｄ： 作業が完了したら，そのデータは残る ユーザ データベース管理システム 普通のプログラム：ファイルに保存しないと，データは残らない トランザクション：基本，すべて自動保存される 29

トランザクション • データベースを扱うプログラムは，普通のプログラムと違う Ａ： 作業途中の結果をそのまま残さない Ｃ： データベースの異状を防ぐ仕組み Ｉ： データベースを複数人が同時使用できる． Ｄ： 作業が完了したら，そのデータは残る • 特性 A, C, I, D を持つプログラムのことをトランザクション と呼んだりする ロールバック： 戻す データベースをトランザクション開始時点に コミット： トランザクションの正常終了 30

トランザクションの開始，ロールバック，コミット • MySQL でのトランザクションの開始 start transaction ※ 他のシステムでは「begin transaction」となることも ある • ロールバック（データベースをトランザクション開始時点 に戻す） rollback • コミット（トランザクションの正常終了） commit 31

ロールバック (rollback) のイメージ 操作１、操作２、操作３ と操作していて、 最初に戻したくなったら・・・ start transaction 操作１ 操作２ 操作３ rollback rollback コマンド データベース管理 システム 32

13-4. ロールバック 33

ロールバックとは • ロールバックは，データベースを，トラン ザクション開始時点に戻すこと • リレーショナルデータベース管理システム の標準機能 • ロールバックしたトランザクションだけが 元に戻る（他の利用者に影響を与えること はない） 34

作業開始 「start transaction」は、 データベースの変更の作業開始を、 データベースシステムに伝えるコマンド （ロールバックの機能を使わないときは省略可） start transaction ユーザ データベース管理システム 35

作業中 start transaction 操作１ 操作２ ユーザ データベース管理システム 36

ロールバック (rollback) 操作１、操作２、操作３ と操作していて、 最初に戻したくなった トランザクション開始 操作１ 操作２ 操作３ ユーザ rollback データベース管理システム 37

ロールバック (rollback) • ロールバック (rollback) は、トランザクショ ンをなかったことにする 38

Paiza.IO を用いた演習 ① URL は https://paiza.io/ja ② 「コード作成を試してみる」をクリック ③ 「MySQL」を選ぶ 39

• https://paiza.io/ja

〇 演習として次を行う トランザクション開始 確認① ロールバック 確認② 確認①（ロールバック前） 確認②（ロールバック後） 40

• システムが，停電などの不測の事態でダウンし、 再起動したときは，ロールバックが自動で実行さ れる。 41

システムのダウン • コンピュータそのものの停止 停電 オペレーティングシステムの異状 機器（コンピュータ本体など）の故障 データベース管理システム自体の不具合 システムのダウンの完全防止は不可能 42

操作途中でのシステムダウン データベース管理システムの責任で， すべての操作を取り消す トランザクション開始 ← × 操作２ × 操作３ × 作業のやりかけ 作業のスタート 操作１ もしここで， システムがダウン → システムの再起動時に 43 自動でロールバック

13-5. ロック，同時実行制御 44

ロックと同時実行制御 • データベース内のデータを使うとき，自動で ロックがかかる • データにロックがかかっていると，他の人の使 用に制限がかかる（同時実行制御） • ロックは，データの数だけある 45

同時実行制御のイメージ 赤信号で止まる 信号が青になったら， 車によく気を付けて渡る 46

同時実行制御のイメージ 道路は， ◆あるときは，歩行者が渡り ◆あるときは，車両が走る 同時に、歩行者と車両がいるのに・・・ 歩行者と車両が衝突することはない 赤信号で止まる 同時実行制御のイメージ 47

同時実行制御とロック 部屋の数だけロックがある ◆ロックがかかっていなければ使 える． ◆使用中はロックがかかる．他の 人は使えない（＝排他） 48

ロック データベースを 使うとき 49

ロック データベースシステムで は，自動でロックがかか る データベースを 使うとき ※ データベースを使い終わると， 自動でロックが外れる 50

ロック 他の人は使えない データベースを 使うとき 自動でロックがかか る 51

ロック ロックのかかってい ないデータは使える データベースを 使うとき データの数だけロッ クがある． （使用中のデータに だけロックがかか る） 52

ロックが無いと、何が起こるのか？ • Aさんは、口座Xから 1000円を引き出したい。 • Bさんは、口座Xに 2000円を預けたい。これがほぼ同時に起きたとする。 もともと 10000円だったとすると Aさん 11000円になるはず Bさん 53

ロックが無いと、何が起こるのか？ • Aさんは、口座Xから 1000円を引き出したい。 • Bさんは、口座Xに 2000円を預けたい。これがほぼ同時に起きたとする。 もともと 10000円だったとすると １００００ １０００ 引く ９０００ Aさん 11000円になるはず Bさん 54

ロックが無いと、何が起こるのか？ • Aさんは、口座Xから 1000円を引き出したい。 • Bさんは、口座Xに 2000円を預けたい。これがほぼ同時に起きたとする。 もともと 10000円だったとすると １００００ １０００ 引く １００００ 使用中のデータ 読み出し ２０００ 足す ９０００ Aさん １２０００ 11000円になるはず Bさん 55

ロックがあれば大丈夫 • Aさんは、口座Xから 1000円を引き出したい。 • Bさんは、口座Xに 2000円を預けたい。これがほぼ同時に起きたとする。 もともと 10000円だったとすると １００００ １０００ 引く ９０００ Aさん １１０００ ２０００ 足す Bさん 11000円になるはず 56

ロックがあれば大丈夫 • Aさんは、口座Xから 1000円を引き出したい。 Bさんは、ロックが • Bさんは、口座Xに 2000円を預けたい。これがほぼ同時に起きたとする。 外れるまで、強制的 に待たされる もともと 10000円だったとすると １００００ １０００ 引く ９０００ Aさん １１０００ ２０００ 足す Bさん 11000円になるはず 57

データベースシステムのロックは データベースシステムのロックは２種類ある 共有ロック ・・・ データ１つに対して，何本でも データの 共有用 排他ロック ・・・ データ１つに対して，１本だけ データの 独占用 共有ロックがかかっているときは，重ねて排他ロックできない． 排他ロックがかかっているときは，重ねて共有ロックできないし，排他ロックもできない 58

13-6. データベースのバックアップ 59

データベースのバックアップ • データベースの破損、消失に備える 60

コピー＆ペーストで バックアップ！？ データベースファイル • いえいえ、それではうまく行きません • データベースシステムは、他の人が使用中かもしれません データベースシステムを止めずにバックアップしたい！ という問題があります 61

データベースのバックアップ • 単なる「データベースファイルのコピー＆ペース ト」ではない． • データベース管理システムが備えるバックアップ コマンドで、バックアップを行う 62

マイクロソフト Access での データベースのバックアップ 1. 「ファイル」，「名前 を付けて保存」 2. 「データベース ファイ ルの種類」 で 「デー タベースに名前を付け て保存」 を選択 3. 「詳細設定」 で「デー タベースのバックアッ プ」 を選択 4. 「名前を付けて保存」 を選択 5. 「保存」 63

全体まとめ ① SQL を用いたデータベース操作（挿入，削除，更新） 新しい行の挿入 条件に合致する行の削除 データの更新 INSERT INTO DELETE FROM WHERE UPDATE SET WHERE ②トランザクション データベースの処理単位 ③ ロールバック データベースをトランザクション開始時点に戻す ④ ロック，同時実行制御 複数が，同時にデータベース操作を行うことができる仕 掛け 64

トランザクション • データベースを扱うプログラムは，普通のプログラムと違う Ａ： 作業途中の結果をそのまま残さない Ｃ： データベースの異状を防ぐ仕組み Ｉ： データベースを複数人が同時使用できる． Ｄ： 作業が完了したら，そのデータは残る • 特性 A, C, I, D を持つプログラムのことをトランザクション と呼んだりする ロールバック： 戻す データベースをトランザクション開始時点に コミット： トランザクションの正常終了 65

トランザクションの開始，ロールバック，コミット • MySQL でのトランザクションの開始 start transaction ※ 他のシステムでは「begin transaction」となることも ある • ロールバック（データベースをトランザクション開始時点 に戻す） rollback • コミット（トランザクションの正常終了） commit 66

13-2 create table T(名前 text, 昼食 text, 料金 integer); insert into T values('A', 'そば', 250); insert into T values('B', 'カレーライス', 400); insert into T values('C', 'カレーライス', 400); insert into T values('D', 'うどん', 250); update T set 料金 = 300 where 昼食 = 'うどん'; delete from T where 名前 = 'A'; select * from T; 67

13-4 create table T(名前 text, 昼食 text, 料金 integer) engine = InnoDB; insert into T values('A', 'そば', 250); insert into T values('B', 'カレーライス', 400); insert into T values('C', 'カレーライス', 400); insert into T values('D', 'うどん', 250); start transaction; update T set 料金 = 300 where 昼食 = 'うどん'; delete from T where 名前 = 'A'; select * from T; rollback; select * from T; 68