備忘録的プログラミングリファレンス

ROLLBACK クエリ

 ROLLBACK クエリはトランザクション操作を制御するためのコマンドで、入力や更新、削除されたデータの保存をキャンセルします。 ROLLBACK によって入力や更新、削除された内容が保存されることはないため追加、変更された内容は残りません。

ROLLBACK クエリ
ROLLBACK;

 自動 COMMIT がオンの状態では START TRANSACTION または BEGIN を宣言しない限りは ROLLBACK が機能しません。 オン状態では、自動で COMMIT が実行されデータの変更は即時に保存されます。

 COMMIT とは、入力や更新されたデータを任意に確定するコマンドです。

ROLLBACK とは

 ROLLBACK とはトランザクション操作を制御するためのコマンドで、入力や更新、削除されたデータの保存をキャンセルするクエリです。
 データの保存がキャンセルされることは結果的に INSERT、UPDATE、DELETE クエリをリクエストする前の状態のままになります。

ROLLBACK クエリ
ROLLBACK;

 データベースにおけるトランザクションとは、クエリのリクエスト〜データ保存までの一連の処理のことをいいます。
 例えば INSERT、UPDATE、DELETE クエリのリクエストは以下のようなトランザクション処理が行われます。

トランザクション
INSERT、UPDATE、DELETE クエリのリクエスト
⇓
データベースに接続
⇓
クエリの実行
⇓
データの変更
⇓
ROLLBACK または COMMIT
⇓
変更されたデータの保存

 ROLLBACK または COMMIT は変更されたデータの保存をキャンセルするか/確定するかのクエリです。
 ROLLBACK は保存をキャンセルすることでデータの変更は反映されません。 COMMIT はデータの保存をただちに行い変更内容がすぐに反映されます。

 データベースの多くが自動で COMMIT が実行されます。そのため、 ROLLBACK が機能しません。
 自動 COMMIT が機能しないようにするには、自動 COMMIT を一時的にオフにするか、START TRANSACTION または BEGIN を宣言します。

 自動 COMMIT がオンであるかは、 MySQL では autocommit プロパティ を、PostgreSQL では AUTOCOMMIT プロパティで確認できます。

MySQL 自動 COMMIT の確認
mysql> SELECT @@autocommit;
PostgreSQL 自動 COMMIT の確認
=> \echo :AUTOCOMMIT

自動 COMMIT の設定

 自動 COMMIT は一時的にオフにすることができます。オン/オフの方法はデータベースによって違いがありますので注意が必要です。

MySQL

 自動 COMMIT をオフにするには、MySQL では autocommit の設定を 0 にします。

MySQL 自動 COMMIT のオフ
mysql> SET autocommit=0;

 自動 COMMIT をオンにするには、autocommit の設定を 1 に設定します。

MySQL 自動 COMMIT のオン
mysql> SET autocommit=1;

PostgreSQL

 PostgreSQL において自動 COMMIT をオフにするには、AUTOCOMMIT の設定を off にします。

PostgreSQL 自動 COMMIT のオフ
=> \set AUTOCOMMIT off

 自動 COMMIT をオンにするには、autocommit の設定を on に設定します。

PostgreSQL 自動 COMMIT のオン
=> \set AUTOCOMMIT on

 自動 COMMIT がオンの状態で ROLLBACK を機能させるには、START TRANSACTION または BEGIN を宣言します。

START TRANSACTION

 START TRANSACTION は一連のトランザクション処理として始めることを宣言します。このコマンドは BEGIN と同じです。
 処理の途中でSET autocommit=0;または\set AUTOCOMMIT offを実行することで自動 COMMIT を一時的に停止することができます。
 自動 COMMIT がオフの状態なら ROLLBACK を使用することができます。ただし、データの変更内容を保存するためには COMMIT を実行する必要があります。

 以下の例では、ファンクションなどで多くの場合 BEGIN を使用すると思いますので、 BEGIN にしています。

BEGIN ( MySQL )
mysql> BEGIN;

mysql> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
..
/* 自動 COMMIT はオン */

/* 自動 COMMIT はオフに */
/* PostgreSQL では \set AUTOCOMMIT off */
mysql> SET autocommit=0;
..

/* データを更新してみる */
mysql> UPDATE goods SET name = 'sweets' WHERE id = 1;
..

mysql> SELECT * FROM goods WHERE id = 1;
+----+--------+------------+
| id | name   | unit_price |
+----+--------+------------+
|  1 | sweets |          3 |
+----+--------+------------+
..

/* ROLLBACK してみる */
mysql> ROLLBACK;
..

/* ROLLBACK が機能しているかを確認 */
mysql> SELECT * FROM goods WHERE id = 1;
+----+-------+------------+
| id | name  | unit_price |
+----+-------+------------+
|  1 | candy |          3 |
+----+-------+------------+
..

COMMIT;
..

 ファンクションではないために END はありません。

 最終的には COMMIT によって元通りのデータの状態で保存しています。

 COMMIT の効果に関してはこのようなスタンドアロンの環境では分かり難いのですが、マルチユーザーで1つのデータベースを利用している場合では COMMIT されない限りはすべてのユーザーにデータの変更は反映されません。

ROLLBACK ポイント

 ROLLBACK にはセーブポイントを設けることで、そこまで遡るだけということができます。ただし、トランザクション処理中のみで、一度 COMMIT したらそれより前には戻せません。

 セーブポイントを設定するには以下のように SAVEPOINT を使用します。

SAVEPOINT example_savepoint;

 以下は SAVEPOINT を使用した例です。2度データを更新しています。2度めの前に SAVEPOINT を設定しています。

SAVEPOINT
mysql> BEGIN;

mysql> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
..
/* 自動 COMMIT はオン */

/* 自動 COMMIT はオフに */
/* PostgreSQL では \set AUTOCOMMIT off */
mysql> SET autocommit=0;
..

/* データを更新してみる */
mysql> UPDATE goods SET name = 'sweets' WHERE id = 1;
..

mysql> SELECT * FROM goods WHERE id = 1;
+----+--------+------------+
| id | name   | unit_price |
+----+--------+------------+
|  1 | sweets |          3 |
+----+--------+------------+
..

/* SAVEPOINT を作成 */
mysql> SAVEPOINT test_savepoint;
..

/* 再度データを更新してみる */
mysql> UPDATE goods SET name = 'ramune' WHERE id = 1;
..

mysql> SELECT * FROM goods WHERE id = 1;
+----+--------+------------+
| id | name   | unit_price |
+----+--------+------------+
|  1 | ramune |          3 |
+----+--------+------------+
..

/* SAVEPOINT まで ROLLBACK してみる */
mysql> ROLLBACK TO SAVEPOINT test_savepoint;
..

/* SAVEPOINT が機能しているかを確認 */
mysql> SELECT * FROM goods WHERE id = 1;
+----+--------+------------+
| id | name   | unit_price |
+----+--------+------------+
|  1 | sweets |          3 |
+----+--------+------------+
..

 今度は最後に COMMIT していません。
 このままでログアウトしたら、name = 'sweets'という更新は破棄されて元のデータに戻ります。