Rolling Back DDL

A senior with experience in Oracle Database told me that DDL cannot be rolled back.
On the other hand, another senior with experience in SQL Server said it could be done.
Since the behavior seems to differ depending on the DBMS, I decided to verify it.

Environment

I built the environment on Docker.

Name	Version
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

Verification Method

I will explicitly start a transaction and execute queries to CREATE/ALTER/DROP, followed by a rollback. (The query below is for MySQL.)

alter_test and drop_test are tables created in advance.

-- Start transaction
BEGIN;

-- Create table (CREATE)
CREATE TABLE create_test(
  id INT
);

-- Add column (ALTER)
ALTER TABLE alter_test ADD COLUMN name VARCHAR(10);

-- Drop table (DROP)
DROP TABLE drop_test;

-- Rollback
ROLLBACK;

Results

Name	Rollback
MySQL	Not possible
PostgreSQL	Possible
SQL Server	Possible
Oracle Database	Not possible
SQLite	Possible

Query execution results (Note: This is long)

MySQL

-- Before verification
mysql> SHOW TABLES;
+------------------+
| Tables_in_sample |
+------------------+
| alter_test       |
| drop_test        |
+------------------+
2 rows in set (0.00 sec)

-- Start transaction
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

-- Create table
mysql> CREATE TABLE create_test(
    -> id INT
    -> );
Query OK, 0 rows affected (0.03 sec)

-- Confirm creation
mysql> SHOW TABLES;
+------------------+
| Tables_in_sample |
+------------------+
| alter_test       |
| create_test      |
| drop_test        |
+------------------+
3 rows in set (0.00 sec)

-- Add column
mysql> ALTER TABLE alter_test ADD COLUMN name VARCHAR(10);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- Confirm column addition
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)

-- Drop table
mysql> DROP TABLE drop_test;
Query OK, 0 rows affected (0.02 sec)

-- Confirm deletion
mysql> SHOW TABLES;
+------------------+
| Tables_in_sample |
+------------------+
| alter_test       |
| create_test      |
+------------------+
2 rows in set (0.00 sec)

-- Rollback
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

-- Changes are still reflected even after 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

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

-- Start transaction
sample=# BEGIN;
BEGIN

-- Create table
sample=*# CREATE TABLE create_test(
id INT
);
CREATE TABLE

-- Confirm table creation
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)

-- Add column
sample=*# ALTER TABLE alter_test ADD COLUMN name VARCHAR(10);
ALTER TABLE

-- Confirm column addition
sample=*# \d alter_test;
                    Table "public.alter_test"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 id     | integer               |           |          |
 name   | character varying(10) |           |          |

-- Drop table
sample=*# DROP TABLE drop_test;
DROP TABLE

-- Confirm deletion
sample=*# \dt
            List of relations
 Schema |    Name     | Type  |  Owner
--------+-------------+-------+----------
 public | alter_test  | table | postgres
 public | create_test | table | postgres
(2 rows)

-- Rollback
sample=*# ROLLBACK;
ROLLBACK

-- State is the same as before verification because of 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

-- Before verification
1> SELECT name FROM sysobjects WHERE xtype = 'U'
2> GO
name
--------------------------------------------------------------------------------
alter_test
drop_test

(2 rows affected)

-- Start transaction
1> BEGIN TRANSACTION;

-- Create table
3> CREATE TABLE create_test(
4>   id INT
5> );
6> GO

-- Confirm table creation
1> SELECT name FROM sysobjects WHERE xtype = 'U'
2> GO
name
--------------------------------------------------------------------------------
alter_test
drop_test
create_test

(3 rows affected)

-- Add column
1> ALTER TABLE alter_test ADD name VARCHAR(10);
2> GO

-- Confirm column addition
1> SELECT * FROM alter_test;
2> GO
id          name
----------- ----------

(0 rows affected)

-- Drop table
1> DROP TABLE drop_test;
2> GO

-- Confirm deletion
1> SELECT name FROM sysobjects WHERE xtype = 'U'
2> GO
name
--------------------------------------------------------------------------------
alter_test
create_test

(2 rows affected)

-- Rollback
1> ROLLBACK;
2> GO

-- State is the same as before verification because of 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

-- Before verification
SQL> SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME IN ('CREATE_TEST', 'ALTER_TEST', 'DROP_TEST');

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

-- Create table
SQL> CREATE TABLE create_test(
  2    id INT
  3  );

Table created.

-- Rollback
SQL> ROLLBACK;

Rollback complete.

-- Already created
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

-- Add column
SQL> ALTER TABLE alter_test ADD (name VARCHAR(10));

Table altered.

-- Rollback
SQL> ROLLBACK;

Rollback complete.

-- Column added
SQL> SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = 'ALTER_TEST';

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

-- Drop table
SQL> DROP TABLE drop_test;

Table dropped.

-- Rollback
SQL> ROLLBACK;

Rollback complete.

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

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

SQLite

-- Before verification
sqlite> .tables
alter_test  drop_test

-- Start transaction
sqlite> BEGIN TRANSACTION;

-- Create table
sqlite> CREATE TABLE create_test(
   ...>
   ...>   id INT
   ...>
   ...>);

-- Confirm creation
sqlite> .tables
alter_test   create_test  drop_test

-- Add column
sqlite> ALTER TABLE alter_test ADD COLUMN name VARCHAR(10);

-- Confirm column addition
sqlite> .schema alter_test
CREATE TABLE alter_test (
  id INT
, name VARCHAR(10));

-- Drop table
sqlite> DROP TABLE drop_test;

-- Confirm deletion
sqlite> .tables
alter_test   create_test

-- Rollback
sqlite> ROLLBACK TRANSACTION;

-- State is the same as before verification because of ROLLBACK
sqlite> .tables
alter_test  drop_test

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

Summary

In MySQL and Oracle Database, an implicit commit seems to occur before DDL statements.
Since they are not rolled back, we need to be even more careful when using these RDBMSs.

References

【Docker】Building Oracle on local for free easily
MySQL :: MySQL 8.0 Reference Manual :: 13.3.3 Statements That Cause an Implicit Commit
3.4. Transactions
SQL Server Transactions Object - SQL Server | Microsoft Learn
SQL Statements: ALTER CLUSTER to ALTER JAVA
Transaction