Webサイト制作コースのお申し込みはこちら Webサイト制作コースのお申し込みはこちら

SQLのロールバックについてまとめています。

SQLのロールバックの使い方

ロールバックは、トランザクション開始後に実行された更新系のクエリを巻き戻して元に戻す命令です。以下、MySQLでの実行例です。

#employees_copyテーブルにデータが存在することを確認
mysql> select * from employees_copy limit 10;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|      4 | 1982-01-01 | Siro       | Takahashi | M      | 2003-07-01 |
|      5 | 1979-08-12 | Yuji       | Satoh     | M      | 2003-04-05 |
|      6 | 1981-12-05 | Kyoko      | Shimada   | F      | 2005-09-01 |
|      7 | 1975-03-15 | Yuri       | Ooishi    | F      | 2004-04-15 |
|  10001 | 1953-09-02 | xGeorgi    | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |
|  10006 | 1953-04-20 | Anneke     | Preusig   | F      | 1989-06-02 |
+--------+------------+------------+-----------+--------+------------+
10 rows in set (0.00 sec)

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

#テーブルのレコードを全削除する
mysql> delete from employees_copy;
Query OK, 300029 rows affected (1.81 sec)

#employees_copyにデータが一見も無いことを確認
mysql> select * from employees_copy limit 10;
Empty set (0.02 sec)

#ロールバック実行
mysql> rollback;
Query OK, 0 rows affected (1.93 sec)

#先ほど削除されたレコードが元に戻っていることを確認
mysql> select * from employees_copy limit 10;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|      4 | 1982-01-01 | Siro       | Takahashi | M      | 2003-07-01 |
|      5 | 1979-08-12 | Yuji       | Satoh     | M      | 2003-04-05 |
|      6 | 1981-12-05 | Kyoko      | Shimada   | F      | 2005-09-01 |
|      7 | 1975-03-15 | Yuri       | Ooishi    | F      | 2004-04-15 |
|  10001 | 1953-09-02 | xGeorgi    | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |
|  10006 | 1953-04-20 | Anneke     | Preusig   | F      | 1989-06-02 |
+--------+------------+------------+-----------+--------+------------+
10 rows in set (0.03 sec)

truncateで削除したデータはロールバックできない

trunateなど、トランザクションに組み込めない（ログを生成しない）SQLはロールバックを実行しても元に戻せません。

ロールバックの例外になるSQLに注意しましょう。

【関連記事】
▶SQL truncate 高速データ削除 deleteやdropとの違いとDBMS間の差異 

# employees_copyにデータが約30万件存在することを確認
mysql> select count(*) from employees_copy;
+----------+
| count(*) |
+----------+
|   300029 |
+----------+
1 row in set (0.05 sec)

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

#truncateでテーブルのデータを全削除
mysql> truncate table employees_copy;
Query OK, 0 rows affected (0.49 sec)

#employees_copyテーブルにデータが存在しないことを確認
mysql> select * from employees_copy limit 10;
Empty set (0.00 sec)

#ロールバックを実行
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

#ロールバック後もデータが巻き戻っていないことを確認
mysql> select * from employees_copy limit 10;
Empty set (0.00 sec)

SQLのロールバック文のDBMSごとの差異

ロールバック機能は標準SQLとして規定されていますが、各DBMSでの独自拡張があるため、それぞれ差異があります。

MySQLでは、start transactionまたはbegin後にクエリを発行後、rollbackでロールバックを実行します。デフォルトでは自動コミットモードになっていて、insert、update、deleteなど更新系のクエリを発行すると即時コミットされます。

参考）MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.3.1 START TRANSACTION、COMMIT、および ROLLBACK 構文

Oracleでは、ロールバック時に以下のようにセーブポイントを指定し、セーブポイントまで巻き戻しが可能です。なお、以下の「SAVEPOINT」は省略可能です。

ROLLBACK TO SAVEPOINT banda_sal;

参考）Oracle公式：ROLLBACK

SQL Serverでは、セーブポイントまでの巻き戻しに加えて、セーブポイント名を格納したユーザ定義変数での巻き戻しが可能です。

例えば、以下の例ではユーザ定義変数@TransactionNameに格納されたセーブポイント名「Transaction1」までデータを巻き戻します。

DECLARE @TransactionName varchar(20) = 'Transaction1';  
ROLLBACK TRAN @TransactionName;

参考）ROLLBACK TRANSACTION (Transact-SQL) – SQL Server | Microsoft Docs

postgreSQLでも、セーブポイントまでの巻き戻しが可能です。なお、SAVEPOINTというキーワードは省略可能で、以下はどちらもセーブポイント「my_savepoint」まで巻き戻すという意味になります。

ROLLBACK TO SAVEPOINT my_savepoint;
ROLLBACK TO my_savepoint;

参考）postgreSQL公式：ROLLBACK TO SAVEPOINT my_savepoint;

まとめ

[commit]

[/commit]

エンジニアになりたい人に選ばれるプログラミングスクール「ポテパンキャンプ 」

ポテパンキャンプは卒業生の多くがWebエンジニアとして活躍している実践型プログラミングスクールです。 1000名以上が受講しており、その多くが上場企業、ベンチャー企業のWebエンジニアとして活躍しています。

基礎的な学習だけで満足せず、実際にプログラミングを覚えて実践で使えるレベルまで学習したいという方に人気です。 プログラミングを学習し実践で使うには様々な要素が必要です。

それがマルっと詰まっているポテパンキャンプでプログラミングを学習してみませんか？

卒業生の多くがWebエンジニアとして活躍

卒業生の多くがWeb企業で活躍しております。
実践的なカリキュラムをこなしているからこそ現場でも戦力となっております。
活躍する卒業生のインタビューもございますので是非御覧ください。

経験豊富なエンジニア陣が直接指導

実践的なカリキュラムと経験豊富なエンジニアが直接指導にあたります。
有名企業のエンジニアも多数在籍し品質高いWebアプリケーションを作れるようサポートします。

満足度高くコスパの高いプログラミングスクール「ポテパンキャンプ」

運営する株式会社ポテパンは10,000人以上のエンジニアのキャリアサポートを行ってきております。
そのノウハウを活かして実践的なカリキュラムを随時アップデートしております。

代表の宮崎もプログラミングを覚えサイトを作りポテパンを創業しました。
本気でプログラミングを身につけたいという方にコスパ良く受講していただきたいと思っておりますので、気になる方はぜひスクール詳細をのぞいてくださいませ。