SQLite WAL 모드와 동시성: 읽기·쓰기·체크포인트 정리

기본 모드와 그 한계

SQLite는 기본적으로 롤백 저널(rollback journal) 방식을 사용합니다. 쓰기가 일어나면 원본 페이지를 -journal 파일에 복사한 뒤 메인 데이터베이스를 수정하고, 커밋 시점에 저널을 삭제하는 식이죠. 만약 쓰는 도중에 프로세스가 죽으면, 저널을 거꾸로 재생해서 변경 사항을 되돌립니다.

단순하고 안전한 방식이지만, 한 가지 뼈아픈 단점이 있습니다. 바로 읽는 쪽과 쓰는 쪽이 같은 파일을 두고 경합한다는 점입니다. 쓰기 작업이 데이터베이스 락을 잡고 있으면 새 트랜잭션을 시작하려는 리더는 진입할 수 없고, 반대로 리더들이 활동 중이면 라이터가 대기해야 합니다. 트래픽이 몰리는 앱 — 예를 들어 동시 요청이 몇 개씩 들어오는 웹 서버라면 — SQLITE_BUSY 에러를 생각보다 자주 마주치게 됩니다.

SQLite WAL 모드는 바로 이 구조를 바꿔줍니다.

WAL 모드가 실제로 하는 일

write-ahead logging은 모델 자체를 뒤집습니다. 메인 데이터베이스 파일을 그 자리에서 수정하는 대신, 커밋된 페이지를 -wal 확장자가 붙은 별도의 파일에 덧붙여(append) 기록합니다. 리더는 여전히 메인 파일을 읽되, 자기가 필요한 페이지의 더 최신 버전이 있는지 WAL을 함께 살펴봅니다.

그 결과, 라이터 한 명과 다수의 리더가 동시에 작업할 수 있습니다. 각 리더는 자기 트랜잭션이 시작된 시점의 일관된 스냅샷을 보고, 라이터는 리더들이 보고 있는 영역을 건드리지 않은 채 WAL에 데이터를 계속 덧붙이기만 하면 됩니다.

PRAGMA journal_mode=WAL;

이 PRAGMA 한 줄이면 데이터베이스가 WAL 모드로 전환됩니다. 이 설정은 파일 헤더에 저장되기 때문에 한 번만 켜두면 됩니다. 이후에 새로 맺는 모든 커넥션은 자동으로 WAL 모드로 동작하니까, 매번 커넥션마다 실행할 필요 없이 데이터베이스를 처음 만들 때(또는 마이그레이션 스크립트 안에서) 한 번만 실행해 주면 충분합니다.

이 PRAGMA는 변경된 모드 값을 반환합니다. wal이 돌아오면 정상적으로 적용된 것이고, 다른 값이 나오면 해당 파일 시스템이 공유 메모리를 지원하지 않을 가능성이 높습니다(자세한 내용은 아래에서 다룹니다).

WAL 모드 적용 및 확인하기

현재 저널 모드는 언제든 다음과 같이 확인할 수 있습니다:

PRAGMA journal_mode=WAL;
PRAGMA journal_mode;

CREATE TABLE messages (id INTEGER PRIMARY KEY, body TEXT);
INSERT INTO messages (body) VALUES ('안녕하세요'), ('세계');
SELECT * FROM messages;

첫 번째 호출은 WAL 모드를 활성화하고 새로 설정된 모드를 반환합니다. 두 번째 호출은 = 없이 현재 모드만 조회하는 용도죠. 이렇게 설정한 뒤 활동이 있을 때 messages.db 폴더를 살펴보면 messages.db, messages.db-wal, messages.db-shm 이렇게 세 개의 파일이 보입니다. 뒤의 두 파일은 열려 있는 커넥션이 있느냐에 따라 생겼다 사라졌다 합니다.

SQLite -wal, -shm 파일의 정체

WAL 모드를 켜면 두 개의 부속 파일이 따라붙는데, 각각 어떤 역할을 하는지 알아둘 필요가 있습니다.

• -wal 파일은 아직 메인 데이터베이스로 합쳐지지 않은 커밋된 트랜잭션을 담고 있습니다. 쓰기가 일어날수록 커지다가 체크포인트 시점에 줄어들거나 초기화됩니다.
• -shm 파일은 공유 메모리 파일입니다. WAL에 대한 인덱스 역할을 해서, 모든 커넥션이 매 쿼리마다 WAL 전체를 훑지 않고도 어떤 페이지가 어디에 있는지 합의할 수 있게 해줍니다.

여기서 중요한 실전 팁 하나. WAL 모드 데이터베이스를 백업할 때 .db 파일만 복사하면 절대 안 됩니다. 가장 최신 데이터는 -wal 파일 안에 들어 있어서, 이게 빠지면 복사본은 오래된 데이터이거나 아예 깨진 상태가 됩니다. 어떤 커넥션도 쓰기를 하지 않는 상태에서 세 파일을 모두 복사하든가, 더 나은 방법으로 SQLite 백업 API(다음 장에서 다룹니다)를 쓰는 게 정답입니다.

SQLite 동시성: 쓰기는 하나, 읽기는 여럿

WAL을 켜도 동시 쓰기가 가능해지는 건 아닙니다. SQLite는 여전히 쓰기를 직렬화해서, 어느 순간이든 쓰기 락을 잡고 있는 트랜잭션은 단 하나뿐입니다. 달라진 건 쓰기가 읽기를 막지 않고, 읽기도 쓰기를 막지 않는다는 점이죠.

그래서 WAL 모드로 돌아가는 일반적인 웹 앱은 다음과 같이 동작합니다.

• 읽기가 많은 엔드포인트는 경합 없이 병렬로 실행됩니다.
• 쓰기 엔드포인트는 서로 잠깐 대기열에 줄을 서지만 읽기는 막지 않습니다.
• 오래 걸리는 읽기 작업(분석 쿼리, 데이터 내보내기 등)이 있어도 쓰기 작업이 기다릴 필요가 없습니다.

만약 두 커넥션이 동시에 쓰기를 시도하면, 두 번째 커넥션은 SQLITE_BUSY 에러를 받게 됩니다. 보통은 적절한 busy timeout을 설정해 해결합니다. 즉, SQLite에게 곧바로 포기하지 말고 잠깐 기다려보라고 알려주는 거죠.

CREATE TABLE messages (id INTEGER PRIMARY KEY, body TEXT);

PRAGMA busy_timeout=5000;

BEGIN IMMEDIATE;
INSERT INTO messages (body) VALUES ('대기 중인 쓰기');
COMMIT;

SELECT * FROM messages;

busy_timeout=5000은 "락이 걸려 있으면 최대 5초까지 기다렸다가 에러를 내라"는 뜻입니다. WAL과 함께 쓰면 실제 앱에서 마주치는 대부분의 경합을 무난히 처리할 수 있습니다. BEGIN IMMEDIATE는 첫 쓰기 시점이 아니라 트랜잭션 시작 시점에 쓰기 락을 잡아두는 방식인데, 여러 커넥션이 동시에 쓰기를 시도할 때 발생하는 락 업그레이드 데드락을 미리 차단해 줍니다.

SQLite 체크포인트: WAL을 본 DB에 반영하기

WAL 파일이 무한정 커질 수는 없겠죠. 체크포인트(checkpoint) 는 WAL에 쌓인 커밋된 페이지들을 메인 데이터베이스 파일로 옮겨 적은 뒤 WAL을 초기화하는 과정을 말합니다.

SQLite는 WAL이 약 1,000 페이지를 넘으면 자동으로 체크포인트를 수행합니다(기본 wal_autocheckpoint 값). 대부분의 앱에서는 이 기본값을 그대로 두어도 충분합니다. 직접 조정하거나 수동으로 트리거하고 싶다면 다음과 같이 하면 됩니다:

PRAGMA wal_autocheckpoint;
PRAGMA wal_autocheckpoint=2000;

PRAGMA wal_checkpoint(TRUNCATE);

wal_checkpoint 프라그마는 다음 모드 중 하나를 인자로 받습니다.

• PASSIVE — 읽기/쓰기를 방해하지 않는 선에서 가능한 만큼 체크포인트를 진행합니다. 기본값입니다.
• FULL — 진행 중인 쓰기 작업이 끝날 때까지 기다린 뒤, 커밋된 내용을 모두 체크포인트합니다.
• RESTART — FULL과 같지만, 새로 들어오는 리더가 기존 WAL을 더 이상 쓰지 못하도록 막습니다.
• TRUNCATE — RESTART와 같으면서, 추가로 WAL 파일 크기를 0바이트로 줄여 버립니다.

서버 환경에서는 이걸 직접 호출할 일이 거의 없습니다. 다만 데스크톱 앱처럼 종료 시 파일 크기를 깔끔하게 정리하고 싶다면, 마지막 커넥션을 닫기 전에 TRUNCATE 체크포인트를 한 번 돌려 주는 정도는 충분히 좋은 습관입니다.

WAL과 함께 쓰면 좋은 프라그마들

WAL만 켜도 좋지만, 실무에서 운영하는 앱들은 보통 여기에 몇 가지 설정을 더 얹어서 사용합니다.

PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;
PRAGMA busy_timeout=5000;
PRAGMA foreign_keys=ON;

간단히 살펴보면 이렇습니다:

• synchronous=NORMAL은 WAL 모드와 함께 쓰기에 가장 권장되는 조합입니다. 애플리케이션 크래시나 OS 크래시 상황에서도 안전하고, 오직 하필 그 순간에 정전이 일어났을 때만 가장 최근 트랜잭션 일부가 유실될 수 있습니다. 그런 경우에도 데이터베이스 자체는 일관성을 유지합니다. 기본값인 FULL은 더 안전하지만 체감될 만큼 느립니다.
• busy_timeout은 앞에서 다뤘습니다.
• foreign_keys=ON은 WAL과는 무관하지만, 모든 연결에서 켜 두는 게 좋습니다. SQLite는 하위 호환성 때문에 외래 키 제약을 기본적으로 꺼 둡니다.

이 설정들은 연결 단위로 적용됩니다(단, journal_mode만은 데이터베이스에 그대로 남습니다). 앱 코드에서 연결을 연 직후 바로 실행해 주세요.

SQLite WAL 모드가 적합하지 않은 경우

WAL은 기본적으로 가장 추천되는 모드지만, 몇 가지 상황에서는 다시 생각해 봐야 합니다:

• 네트워크 파일시스템. WAL은 데이터베이스에 접근하는 프로세스들 사이의 공유 메모리(mmap)에 의존합니다. NFS, SMB 같은 네트워크 파일시스템은 이를 안정적으로 지원하지 않습니다. 데이터베이스가 네트워크 공유 위에 있다면 롤백 저널 모드를 쓰는 편이 낫고, 사실 가장 좋은 선택은 SQLite를 네트워크 공유에 두지 않는 것입니다.
• 읽기 전용 매체. WAL은 -wal, -shm 파일을 만들어야 합니다. CD-ROM처럼 쓰기가 불가능한 매체에 있는 데이터베이스는 쓰기가 필요 없는 저널 모드를 쓰거나 mode=ro로 읽기 전용으로 열어야 합니다.
• 동시 읽기가 없는 단일 쓰기 배치 작업. 이 경우 WAL을 써도 손해는 없지만 얻는 것도 없습니다. 기본 롤백 저널로도 충분합니다.

웹 백엔드, 데스크톱 앱, 모바일 앱, 로컬 스토리지를 쓰는 임베디드 기기 등 전체 애플리케이션의 95%에는 WAL이 정답입니다.

실전에 가까운 설정 예시

대부분의 프로덕션 SQLite 환경에서 쓰는 형태를 그대로 실행 가능한 PRAGMA로 정리하면 다음과 같습니다:

PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;
PRAGMA busy_timeout=5000;
PRAGMA foreign_keys=ON;
PRAGMA temp_store=MEMORY;

CREATE TABLE IF NOT EXISTS events (
    id INTEGER PRIMARY KEY,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    payload TEXT NOT NULL
);

INSERT INTO events (payload) VALUES ('login'), ('signup'), ('purchase');
SELECT id, created_at, payload FROM events;

temp_store=MEMORY로 설정하면 임시 테이블과 인덱스를 디스크가 아닌 RAM에 저장합니다. 메모리에 여유가 있다면 공짜로 얻을 수 있는 작은 성능 향상입니다.

애플리케이션의 DB 초기화 코드에서 커넥션을 열 때 한 번만 이 설정을 적용해 두면, SQLite 기반 앱이 동시 부하 상황에서 무난하게 동작하기 위한 대부분의 준비가 끝난 셈입니다.

다음: 백업과 복원

이제 DB 파일에 -wal과 -shm 파일이 같이 따라다니게 되었기 때문에, 단순히 파일을 복사하는 방식은 더 이상 안전한 백업이 아닙니다. 다음 장에서는 운영 중인 SQLite 데이터베이스를 제대로 백업하는 방법을 다룹니다. .backup 명령어, 온라인 백업 API, 그리고 앱을 내리지 않고도 일관된 스냅샷을 떠야 할 때 쓸 수 있는 방법까지 함께 살펴보겠습니다.