SQL ゼロから始めるデータベース操作 第4章 トランザクション

今回は、データベースの安全性のキモとなるトランザクションを勉強します。

今回のキーワード
トランザクション

CD付 SQL ゼロからはじめるデータベース操作 (プログラミング学習シリーズ)

トランザクションについて

トランザクションとは？

トランザクションとは、セットで実行されるべき１つ以上の更新処理の集まりのこと。

ここでの更新処理はINSERT, UPDATE, DELETEのことです。
下のイメージだと、データ挿入、データ更新、データ更新、データ削除を
１つのトランザクションにしています。

トランザクションの作成・利用方法

トランザクション開始

トランザクション開始文は、標準SQL規約ではあいまいな設定のため、
各DBMSで異なります。
また、Oracleは常にトランザクションで処理するため、
データベース接続時に自動的にトランザクションが開始され、
トランザクションが終了されると自動的に再度トランザクションを開始するので
トランザクション開始コマンドというものはありません。

本記事ではMySQLを使用しているので、以下のコマンドでトランザクションを開始します。

mysql> START TRANSACTION

使用例

以下のようなテーブルに対し、

• VALUEを現在の２倍の値に更新
  
• VALUEが20000を越えたものは20000に更新
  の２つの処理を行います。

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)

ここまでで処理は完了です。
この後、変更を保存するか、元に戻すかの２つの処理をおこなうことで
トランザクションが終了します。

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の場合、初期設定では自動コミットモードとなっており、
これは１つのSQL文で１つのトランザクションというルールが自動的に適用されるモードです。
つまり、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するまでを１つのトランザクションとしています。

そのため、COMMIT前であれば、ROLLBACKで今までの処理を取り消す事ができるようになっています。

気になったこと

暗黙的なトランザクションを行っているDBMSで、明示的なトランザクションを宣言した場合、
どのように処理されるのでしょうか？

考えられるケースは２つあります。

• トランザクションがネストする
  

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;

気になるので後日、調べてみようと思います。