COMMIT文とROLLBACK文

トランザクションとはデータベース内の処理の単位です。

今までDML文として、SELECT,INSERT,UPDATE,DELETE文を紹介してきましたが、
これらのSQL文をデータベースではトランザクションと呼んでいます。

例えば、以下の様にSQL文を実行したとします。

---トランザクション1------
　　INSERT 　
　　　↓　　
　　UPDATE
　　　↓　　
　　DELETE

上記のINSERTからDELETEまでの処理は、まとめて1つの処理としてみなされます。
またこれらのSQL文はまだデータベースには反映されていません。

これらの処理をデータベースに反映する、
または取り消しするには以下のコマンドを実行します。

[構文]　データの確定
　COMMIT;

上記のコマンドは今までのトランザクション内の処理を確定しデータベースに反映します。

上記例では、トランザクション1のINSERT,UPDATE,DELETEがデータベースに反映されます。
SELECT文はデータを参照するコマンドなので、データベースに反映される処理ではありません。

また今までの処理を取消したい場合は、

[構文]　データの取消
　ROLLBACK;

上記のコマンドで取り消しを行うことができます。
COMMITコマンドはデータの確定、ROLLBACKはデータの取消となります。
これらのコマンドはトランザクション全体の処理をまとめて、確定または取消します。

トランザクションの開始と終了

トランザクションはデータベース内の処理であり、開始と終了があります。
開始はDML文を実行した時点です。

終了は先ほどのCOMMIT,ROLLBACKコマンドにより処理が終了します。
そのほかのコマンドでも処理が終了します。

トランザクションの開始	DML文の実行
トランザクションの終了	COMMIT,ROLLBACKの発行 DDL (CREATE,ALTER,DROP,TRUNCATE)の発行 DCL (GRANT,REVOKE)の発行

まだDDL,DCL文は学んでいませんが、上記コマンドを途中で実行した場合も、
暗黙的にcommitコマンドが実行され、トランザクションが終了します。

間違えてトランザクション中にTRUNCATEコマンドなどを実行してしまうと、
データベースに今までの処理が反映されてしまいますのでご注意が必要です。

では実行例を見ていきましょう。


  SQL> select emp_id,last_name from employees2 where emp_id=10;

      EMP_ID LAST_NAME
  ---------- ------------------------------
          10 Yumi


  SQL> delete from employees2 where emp_id=10;

  1 row deleted.


  SQL> select emp_id,last_name from employees2 where emp_id=10;

  no rows selected


  SQL> rollback;

  Rollback complete.


  SQL> select emp_id,last_name from employees2 where emp_id=10;

      EMP_ID LAST_NAME
  ---------- ------------------------------
          10 Yumi

一度DELETEコマンドを実行しましたが、rollbackコマンドにより取消が行われています。
続いて、commitの実行例です。


  SQL> select emp_id,last_name from employees2 where emp_id=10;

      EMP_ID LAST_NAME
  ---------- ------------------------------
          10 Yumi


  SQL> delete from employees2 where emp_id=10;

  1 row deleted.


  SQL> commit;

  Commit complete.


  SQL> select emp_id,last_name from employees2 where emp_id=10;

  no rows selected

  SQL> rollback;

  Rollback complete.

  SQL> select emp_id,last_name from employees2 where emp_id=10;

  no rows selected

上記ではDELETEコマンド後にcommitを実行しています。
ここでDELETE文はデータベースに反映されます。

その後にROLLBACKを実行していますが、すでにcommitが実行されている為、
元に戻すことは出来ません。

もしこの状態でDELETE文を取り消す場合はデータベースを
バックアップから戻して復旧作業が必要となります。

一度COMMITを行うと元に戻すことは簡単ではないので注意して実施してください。

また複数トランザクションを実施した場合です。


  SQL> DELETE1 …(1)
  
  SQL> UPDATE1 …(2)
  
  SQL> INSERT1 …(3)
  
  SQL> ROLLBACK …(4)
  
  SQL> INSERT2 …(5)
  
  SQL> DELETE2 …(6)
  
  SQL> ROLLBACK …(7)

(7)ROLLBACKではどのトランザクションまでが取り消しされるでしょうか。
答えは(5)INSERT2,(6)DELETE2のみを取消します。

(1)～(3)のDML文が実行され、(4)ROLLBACKが実行されているので、
ここで今までの変更は取り消しされ、トランザクションは終了します。

次に(5)INSERT2が実行されているので、ここで新しいトランザクションが開始されます。
その為、(7)ROLLBACKは新しいトランザクションが開始された時点まで取り消しするので、
(5)INSERT2,(6)DELETE2を取消します。

いかがでしたでしょうか。トランザクションはなんなのか理解することができたでしょうか。
この内容はデータベースの中でも重要な部分なので、
少しでも疑問点があれば実機で検証してみるのも良いでしょう。

トランザクション

トランザクションは何故複数のDML文で1処理なのでしょうか。
1DML文で1トランザクションの方が分かりやすいと思いませんか？

この理由は銀行のシステムを考えてみると、
複数のDML文を1つのトランザクション(1処理)として扱う必要があることが分かってきます。

例えばATMで自分の銀行の口座から、別の銀行に1万円振り込むときの処理を考えてみましょう。
この時、この処理を行うことにより以下のコマンドが実行されることが想定されます。

1. UPDATE 自分の口座 - 10000
2. UPDATE 別の銀行口座 + 10000

この2つのUPDATE文は両方とも成功する必要があります。
1つ目のUPDATEは成功し、2つ目のUPDATEが失敗してしまった場合、
銀行のシステムは致命的な問題となってしまいます。

そのためこの2つのUPDATEはどちらかが失敗した場合は、両方とも取消する必要があるのです。

このようにシステムは複数のSQL文によって構成されていることがあり、
データベースの処理としてはDMLをまとめて1処理として見なすほうが都合がよいのです。

SAVEPOINT文

それでは、少し特殊なコマンドも紹介していきます。
それはSAVEPOINTです。これはゲームでセーブという機能がありますが、
それと同じ機能です。

ROLLBACKはトランザクションが開始された時点まで取消を行いますが、
SAVEPOINTを作成するとトランザクションの途中まで取消を行うことができます。

構文は以下の通りです。

[構文]　セーブポイントの作成
　SAVEPOINT セーブポイント名;

[構文]　セーブポイントまでROLLBACK
　ROLLBACK TO セーブポイント名;

上記コマンドを使用してセーブポイントを作成し、セーブポイントまで戻ることができます。
SAVEPOINTの注意点は以下の通りです。

・ROLLABCK TO セーブポイント名のコマンドではトランザクションは終了しない
・1トランザクション内に複数のセーブポイントを作成することが可能
・同じ名前のセーブポイント名で作成してしまうと上書きされる

では実行例を見ていきましょう。


  SQL> select emp_id,last_name from employees2;

      EMP_ID LAST_NAME
  ---------- ------------------------------
           1 Hiroshi
           2 Takashi
           3 Keiko
           4 yoko
           5 Sawa
           6 Tomonori
          ～ 省略 ～
          18 Tetsuya
          19 Tsutomu
          20 Shuya

  19 rows selected.


  SQL> delete from employees2 where emp_id = 1 ;

  1 row deleted.


  SQL> delete from employees2 where emp_id = 2 ;

  1 row deleted.


  SQL> select emp_id,last_name from employees2;

      EMP_ID LAST_NAME
  ---------- ------------------------------
           3 Keiko
           4 yoko
           5 Sawa
           6 Tomonori
          ～ 省略 ～
          18 Tetsuya
          19 Tsutomu
          20 Shuya

  17 rows selected.


  SQL> savepoint S1;

  Savepoint created.


  SQL> delete from employees2 where emp_id = 3;

  1 row deleted.


  SQL> delete from employees2 where emp_id = 4;

  1 row deleted.


  SQL> rollback to S1;

  Rollback complete.


  SQL> select emp_id,last_name from employees2;

      EMP_ID LAST_NAME
  ---------- ------------------------------
           3 Keiko
           4 yoko
           5 Sawa
           6 Tomonori
          ～ 省略 ～
          18 Tetsuya
          19 Tsutomu
          20 Shuya

  17 rows selected.


  SQL> rollback;

  Rollback complete.


  SQL>  select emp_id,last_name from employees2;

      EMP_ID LAST_NAME
  ---------- ------------------------------
           1 Hiroshi
           2 Takashi
           3 Keiko
           4 yoko
           5 Sawa
           6 Tomonori
          ～ 省略 ～
          18 Tetsuya
          19 Tsutomu
          20 Shuya

  19 rows selected.

上記実行例では一度S1のセーブポイントまで戻っていますが、
最後にROLLBACKコマンドを実行している為、
トランザクション内の処理がすべて取消されています。

このように「ROLLABCK TO　セーブポイント」はトランザクションが終了しません。

いかがでしたでしょうか。今回はトランザクションについて学んでいきました。