하나의 트랜잭션 안에서 DDL과 DML을 함께 써도 될까?

개요

결론부터 말하면 PostgreSQL을 제외한 MySQL, MariaDB, Oracle에서는 트랜잭션 내에서 DDL을 실행하는 순간 그 이전에 수행된 모든 DML이 자동으로 커밋되어 예상치 못한 결과가 나올 수 있으니 작성을 지양하는 편이 좋다.

START TRANSACTION;

-- DML
INSERT INTO test (id) VALUES (1);

-- DDL (PostgreSQL을 제외한 DBMS에서는 이 시점에 암묵적 COMMIT 발생)
CREATE TABLE test2 (id INT);

ROLLBACK; -- 롤백은 실행되지만 이미 커밋이 발생했기 때문에 아무런 영향 없음

부끄럽지만 나는 그동안 DDL은 트랜잭션이 적용되지 않는다는 사실은 알고 있었지만 DDL을 실행하는 순간 그 이전의 DML까지도 자동으로 커밋된다는 점은 몰랐다. 그리고 MariaDB만 주로 사용하다 보니 PostgreSQL에서는 DDL조차도 트랜잭션 내에서 정상적으로 롤백된다는 점도 이번에 처음 알게 되었다.

 

MySQL, MariaDB

MySQL(InnoDB 기준)과 MariaDB는 DDL 문장을 트랜잭션과 완전히 독립된 별도 트랜잭션으로 처리한다. 따라서 DDL이 실행되기 직전에 트랜잭션 블록 내에서 실행되었더라도 기존에 진행 중이던 모든 트랜잭션은 암묵적으로 커밋된다.

MySQL - https://dev.mysql.com/doc/refman/8.4/en/implicit-commit.html

MariaDB - https://mariadb.com/kb/en/sql-statements-that-cause-an-implicit-commit

심지어 DDL과 DML이 서로 다른 테이블을 다루더라도, 그리고 DDL이 실패하더라도 성공 여부와 상관없이 시도 시점에 커밋이 발생한다. 즉 DDL과 DML은 하나의 원자적 트랜잭션 단위로 묶이지 않고 서로 독립적으로 처리된다.

START TRANSACTION;

INSERT INTO test (id) VALUES (1);

-- TEMPORARY TABLE 생성 (암묵적 커밋 발생하지 않음)
CREATE TEMPORARY TABLE temp_test (id INT);

ROLLBACK; -- 위의 INSERT까지 모두 롤백됨

다만 위와 같이 CREATE TEMPORARY TABLE과 같은 일부 명령은 예외적으로 암묵적 커밋을 발생시키지 않기 때문에 트랜잭션 내 DML과 함께 롤백된다.

 

Oracle

Oracle 또한 트랜잭션 내에서 DDL을 실행하면 즉시 암묵적 커밋이 발생한다. 따라서 MySQL, MariaDB와 마찬가지로 DDL과 DML을 하나의 트랜잭션 단위로 묶을 수 없다.

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Types-of-SQL-Statements.html

 

PostgreSQL

PostgreSQL은 다른 RDBMS와 달리 대부분의 SQL 문장, 심지어 DDL조차도 트랜잭션 블록 내에서 실행할 수 있으며 실행 중 자동 커밋이 발생하지 않는다. 이 덕분에 DDL도 DML과 함께 롤백이 가능하다.(단, CREATE/DROP DATABASE, CREATE/DROP TABLESPACE, CLUSTER와 같이 데이터베이스 전체나 물리적 저장소에 영향을 미치는 명령어는 트랜잭션 블록 내에서 사용 불가)

MySQL과 달리 PostgreSQL에서 이러한 동작이 가능한 이유는 MVCC 적용 범위의 차이 때문이다. MySQL(InnoDB)은 MVCC를 사용자 데이터에만 적용하고 메타데이터(테이블 정의 등)는 별도로 관리되기 때문에 트랜잭션으로 롤백할 수 없지만, PostgreSQL은 시스템 카탈로그까지 일반 테이블로 간주하기 때문에 MVCC 대상으로 관리하므로 DDL 변경도 트랜잭션 내에서 안전하게 롤백할 수 있다.

https://www.postgresql.org/docs/current/catalogs.html#:~:text=The%20system%20catalogs%20are%20the,have%20been%20made%20available%20as