💭
DDLをロールバックする
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
Discussion