공공데이터포털(data.go.kr)이나 지자체 인허가 시스템에서 제공하는 사업자, 병의원, 부동산 실거래가 등의 대규모 데이터셋을 정기적으로 수집하여 내부 RDBMS에 동기화하는 배치 파이프라인을 운영하다 보면, 데이터베이스의 동시성 저하와 락(Lock) 경합 문제에 직면하게 됩니다.
특히 서비스 운영 중에 수십만에서 100만 건 규모의 테이블을 대상으로 실시간 조회가 빈번히 일어나는 상황에서 단순 INSERT ... ON DUPLICATE KEY UPDATE나 대규모 단일 트랜잭션 벌크 적재를 실행하면, InnoDB 엔진의 넥스트 키 락(Next-Key Lock) 및 갭 락(Gap Lock)으로 인해 일반 사용자의 조회 및 단건 쓰기 쿼리가 락 타임아웃(Lock Wait Timeout Exceeded)에 걸리는 현상이 발생합니다.
이번 글에서는 MySQL 8.0 InnoDB 스토리지 엔진과 PostgreSQL의 락 메커니즘 공식 문서를 기반으로, 대량 적재 시 발생하는 내부 경합 요인을 분석하고, 서비스 무중단 동기화를 위한 스테이징 테이블(Staging Table) 원자적 스왑(Atomic Swap) 기법과 청크 분할(Chunking) 트랜잭션 아키텍처를 정리합니다.
1. 대량 단일 트랜잭션 적재 시 발생하는 3대 엔진 부하
수십만 건의 레코드를 하나의 트랜잭션에서 일괄 처리할 때 스토리지 엔진 내부에서는 다음과 같은 구조적 부하가 발생합니다:
- Undo Tablespace 및 버퍼 풀 압박: 대량의
UPDATE또는INSERT가 단일 트랜잭션으로 묶이면, MVCC(다중 버전 동시성 제어)를 유지하기 위한 Undo 로그가 디스크를 급격히 잠식하며 버퍼 풀(Buffer Pool)의 더티 페이지(Dirty Page) 비율이 임계치를 초과하여 체크포인트 플러시 지연을 초래합니다. - 보조 인덱스(Secondary Index)의 갭 락(Gap Lock) 확산:
INSERT ... ON DUPLICATE KEY UPDATE문은 유니크 제약 조건을 확인하는 과정에서 일치하는 행뿐만 아니라 인접한 인덱스 레코드 사이의 빈 공간(Gap)까지 락을 획득합니다. 이로 인해 동일 범위에 접근하는 실시간 사용자 트랜잭션이 줄지어 블로킹됩니다. - Redo 로그 / WAL(Write-Ahead Logging) 플러시 병목: 트랜잭션 커밋 시점마다 동기적 디스크 I/O가 집중되어 전체 쿼리 레이턴시가 수 초에서 수십 초까지 치솟습니다.
2. 무중단 전체 동기화: 스테이징 테이블 & 원자적 스왑(Atomic Swap)
매일 또는 매주 공공데이터 전체 스냅샷을 갱신해야 하는 경우, 운영 중인 서비스 테이블(Live Table)을 직접 수정하지 않고 격리된 스테이징 테이블(Staging Table)을 활용하는 아키텍처가 가장 안전합니다.
- 스테이징 테이블 생성: 운영 테이블과 동일한 DDL로
table_staging을 생성합니다. 적재 속도 극대화를 위해 초기 적재 시점에는 외래키(Foreign Key) 검사와 보조 인덱스를 일시 비활성화하거나 최소화합니다. - 벌크 데이터 고속 적재: 백그라운드 배치 프로세스가 스테이징 테이블에 데이터를 적재합니다. 이때 운영 테이블에는 어떠한 락도 걸리지 않으므로 실시간 서비스 성능에 영향을 주지 않습니다.
- 인덱스 빌드 및 데이터 검증: 적재 완료 후 필요한 보조 인덱스를 일괄 생성하고, 행 수 및 무결성을 검증합니다.
- 원자적 RENAME TABLE 실행: 두 테이블의 이름을 단일 쿼리로 맞바꿉니다.
-- MySQL 원자적 테이블 스왑 쿼리
-- 단일 메타데이터 락(MDL)을 통해 0.001초 미만의 시간으로 순식간에 교체됩니다.
RENAME TABLE
business_store TO business_store_old,
business_store_staging TO business_store;
-- 교체 완료 후 백그라운드에서 이전 테이블 정리
DROP TABLE business_store_old;이 방식은 RENAME TABLE 실행 순간에만 극히 짧은 메타데이터 락(Metadata Lock)을 점유하므로, 사용자는 수십만 건의 데이터 갱신을 락 대기 없이 밀리초 단위의 전환으로 체감할 수 있습니다.
3. 증분 동기화(Upsert) 시 청크 분할(Chunking) 트랜잭션 설계
전체 교체가 아닌 변경분만 반영해야 하는 증분 적재의 경우, 단일 트랜잭션 크기를 1,000~5,000건 단위의 청크(Chunk)로 분할하여 커밋해야 합니다.
import math
import logging
from typing import List, Dict, Any
logger = logging.getLogger(__name__)
def execute_chunked_upsert(cursor, records: List[Dict[str, Any]], chunk_size: int = 2000):
total_records = len(records)
total_chunks = math.ceil(total_records / chunk_size)
insert_sql = """
INSERT INTO pharmacy_info (id, hira_code, name, address, latitude, longitude, updated_at)
VALUES (%(id)s, %(hira_code)s, %(name)s, %(address)s, %(latitude)s, %(longitude)s, NOW())
ON DUPLICATE KEY UPDATE
name = VALUES(name),
address = VALUES(address),
latitude = VALUES(latitude),
longitude = VALUES(longitude),
updated_at = NOW();
"""
for i in range(total_chunks):
start_idx = i * chunk_size
end_idx = min(start_idx + chunk_size, total_records)
chunk = records[start_idx:end_idx]
try:
cursor.executemany(insert_sql, chunk)
cursor.connection.commit()
logger.info(f"Chunk [{i+1}/{total_chunks}] {len(chunk)}건 커밋 완료.")
except Exception as e:
cursor.connection.rollback()
logger.error(f"Chunk [{i+1}/{total_chunks}] 실행 실패: {e}")
raise e4. 엔터프라이즈 배치 적재 성능 체크리스트
- PK 기반 순차 정렬(Clustered Index Order): 벌크 인서트할 레코드를 데이터베이스에 전송하기 전에 애플리케이션 메모리에서 기본키(Primary Key) 순서로 정렬하십시오. 순차 인서트는 B-Tree 리프 노드의 페이지 스플릿(Page Split)을 방지하여 디스크 I/O를 절반 이하로 줄입니다.
- 고유 인덱스(Unique Index) 범위 최소화: 갭 락은 주로 유니크 보조 인덱스에서 발생하므로, 변경 빈도가 높은 비즈니스 식별자는 테이블 설계 단계에서 정규화하거나 불필요한 보조 인덱스를 정비하십시오.
- autocommit = 0 및 커넥션 풀 관리: 매 행마다 암시적 커밋(Autocommit)이 발생하면 I/O 오버헤드로 인해 처리 시간이 기하급수적으로 늘어납니다. 명시적 트랜잭션 블록을 구성하십시오.
