💭

DDLをロールバックする

2022/10/14に公開

Oracle Database の経験がある先輩は、DDL はロールバックできないと言っていた。
一方で SQL Server の経験がある先輩はできると言っていた。
DBMS によって挙動が違うようなので、検証してみました。

環境

Docker 上に環境を構築します。

名称	バージョン
MySQL	8.0.31
PostgreSQL	14.5
SQL Server	2019 15.0.4261.1
Oracle Database	21c Express Edition Release 21.0.0.0.0
SQLite	3.39.4

docker-compose.yml

version: '3.9'

services:
  mysql:
    image: mysql:8.0.31
    environment:
      MYSQL_ROOT_PASSWORD: 'root'
    ports:
      - 3306:3306

  postgresql:
    image: postgres:14.5
    environment:
      POSTGRES_USER: 'postgres'
      POSTGRES_PASSWORD: 'postgres'
    ports:
      - 5432:5432

  sqlserver:
    image: mcr.microsoft.com/mssql/server:2019-latest
    environment:
      ACCEPT_EULA: 'Y'
      SA_PASSWORD: 'rootRootr00t'
    ports:
      - 1433:1433

  oracle:
    image: oracle/database:21.3.0-xe
    environment:
      - ORACLE_PWD=passw0rd
    ports:
      - 1521:1521

  sqlite:
    image: keinos/sqlite3:latest
    tty: true

検証方法

トランザクションを明示的に開始し、CREATE/ALTER/DROP を実行後にロールバックするクエリを実行します。(下記は MySQL 用のクエリです。)

alter_test と drop_test はあらかじめ作成してあるテーブルです。

-- トランザクション開始
BEGIN;

-- テーブル作成(CREATE)
CREATE TABLE create_test(
  id INT
);

-- カラム追加(ALTER)
ALTER TABLE alter_test ADD COLUMN name VARCHAR(10);

-- テーブル削除(DROP)
DROP TABLE drop_test;

-- 取り消し
ROLLBACK;

結果

名称	ロールバック
MySQL	できない
PostgreSQL	できる
SQL Server	できる
Oracle Database	できない
SQLite	できる

クエリの実行結果(長いので注意)

MySQL

-- 検証前
mysql> SHOW TABLES;
+------------------+
| Tables_in_sample |
+------------------+
| alter_test       |
| drop_test        |
+------------------+
2 rows in set (0.00 sec)

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

-- テーブル作成
mysql> CREATE TABLE create_test(
    -> id INT
    -> );
Query OK, 0 rows affected (0.03 sec)

-- 作成されたことを確認
mysql> SHOW TABLES;
+------------------+
| Tables_in_sample |
+------------------+
| alter_test       |
| create_test      |
| drop_test        |
+------------------+
3 rows in set (0.00 sec)

-- カラム追加
mysql> ALTER TABLE alter_test ADD COLUMN name VARCHAR(10);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- カラム追加されていることを確認
mysql> DESC alter_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

-- テーブル削除
mysql> DROP TABLE drop_test;
Query OK, 0 rows affected (0.02 sec)

-- 削除されていることを確認
mysql> SHOW TABLES;
+------------------+
| Tables_in_sample |
+------------------+
| alter_test       |
| create_test      |
+------------------+
2 rows in set (0.00 sec)

-- 取り消し
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

-- ROLLBACK後も変更は反映されている
mysql> SHOW TABLES;
+------------------+
| Tables_in_sample |
+------------------+
| alter_test       |
| create_test      |
+------------------+
2 rows in set (0.00 sec)

mysql> DESC alter_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

PostgreSQL

-- 検証前
sample=# \dt
           List of relations
 Schema |    Name    | Type  |  Owner
--------+------------+-------+----------
 public | alter_test | table | postgres
 public | drop_test  | table | postgres
(2 rows)

-- トランザクション開始
sample=# BEGIN;
BEGIN

-- テーブル作成
sample=*# CREATE TABLE create_test(
id INT
);
CREATE TABLE

-- テーブル作成されていることを確認
sample=*# \dt
            List of relations
 Schema |    Name     | Type  |  Owner
--------+-------------+-------+----------
 public | alter_test  | table | postgres
 public | create_test | table | postgres
 public | drop_test   | table | postgres
(3 rows)

-- カラム追加
sample=*# ALTER TABLE alter_test ADD COLUMN name VARCHAR(10);
ALTER TABLE

-- カラム追加されていることを確認
sample=*# \d alter_test;
                    Table "public.alter_test"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 id     | integer               |           |          |
 name   | character varying(10) |           |          |

-- テーブル削除
sample=*# DROP TABLE drop_test;
DROP TABLE

-- 削除されていることを確認
sample=*# \dt
            List of relations
 Schema |    Name     | Type  |  Owner
--------+-------------+-------+----------
 public | alter_test  | table | postgres
 public | create_test | table | postgres
(2 rows)

-- 取り消し
sample=*# ROLLBACK;
ROLLBACK

-- ROLLBACK したので検証前と同じ状態
sample=# \dt
           List of relations
 Schema |    Name    | Type  |  Owner
--------+------------+-------+----------
 public | alter_test | table | postgres
 public | drop_test  | table | postgres
(2 rows)

sample=# \d alter_test;
             Table "public.alter_test"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           |          |

SQL Server

-- 検証前
1> SELECT name FROM sysobjects WHERE xtype = 'U'
2> GO
name
--------------------------------------------------------------------------------
alter_test
drop_test

(2 rows affected)

-- トランザクション開始
1> BEGIN TRANSACTION;

-- テーブル作成
3> CREATE TABLE create_test(
4>   id INT
5> );
6> GO

-- テーブル作成されていることを確認
1> SELECT name FROM sysobjects WHERE xtype = 'U'
2> GO
name
--------------------------------------------------------------------------------
alter_test
drop_test
create_test

(3 rows affected)

-- カラム追加
1> ALTER TABLE alter_test ADD name VARCHAR(10);
2> GO

-- カラム追加されていることを確認
1> SELECT * FROM alter_test;
2> GO
id          name
----------- ----------

(0 rows affected)

-- テーブル削除
1> DROP TABLE drop_test;
2> GO

-- 削除されていることを確認
1> SELECT name FROM sysobjects WHERE xtype = 'U'
2> GO
name
--------------------------------------------------------------------------------
alter_test
create_test

(2 rows affected)

-- 取り消し
1> ROLLBACK;
2> GO

-- ROLLBACK したので検証前と同じ状態
1> SELECT name FROM sysobjects WHERE xtype = 'U'
2> GO
name
--------------------------------------------------------------------------------
alter_test
drop_test

(2 rows affected)

1> SELECT * FROM alter_test;
2> GO
id
-----------

(0 rows affected)

Oracle Database

-- 検証前
SQL> SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME IN ('CREATE_TEST', 'ALTER_TEST', 'DROP_TEST');

TABLE_NAME
--------------------------------------------------------------------------------
ALTER_TEST
DROP_TEST

-- テーブル作成
SQL> CREATE TABLE create_test(
  2    id INT
  3  );

Table created.

-- 取り消し
SQL> ROLLBACK;

Rollback complete.

-- 作成されている
SQL> SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME IN ('CREATE_TEST', 'ALTER_TEST', 'DROP_TEST');

TABLE_NAME
--------------------------------------------------------------------------------
ALTER_TEST
CREATE_TEST
DROP_TEST

-- カラム追加
SQL> ALTER TABLE alter_test ADD (name VARCHAR(10));

Table altered.

-- 取り消し
SQL> ROLLBACK;

Rollback complete.

-- カラム追加されている
SQL> SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = 'ALTER_TEST';

COLUMN_NAME
--------------------------------------------------------------------------------
NAME
ID

-- テーブル削除
SQL> DROP TABLE drop_test;

Table dropped.

-- 取り消し
SQL> ROLLBACK;

Rollback complete.

-- 削除されている
SQL> SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME IN ('CREATE_TEST', 'ALTER_TEST', 'DROP_TEST');

TABLE_NAME
--------------------------------------------------------------------------------
ALTER_TEST
CREATE_TEST

SQLite

-- 検証前
sqlite> .tables
alter_test  drop_test

-- トランザクション開始
sqlite> BEGIN TRANSACTION;

-- テーブル作成
sqlite> CREATE TABLE create_test(
   ...>   id INT
   ...> );

-- 作成されていることを確認
sqlite> .tables
alter_test   create_test  drop_test

-- カラム追加
sqlite> ALTER TABLE alter_test ADD COLUMN name VARCHAR(10);

-- カラム追加されていることを確認
sqlite> .schema alter_test
CREATE TABLE alter_test (
  id INT
, name VARCHAR(10));

-- テーブル削除
sqlite> DROP TABLE drop_test;

-- 削除されていることを確認
sqlite> .tables
alter_test   create_test

-- 取り消し
sqlite> ROLLBACK TRANSACTION;

-- ROLLBACK したので検証前と同じ状態
sqlite> .tables
alter_test  drop_test

sqlite> .schema alter_test
CREATE TABLE alter_test (
  id INT
);

まとめ

MySQL と Oracle Database では、DDL の前に暗黙のコミットが行われるようです。
ロールバックされないので、上記の RDBMS を使うときはより一層気を付けないといけないですね。

参考

【Docker】Oracleを無料で簡単にローカルに構築する
 MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.3.3 暗黙的なコミットを発生させるステートメント
 3.4. トランザクション
 SQL Server の Transactions オブジェクト - SQL Server | Microsoft Learn
SQL文: ALTER CLUSTER～ALTER JAVA
Transaction

GitHubで編集を提案

環境

検証方法

結果

MySQL

PostgreSQL

SQL Server

Oracle Database

SQLite

まとめ

参考

Discussion