SQLite WAL에서 SQLITE_BUSY_SNAPSHOT이 날 때: 읽기부터 트랜잭션 재시도

SQLite WAL에서 SELECT로 값을 읽은 뒤 다른 연결이 먼저 커밋했다면, 같은 트랜잭션의 UPDATE가 SQLITE_BUSY_SNAPSHOT으로 실패할 수 있습니다. 같은 UPDATE만 반복하기보다 이전 읽기 트랜잭션을 끝내고 새 값으로 읽기·계산·쓰기를 다시 수행해야 하는 경우입니다. 먼저 일반적인 잠금 경합과 오류 코드를 구분하세요.

아래 실습은 임시 DB의 값 10을 두 연결로 다룹니다. 첫 연결은 10을 읽은 상태를 유지하고, 둘째 연결이 11을 커밋합니다. 첫 연결의 오래된 쓰기는 두 번 모두 코드 517로 실패하며, 전체 읽기·수정 흐름을 새로 시작하면 최종값이 12가 됩니다. 운영 데이터베이스를 수정하거나 외부 요청을 재전송하지 않습니다.

한 연결의 오래된 읽기 스냅샷과 다른 연결이 커밋한 새 값이 공존하는 개념 그림 · 실행 수치나 실제 서비스 화면이 아닌 개념 그림입니다.

WAL에서 읽기 스냅샷이 유지되는 이유

WAL 모드에서 읽기 트랜잭션은 자신이 시작한 읽기 상태를 유지할 수 있고, 다른 연결은 새 변경을 커밋할 수 있습니다. 오래된 읽기가 나중 쓰기로 그대로 승격되면 이미 커밋된 변경과 충돌하므로 SQLITE_BUSY_SNAPSHOT이 발생할 수 있습니다. 이 상황을 읽은 값이 자동으로 최신 값이 됐다고 생각하면 놓칩니다.

오류 메시지가 database is locked처럼 보여도 원인을 메시지만으로 합치지 마세요. 이 예제의 Python sqlite3 예외에서는 sqlite_errorname과 sqlite_errorcode를 읽습니다. SQLITE_BUSY_SNAPSHOT은 확장 코드 517이고 일반 SQLITE_BUSY 5와 구분합니다. 실제 드라이버가 어떤 상세 코드를 제공하는지도 확인해야 합니다.

오래된 읽기 상태가 최신 데이터의 쓰기로 승격되지 못하는 스냅샷 충돌 개념 그림 · 실행 수치나 실제 서비스 화면이 아닌 개념 그림입니다.

두 연결의 순서를 고정해 재현합니다

Python 3.14.7·SQLite 3.53.4에서 실행했습니다. wal_snapshot_demo.py로 저장해 python3 wal_snapshot_demo.py로 실행하세요. 같은 스레드에서 두 연결의 명령 순서를 정했으므로 임의의 sleep이나 실행 속도에 기대지 않습니다. isolation_level=None으로 묵시적 트랜잭션 시작을 피하고 BEGIN·ROLLBACK·COMMIT을 SQL로 명시합니다.

import sqlite3
from pathlib import Path
from tempfile import TemporaryDirectory

# Two connections, one thread, deterministic order; temporary local DB only.
with TemporaryDirectory() as directory:
    path = Path(directory) / 'snapshot.db'
    reader = sqlite3.connect(path, isolation_level=None, timeout=0)
    writer = sqlite3.connect(path, isolation_level=None, timeout=0)
    try:
        mode = reader.execute('PRAGMA journal_mode=WAL').fetchone()[0]
        assert mode.lower() == 'wal'
        reader.execute('CREATE TABLE counter(id INTEGER PRIMARY KEY, value INTEGER)')
        reader.execute('INSERT INTO counter VALUES (1, 10)')
        reader.execute('BEGIN')
        old_value = reader.execute('SELECT value FROM counter WHERE id=1').fetchone()[0]
        writer.execute('UPDATE counter SET value=value+1 WHERE id=1')
        print('reader snapshot=', old_value)
        print('writer committed=', writer.execute(
            'SELECT value FROM counter WHERE id=1').fetchone()[0])
        for attempt in [1, 2]:
            try:
                reader.execute('UPDATE counter SET value=? WHERE id=1', (old_value + 1,))
            except sqlite3.OperationalError as exc:
                print('same transaction attempt=', attempt,
                      'error=', exc.sqlite_errorname, 'code=', exc.sqlite_errorcode)
                assert exc.sqlite_errorcode == sqlite3.SQLITE_BUSY_SNAPSHOT
            else:
                raise AssertionError('stale snapshot unexpectedly upgraded')
        reader.execute('ROLLBACK')
        reader.execute('BEGIN IMMEDIATE')
        fresh_value = reader.execute('SELECT value FROM counter WHERE id=1').fetchone()[0]
        reader.execute('UPDATE counter SET value=? WHERE id=1', (fresh_value + 1,))
        reader.execute('COMMIT')
        final = writer.execute('SELECT value FROM counter WHERE id=1').fetchone()[0]
        print('retry read=', fresh_value, 'final=', final)
        assert final == 12
    finally:
        if reader.in_transaction:
            reader.execute('ROLLBACK')
        reader.close()
        writer.close()

실제 출력: 같은 오래된 트랜잭션에서는 반복해도 실패합니다

reader snapshot= 10
writer committed= 11
same transaction attempt= 1 error= SQLITE_BUSY_SNAPSHOT code= 517
same transaction attempt= 2 error= SQLITE_BUSY_SNAPSHOT code= 517
retry read= 11 final= 12

첫 SELECT의 10은 reader가 보유한 스냅샷의 값입니다. writer의 UPDATE는 그 연결에서 커밋되어 11이 됩니다. reader는 같은 트랜잭션에서 예전 값에 1을 더한 11을 쓰려 하지만 두 시도 모두 거절됩니다. 뒤의 ROLLBACK이 이전 읽기를 끝내고, 새 트랜잭션의 SELECT가 11을 읽어 12를 만듭니다.

이전 트랜잭션을 끝내고 새로 읽고 계산해 앞선 변경을 보존하는 재시도 개념 그림 · 실행 수치나 실제 서비스 화면이 아닌 개념 그림입니다.

ROLLBACK 뒤에도 옛 Python 값을 쓰면 갱신을 잃을 수 있습니다

ROLLBACK만 호출한 뒤 메모리에 남아 있는 old_value + 1을 다시 쓰는 것은 이번 의도에 맞는 재시도가 아닙니다. 앞선 증가가 반영된 11을 다시 읽고 그 값에서 1을 더해야 두 증가가 보존됩니다. 어떤 SELECT와 계산을 함께 다시 해야 하는지 업무 단위로 정해야 합니다.

새 시도에서 BEGIN IMMEDIATE를 사용한 것은 쓰기 의도를 먼저 확보한 뒤 읽기·수정을 하기 위한 선택입니다. 다른 쓰기 트랜잭션이 이미 있으면 시작부터 SQLITE_BUSY로 실패할 수 있고 모든 경합이 사라지는 것은 아닙니다. 일반 잠금 경합의 대기 정책과 스냅샷 갱신을 구분해야 합니다.

대기 시간과 재시도 정책의 적용 범위

busy_timeout이나 연결 timeout은 일부 잠금 경합에서 기다리는 방법입니다. 오래된 읽기 스냅샷 자체를 최신 상태로 바꾸지는 않습니다. 이미 스냅샷 충돌이 확인된 상황에서 같은 트랜잭션의 문장만 반복하는 것을 복구로 간주하지 않습니다. 이 실습은 timeout=0으로 즉시 오류를 관찰하며 운영용 대기 시간을 추천하는 예제가 아닙니다.

실제 프로그램에서는 시도 횟수·전체 제한 시간·다른 오류의 중단 조건을 정하고, 실패한 트랜잭션을 정리한 뒤 재시도합니다. 이메일·외부 API·파일 생성 같은 부작용이 읽기·수정 과정에 섞여 있다면 무조건 통째로 반복해서는 안 됩니다. DB 롤백이 이미 일어난 외부 효과까지 되돌려 주지는 않습니다.

Python의 autocommit=True 모드에서 Connection.commit·rollback의 동작까지 이 코드와 같다고 해석하지 마세요. 이 예제는 SQL 트랜잭션 제어를 명시합니다. 공유 캐시·read_uncommitted·운영 DB의 모드 변경·백업 중 복사 방법도 실험 범위 밖입니다. WAL은 항상 여러 쓰기를 동시에 허용하는 모드가 아닙니다.

확인할 결과물과 다음 단계

오류 이름·코드, 이전 읽기 값, 다른 연결의 커밋 값, 새 트랜잭션의 재조회 값과 최종값을 남기면 재시도 범위를 검토할 수 있습니다. 실제 데이터에 적용하기 전 복구 사본이 필요하다면 실행 중 SQLite 백업과 WAL 파일의 범위를 먼저 확인하세요.

스냅샷 충돌에서는 문장만이 아니라 읽기·계산의 기준도 새로 정해야 합니다. 오류 코드를 나누고 새 트랜잭션에서 필요한 값을 다시 읽되, 외부 부작용과 재시도 한도를 함께 설계하세요.