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;
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]
- ロールバックは、トランザクション中に実行された更新系クエリの変更を巻き戻す
- truncateなど、一部のSQLはロールバック対象外
- ロールバック機能は、各DBMSで差異あり
[/commit]