SQLite는 한 번에 하나만 쓴다 — Writer 직렬화와 집계 DB 분리

개인 프로젝트 백엔드는 Rust(Axum) + SQLite입니다.

 

캐릭터 검색, 경험치 히스토리, 로그인 세션처럼 요청마다 짧게 쓰는 OLTP와, 유니온 랭킹을 긁어 월드·직업·유니온 구간 인구를 다시 만드는 배치 집계가 한 프로세스에 공존합니다.

 

처음엔 db 파일 하나면 충분해 보였습니다. 트래픽이 붙고 집계 배치가 커지자 문제가 분명해졌습니다.

 

SQLite의 writer는 하나인데, 웹 요청의 짧은 INSERT와 집계의 긴 DROP/INSERT가 같은 락을 놓고 줄을 섭니다.

 

이 글은 그때 넣은 두 가지 장치를 정리합니다.

1. 앱 레벨 writer 직렬화
2. 집계 전용 DB 분리

SQLite가 실제로 막아 주는 것

WAL(Write-Ahead Logging)을 켜면 읽기는 쓰기와 겹칠 수 있습니다.

 

그래도 writer는 데이터베이스당 하나입니다.

 

여러 Tokio 태스크가 동시에 INSERT를 날리면 둘 중 하나는 SQLITE_BUSY(database is locked)를 받습니다.

 

busy_timeout으로 잠깐 기다리게 할 수는 있습니다. 개인 프로젝트도 5초를 넣었습니다.

.busy_timeout(Duration::from_secs(5))

타임아웃은 완충일 뿐입니다.

 

웹 서버처럼 요청이 겹치는 환경에서는 “누가 먼저 쓰나”를 SQLite 재시도에 맡기지 않는 편이 낫습니다.

 

대기 큐를 앱이 소유해야 타임아웃·재시도·데드락을 예측할 수 있습니다.

 

커넥션 풀도 읽기 위주로 작게 유지합니다. 주석 그대로입니다.

SQLite는 한 번에 하나의 쓰기만 허용합니다. 쓰기는 db_write_lock으로 앱 레벨 직렬화하므로, 풀은 읽기 위주로 작게 유지합니다.

앱에서 쓰기를 한 줄로 세우기

전역 상태에 Tokio Mutex<()>를 두고, 모든 쓰기를 그 락 안에서만 실행합니다.

/// SQLite는 단일 writer만 허용하므로, 앱 레벨에서 쓰기를 직렬화합니다.
pub db_write_lock: Arc<Mutex<()>>,

pub async fn with_db_write<R>(&self, fut: impl Future<Output = R>) -> R {
    let _guard = self.db_write_lock.lock().await;
    fut.await
}

포인트는 두 가지입니다.

 

읽기는 락을 잡지 않습니다. user_info 조회, 검색수 TOP10, 세션 확인은 풀로 바로 SELECT합니다.

 

WAL 아래에서는 읽기가 writer 큐에 서지 않아야 검색 페이지가 살아 있습니다.

 

인자 Future는 락을 잡은 뒤에만 poll합니다. async { sqlx::query(...).execute(...).await }를 넘기면, 블록을 만드는 시점에는 I/O가 시작되지 않습니다.

 

락을 얻은 다음 fut.await할 때 비로소 SQL이 나갑니다.

 

락 밖에서 쓰기를 시작해 두고 락만 기다리는 실수를 줄이는 형태입니다.

 

호출부는 대략 이런 식입니다.

state
    .with_db_write(upsert_user_info_in(state.db_pool.as_ref(), nickname, ocid))
    .await

검색 한 번에 캐릭터를 기억하고, 경험치 행을 넣고, 전투력을 갱신하고, 세션을 저장합니다.

 

전부 이 함수를 통과합니다.

 

SQLite writer 락과 앱 큐가 1:1이 됩니다.

중첩 락은 데드락입니다

Tokio Mutex는 재진입(reentrant)이 아닙니다.

 

이미 with_db_write 안에서 다시 with_db_write를 호출하면, 같은 태스크가 자신이 잡은 락을 기다리다가 멈춥니다.

 

전투력 갱신 경로에 그 함정이 있었습니다.

 

“없으면 user_info를 만들고, 있으면 max_power_level만 올립니다”를 헬퍼 두 개로 나누면, 둘 다 쓰기여서 각각 락을 잡으려 합니다.

 

그래서 upsert와 update를 한 잠금, 한 문장으로 합쳤습니다.

INSERT INTO user_info (nickname, ocid, max_power_level)
VALUES (?1, ?2, ?3)
ON CONFLICT(ocid) DO UPDATE SET
    nickname = excluded.nickname,
    max_power_level = MAX(
        COALESCE(user_info.max_power_level, 0),
        excluded.max_power_level
    )

규칙이 단순해집니다.

• 쓰기의 진입점은 with_db_write 하나
• 그 안에서는 SQL만 실행하고, 다시 락을 잡지 않습니다
• “읽고 나서 쓰기”가 필요하면 읽기는 락 밖에서, 쓰기는 한 번에

네트워크는 병렬, DB 쓰기만 직렬

경험치 히스토리는 날짜별 스냅샷을 Nexon API에서 가져옵니다.

 

HTTP는 buffer_unordered로 겹칩니다.

 

응답을 받은 뒤에야 with_db_write로 한 줄씩 넣습니다.

Nexon fetch  ──┐
Nexon fetch  ──┼── 완료 분부터 ──► with_db_write (한 줄)
Nexon fetch  ──┘

외부 I/O를 writer 락 안에 넣으면 검색 upsert까지 같이 멈춥니다.

 

“느린 쪽은 병렬, SQLite가 못 하는 쪽만 직렬”이 이 패턴의 전부입니다.

파일을 나누면 writer가 두 개가 됩니다

SQLite의 writer 단위는 프로세스가 아니라 데이터베이스 파일입니다. 파일을 나누면 락도 나뉩니다.

one.db    검색·세션·경험치     one_db_write_lock
two.db    인구 분포·스냅샷     two_db_write_lock

상태도 락도 쌍으로 둡니다.

pub one_db_pool: Arc<DbPool>,
pub one_db_write_lock: Arc<Mutex<()>>,

pub two_db_pool: Arc<DbPool>,
pub two_db_write_lock: Arc<Mutex<()>>,

집계 저장은 with_two_db_write만 탑니다.

 

배치가 테이블을 갈아끼우는 동안에도 주 DB 쓰기는 다른 파일의 writer로 진행됩니다.

 

배포 머신은 여전히 SQLite 두 파일입니다.

 

Postgres를 들이지 않고 OLTP/OLAP 경계를 파일 단위로 친 것입니다.

집계 DB 안에서도 읽기 공백을 줄입니다

파일 분리만으로 부족합니다.

 

replace_all이 기존 테이블을 비우고 다시 채우면, 그 사이에 통계 API가 빈 결과를 줄 수 있습니다.

 

그래서 shadow 테이블에 넣고 이름만 바꿉니다.

CREATE TABLE user_distribution__new (...)
INSERT chunk (200행씩, 행당 바인드 7개)
DROP TABLE user_distribution
ALTER TABLE user_distribution__new RENAME TO user_distribution
인덱스 재생성
COMMIT

커밋 전까지 읽기는 옛 테이블을 봅니다.

 

실패하면 __new만 버리고 다음 실행에서 DROP TABLE IF EXISTS user_distribution__new로 잔여를 치웁니다.

 

200행 청크는 SQLite 변수 한도(기본 32766)를 피하면서 round-trip을 줄이려는 숫자입니다.

 

집계 테이블(user_distribution_by_world 등)은 원본을 매번 스캔하지 말라는 사전 계산입니다. 공개 API는 이 작은 테이블과 날짜 스냅샷만 읽습니다.

 

그 위에는 TTL 인메모리 캐시가 한 겹 더 있습니다.

 

같은 날짜 쌍 요청이 동시에 들어오면 mutex로 계산을 한 번만 합니다(캐시 스탬피드 방지).

 

배치가 무거워도 사용자 통계 화면의 읽기 경로는 “사전 집계 + TTL”입니다.

 

writer 직렬화와 파일 분리는 그 배치가 다른 기능의 쓰기를 막지 않게 하려는 장치입니다.

정리하면

SQLite를 웹 API 뒤에 둘 때 자주 하는 착각은 “WAL이면 동시 쓰기도 됩니다”와 “풀을 키우면 writer가 늘어납니다”입니다. 둘 다 아닙니다.

 

개인 프로젝트에서 유효했던 규칙은 이렇습니다.

1. 쓰기는 앱이 한 줄로 세웁니다. busy_timeout은 백스톱입니다.
2. 읽기는 그 줄에 서지 않습니다. WAL의 이득을 여기 씁니다.
3. 락은 재진입이 아닙니다. 중첩 with_db_write 대신 SQL을 합칩니다.
4. 느린 I/O를 writer 락 안에 넣지 않습니다. HTTP는 병렬, INSERT만 직렬입니다.
5. 긴 배치와 짧은 upsert를 같은 파일에 두지 않습니다. 파일이 곧 writer 경계입니다.
6. 통째로 갈아끼울 때는 shadow + rename으로 읽기 공백을 줄입니다.

“검색 한 번의 짧은 쓰기”와 “하루 한 번의 큰 집계”가 섞인 단일 서버라면, SQLite 두 파일과 mutex 두 개로도 경계를 충분히 그을 수 있습니다.

 

핵심은 엔진을 바꾸는 것이 아니라 누가 얼마나 오래 writer를 붙드는지를 코드에 드러내는 일입니다.