Devin.KR
통합 검색 Ctrl K

DML·TCL 동작 예측 - COMMIT·ROLLBACK·SAVEPOINT

개발자KR 조회 15

이 장에서 배우는 것

온라인 서점 시스템에서 주문을 처리하는 스크립트는 여러 개의 INSERT·UPDATE를 하나의 트랜잭션(transaction)으로 묶어 실행한다. 이 묶음이 어디서 시작해서 어디서 끝나는지, 중간에 저장점(savepoint)을 찍었을 때 롤백(rollback)이 정확히 어디까지 되돌아가는지를 눈으로 예측하지 못하면, 운영 중에 데이터가 사라지거나 반대로 사라져야 할 데이터가 남는 사고로 이어진다. 이 장은 COMMIT·ROLLBACK·SAVEPOINT가 만드는 경계를 코드로 직접 그려보고, DDL이 섞였을 때 벌어지는 묵시적 커밋과 MERGE 계열 구문의 커밋 시점까지 함께 정리한다.

  • 트랜잭션 경계를 기준으로 COMMIT과 ROLLBACK이 미치는 범위를 예측할 수 있다.
  • SAVEPOINT를 지정하고 ROLLBACK TO SAVEPOINT가 되돌리는 구간을 정확히 짚어낼 수 있다.
  • DDL 실행이 트랜잭션에 묵시적 커밋을 일으키는 상황을 MySQL 8과 Oracle 각각의 관점에서 설명할 수 있다.
  • MERGE 문과 MySQL의 대체 구문이 DDL이 아닌 일반 DML로서 커밋 시점을 따른다는 점을 이해한다.
  • 트랜잭션이 섞인 SQL 스크립트를 보고 최종 테이블 값과 행 수를 손으로 예측할 수 있다.

문제 상황

서점 운영팀의 한 개발자가 주문 정정 배치 스크립트를 급하게 작성했다. 스크립트 앞부분에서 잘못 입력된 주문 수량을 SAVEPOINT로 감싸 두었는데, 뒷부분에 회원 테이블에 컬럼을 추가하는 ALTER TABLE 문을 실수로 함께 넣어 버렸다. 실행 도중 값이 이상한 것을 발견하고 ROLLBACK을 호출했지만, 이미 반영된 회원 데이터는 그대로 남아 있었다. "분명 ROLLBACK을 불렀는데 왜 안 되돌아가는가"라는 질문에서 출발해, 이 장은 COMMIT·ROLLBACK·SAVEPOINT·DDL이 뒤섞였을 때의 동작을 순서대로 정리한다.

이 장에서 쓰는 도서 테이블의 초기 상태는 다음과 같다. 회원 테이블에는 member_id 1(김도윤, GOLD)과 2(이서연, SILVER) 두 행만 들어 있다.

도서(BOOK) 테이블의 초기 상태
book_idtitlepricestock_qty
101SQL 기본과 활용2800012
102데이터 모델링 입문250005

트랜잭션 경계와 COMMIT·ROLLBACK

MySQL 8은 기본적으로 AUTOCOMMIT이 켜져 있어 각 DML 문이 실행 즉시 확정된다. START TRANSACTION(또는 BEGIN)을 실행하는 순간부터 그 세션은 명시적 트랜잭션 모드로 들어가고, 이후 COMMIT이나 ROLLBACK을 만날 때까지 변경 사항은 확정되지 않는다. Oracle은 반대로 세션이 시작되면 곧바로 암묵적 트랜잭션 상태이며, 별도의 시작 명령 없이 첫 DML부터 트랜잭션이 진행된다.

트랜잭션 경계 안에서 COMMIT은 그 시점까지의 모든 변경을 영구히 확정하고 트랜잭션을 종료한다. ROLLBACK은 인자 없이 호출하면 트랜잭션 시작 시점까지 모든 변경을 취소하고 역시 트랜잭션을 종료한다. 두 명령 모두 새 트랜잭션이 곧바로 이어지므로, 다음 DML은 새로운 경계 안에서 실행된다.

SAVEPOINT 롤백 범위

트랜잭션 하나를 통째로 취소하고 싶지 않을 때는 SAVEPOINT로 중간 지점을 표시해 둔다. ROLLBACK TO SAVEPOINT 이름은 그 지점 이후의 변경만 취소하고, 이전 변경과 트랜잭션 자체는 그대로 유지한다. 즉 트랜잭션은 살아 있는 상태로 계속 작업을 이어가거나 COMMIT할 수 있다.

주의할 점은 두 가지다. 첫째, 같은 이름으로 SAVEPOINT를 다시 지정하면 이전 위치 정보는 사라지고 가장 최근 지정 지점으로 대체된다. 둘째, 어떤 저장점으로 롤백하면 그 저장점 이후에 만들어진 다른 저장점들은 함께 소멸한다. COMMIT이 실행되면 세션에 남아 있던 모든 저장점은 자동으로 해제된다.

ROLLBACK TO SAVEPOINT는 저장점 이후의 변경만 취소하고 그 이전 변경과 트랜잭션 자체는 유지한다

DDL 자동 커밋과 MERGE 동작

DDL과 묵시적 커밋

CREATE, ALTER, DROP, TRUNCATE 같은 DDL 문은 MySQL 8과 Oracle 모두에서 실행 전에 대기 중이던 트랜잭션을 묵시적으로 커밋시킨다. 즉 DDL 문 앞에 있던 INSERT·UPDATE·DELETE는 DDL이 실행되는 순간 이미 확정되어 버리고, 그 뒤에 ROLLBACK을 호출해도 DDL 이전 변경에는 영향을 주지 못한다. TRUNCATE TABLE은 겉보기에 DELETE와 비슷하지만 DDL로 분류되므로 같은 이유로 롤백 대상이 되지 않는다.

DDL 실행 시 트랜잭션 처리 방식 비교
구분MySQL 8Oracle
기본 AUTOCOMMIT켜짐, 문장마다 즉시 확정꺼짐, 세션 시작과 동시에 트랜잭션 진행
트랜잭션 시작 방법START TRANSACTION 또는 BEGIN별도 시작문 없이 첫 DML부터 시작
DDL 실행 시 동작실행 직전 대기 중 변경을 묵시적 커밋실행 전후 모두 묵시적 커밋
TRUNCATE TABLE 롤백불가(DDL로 처리)불가(DDL로 처리)
트랜잭션 중간에 DDL을 실행하면 그 이전 변경 사항이 즉시 커밋되어 이후 ROLLBACK으로 되돌릴 수 없다

MERGE와 MySQL의 대체 구문

MERGE 문은 원본 키가 이미 있으면 UPDATE, 없으면 INSERT로 분기하는 단일 DML이다. Oracle은 MERGE를 표준 문법으로 지원하지만 MySQL 8은 MERGE 문 자체를 지원하지 않고, 대신 INSERT ... ON DUPLICATE KEY UPDATE로 같은 효과를 낸다. 중요한 점은 MERGE도 ON DUPLICATE KEY UPDATE도 DDL이 아니라 일반 DML이라는 것이다. 둘 다 트랜잭션 안에서 실행되면 COMMIT 전까지는 확정되지 않고, ROLLBACK TO SAVEPOINT로 되돌릴 수 있는 대상에 포함된다.

-- Oracle 문법 참고용(이 환경에서는 실행하지 않음)
MERGE INTO book b
USING (SELECT 103 AS book_id, '반정규화 실무' AS title,
              26000 AS price, 8 AS stock_qty FROM dual) s
ON (b.book_id = s.book_id)
WHEN MATCHED THEN
  UPDATE SET b.stock_qty = b.stock_qty + s.stock_qty
WHEN NOT MATCHED THEN
  INSERT (book_id, title, price, stock_qty)
  VALUES (s.book_id, s.title, s.price, s.stock_qty);
MERGE 문 지원 비교
구분OracleMySQL 8대체 방법
조건부 INSERT/UPDATE 단일문MERGE 지원미지원INSERT ... ON DUPLICATE KEY UPDATE
키 충돌 시 삭제 옵션WHEN MATCHED ... DELETE 가능미지원별도 DELETE 문 필요
커밋 시점일반 DML과 동일해당 없음ON DUPLICATE KEY UPDATE도 일반 DML과 동일

완성 코드

00_setup.sql

DROP TABLE IF EXISTS order_detail;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS book;
DROP TABLE IF EXISTS member;

CREATE TABLE member (
  member_id   INT PRIMARY KEY,
  name        VARCHAR(20) NOT NULL,
  grade       VARCHAR(10) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE book (
  book_id     INT PRIMARY KEY,
  title       VARCHAR(50) NOT NULL,
  price       INT NOT NULL,
  stock_qty   INT NOT NULL
) ENGINE=InnoDB;

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  member_id   INT NOT NULL,
  order_date  DATE NOT NULL,
  status      VARCHAR(10) NOT NULL,
  FOREIGN KEY (member_id) REFERENCES member(member_id)
) ENGINE=InnoDB;

CREATE TABLE order_detail (
  order_id    INT NOT NULL,
  book_id     INT NOT NULL,
  qty         INT NOT NULL,
  sale_price  INT NOT NULL,
  PRIMARY KEY (order_id, book_id),
  FOREIGN KEY (order_id) REFERENCES orders(order_id),
  FOREIGN KEY (book_id) REFERENCES book(book_id)
) ENGINE=InnoDB;

INSERT INTO member VALUES (1, '김도윤', 'GOLD'), (2, '이서연', 'SILVER');
INSERT INTO book VALUES
  (101, 'SQL 기본과 활용', 28000, 12),
  (102, '데이터 모델링 입문', 25000, 5);

01_savepoint_demo.sql

START TRANSACTION;

INSERT INTO orders VALUES (9001, 1, '2026-09-29', 'READY');

SAVEPOINT sp_order;

UPDATE book SET stock_qty = stock_qty - 20 WHERE book_id = 101;
INSERT INTO order_detail VALUES (9001, 101, 20, 28000);

SELECT book_id, stock_qty FROM book WHERE book_id = 101;

ROLLBACK TO SAVEPOINT sp_order;

SELECT book_id, stock_qty FROM book WHERE book_id = 101;
SELECT * FROM order_detail WHERE order_id = 9001;

UPDATE book SET stock_qty = stock_qty - 2 WHERE book_id = 101;
INSERT INTO order_detail VALUES (9001, 101, 2, 28000);

SAVEPOINT sp_detail;

INSERT INTO book (book_id, title, price, stock_qty)
VALUES (103, '반정규화 실무', 26000, 8)
ON DUPLICATE KEY UPDATE stock_qty = stock_qty + 8;

COMMIT;

SELECT * FROM orders;
SELECT * FROM order_detail;
SELECT book_id, title, stock_qty FROM book ORDER BY book_id;

02_ddl_autocommit_demo.sql

START TRANSACTION;

INSERT INTO member VALUES (3, '박준서', 'SILVER');

CREATE TABLE tmp_audit (
  audit_id INT PRIMARY KEY,
  memo     VARCHAR(50)
);

ROLLBACK;

SELECT * FROM member ORDER BY member_id;

줄별 해설

  • START TRANSACTION; AUTOCOMMIT을 잠시 끄고 명시적 트랜잭션을 연다. 이 시점부터 COMMIT을 만나기 전까지 아무 변경도 다른 세션에 보이지 않는다.
  • SAVEPOINT sp_order; 주문 헤더가 정상 입력된 시점을 저장점으로 표시한다. 이후 잘못될 수도 있는 재고 처리를 이 지점으로 되돌릴 수 있게 준비하는 것이다.
  • UPDATE book ... -20 / INSERT order_detail ... 20 실수로 20권을 차감하는 상황을 재현한다. 재고에 CHECK 제약이 없으므로 DB는 음수 재고를 막지 않는다.
  • ROLLBACK TO SAVEPOINT sp_order; sp_order 지정 이후의 UPDATE와 INSERT만 취소된다. sp_order 이전에 실행한 주문 헤더 INSERT는 그대로 남는다.
  • UPDATE book ... -2 / INSERT order_detail ... 2 올바른 수량으로 다시 반영한다. 트랜잭션은 롤백 이후에도 살아 있으므로 이어서 작업할 수 있다.
  • SAVEPOINT sp_detail; 상세 반영이 끝난 지점을 새 이름으로 표시한다. sp_order와 다른 이름을 써서 두 지점을 모두 남긴다.
  • INSERT ... ON DUPLICATE KEY UPDATE ... book_id 103은 아직 없으므로 INSERT 분기가 실행된다. 이 문장은 DDL이 아니므로 COMMIT 전까지는 확정되지 않는다.
  • COMMIT; 트랜잭션 시작부터 지금까지의 모든 변경을 한 번에 확정하고 저장점을 모두 해제한다.
  • CREATE TABLE tmp_audit(...) DDL 실행 직전 대기 중이던 회원 INSERT가 묵시적으로 커밋된다. 이 라인이 실행된 순간 이미 되돌릴 수 없는 상태가 된다.
  • ROLLBACK; DDL 이후 새로 시작된 트랜잭션에는 취소할 변경이 없으므로 실질적인 효과가 없다.

실행 결과

00_setup.sql과 01_savepoint_demo.sql을 차례로 실행한다.

mysql> source 00_setup.sql
mysql> source 01_savepoint_demo.sql

잘못된 재고 차감 직후 SELECT 결과는 다음과 같다. 재고가 음수로 내려갔다.

+---------+-----------+
| book_id | stock_qty |
+---------+-----------+
|     101 |        -8 |
+---------+-----------+
1 row in set (0.00 sec)

ROLLBACK TO SAVEPOINT sp_order 이후 재고는 저장점 시점으로 돌아가고, 저장점 이후에 넣은 주문상세는 사라진다.

+---------+-----------+
| book_id | stock_qty |
+---------+-----------+
|     101 |        12 |
+---------+-----------+
1 row in set (0.00 sec)

Empty set (0.00 sec)

올바른 수량으로 다시 반영하고 COMMIT까지 마친 뒤 최종 상태를 확인한다.

+----------+-----------+------------+--------+
| order_id | member_id | order_date | status |
+----------+-----------+------------+--------+
|     9001 |         1 | 2026-09-29 | READY  |
+----------+-----------+------------+--------+
1 row in set (0.00 sec)

+----------+---------+-----+------------+
| order_id | book_id | qty | sale_price |
+----------+---------+-----+------------+
|     9001 |     101 |   2 |      28000 |
+----------+---------+-----+------------+
1 row in set (0.00 sec)

+---------+--------------------+-----------+
| book_id | title              | stock_qty |
+---------+--------------------+-----------+
|     101 | SQL 기본과 활용   |        10 |
|     102 | 데이터 모델링 입문 |         5 |
|     103 | 반정규화 실무      |         8 |
+---------+--------------------+-----------+
3 rows in set (0.00 sec)

02_ddl_autocommit_demo.sql을 실행하면 ROLLBACK을 호출했음에도 회원 데이터가 그대로 남는다.

mysql> source 02_ddl_autocommit_demo.sql
+-----------+-----------+--------+
| member_id | name      | grade  |
+-----------+-----------+--------+
|         1 | 김도윤    | GOLD   |
|         2 | 이서연    | SILVER |
|         3 | 박준서    | SILVER |
+-----------+-----------+--------+
3 rows in set (0.00 sec)

실무에서 자주 틀리는 것

SAVEPOINT만 찍고 트랜잭션을 닫지 않는다

ROLLBACK TO SAVEPOINT는 트랜잭션을 종료하지 않는다. 이 사실을 잊고 스크립트를 끝내면 커넥션이 반환된 뒤에도 트랜잭션이 열린 채로 락을 붙잡고 있을 수 있다.

-- 틀린 코드
START TRANSACTION;
UPDATE book SET stock_qty = stock_qty - 1 WHERE book_id = 101;
SAVEPOINT sp1;
UPDATE order_detail SET qty = 1 WHERE order_id = 9001 AND book_id = 101;
ROLLBACK TO SAVEPOINT sp1;
-- 스크립트 종료, COMMIT/ROLLBACK 호출 없음
-- 고친 코드
START TRANSACTION;
UPDATE book SET stock_qty = stock_qty - 1 WHERE book_id = 101;
SAVEPOINT sp1;
UPDATE order_detail SET qty = 1 WHERE order_id = 9001 AND book_id = 101;
ROLLBACK TO SAVEPOINT sp1;
COMMIT;

DML 트랜잭션 안에 DDL을 섞는다

DDL이 실행되는 순간 그 이전 변경이 묵시적으로 커밋되므로, 뒤이은 ROLLBACK은 앞부분에 영향을 주지 못한다.

-- 틀린 코드
START TRANSACTION;
INSERT INTO member VALUES (4, '최민재', 'SILVER');
ALTER TABLE member ADD COLUMN phone VARCHAR(20);
UPDATE member SET phone = '010-0000-0000' WHERE member_id = 4;
ROLLBACK; -- INSERT는 이미 커밋되어 사라지지 않는다
-- 고친 코드: DDL은 별도 스크립트로 분리한다
-- 01_alter_member.sql
ALTER TABLE member ADD COLUMN phone VARCHAR(20);

-- 02_insert_member.sql
START TRANSACTION;
INSERT INTO member VALUES (4, '최민재', 'SILVER');
UPDATE member SET phone = '010-0000-0000' WHERE member_id = 4;
COMMIT;

SAVEPOINT 이름을 재사용한다

같은 이름으로 SAVEPOINT를 다시 지정하면 이전 지점 정보는 사라지고 최신 지점으로 덮어써진다.

-- 틀린 코드
SAVEPOINT sp1;
UPDATE book SET stock_qty = stock_qty - 1 WHERE book_id = 102;
SAVEPOINT sp1;  -- 이전 sp1 위치가 사라진다
UPDATE book SET stock_qty = stock_qty - 1 WHERE book_id = 102;
ROLLBACK TO SAVEPOINT sp1;  -- 원래 의도한 지점이 아니다
-- 고친 코드: 저장점마다 고유한 이름을 쓴다
SAVEPOINT sp_stage1;
UPDATE book SET stock_qty = stock_qty - 1 WHERE book_id = 102;
SAVEPOINT sp_stage2;
UPDATE book SET stock_qty = stock_qty - 1 WHERE book_id = 102;
ROLLBACK TO SAVEPOINT sp_stage1;

UPSERT를 개별 문장으로 쪼갠다

MERGE의 효과를 UPDATE와 INSERT 두 문장으로 나누어 흉내 내면, 두 문장 사이의 틈에서 중복 키 오류나 경쟁 조건이 생길 수 있다.

-- 틀린 코드
UPDATE book SET stock_qty = stock_qty + 8 WHERE book_id = 103;
-- book_id 103이 없으면 0행 적용되고, 아래 INSERT도 이미 있는 값이면 오류가 난다
INSERT INTO book (book_id, title, price, stock_qty)
VALUES (103, '반정규화 실무', 26000, 8);
-- 고친 코드: 한 문장으로 처리한다
INSERT INTO book (book_id, title, price, stock_qty)
VALUES (103, '반정규화 실무', 26000, 8)
ON DUPLICATE KEY UPDATE stock_qty = stock_qty + 8;

한눈에 보기

COMMIT·ROLLBACK·SAVEPOINT·DDL이 되돌리는 범위
명령/상황되돌리는 범위트랜잭션 유지 여부비고
COMMIT없음(모두 확정)종료, 새 트랜잭션 시작저장점도 모두 해제
ROLLBACK(인자 없음)트랜잭션 시작 시점까지 전체종료, 새 트랜잭션 시작-
ROLLBACK TO SAVEPOINT spsp 지정 시점 이후 변경만유지, 계속 작업 가능sp 이후 만든 다른 저장점은 소멸
DDL 실행(CREATE/ALTER/DROP/TRUNCATE)실행 전 대기 중이던 변경 전부 즉시 커밋새 트랜잭션 시작MySQL 8, Oracle 공통

연습 문제

  1. book_id 101의 초기 재고가 12일 때, 다음을 실행하고 COMMIT까지 마쳤다. 최종 stock_qty는 얼마인가. 
    START TRANSACTION;
UPDATE book SET stock_qty = stock_qty - 3 WHERE book_id = 101;
SAVEPOINT sp_a;
UPDATE book SET stock_qty = stock_qty - 5 WHERE book_id = 101;
SAVEPOINT sp_b;
UPDATE book SET stock_qty = stock_qty - 2 WHERE book_id = 101;
ROLLBACK TO SAVEPOINT sp_a;
COMMIT;
  2. 다음 스크립트를 실행한 뒤 member_id 5의 grade는 무엇이 되는가. 이유를 함께 쓰시오. 
    START TRANSACTION;
INSERT INTO member VALUES (5, '오하은', 'GOLD');
UPDATE member SET grade = 'VIP' WHERE member_id = 5;
TRUNCATE TABLE tmp_log;
UPDATE member SET grade = 'GOLD' WHERE member_id = 5;
ROLLBACK;
  3. book_id 102의 초기 재고가 5일 때, 다음을 실행하고 COMMIT까지 마쳤다. 최종 stock_qty는 얼마인가. 
    START TRANSACTION;
UPDATE book SET stock_qty = stock_qty - 1 WHERE book_id = 102;
SAVEPOINT sp1;
UPDATE book SET stock_qty = stock_qty - 1 WHERE book_id = 102;
SAVEPOINT sp1;
UPDATE book SET stock_qty = stock_qty - 1 WHERE book_id = 102;
ROLLBACK TO SAVEPOINT sp1;
COMMIT;
  4. book_id 101의 stock_qty가 10, title이 'SQL 기본과 활용'인 상태에서 다음을 실행했다. 실행 후 stock_qty와 title은 각각 어떻게 되는가. 
    INSERT INTO book (book_id, title, price, stock_qty)
VALUES (101, 'SQL 기본과 활용 개정판', 28000, 5)
ON DUPLICATE KEY UPDATE stock_qty = stock_qty + VALUES(stock_qty);

정답과 해설

  1. 9. 초기값 12에서 첫 UPDATE로 9가 되고 그 지점에서 sp_a를 지정한다. 이어서 5를 더 빼 4가 되고 sp_b를 지정한 뒤 2를 더 빼 2가 된다. ROLLBACK TO SAVEPOINT sp_a는 sp_a가 지정된 시점, 즉 9로 되돌리므로 COMMIT 후 값은 9다.
  2. VIP. TRUNCATE TABLE은 DDL이므로 실행되는 순간 그 이전의 INSERT와 첫 번째 UPDATE(grade='VIP')가 묵시적으로 커밋된다. TRUNCATE 이후에 실행된 두 번째 UPDATE(grade='GOLD')만 새 트랜잭션에 속하므로 ROLLBACK의 대상이 되어 취소된다. 따라서 grade는 TRUNCATE 시점에 이미 확정된 'VIP'로 남는다.
  3. 3. 초기값 5에서 첫 UPDATE로 4가 되고 이 지점에서 sp1을 지정한다. 두 번째 UPDATE로 3이 된 뒤 같은 이름 sp1을 다시 지정하면 앞서 4였던 위치 정보는 사라지고 3인 지점이 새 sp1이 된다. 세 번째 UPDATE로 2가 된 뒤 ROLLBACK TO SAVEPOINT sp1은 가장 최근에 지정된 3으로 되돌아가므로 COMMIT 후 값은 3이다.
  4. stock_qty는 15, title은 그대로 'SQL 기본과 활용'이다. book_id 101이 이미 존재하므로 INSERT 대신 ON DUPLICATE KEY UPDATE 분기가 실행되어 stock_qty = 10 + 5 = 15가 된다. UPDATE 절에 title을 명시하지 않았으므로 title 값은 변경되지 않는다.

댓글 0

아직 댓글이 없습니다. 첫 댓글을 남겨 보세요.

댓글을 남기려면 로그인이 필요합니다.