SAMPLE

13トランザクションを使いこなす（備忘録）→ > 01p > 02p > 03p > 04p > 05p > END

１３トランザクションを使いこなす(p２８３)
（１）《ストレージエンジンとは (p２８３)》
（２）《ストレエンジンを設定する　ｐ２８５》
（３）《トランザクションとは　ｐ２８７》
（４）《トランザクションを使う　ｐ０００》
（５）《自動コミット機能　ｐ２９２》
＜まとめ＞
＜チェック＞
＜練習問題＞
END

13 トランザクションを使いこなす

単純にINSERT、SELECT、DELETEするだけでデータベースが運用できるわけではありません。実践では複雑に絡み合った複数のテーブルを使って大量のデータを扱い、処理の効率を考え、そして不慮のデータ消滅も許されません。MySQLの勉強も佳境を迎えています。条件を設定した抽出、編集から、リレーショナルの名にふさわしい複数のテーブルの利用。そして「ビュー」「ストアドプロシージャ」「トランザクション」「ファイル操作」という、より実践的で必要とされる内容を解説します。
☆CHAPTER08⇒いろんな条件で抽出する
☆CHAPTER09⇒テーブルを編集する
☆CHAPTER10⇒複数のテーブルを利用する
☆CHAPTER11⇒ビューを使いこなす
☆CHAPTER12⇒ストアドプロシーｼﾞｬを使いこなす
★CHAPTER13⇒トランザクションを使いこなす
☆CHAPTER14⇒ファイルを使ったやり取り

トランザクションを使いこなす(p２８３)

複数の処理を、１つのまとめた手続きとして扱う｢トランザクション｣は、データベースを実際に運用する上で、欠かすことのできない機能といえます。ここでは、MySQL特有のストレージエンジンの設定と合わせて、トランザクションについて解説します。

（１）《ストレージエンジンとは (p２８３)》

（１－１）＜ストレージエンジンとは何か＞

トランザクションの前に、ＭｙＱＳＬではストレージエンジンについて知っておく必要があります。ＭｙＳＱＬは、その機能が大きく２つに分かれます。１つは「接続する」「ＳＱＬ文の内容を事前に調査する」といった機能、いわばデータベースの「上」の部分です。そしてもう一つは、その「上」の部分からの指示によって「実際の検索やファイル操作などの作業を行なう」機能を持つ、「下」の部分です。この「下」の部分を、「ストレージエンジン」といいます。【図１３－１ストレージエンジン　詳細省略】

（１－２）＜ストレージエンジンの種類＞

ＭｙＳＱＬでは、ストレージエンジンが複数用意され、ユーザーは目的や自分の好みによって、ストレージエンジンを選ぶことができます。さらに、テーブルごとに、「Ａテーブルは○○○ストレージエンジン、Ｂテーブルは×××ストレージエンジン」といったようにストレージエンジンを指定することができるのです。このようにストレージエンジンが独立し、ユーザーが選ぶことができるには、数あるＲＤＢＭＳにおいても、ＭＹＳＱＬ固有の特徴です。現在ＭｙＳＱＬでは、主に次のようなストレージエンジンを利用することができます。【●ＭｙＳＱＬ利用できる主なストレージエンジン　画像】 ※ここでは、このうちのＭｙＳＱＬとＩｎｎｏＤＢを扱います。現時点では「トランザクションを使うならＩｎｎｏＤＢ（イノディービィ）、使わないならＭｙＩＳＡＭ（マイアイサム）」と考えて差し支えありません。

▲Ｔｏｐ

（２）《ストレエンジンを設定する　ｐ２８５》

後述する「トランザクション」の機能を利用するためには、ストレージエンジンとして「ＩｎｎｏＤＢ」を使います。多くの場合、ＩｎｎｏＤＢ使うには「my.cnf」の設定が必要です。まず、「my.cnf」の設定を変更し、ＩｎｎｏＤＢが利用できるようにして下さい。

（２－１）＜ストレージエンジンの確認＞

さて、今までみなさんが使っていたテーブルの、ストレージエンジンを確認してみましょう。
テーブルの詳細な情報を表示するときは「SHOW CREATE TABLE」のコマンドを使います。
SHOW CREATE TABLE tb;

●実行結果

mysql> SHOW CREATE TABLE tb;
+-------+----------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------------------------------------+
| Table | Create Table
|
+-------+----------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------------------------------------+
| tb | CREATE TABLE `tb` (
`bang` varchar(20) collate latin1_general_ci default NULL,
`uria` int(11) default NULL,
`tuki` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci |
+-------+----------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------------------------------------+
1 row in set (0.02 sec)

mysql>

※ストレージエンジンは、「ENGINE=××」の部分で確認できます。上の例では、テーブル｢tb｣に「MyISAM」が使われていることがわかります。テーブルを作るとき、特にストレージエンジンを指定しなければ、デフォルトである「MyISAM」が選択されます。

mysql> SHOW CREATE TABLE tb \G
*************************** 1. row ***************************
Table: tb
Create Table: CREATE TABLE `tb` (
`bang` varchar(20) collate latin1_general_ci default NULL,
`uria` int(11) default NULL,
`tuki` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
1 row in set (0.00 sec)

mysql>

（２－２）＜ストレージエンジンの変更＞

テーブルのストレージエンジンは、後から変更することもできます。ここではとトラザクションを体験するために、テーブル｢tb｣のストレージエンジンを「InnoDB」に変更してみましょう。ストレージエンジンを変更するときは、「ALTER TABLE」を使います。

●書式⇒ストレージエンジンをInnoDBに変更する

ALTER TABLE tb ENGINE = InnoDB;

□実行結果

mysql> ALTER TABLE tb ENGINE = InnoDB;
Query OK, 11 rows affected, 1 warning (0.55 sec)
Records: 11 Duplicates: 0 Warnings: 0

※「Query ok」と表示されても、ストレージエンジンが変更されないこともあります。
必ず、「SHOW CREATE TABLE tb;」を実行して「InnoDB」に変更されたことを確認して下さい。

□実行結果

mysql> SHOW CREATE TABLE tb;
+-------+---------------------------------------------------------------------- ---------------------------------------------------------+
| Table | Create Table

|
+-------+---------------------------------------------------------------------- ---------------------------------------------------------+
| tb | CREATE TABLE `tb` (
`bang` varchar(20) collate latin1_general_ci default NULL,
`uria` int(11) default NULL,
`tuki` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci |
+-------+---------------------------------------------------------------------- ---------------------------------------------------------+
1 row in set (0.01 sec)

mysql>※確認で「ENGINE=InnoDB」とならない場合は、「my.cnf」の設定が正しく変更されない可能性があります。確認して下さい。

★「my.cnf」の設定変更部分　抜粋★
-　-　-　-　-　-　-　-　-　-　-　-　-　-　-　-　-　-　-　-　-　-　
-　-　-　-　-　-　-　-　-　-　-　-　-　-　-　-　-　-　-　-　-　-　
# Uncomment the following if you are NOT using BDB tables
skip-bdb

#skip-innodb
# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = C:/xampp/mysql/data/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = C:/xampp/mysql/data/
innodb_log_arch_dir = C:/xampp/mysql/data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
set-variable = innodb_buffer_pool_size=16M
set-variable = innodb_additional_mem_pool_size=2M
# Set .._log_file_size to 25 % of buffer pool size
set-variable = innodb_log_file_size=5M
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1
set-variable = innodb_lock_wait_timeout=50

[mysqldump]
quick
-　-　-　-　-　-　-　-　-　-　-　-　-　-　-　-　-　-　-　-　-　-　
-　-　-　-　-　-　-　-　-　-　-　-　-　-　-　-　-　-　-　-　-　-　

▲Ｔｏｐ

（３）《トランザクションとは　ｐ２８７》

複数の処理をまとめて扱う機能をトランザクションといいます。トランザクションを始めてから、結果をデータベースに反映させることをコミット、また反映しないで元に戻すことをロールバックといいます。Ｅｘｃｅｌなどパソコンの処理に慣れていると、何でもアンドウで元に戻せると思いがちですが、データベースの世界では、「トランザクションを行ない限り、一度データを変更したら元に戻せない」と考えて置いた方がよいでしょう。

▲Ｔｏｐ

（４）《トランザクションを使う　ｐ０００》

ここでは、今まで何回も登場してきた売上情報テーブル｢tb｣の全レコードを、大胆に削除してみます。そして、そして、その処理を戻して（ROLLBACK）見ることにしましょう。

（４－１）＜実行前の注意＞

なお、大切なデータを削除してしまった後に万が一元に戻せないと困りますので、ぜひとトランザクションの実験では、失っても困らないデータを使って下さい。
また「DROP」などいくつかのコマンドはロールバックできない（元に戻せない）ので注意下さい。
また、繰り返しになりますが、ストレージエンジンが「MyISAM」のテーブルではトランザクション機能が使えません。
必ずテーブル｢tb｣が「InnoDB」になっていることを確認してから実行して下さい。

（４－２）＜トランザクションの開始＞

最初に、テーブル｢tb｣にあるデータを表示します。

□実行結果

●書式⇒トランザクションの開始

START TRANSACTION;
※あるいは、「BEGIN」や「BEGIN WORK」と入力してもかまいません。次のように「Query OK」のメッセージが表示されることを確認して下さい。

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql>

●実行結果

※これが表示されないと、トランザクションは働いていないことになり、取り返しのつかないことになってしまいます。では、テーブル｢tb｣の全レコードを大胆に削除してみましょう。 DELETE FROM tb;

mysql> DELETE FROM tb;
Query OK, 11 rows affected (0.01 sec)

mysql>

●実行結果

※「WHERE」などの条件を付けていないので、テーブル｢tb｣のすべてが削除されてしまいます。実はこの時点で、テーブルの一部の機能にロックがかかります。そのため、別のセッションで「INSERT」などを実行することができません。たとえばここで、コマンドプロントをもう１つ起動して、ＭｙＳＱＬモニタでテーブル｢tb｣に対して「INSERT」を実行しょうとします。でも、トランザクションを実行したＭｙＳＱＬモニタで「COMMIT;」「ROLLBACK;」を実行するまでは、その処理が行なわれることはありません。

（４－３）＜テーブルの内容の確認＞

さて、テーブル｢tb｣にはレコードは残っていないはずです。確認してみましょう。トランザクションを実行したＭｙＳＱＬモニタで確認して下さい。 SELECT * FROM tb;

mysql> SELECT * FROM tb;
Empty set (0.00 sec)

mysql>

●実行結果

※「Empty Set」と表示されました。「レコードは残っていない」ということです。 ※はたして元に戻るでしょうか?

●ロールバックして元に戻す

では、元に戻します。この場合はROLLBACKコマンドです。

●書式⇒ロールバック（元に戻す）

ROLLBACK;

mysql> ROLLBACK;
Query OK, 0 rows affected (0.16 sec)

mysql>

■実行結果

※これで「DELETE」コマンドの結果は反映されないはずです。SELECTコマンドで結果を確認してみましょう。

■実行結果

mysql> select * from tb;
+--------+------+------+
| bang | uria | tuki |
+--------+------+------+
| A103 | 101 | 4 |
| A102 | 54 | 5 |
| A104 | 181 | 4 |
| A101 | 184 | 4 |
| A103 | 17 | 5 |
| A101 | 300 | 5 |
| A102 | 205 | 6 |
| A104 | 93 | 5 |
| A103 | 12 | 6 |
| A107 | 87 | 6 |
| 意地悪 | 50 | NULL |
+--------+------+------+
11 rows in set (0.00 sec)

mysql>

mysql>select * from tb;
※無事に元に戻りました。
トランザクションは「ROLLBACK」を実行した時点で確定しています。
もう１度実験するときは、必ず「START TRANSACTION;」でトランザクションを開始します。
前ページの例で、もし「ROLLBACK;」する代わりに「COMMIT」とすると、レコードを削除した結果がコミット（反映）され、すべてのレコードが完全に消去されます。

●書式⇒コミット（反映する）

COMMIT;

▲Ｔｏｐ

（５）《自動コミット機能　ｐ２９２》

ＭｙＳＱＬでコマンドを実行すると、通常そのままの処理が確定されます。
つまりユーザーが意識しなくても、すべてのコマンドが自動的に「COMMIT」される。
ということなのです。特にストレージエンジンが「MyISAM」の場合、トランザクションの機能自体が無いので、いや応もなくすべてのコマンドはコミットされてしまいます。

このように「自動的にコミットする機能」といいます。ズバリそのままです。
デフォルトでは、この自動コミット機能はオンの状態になっています。
しかし、ストレージエンジンが「InnoDB」のとき、「START TRANSACTION」（あるいはBEGIN）を実行すると、｢COMMIT｣とコマンドするまではコミットしなくなります。
そのおかげで、｢ロールバックせよ｣｢ROLLBACK｣と命令できるようになるのです。
【図１３－４　自動コミット機能　画像Ｐ２９２】
※自動コミット機能は、ユーザーが強制的にオフに設定することもできます。｢自動コミット機能｣をオフにすれば、ＳＱＬ文を実行してもコミットされず、｢COMMIT｣で確定し、｢ROLLBACK｣で元に戻ることになります。

（５－１）＜自動コミット機能をオフにする＞

それでは、自動コミット機能をオフにしてみましょう。あらかじめテーブル｢tb｣のストレージエンジンが｢InnoDB｣であることを確認してください。自動コミット機能をオフにするときは、次のように SET AUTOCOMMIT=0; コマンドを実行します。

●書式⇒自動コミット機能をオフにする

SET AUTOCOMMIT=0;

■実行結果

mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.09 sec)

※これで、自動的にコミットされなくなりました。しかし、｢DELETE｣では不安もありますので、ここは｢INSERT｣コマンドを実行してみましょう。
INSERT INTO tb VALUES('test',555,555);

■実行結果

※内容を確認してみましょう。｢SELECT *FROM tb;｣を実行すれば、 INSERTしたレコード表示されるはずです。

mysql> INSERT INTO tb VALUES('test',555,555);
Query OK, 1 row affected (0.11 sec)

mysql> SELECT *FROM tb;
+--------+------+------+
| bang | uria | tuki |
+--------+------+------+
| A103 | 101 | 4 |
| A102 | 54 | 5 |
| A104 | 181 | 4 |
| A101 | 184 | 4 |
| A103 | 17 | 5 |
| A101 | 300 | 5 |
| A102 | 205 | 6 |
| A104 | 93 | 5 |
| A103 | 12 | 6 |
| A107 | 87 | 6 |
| 意地悪 | 50 | NULL |
| test | 555 | 555 |
+--------+------+------+
12 rows in set (0.12 sec)

mysql>

■実行結果

※はたしてロールバックで生きるでしょうか?
ROLLBACK;
※｢SELECT * FROM tb;｣を実行すれば、INSERTしたレコードは表示されないはずです。

mysql> ROLLBACK;
Query OK, 0 rows affected (0.49 sec)

mysql> SELECT * FROM tb;
+--------+------+------+
| bang | uria | tuki |
+--------+------+------+
| A103 | 101 | 4 |
| A102 | 54 | 5 |
| A104 | 181 | 4 |
| A101 | 184 | 4 |
| A103 | 17 | 5 |
| A101 | 300 | 5 |
| A102 | 205 | 6 |
| A104 | 93 | 5 |
| A103 | 12 | 6 |
| A107 | 87 | 6 |
| 意地悪 | 50 | NULL |
+--------+------+------+
11 rows in set (0.00 sec)

mysql>

■実行結果

※自動的にコミットされないことがわかります。ただし、｢自動コミット機能｣がオフのまま作業を続け、もし｢COMMIT｣しないでMySQLを終了すれば、その作業内容は反映されないので注意が必要です。

（５－２）＜オフにした自動コミット機能をオンにする＞

オフにした自動コミット機能を元のオンにする方法です。次の操作で、必ず自動コミット機能を元の状態に戻しておいてください。
SET AUTOCOMMIT=1;

SELECT @@AUTOCOMMIT;

●実行結果

mysql> SET AUTOCOMMIT=1;
Query OK, 0 rows affected (0.00 sec)

mysql>

mysql> SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)

mysql>

（５－３）＜トランザクションが利用できる範囲＞

トランザクションによって｢何でもロールマックで元に戻せる｣というわけではありません。
例えば次のようなコマンドは、自動的コミットされてしまいます。
DROP DATABASE
DROP TABLE
DROP
ALTER TABLE
つまり、たとえトランザクションを実行しても、元に戻すことができないことがあるので注意が必要です。

▲Ｔｏｐ

＜まとめ＞

●トランザクションとは何か、どのようなストレージエンジンがあるのか
●トランザクションとは何か
●トランザクションを開始する方法
●コミットあるいはロールバックする方法
●自動コミット機能とは何か
※実際のデータベース処理では欠かすことのできないトランザクション。ちなみに新ストレージエンジン｢Falcon｣もトランザクションをサポートしています。
今後Falcomは、InnoDBに代わるストレージエンジンとして利用されるでしようか。

▲Ｔｏｐ

＜チェック＞

□ストレージエンジンをInnoDBに変更することができる。
□トランザクションが開始できる
□明示的にコミットあるいはロールバックすることができる
□自動コミット機能を理解し、設定することができる
□ロールバックできない命令があることを知っている

▲Ｔｏｐ

＜練習問題＞

問題１
次のようなinnoDBが設定されているテーブル｢tran｣があります。
トランザクションを開始し｢UPDATE tran SET a=777;｣を実行後、コミットせずにMYSQLモニタを終了します。
その後、もう一度MySQLモニタを起動し、そのデータがどのようになったかを確認してください。
○テーブル｢tran｣

《解答1》

次のように実行します。
①テーブル｢tran｣の内容を確認します。

②トランザクションを開始します。

③UPDATEを実行し、実行後のデータを確認します（｢a｣は777に変更された）。

④｢EXIT｣コマンドで、MySQLモニタを終了します。

⑤MySQLモニタを起動し、テーブル｢tran｣の内容を確認します。（｢a｣は元の100に戻っている。777への変更は破棄されている）。

※コミットしないで終了すると、その内容は破棄されてしまうことがわかります。つまり、トランザクションをコミットせずに接続を閉じると、自動的にロールバックされてしまうのです。

▲Ｔｏｐ

13 トランザクションを使いこなす

トランザクションを使いこなす(p２８３)

（１）《ストレージエンジンとは (p２８３)》

（１－１）＜ストレージエンジンとは何か＞

（１－２）＜ストレージエンジンの種類＞

（２）《ストレエンジンを設定する ｐ２８５》

（２－１）＜ストレージエンジンの確認＞

●実行結果

（２－２）＜ストレージエンジンの変更＞

●書式⇒ストレージエンジンをInnoDBに変更する

□実行結果

□実行結果

（３）《トランザクションとは ｐ２８７》

（４）《トランザクションを使う ｐ０００》

（４－１）＜実行前の注意＞

（４－２）＜トランザクションの開始＞

□実行結果

●書式⇒トランザクションの開始

●実行結果

●実行結果

（４－３）＜テーブルの内容の確認＞

●実行結果

●ロールバックして元に戻す

●書式⇒ロールバック（元に戻す）

■実行結果

■実行結果

●書式⇒コミット（反映する）

（５）《自動コミット機能 ｐ２９２》

（５－１）＜自動コミット機能をオフにする＞

●書式⇒自動コミット機能をオフにする

■実行結果

■実行結果

■実行結果

■実行結果

（５－２）＜オフにした自動コミット機能をオンにする＞

●実行結果

（５－３）＜トランザクションが利用できる範囲＞

＜まとめ＞

＜チェック＞

＜練習問題＞

《解答1》

（２）《ストレエンジンを設定する　ｐ２８５》

（３）《トランザクションとは　ｐ２８７》

（４）《トランザクションを使う　ｐ０００》

（５）《自動コミット機能　ｐ２９２》