MySQLとトランザクション

トランザクションとは・・・？

以前、当ブログではMicrosoft　Accessでトランザクションを扱ったことがあります。

家計簿を作ろう～Access　その6～

このときは「処理として導入してみた」というだけで、その中身についてなんにも説明してなかったのでここで改めて調べてみました。

大雑把に言うと

複数の処理（クエリ）を一纏めの処理として扱う
「全ての処理が成功」　or　「全ての処理が失敗」のいずれかが保障される

ということになります。なんだか分かりにくいですね？

トランザクションの説明で必ずといっていいほど使われている「金融機関の資金移動処理」を用いて考えてみましょう。

資金移動処理の流れを挙げる

口座間で資金の移動を行う際の処理内容を見てみると

・口座Aから資金を引き落とす

・口座Bへ資金を振り込む

という2段階から構成されています。

途中で処理が失敗した場合・・・

これら2つの処理を一括りで扱う場合、まず最初の処理でエラーが発生したとします。すると振り込むお金が発生していないのに口座Bへお金を入金することになってしまいます。

エラーになるのか、どこからもお金を持ってこずに口座Bへの入金が発生するのかは処理内容によって変わってくるのかもしれませんが、いずれにしても不整合が生じる形になってしまいます。

一方、口座Aからの引き落としに成功した後の処理でコケた場合はどうでしょう。口座Bに振り込まれるはずだったお金が宙に浮いたままになってしまいますよね。お金が引き落とされた口座Aがとてもかわいそうです。というか、現実でそんなことが発生したら事件になりますよね？

全て成功か全て失敗か

そこで登場するのがトランザクション。

両方の処理が成功した場合のみ「全て成功」として処理結果が反映され、それ以外（片方が失敗でも両方失敗でも）の場合は「全て失敗」として処理自体がなかったことになるのがトランザクション処理というやつなんですって。

資金移動で言うなれば、引き落としと振込みの両者がそれって成功して初めて処理結果を反映する、どちらか一方でも失敗すれば引き落としも振り込みもキャンセルして無効になるということです。

MySQLでのトランザクションの使用方法

MySQLでトランザクションを使用するためには、データベースエンジンに「InnoDB」を指定してやる必要があります。（この辺はまた今度）

テーブル毎に設定されるデータベースエンジン、こいつを確認するにはコンソールで

↓MySQLクライアントを起動
$ mysql -u root -p
（パスワードを入力）

↓「information_schema」データベースを選択
mysql> use infomation_schema;

↓SQLで、選んだデータベース内のテーブル名とデータベースエンジンを調べる
mysql> select table_name, engine from tables where table_schema = 'データベース名';
+-------------------+--------+
| table_name        | engine |
+-------------------+--------+
| table1            | InnoDB |
| table2            | InnoDB |
| table3            | MyISAM |
+-------------------+--------+

と入力してやると調べることができます。

データベースエンジンにInnoDBを使用しているテーブルに対しては

START　TRANSACTIONコマンドでトランザクションを開始（BEGINコマンドでも可能）
処理を実行
COMMITコマンドで結果を反映　または　ROLLBACKコマンドで処理結果を破棄

というトランザクション処理を行うことができます。

実際にトランザクションを動かしてみる

それではいよいよトランザクション処理をおこなってみましょう。

これまた以前の投稿で使用した、くだらないテーブルがあります。

PHPでSELECT文を発行してみる

このときのテーブルを使って、データの修正を行ってみることにします。（あくまでトランザクションの動きを確かめるためなので、今回は複数処理を行うのではなく簡単な処理にしました）

現在、以下のようなデータを格納した表があるとしましょう。

T_商品リスト

商品コード	商品名	値段
011	こん棒	80
012	鉄パイプ	180
013	釘バット	500

ここに「商品コード：014」　「商品名：日本刀」　「値段：10000」というデータを挿入したときのROLLBACKとCOMMITの結果の違いを見てみましょう。

まずはROLLBACKから。MySQLで

↓トランザクションの開始
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

↓T_商品リストにデータを追加
mysql> insert into T_商品リスト (商品コード,商品名,値段)   values ('014','日本刀','10000');
Query OK, 1 row affected (0.00 sec)

↓処理結果を確認。データが挿入されていることがわかりますね。
mysql> select * from T_商品リスト;
+-----------------+--------------+--------+
| 商品コード      | 商品名       | 値段   |
+-----------------+--------------+--------+
| 011             | こん棒       | 80     |
| 012             | 鉄パイプ     | 180    |
| 013             | 釘バット     | 500    |
| 014             | 日本刀       | 10000  |
+-----------------+--------------+--------+
4 rows in set (0.00 sec)

↓ROLLBACKコマンドでキャンセルする
mysql> rollback;
Query OK, 0 rows affected (0.05 sec)

↓再度処理結果を確認すると、追加したデータがクリアされ、元に戻っている
mysql> select * from T_商品リスト;
+-----------------+--------------+--------+
| 商品コード      | 商品名       | 値段   |
+-----------------+--------------+--------+
| 011             | こん棒       | 80     |
| 012             | 鉄パイプ     | 180    |
| 013             | 釘バット     | 500    |
+-----------------+--------------+--------+
3 rows in set (0.00 sec)

続いてはCOMMITを試します。

↓トランザクションの開始
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

↓T_商品リストにデータを追加
mysql> insert into T_商品リスト (商品コード,商品名,値段)   values ('014','日本刀','10000');
Query OK, 1 row affected (0.00 sec)

↓処理結果を確認。
mysql> select * from T_商品リスト;
+-----------------+--------------+--------+
| 商品コード      | 商品名       | 値段   |
+-----------------+--------------+--------+
| 011             | こん棒       | 80     |
| 012             | 鉄パイプ     | 180    |
| 013             | 釘バット     | 500    |
| 014             | 日本刀       | 10000  |
+-----------------+--------------+--------+
4 rows in set (0.00 sec)

↓COMMITコマンドで処理を確定
mysql> COMMIT;
Query OK, 0 rows affected (0.05 sec)

↓再度処理結果を確認すると処理結果が反映されて残っていることがわかります。
mysql> select * from T_商品リスト;
+-----------------+--------------+--------+
| 商品コード      | 商品名       | 値段   |
+-----------------+--------------+--------+
| 011             | こん棒       | 80     |
| 012             | 鉄パイプ     | 180    |
| 013             | 釘バット     | 500    |
| 014             | 日本刀       | 10000  |
+-----------------+--------------+--------+
4 rows in set (0.00 sec)

このようにROLLBACKとCOMMITを使い分けることで処理結果に違いが出る事が確認できました。

今回はデータの挿入のみを行いましたが、削除や修正を行う場合でもROLLBACKとCOMMITを使うことで結果を制御することが可能です。

まとめ

このように「START　TRANSACTION」、「COMMIT」、「ROLLBACK」を組み合わせてトランザクション処理を行うことで処理結果を反映するのか全て破棄するのかを制御することができます。

金融機関のみならず、例えば物流関係であれば「出荷データを未出荷→出荷済へ書き換える」処理と「商品の在庫数を出荷した分だけ引き落とす」処理を纏めて行うことで、処理途中にエラーが発生しても在庫数の不一致を防ぐことができると思います。

様々な業界で使用されているデータベースにおいて、もはや避けて通ることができないぐらい浸透しているトランザクション処理。

MySQLでもAccessでももう少し実用的な例を踏まえてトランザクション処理を行うストアドプロシージャかVBAを公開したいものです。