SQLite database is locked: WAL만 켜지 말고 busy_timeout 확인하기

WAL 모드인데도 쓰기가 실패한 이유

SQLite를 WAL 모드로 바꿨는데도 Python 로그에 database is locked가 남을 수 있다. WAL이 읽기와 쓰기의 동시 진행을 돕는 것은 맞지만, 여러 writer가 동시에 커밋하도록 바꾸는 설정은 아니다. SQLite 공식 WAL 문서도 reader와 writer는 함께 진행할 수 있지만 writer는 한 번에 하나라고 설명한다.

이번 실험에서는 첫 연결이 BEGIN IMMEDIATE로 쓰기 트랜잭션을 시작하고 한 행을 넣은 채 커밋을 미뤘다. 그동안 다른 연결에서 읽기 한 번과 timeout이 서로 다른 쓰기 세 번을 실행했다. 실제 서비스 DB 대신 임시 디렉터리의 합성 events 테이블만 사용했다.

결과는 단순했다. 읽기는 0.1ms에 끝나 이미 커밋된 baseline만 봤다. timeout=0인 writer는 0.1ms 만에 잠금 오류가 났고, timeout=0.2인 writer는 203.7ms를 기다린 뒤 같은 오류가 났다. 반대로 기존 writer를 450ms 뒤 해제하고 timeout=1.5를 준 쓰기는 536.5ms 뒤 커밋됐다.

같은 잠금에서 timeout만 바꿔 비교했다

0.2초 설정이 정확히 200.0ms에 끝나지 않은 것은 이상 현상이 아니다. busy timeout은 잠금이 풀릴 때까지 정해진 누적 시간 동안 기다리게 하는 상한이며, 스레드 스케줄링과 내부 대기 간격 때문에 실제 경과 시간은 설정값과 조금 다를 수 있다. timeout보다 먼저 잠금이 풀린 1.5초 사례는 상한까지 기다리지 않고 성공했다.

Python timeout은 초, PRAGMA busy_timeout은 밀리초다

Python 3.10 sqlite3.connect의 timeout 인자는 잠긴 테이블이 풀리기를 연결이 기다리는 초 단위 값이다. 기본값은 5초다. SQLite의 PRAGMA busy_timeout은 같은 종류의 대기 시간을 밀리초 단위로 설정한다. 단위가 다르므로 1500을 connect의 timeout에 그대로 넣으면 1.5초가 아니라 1500초가 된다.

import sqlite3

# connect의 timeout은 초
connection = sqlite3.connect('app.sqlite3', timeout=1.5)

# PRAGMA 값은 밀리초로 조회된다
print(connection.execute('PRAGMA busy_timeout').fetchone())

try:
    connection.execute('BEGIN IMMEDIATE')
    connection.execute('INSERT INTO jobs(name) VALUES (?)', ('sample',))
    connection.commit()
except sqlite3.OperationalError:
    connection.rollback()
    raise
finally:
    connection.close()

위 코드는 단위와 rollback 위치를 보여주는 조각일 뿐 jobs 테이블을 만드는 완전한 프로그램은 아니다. 재현은 글 아래의 실험 스크립트를 사용한다. 애플리케이션에서 PRAGMA busy_timeout을 다시 실행하면 연결의 기존 busy handler 설정을 바꿀 수 있으므로, 연결을 만든 라이브러리와 초기화 코드를 함께 확인해야 한다.

timeout을 늘리기 전에 잠금을 오래 쥔 구간을 찾는다

잠금 오류가 보이면 먼저 어느 코드가 트랜잭션을 열고 언제 commit 또는 rollback하는지 확인한다. 트랜잭션 안에서 HTTP 요청, 큰 파일 처리, 사용자 입력 대기처럼 DB와 무관한 일을 수행하면 그 시간만큼 다른 writer의 대기도 길어진다. 예외 경로에서 rollback이나 close가 누락됐는지도 살펴본다.

timeout을 늘리는 조치는 정상적인 짧은 겹침을 흡수할 때 유효하다. 그러나 10초짜리 트랜잭션을 30초 동안 기다리게 만드는 식으로 원인을 가리면 요청 지연과 대기 작업만 늘 수 있다. 먼저 실제 트랜잭션 시간을 측정하고 그보다 조금 긴 한도를 정한 뒤, 한도를 넘겼을 때 사용자에게 어떻게 알리고 작업을 어떻게 재시도할지 정한다.

BEGIN IMMEDIATE는 충돌을 앞에서 드러낸다

SQLite 트랜잭션 문서에 따르면 BEGIN IMMEDIATE는 쓰기 트랜잭션을 즉시 시작한다. 다른 writer가 이미 활성 상태라면 시작 지점에서 SQLITE_BUSY가 날 수 있다. 이번 실험은 잠금 시점을 확실히 만들기 위해 이 방식을 사용했다. 모든 쓰기를 IMMEDIATE로 바꾸라는 권장은 아니다.

기본 DEFERRED 트랜잭션은 첫 SQL의 종류에 따라 읽기 또는 쓰기 트랜잭션이 시작되고, 읽은 뒤 쓰기로 승격하는 순간 충돌할 수도 있다. 애플리케이션의 오류 위치가 INSERT가 아니라 트랜잭션 시작 또는 갱신 지점인지 로그에 남기면 대기 시간을 조정할 근거가 생긴다.

WAL 전환은 별도의 운영 결정이다

이번 실험은 처음부터 WAL인 DB에서 reader와 writer의 동시 진행, writer끼리의 직렬화를 관찰했다. 따라서 결과만으로 기존 rollback journal DB를 WAL로 바꾸면 장애가 해결된다고 말할 수 없다. WAL에는 checkpoint 운영이 필요하고, 같은 호스트의 프로세스가 공유 메모리를 사용할 수 있어야 한다. 네트워크 파일시스템에는 그대로 적용할 수 없다.

journal_mode 변경을 검토한다면 사용하는 SQLite 버전, 파일시스템, 백업 방식, checkpoint 지연과 디스크 증가를 별도로 시험한다. 특히 DB 본체만 복사하는 백업은 WAL에 남은 커밋을 빠뜨릴 수 있으므로 기존 백업 글의 복구 대조 절차도 함께 확인해야 한다.

이번 측정으로 말할 수 있는 범위

확인한 환경은 Linux, Python 3.10.12, SQLite 3.37.2의 단일 프로세스·여러 연결이다. 최종 무결성 검사는 ok였고, 실패한 두 contender의 행은 없으며 성공한 writer의 행을 포함해 5개가 남았다. 미커밋 행이 reader에게 보이지 않은 것도 확인했다.

다중 프로세스와 여러 서버, ORM 연결 풀, 실제 쿼리 부하, 네트워크 파일시스템은 시험하지 않았다. 0.2초나 1.5초를 권장값으로 제시하는 자료도 아니다. 자신의 서비스에서는 쓰기 시간 분포와 허용 가능한 응답 지연을 측정해 timeout을 정하고, 충돌이 반복되면 쓰기 구조나 저장소 선택까지 다시 검토해야 한다.

python3 lab-sqlite-busy-timeout.py --output new-observations.json