데이터베이스의 일관성과 무결성을 유지하는 데 있어 트랜잭션 관리는 핵심적인 역할을 한다. 오라클 데이터베이스에서는 TCL(Transaction Control Language) 명령어를 통해 트랜잭션을 효과적으로 제어할 수 있다. TCL의 주요 명령어인 COMMIT, ROLLBACK, SAVEPOINT는 데이터 변경 작업의 영구 반영, 취소, 부분 취소를 관리하는 데 사용된다. 이번 포스트에서는 COMMIT, ROLLBACK, SAVEPOINT에 대해 알아보자.
1. 트랜잭션의 기본 개념
1.1 트랜잭션이란 무엇인가?
트랜잭션은 데이터베이스의 상태를 변화시키는 하나의 논리적 작업 단위다. 복수의 SQL 문이 모여 하나의 작업을 이루는 경우가 많다. 예를 들어, 계좌 이체는 출금과 입금이라는 두 작업이 모두 성공하거나 모두 실패해야 하는 하나의 트랜잭션이다.
1.2 ACID 속성
트랜잭션은 다음 네 가지 ACID 속성을 따라야 한다:
- 원자성(Atomicity): 트랜잭션의 모든 연산은 모두 실행되거나 모두 실행되지 않아야 한다.
- 일관성(Consistency): 트랜잭션이 실행을 성공적으로 완료하면 데이터베이스는 일관된 상태를 유지해야 한다.
- 격리성(Isolation): 동시에 실행되는 트랜잭션들이 서로 영향을 미치지 않도록 격리되어야 한다.
- 지속성(Durability): 성공적으로 완료된 트랜잭션의 결과는 영구적으로 반영되어야 한다.
1.3 오라클에서의 트랜잭션 처리 방식
오라클에서 트랜잭션은 다음과 같은 특징을 가진다:
- 첫 번째 DML 문(INSERT, UPDATE, DELETE)이 실행될 때 트랜잭션이 시작된다.
- COMMIT 또는 ROLLBACK 문이 실행될 때 트랜잭션이 종료된다.
- DDL 문(CREATE, ALTER, DROP 등)이나 DCL 문(GRANT, REVOKE)이 실행되면 자동으로 COMMIT이 발생한다.
- 정상적인 데이터베이스 종료 시 자동으로 COMMIT이 발생한다.
- 비정상적인 종료(시스템 충돌 등)의 경우 자동으로 ROLLBACK이 발생한다.
2. COMMIT 구문: 트랜잭션 확정하기, 저장하기
2.1 COMMIT의 기본 구문과 동작
COMMIT 명령어는 현재 트랜잭션의 모든 변경사항을 데이터베이스에 영구적으로 반영한다.
COMMIT [WORK] [COMMENT 'comment_text'];
예시:
-- 기본 COMMIT
UPDATE accounts SET balance = balance - 1000 WHERE account_id = 101;
UPDATE accounts SET balance = balance + 1000 WHERE account_id = 202;
COMMIT;
-- 주석과 함께 COMMIT (감사 목적으로 유용)
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 50;
COMMIT COMMENT 'Annual salary increase for department 50';
WORK는 선택적 키워드로, 구문 명확성을 위해 사용할 수 있다. COMMENT 옵션은 감사 목적으로 트랜잭션에 주석을 추가할 수 있게 해준다.
2.2 COMMIT 이후 발생하는 효과
COMMIT을 실행하면 다음과 같은 효과가 발생한다:
- 모든 데이터 변경이 데이터베이스에 영구적으로 저장된다.
- 다른 사용자가 변경된 데이터를 볼 수 있게 된다.
- 해당 데이터에 설정된 모든 잠금(lock)이 해제된다.
- 저장점(SAVEPOINT)이 모두 삭제된다.
- 새로운 트랜잭션이 시작된다.
2.3 COMMIT 사용 시 고려사항
- 데이터 변경 작업이 논리적으로 완료된 시점에 COMMIT을 수행해야 한다.
- COMMIT 이후에는 ROLLBACK으로 변경을 취소할 수 없다.
- 장시간 실행되는 트랜잭션은 다른 사용자의 작업을 차단할 수 있으므로 적절한 지점에서 COMMIT하는 것이 좋다.
- AUTOCOMMIT 설정이 활성화된 경우 각 SQL 문 실행 후 자동으로 COMMIT이 발생한다.
-- SQL*Plus에서 자동 커밋 설정/해제
SET AUTOCOMMIT ON; -- 활성화
SET AUTOCOMMIT OFF; -- 비활성화 (기본값)
3. ROLLBACK 구문: 트랜잭션 취소하기
3.1 ROLLBACK의 기본 구문과 동작
ROLLBACK 명령어는 현재 트랜잭션의 모든 변경사항을 취소하고 이전 상태로 되돌린다.
ROLLBACK [WORK] [TO [SAVEPOINT] savepoint_name];
예시:
-- 기본 ROLLBACK (전체 트랜잭션 취소)
UPDATE accounts SET balance = balance - 1000 WHERE account_id = 101;
UPDATE accounts SET balance = balance + 1000 WHERE account_id = 202;
-- 오류 발생 시
ROLLBACK;
-- 특정 저장점까지만 ROLLBACK
INSERT INTO orders VALUES (1001, 'C001', SYSDATE);
SAVEPOINT order_created;
INSERT INTO order_items VALUES (1001, 'P001', 2, 100);
INSERT INTO order_items VALUES (1001, 'P002', 1, 200);
-- 주문 상품에 문제가 있는 경우
ROLLBACK TO SAVEPOINT order_created;
-- 이제 주문은 유지되고 주문 상품만 취소됨
COMMIT;
WORK는 선택적 키워드다. TO SAVEPOINT 절을 사용하면 특정 저장점까지만 롤백할 수 있다.
3.2 ROLLBACK 이후 발생하는 효과
ROLLBACK을 실행하면 다음과 같은 효과가 발생한다:
- 모든 데이터 변경이 취소되고 이전 상태로 복원된다.
- 해당 데이터에 설정된 모든 잠금(lock)이 해제된다.
- 새로운 트랜잭션이 시작된다.
부분 ROLLBACK(저장점까지의 ROLLBACK)의 경우:
- 지정된 저장점 이후의 변경만 취소된다.
- 저장점 이전의 변경은 유지된다.
- 트랜잭션은 계속 진행 중이며, COMMIT이나 ROLLBACK으로 명시적으로 종료해야 한다.
3.3 암시적 ROLLBACK이 발생하는 상황
다음 상황에서는 자동으로 ROLLBACK이 발생한다:
- 시스템 장애(전원 공급 중단, 하드웨어 오류 등)
- 네트워크 연결 끊김
- 비정상적인 클라이언트 종료
- 데이터베이스 서버 비정상 종료
이러한 상황에 대비하여 중요한 작업 중간에 주기적으로 COMMIT하거나 SAVEPOINT를 설정하는 것이 좋다.
3.4 ROLLBACK 사용 시 고려사항
- DDL 문 실행 후에는 ROLLBACK이 불가능하다(자동 COMMIT 발생).
- 대량의 데이터를 처리하는 트랜잭션에서 ROLLBACK은 시간이 오래 걸릴 수 있다.
- 일부 변경만 취소하려면 SAVEPOINT를 사용해야 한다.
- 읽기 일관성을 위해 SELECT 문은 ROLLBACK의 영향을 받지 않는다.
4. SAVEPOINT 구문: 저장점 설정하기
4.1 SAVEPOINT의 기본 구문과 동작
SAVEPOINT 명령어는 트랜잭션 내에 중간 저장점을 설정하여 부분적인 롤백을 가능하게 한다.
SAVEPOINT savepoint_name;
예시:
-- 복잡한 트랜잭션에서 저장점 활용
-- 1. 고객 정보 변경
UPDATE customers SET address = '서울시 강남구' WHERE customer_id = 1001;
SAVEPOINT customer_updated;
-- 2. 주문 생성
INSERT INTO orders VALUES (2001, 1001, SYSDATE);
SAVEPOINT order_created;
-- 3. 주문 상세 추가
INSERT INTO order_details VALUES (2001, 'ITEM1', 2, 50000);
INSERT INTO order_details VALUES (2001, 'ITEM2', 1, 30000);
-- 주문 상세에 문제가 있는 경우
ROLLBACK TO SAVEPOINT order_created;
-- 주문 정보까지 문제가 있는 경우
ROLLBACK TO SAVEPOINT customer_updated;
-- 모든 것을 취소하려면
ROLLBACK;
4.2 SAVEPOINT 관리 주의사항
- 같은 이름의 저장점을 여러 번 설정하면 마지막으로 설정한 위치로 덮어써진다.
- 저장점으로 롤백한 후에는 해당 저장점 이후에 설정한 다른 저장점들은 모두 제거된다.
- COMMIT 실행 시 모든 저장점은 제거된다.
- 오라클은 최대 5개의 저장점을 유지한다(구버전 기준).
4.3 SAVEPOINT를 효과적으로 활용하는 전략
- 논리적으로 구분되는 작업 단위마다 저장점을 설정한다.
- 오류 발생 가능성이 높은 복잡한 연산 전에 저장점을 설정한다.
- 대량의 데이터를 처리할 때는 일정 간격으로 저장점을 설정하여 부분 실패 시 복구를 용이하게 한다.
- 저장점 이름은 작업 내용을 명확히 반영하도록 의미 있게 지정한다.
-- 대량 데이터 처리에서 SAVEPOINT 활용
DECLARE
v_count NUMBER := 0;
BEGIN
FOR r IN (SELECT * FROM large_table WHERE status = 'PENDING') LOOP
UPDATE large_table
SET status = 'PROCESSED', process_date = SYSDATE
WHERE id = r.id;
v_count := v_count + 1;
-- 100개마다 저장점 설정
IF MOD(v_count, 100) = 0 THEN
SAVEPOINT batch_100;
-- 오류 확인 로직
IF some_error_condition THEN
ROLLBACK TO SAVEPOINT batch_100;
-- 오류 처리
END IF;
END IF;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
/
오라클 데이터베이스에서 TCL 구문(COMMIT, ROLLBACK, SAVEPOINT)은 데이터 일관성과 무결성을 유지하는데 꼭 필요한 요소이다. 적절한 시점에서 트랜잭션을 commit하거나 rollback하여 아까운 데이터를 날려버리는 일이 없도록 주의하자규용!