SQL ゼロから始めるデータベース操作 第4章 トランザクション
今回は、データベースの安全性のキモとなるトランザクションを勉強します。
今回のキーワード
トランザクション
CD付 SQL ゼロからはじめるデータベース操作 (プログラミング学習シリーズ)
- 作者: ミック
- 出版社/メーカー: 翔泳社
- 発売日: 2010/06/29
- メディア: 大型本
- 購入: 39人 クリック: 484回
- この商品を含むブログ (16件) を見る
トランザクションについて
トランザクションとは?
トランザクションとは、セットで実行されるべき1つ以上の更新処理の集まりのこと。
ここでの更新処理はINSERT
, UPDATE
, DELETE
のことです。
下のイメージだと、データ挿入、データ更新、データ更新、データ削除を
1つのトランザクションにしています。
トランザクションの作成・利用方法
トランザクション開始
トランザクション開始文は、標準SQL規約ではあいまいな設定のため、
各DBMSで異なります。
また、Oracleは常にトランザクションで処理するため、
データベース接続時に自動的にトランザクションが開始され、
トランザクションが終了されると自動的に再度トランザクションを開始するので
トランザクション開始コマンドというものはありません。
本記事ではMySQLを使用しているので、以下のコマンドでトランザクションを開始します。
mysql> START TRANSACTION
使用例
以下のようなテーブルに対し、
- VALUEを現在の2倍の値に更新
- VALUEが20000を越えたものは20000に更新
の2つの処理を行います。
mysql> SELECT * FROM Sample WHERE Category='文房具'; +------+-----------------+-------+------------+-----------+ | id | Name | VALUE | Date | Category | +------+-----------------+-------+------------+-----------+ | 0002 | えんぴつ | 100 | 2009-11-27 | 文房具 | | 0004 | 消しゴム | 12500 | 2009-12-11 | 文房具 | | 0007 | ボールペン | 8500 | 2010-06-08 | 文房具 | | 0008 | クレヨン | 820 | 2010-06-15 | 文房具 | +------+-----------------+-------+------------+-----------+ 4 rows in set (0.00 sec) mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE Sample -> SET VALUE = VALUE * 2 -> WHERE Category = '文房具'; Query OK, 4 rows affected (0.00 sec) Rows matched: 4 Changed: 4 Warnings: 0 mysql> UPDATE Sample -> SET VALUE = 20000 -> WHERE VALUE > 20000 AND Category = '文房具'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM Sample WHERE Category='文房具'; +------+-----------------+-------+------------+-----------+ | id | Name | VALUE | Date | Category | +------+-----------------+-------+------------+-----------+ | 0002 | えんぴつ | 200 | 2009-11-27 | 文房具 | | 0004 | 消しゴム | 20000 | 2009-12-11 | 文房具 | | 0007 | ボールペン | 17000 | 2010-06-08 | 文房具 | | 0008 | クレヨン | 1640 | 2010-06-15 | 文房具 | +------+-----------------+-------+------------+-----------+ 4 rows in set (0.00 sec)
ここまでで処理は完了です。
この後、変更を保存するか、元に戻すかの2つの処理をおこなうことで
トランザクションが終了します。
COMMIT
トランザクションを終了し、保存します。
ファイルで言えば上書き保存して終了
です。
これはすべてのDBMSで共通です。
mysql> COMMIT;
使用例
トランザクション開始の使用例の処理を行った後、
COMMITした場合、トランザクションが終了し、データが保存されます。
mysql> COMMIT; Query OK, 0 rows affected (0.01 sec) mysql> SELECT * FROM Sample WHERE Category='文房具'; +------+-----------------+-------+------------+-----------+ | id | Name | VALUE | Date | Category | +------+-----------------+-------+------------+-----------+ | 0002 | えんぴつ | 200 | 2009-11-27 | 文房具 | | 0004 | 消しゴム | 20000 | 2009-12-11 | 文房具 | | 0007 | ボールペン | 17000 | 2010-06-08 | 文房具 | | 0008 | クレヨン | 1640 | 2010-06-15 | 文房具 | +------+-----------------+-------+------------+-----------+ 4 rows in set (0.00 sec)
ROLLBACK
トランザクションを終了し、トランザクション開始前に戻します。
ファイルで言えば保存せず終了
です。
これはすべてのDBMSで共通です。
mysql> ROLLBACK;
使用例
トランザクション開始の使用例の処理を行った後、
ROLLBACKした場合、以下のように元のデータに戻ります。
mysql> ROLLBACK; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM Sample WHERE Category='文房具'; +------+-----------------+-------+------------+-----------+ | id | Name | VALUE | Date | Category | +------+-----------------+-------+------------+-----------+ | 0002 | えんぴつ | 100 | 2009-11-27 | 文房具 | | 0004 | 消しゴム | 12500 | 2009-12-11 | 文房具 | | 0007 | ボールペン | 8500 | 2010-06-08 | 文房具 | | 0008 | クレヨン | 820 | 2010-06-15 | 文房具 | +------+-----------------+-------+------------+-----------+ 4 rows in set (0.00 sec)
暗黙的なトランザクション
ほとんどのDBMSは暗黙的にトランザクションを開始しています。
例えば、MySQLの場合、初期設定では自動コミットモード
となっており、
これは1つのSQL文で1つのトランザクションというルールが自動的に適用されるモードです。
つまり、START TRANSACTION
〜COMMIT
が自動的に行われていることですね。
例えば、以下のようなSQL文を入力した場合、
UPDATE Sample Set Value=1000 WHERE Value=980;
DBMSの内部ではこういう風に処理しています。
START TRANSACTION; UPDATE Sample Set Value=1000 WHERE Value=980; COMMIT;
この特性のため、ROLLBACKしてもすでにCOMMITされているため戻せません。
逆に、Oracleはデフォルトで最初のSQL文の処理を始める時点でトランザクションを始め、
ユーザーがCOMMIT、もしくはROLLBACKするまでを1つのトランザクションとしています。
そのため、COMMIT前であれば、ROLLBACKで今までの処理を取り消す事ができるようになっています。
気になったこと
暗黙的なトランザクションを行っているDBMSで、明示的なトランザクションを宣言した場合、
どのように処理されるのでしょうか?
考えられるケースは2つあります。
- トランザクションがネストする
mysql> START TRANSACTION; <暗黙的> START TRANSACTION; UPDATE Sample SET Value=1000 WHERE Value=980; <暗黙的>COMMIT; mysql>COMMIT;
- 暗黙的なトランザクションの宣言をしない
mysql> START TRANSACTION; UPDATE Sample SET Value=1000 WHERE Value=980; mysql>COMMIT;
気になるので後日、調べてみようと思います。