처리 대상 데이터를 커서로 조회한 후 한 건씩 INSERT하는 배치 프로그램에서, 매 반복마다 COMMIT하면 트랜잭션 완료 비용이 반복되어 전체 처리시간이 길어질 수 있습니다.
아래 코드는 DBMS별 문법 차이를 제거한 슈도코드(개념 예제)입니다. 그대로 실행하는 SQL은 아닙니다.
BAD
매 행마다 COMMIT 수행
CURSOR = SELECT 처리대상_데이터
FOR EACH ROW IN CURSOR LOOP
INSERT INTO 대상테이블
VALUES (ROW의 처리결과)
COMMIT -- 매 행마다 커밋
END LOOP
Plain Text
복사
문제: 처리 대상이 100,000건이면 INSERT도 100,000번, COMMIT도 최대 100,000번 발생합니다. 중간에 오류가 발생하면 앞서 커밋된 행은 전체 롤백으로 되돌릴 수 없습니다.
•
루프 안의 COMMIT은 INSERT마다 트랜잭션을 확정합니다. 로그 기록·동기화 및 트랜잭션 처리 비용이 반복되어, 특히 대량 데이터 처리에서 병목이 될 수 있습니다.
GOOD ①
루프 완료 후 한 번 COMMIT
CURSOR = SELECT 처리대상_데이터
BEGIN TRANSACTION
FOR EACH ROW IN CURSOR LOOP
INSERT INTO 대상테이블
VALUES (ROW의 처리결과)
END LOOP
COMMIT -- 처리 완료 후 한 번 커밋
Plain Text
복사
개선: 같은 100,000건을 하나의 트랜잭션으로 처리할 수 있다면 COMMIT은 한 번만 실행합니다. 전체 작업의 원자성이 필요할 때도 적합합니다.
•
업무적으로 하나의 작업인 경우 루프 밖에서 한 번만 COMMIT합니다. 불필요한 트랜잭션 종료 횟수를 줄이고 오류 발생 시 전체를 함께 롤백할 수 있습니다.
GOOD ②
1만 건 단위로 나누어 COMMIT
BATCH_SIZE = 10000
LAST_COMMITTED_KEY = 최초_처리_기준키
LOOP
BEGIN TRANSACTION
BATCH = SELECT 처리대상_데이터
WHERE 처리키 > LAST_COMMITTED_KEY
ORDER BY 처리키
LIMIT BATCH_SIZE
IF BATCH IS EMPTY THEN
ROLLBACK
EXIT LOOP
END IF
FOR EACH ROW IN BATCH LOOP
INSERT INTO 대상테이블
VALUES (ROW의 처리결과)
END LOOP
COMMIT -- 최대 1만 건을 처리하고 커밋
LAST_COMMITTED_KEY = BATCH.마지막_처리키
END LOOP
Plain Text
복사
개선: 10만 건을 하나의 거대한 트랜잭션으로 처리하지 않고, 최대 1만 건씩 트랜잭션을 나누어 커밋합니다. 건별 커밋보다 커밋 횟수를 크게 줄이면서, 단일 트랜잭션의 크기·잠금 유지시간·장애 시 재처리 범위를 제한할 수 있습니다.
주의: 처리키는 유일하고 안정적인 정렬 기준이어야 하며, 오류로 중단된 후 재시작할 때는 마지막으로 성공적으로 커밋한 처리키를 기준으로 재개해야 합니다. 부분 커밋을 허용하는 구조이므로 재처리 시 중복 INSERT 방지도 고려합니다. 이 예제는 커서 안에서 COMMIT하지 않고, 배치별로 조회 대상을 나누는 슈도코드입니다.
•
10만 건을 한 번에 커밋하기 부담스럽고 부분 완료가 허용되는 업무라면, 1만 건씩 조회·처리·커밋하는 방식을 검토합니다. 10만 건을 모두 처리하면 10회 커밋하므로 트랜잭션 크기와 커밋 횟수 사이에서 균형을 잡을 수 있습니다.
처리 방식 비교
항목 | BAD: 건별 커밋 | GOOD ①: 일괄 커밋 | GOOD ②: 1만 건 배치 |
10만 건 처리 시 INSERT | 100,000회 | 100,000회 | 100,000회 |
10만 건 처리 시 COMMIT | 최대 100,000회 | 1회 | 10회 |
커밋 관련 로그 동기화 부담 | 빈번하게 반복 | 최소화 | 배치마다 발생 |
처리 중 오류 | 이전 커밋분 유지 | 전체 롤백 가능 | 완료된 배치는 유지 |
트랜잭션 크기 | 1건 | 100,000건 | 최대 10,000건 |
위 건수는 구조를 설명하기 위한 예시입니다. 실제 소요시간과 성능 차이는 DBMS, 디스크, 로그 설정, 처리량에 따라 달라집니다.
추가 개선 — 가능하면 집합 기반 처리
행별 계산이 반드시 필요한 것이 아니라면, 커서와 반복 INSERT를 제거하는 방법도 검토합니다.
BEGIN TRANSACTION
INSERT INTO 대상테이블
SELECT 필요한_컬럼_및_변환값
FROM 처리대상_데이터
COMMIT
Plain Text
복사
집합 기반 INSERT는 반복 SQL 실행 비용까지 줄일 수 있어, 단순 데이터 이관·변환에는 우선 검토할 만한 방법입니다. 다만 행별 복잡한 업무 로직이 있으면 커서 처리가 필요할 수 있습니다.
DBMS별 주의사항
•
Oracle: Oracle 역시 잦은 커밋이 log file sync 대기를 증가시킬 수 있습니다. Oracle에서는 문제가 없고 PostgreSQL/MySQL에서만 느리다는 뜻은 아닙니다. Oracle 공식 성능 튜닝 문서도 건별 커밋을 줄이는 배치 커밋을 권고합니다.
•
PostgreSQL: 일반적으로 COMMIT에는 WAL 동기화 비용이 따릅니다. 또한 PL/pgSQL의 읽기 전용 쿼리 기반 FOR ... IN SELECT 루프에서 COMMIT하면 처음 커밋 시 커서가 holdable cursor로 전환되어 조회 결과가 모두 평가될 수 있습니다. 따라서 커서 루프 안에 건수 조건만 추가하여 COMMIT하는 방식도 무조건 안전한 개선책은 아닙니다. 배치 처리가 필요하면 조회 대상을 키 범위 등으로 분할하여 트랜잭션마다 새로 조회·처리하는 구조를 검토합니다. 또한 프로시저 내부 트랜잭션 제어는 호출 문맥에 제약이 있습니다.
•
MySQL(InnoDB): 변경 트랜잭션마다 커밋하면 로그 내구성 처리로 인한 비용이 누적될 수 있습니다. MySQL 공식 문서도 관련 변경 작업을 트랜잭션으로 묶는 방식을 권고합니다.
참고 문서
본 페이지는 처리 패턴을 설명하는 슈도코드 사례입니다. 기존 테이블 및 데이터는 수정하지 않았으며, 실행계획과 실제 측정값은 포함하지 않았습니다.
TIP
•
1만 건은 설명을 위한 예시이며 고정된 최적값은 아닙니다. 잠금 유지시간, WAL·로그 부담, 장애 복구 및 재처리 요건에 따라 배치 크기를 정하고, 처리 대상의 키 및 재시작 기준도 명확히 관리해야 합니다.
KEY POINT
•
건별 COMMIT을 반복하기보다 업무의 트랜잭션 경계에 맞춰 커밋 단위를 결정합니다. 전체 원자성이 필요하면 일괄 커밋, 부분 완료가 허용되면 안정적인 키를 기준으로 한 분할 배치를 검토합니다.