DDL과 트랜잭션, 그리고 Atomic DDL

협업 프로젝트 과정에서 새로운 버전을 배포하기 위한 작업을 하던 도중이었습니다. MySQL 이벤트를 통해 스키마 변경 및 데이터 조작을 진행하고자 하였고, 다음과 같은 스크립트를 작성했습니다.

delimiter &&
create event if not exists v_1_1_1
ON SCHEDULE AT '2023-09-21 04:00:00'
DO
BEGIN
    set autocommit = false;
    
    # DDL
    rename table pomodoro_room to pomodoro_study;
    alter table participant_code add column pomodoro_study_id bigint not null;
    update participant_code join pomodoro_study on pomodoro_study.participant_code_id = participant_code.id 
    	set participant_code.pomodoro_study_id = pomodoro_study.id;  
    alter table pomodoro_study drop column participant_code_id;
    alter table pomodoro_progress change pomodoro_room_id pomodoro_study_id bigint not null;
    
    commit;
    set autocommit = true;
END &&
delimiter ;

 

해당 이벤트를 등록한 뒤, 정상적으로 작동할 것으로 예측했으나 스크립트에 오류가 있었습니다. 따라서 이벤트는 정상적으로 수행되지 않았고, 저희는 autocommit을 통해 트랜잭션을 설정해줬기 때문에 자동 롤백이 될 것을 기대했습니다.

 

하지만 예상과는 다르게 DDL은 트랜잭션이 적용되지 않는 것 처럼 보였고, 롤백이 되지 않아서 변경사항이 절반만 데이터베이스에 반영되는 문제가 발생했습니다. 따라서 이번 아티클에서는 DDL과 트랜잭션의 관계를 알아보고 부가적인 개념인 'Atomic DDL'에 대해서도 정리해보도록 하겠습니다.

 

DDL과 트랜잭션

위 문제 상황이 발생하자마자 들었던 생각은 'DDL은 트랜잭션 자체를 지원하지 않을 것이다' 였습니다. 하지만 MySQL 공식 문서에 따르면 DDL은 트랜잭션을 지원하지 않는게 아니라 호출이 되자마자 커밋이 된다고 합니다. 즉, DDL 문 하나 당 하나의 트랜잭션으로 처리가 되는 것이죠.

DDL statements automatically commit the current transaction; they cannot be rolled back. - MySQL 8.0 Reference

또한, DDL문은 선언되는 순간 현재 세션에서 실행중인 모든 트랜잭션을 암시적으로 커밋하고 종료합니다. 그렇기 때문에 DDL문이 포함되는 순간 롤백 기능을 사용할 수 없는 것입니다.

DDL statements, atomic or otherwise, implicitly end any transaction that is active in the current session, as if you had done a COMMIT before executing the statement. - MySQL 8.0 Reference

따라서 아래와 같이 DML과 DDL을 혼합해서 스크립트를 작성하는 경우에는 DDL이 실행되는 순간 해당 세션의 트랜잭션이 모두 종료 및 커밋이 되므로 롤백 기능을 사용할 수 없습니다.

set autocommit = false;

# DML
INSERT INTO ...;
INSERT INTO ...;

# DDL
alter table ...; # DDL이 선언되는 동시에 위 INSERT문들이 커밋된다

commit;
set autocommit = true;

 

 

Atomic DDL

비슷한 개념인 Atomic DDL에 대해서도 소개하도록 하겠습니다. 이는 MySQL 8.0부터 지원하는 개념으로 이름에서부터 알 수 있듯이 원자적인 DDL문을 지원한다는 것입니다. DDL 문 수행 중 어떤 문제가 발생해도 반드시 성공과 실패, 둘 중 하나의 상태임을 보장합니다. 

 

Atomic DDL 기능이 없을 때에는 다음과 같이 DDL을 실행하는 중 오류가 발생하더라도 일부만 반영되는 문제가 발생했었습니다.

mysql> CREATE TABLE t1 (c1 INT);
mysql> DROP TABLE t1, t2;
ERROR 1051 (42S02): Unknown table 'test.t2'
mysql> SHOW TABLES;
Empty set (0.00 sec)

하지만 Atomic DDL 기능이 도입되면서,  MySQL 8.0부터는 위와 같이 DDL이 일부만 적용되는 경우는 없다고 합니다. 아래 예시가 Atomic DDL 기능을 활용했을 때의 시나리오입니다.

mysql> CREATE TABLE t1 (c1 INT);
mysql> DROP TABLE t1, t2;
ERROR 1051 (42S02): Unknown table 'test.t2'
mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+

t1, t2에 대해 모두 DROP TABLE을 선언했지만 DROP TABLE T2를 수행하는 과정에서 예외가 발생했고, 정상적으로 롤백이 된 모습입니다. 추가로, DDL 작업 중 서버가 종료되는 등과 같은 상황에서도 원자성을 보장한다고 합니다.

 

참고 자료

https://dev.mysql.com/doc/refman/8.0/en/atomic-ddl.html

https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_ddl