SQLite WAL 실전: 잠금 재현부터 안전한 백업과 모드 되돌리기까지
로컬 데이터베이스의 읽기·쓰기 경쟁을 줄이고 싶은 개발자를 위한 실습입니다. Python 예제로 작성자 잠금, 체크포인트, 백업 복원을 확인하고 WAL 수정 버전과 파일 권한의 경계를 짚습니다.
SQLite의 WAL 모드는 읽기와 쓰기가 서로 기다리는 시간을 줄이는 유용한 선택입니다. 하지만 ‘설정 한 줄로 동시 쓰기가 무제한이 된다’는 기능은 아닙니다. 동시에 쓸 수 있는 작성자는 여전히 한 명이며, 오래 열린 읽기 트랜잭션과 잘못된 파일 복사는 운영 문제로 이어질 수 있습니다. 작은 내부 도구나 로컬 작업 기록을 저장하는 개발자를 위해 WAL 활성화부터 잠금 재현, 일관된 백업과 원상복구까지 한 흐름으로 살펴봅니다.
버전부터 확인
이 글은 2026년 9월 30일 SQLite 공식 WAL 문서와 Python sqlite3 문서를 직접 확인했습니다. 중요한 버전 주의점이 있습니다. SQLite 공식 문서는 드문 WAL-reset 경합 문제의 수정이 3.51.3 이상에 포함되며, 일부 이전 계열에는 3.44.6과 3.50.7 백포트가 있다고 명시합니다. 여러 연결이 같은 파일에 쓰기 또는 체크포인트를 동시에 수행하는 배포라면 수정 포함 여부를 확인해야 합니다. 단순히 운영체제가 최신이거나 Python이 최신이라고 판단하지 말고 실제 연결 라이브러리의 버전을 봅니다.
아래 예제는 macOS에서 Python 3.9.6과 SQLite 3.51.0으로 순차 실행해 출력과 복원 결과를 확인했습니다. 이 환경은 위 수정 버전이 아니므로 운영용 동시성 안전성을 검증했다고 주장하지 않습니다. 실제 동시 실행 경합이나 장애 복구·전원 손실 실험은 수행하지 않았습니다. 운영에는 수정된 SQLite를 포함한 배포판을 사용하세요. Python 3.12부터 추가된 autocommit 인자와 혼동을 피하기 위해 예제는 구버전에서도 가능한 isolation_level=None과 명시적 SQL 트랜잭션을 사용합니다.
설치와 작업 폴더
Python 3와 sqlite3 모듈, 쓰기 가능한 로컬 디스크 폴더가 필요합니다. Python은 공식 배포판 또는 조직이 승인한 패키지 관리자로 설치합니다. Python 공식 설치 페이지의 운영체제별 안내를 따르고, macOS의 시스템 Python을 덮어쓰지 않습니다. 대부분의 CPython 배포판에는 sqlite3가 포함되므로 pip install sqlite3는 하지 않습니다. 모듈이 없다면 Python 배포판의 선택 모듈 지원을 확인하세요.
macOS·Linux의 셸에서 아래를 실행합니다. Windows PowerShell에서는 python3 대신 설치 상태에 따라 py 또는 python을 쓰고, Python 코드 블록을 같은 파일명으로 저장하면 됩니다. 셸 폴더 생성과 Windows 설치 단계는 공식 문서에 근거한 안내이며 이 작업에서 새 Python 설치를 실행한 것은 아닙니다.
mkdir sqlite-wal-demo
cd sqlite-wal-demo
python3 -c "import sys, sqlite3; print(sys.version); print(sqlite3.sqlite_version)"기존 업무 DB가 아닌 빈 폴더에서 시작합니다. 예제의 events.db와 backup.db는 학습용 파일입니다. 작업 폴더를 바꾸더라도 두 파일이 운영 DB를 가리키지 않게 확인합니다. NFS·SMB 같은 네트워크 파일시스템에 두지 않습니다. WAL은 같은 호스트의 공유 메모리와 파일 잠금 지원을 전제로 하며, 여러 서버가 같은 공유 파일을 열게 하는 구성은 적합하지 않습니다.
최소 실행
다음 코드를 wal_demo.py로 저장합니다. 처음 실행에서 WAL 활성화, 테이블 생성, 이벤트 한 건 저장과 조회를 확인합니다. 같은 폴더에서 다시 실행해도 INSERT OR IGNORE 때문에 같은 키가 중복되지 않습니다. SQL 값은 문자열 이어붙이기 대신 매개변수로 전달합니다.
import sqlite3
con = sqlite3.connect("events.db", isolation_level=None, timeout=2.0)
try:
mode = con.execute("PRAGMA journal_mode=WAL").fetchone()[0]
if mode != "wal":
raise RuntimeError(f"WAL activation failed: {mode}")
con.execute("PRAGMA synchronous=FULL")
con.execute("CREATE TABLE IF NOT EXISTS events (id TEXT PRIMARY KEY, body TEXT NOT NULL)")
con.execute("BEGIN IMMEDIATE")
try:
con.execute("INSERT OR IGNORE INTO events VALUES (?, ?)", ("evt-001", "received"))
con.execute("COMMIT")
except Exception:
con.execute("ROLLBACK")
raise
print("journal_mode=", mode)
print(con.execute("SELECT id, body FROM events ORDER BY id").fetchall())
finally:
con.close()python3 wal_demo.py성공하면 journal_mode= wal과 [('evt-001', 'received')]가 나옵니다. BEGIN IMMEDIATE는 쓰기 트랜잭션 시작 때 작성자 잠금을 확보합니다. HTTP 호출이나 사용자 입력을 이 트랜잭션 안에서 기다리지 않습니다. 처리에 필요한 외부 데이터를 먼저 확보하고 DB 변경만 짧게 수행하세요. 예외가 나면 롤백하고 연결은 finally에서 닫습니다.
잠금을 직접 본다
다음 코드를 lock_demo.py로 저장하고 python3 lock_demo.py로 실행합니다. 첫 연결이 쓰기 잠금을 잡은 동안 두 번째 연결이 쓰기를 시도하도록 순서대로 구성했습니다. 실제 병렬 스레드 테스트는 아니지만 WAL이 복수 작성자를 동시에 허용하지 않는다는 점을 확인할 수 있습니다.
import sqlite3
first = sqlite3.connect("events.db", isolation_level=None, timeout=0.2)
second = sqlite3.connect("events.db", isolation_level=None, timeout=0.2)
try:
first.execute("BEGIN IMMEDIATE")
first.execute("INSERT OR IGNORE INTO events VALUES (?, ?)", ("evt-002", "pending"))
print("reader_count=", second.execute("SELECT COUNT(*) FROM events").fetchone()[0])
try:
second.execute("BEGIN IMMEDIATE")
except sqlite3.OperationalError as error:
print("writer_blocked=", str(error))
else:
second.execute("ROLLBACK")
raise RuntimeError("Expected writer contention")
first.execute("ROLLBACK")
finally:
second.close()
first.close()최소 실행 직후에는 reader_count= 1, writer_blocked= database is locked가 예상 결과입니다. 두 번째 연결은 첫 연결의 미커밋 행을 보지 않지만 기존 데이터는 읽을 수 있습니다. 첫 연결을 롤백하므로 evt-002는 저장되지 않습니다. 실제 서비스에서는 잠금 예외를 무한 반복하지 말고 최대 횟수와 총 대기 시간을 제한합니다. 이벤트 ID의 고유 제약처럼 중복 실행을 견딜 수 있는 설계도 필요합니다.
설정의 범위
journal_mode=WAL은 DB 파일에 지속됩니다. 연결을 다시 열어도 WAL이 유지됩니다. 반면 timeout과 synchronous 같은 연결 수준 설정은 각 연결에서 의도에 맞게 적용·확인합니다. Python의 timeout은 잠금이 풀리기를 기다리는 시간이며 쿼리의 전체 실행 제한 시간이 아닙니다. 이를 늘리는 것만으로 장시간 트랜잭션이 해결되지는 않습니다.
예제는 내구성을 우선해 synchronous=FULL을 사용했습니다. WAL에서 NORMAL은 동기화 비용을 줄이는 선택이지만 전원 손실·하드 리셋 뒤 최근 커밋이 되돌아갈 수 있으므로 데이터의 중요도에 맞춰 판단해야 합니다. 공식 문서에 따르면 자동 체크포인트 기본 임계값은 1,000페이지이고 빌드 설정에 따라 달라질 수 있습니다. 페이지 수를 파일 크기의 고정 바이트 수로 착각하지 않습니다.
긴 읽기 트랜잭션이 오래된 스냅샷을 유지하면 체크포인트가 충분히 진행하지 못해 WAL이 커질 수 있습니다. 결과를 읽다가 외부 API를 기다리는 코드, 닫히지 않은 커서와 연결을 먼저 점검합니다. 무조건 체크포인트를 더 자주 호출하면 쓰기 지연이 늘어날 수 있으므로 파일 크기, 트랜잭션 시간, 지연 분포를 함께 측정합니다.
백업과 복원 검증
WAL의 최신 변경은 본체 DB가 아니라 -wal 파일에 있을 수 있습니다. 실행 중인 DB에서 본체 파일 하나만 복사하면 최신 커밋을 놓칠 수 있습니다. -wal이나 -shm을 임의로 삭제하는 것도 해결책이 아닙니다. 여기서는 SQLite 백업 API를 감싼 Python backup()으로 일관된 복사본을 만듭니다. 기존 백업을 덮어쓰지 않도록 목적 파일 존재 여부를 거부합니다.
백업 DB도 WAL 모드를 유지할 수 있어 검사 연결에 보조 파일 생성 권한이 필요합니다. 예제는 기존 파일만 여는 mode=rw를 사용하고 실제 데이터 변경 SQL은 실행하지 않습니다. 다음 코드를 backup_demo.py에 저장한 뒤 python3 backup_demo.py로 실행하세요. PRAGMA integrity_check는 구조적 무결성을 검사할 뿐 업무 데이터의 정확성을 증명하지 않으므로, 이벤트 개수와 실제 행도 함께 확인합니다.
from pathlib import Path
import sqlite3
if Path("backup.db").exists():
raise SystemExit("backup.db already exists; choose a new backup filename")
source = sqlite3.connect("events.db", isolation_level=None)
target = sqlite3.connect("backup.db", isolation_level=None)
try:
source.backup(target)
finally:
target.close()
source.close()
restored = sqlite3.connect("file:backup.db?mode=rw", uri=True)
try:
result = restored.execute("PRAGMA integrity_check").fetchone()[0]
rows = restored.execute("SELECT id, body FROM events ORDER BY id").fetchall()
print("integrity=", result)
print("restored_rows=", rows)
if result != "ok" or rows != [("evt-001", "received")]:
raise RuntimeError("Backup verification failed")
finally:
restored.close()학습 예제에서는 integrity= ok와 저장했던 한 행이 나와야 합니다. 실제 복원은 서비스를 정지하고 기존 DB를 보존한 뒤 검증된 백업을 새 경로에 배치해 접근 경로를 바꾸는 절차로 진행합니다. 사용자 트래픽을 받는 DB 위에 파일을 바로 덮어쓰지 않습니다. 실제 운영 서비스 교체는 이 글에서 실행하지 않았습니다.
권한과 비용
SQLite 자체는 별도 DB 서버 프로세스가 필요 없지만 파일 권한은 애플리케이션의 책임입니다. DB 본체와 WAL·공유 메모리 파일을 만들 수 있도록 디렉터리 권한이 필요합니다. 권한 오류를 해결한다고 폴더 전체를 누구나 쓸 수 있게 만들지 않습니다. 애플리케이션 전용 계정과 최소 권한을 사용합니다.
이 예제는 외부 네트워크나 유료 API를 사용하지 않습니다. 디스크 저장·백업 비용은 발생할 수 있고, 기본 DB 파일이 암호화된 저장소라는 보장은 없습니다. 개인식별 정보나 토큰을 넣는 경우 접근 통제와 저장 장치 암호화, 보존 정책을 별도로 설계합니다. 백업에도 같은 데이터가 들어가므로 백업을 더 느슨한 권한으로 보관하면 안 됩니다.
오류와 진단
database is locked가 반복되면 쓰기 트랜잭션을 오래 잡는 연결부터 찾습니다. timeout을 조금 늘리는 것은 일시적 경쟁을 흡수하지만 단일 작성자의 처리량 한계를 없애지 못합니다. 쓰기 작업을 큐로 모으거나 트랜잭션을 짧게 하고, 지속적인 다중 작성자가 필요하다면 서버형 DB를 검토합니다.
WAL 활성화 결과가 wal이 아니면 경로·파일시스템·권한과 현재 트랜잭션 상태를 확인합니다. unable to open database file에서는 부모 디렉터리 존재와 쓰기 권한을 봅니다. backup.db already exists는 보호 장치의 정상 동작이므로 새 이름을 지정하세요. 라이브러리 버전은 sqlite3.sqlite_version으로 다시 확인합니다. 시스템의 sqlite3 CLI 버전과 Python에 연결된 라이브러리 버전이 다를 수 있습니다.
모드 되돌리기
다른 연결과 서비스를 모두 닫고 백업을 확인한 다음, 아래 코드를 rollback_mode.py에 저장해 python3 rollback_mode.py로 실행합니다. WAL 내용을 본체에 반영한 뒤 DELETE 저널 모드로 돌아가는 순서입니다. 예제의 되돌리기 역시 로컬 학습 DB에서 실행했습니다.
import sqlite3
con = sqlite3.connect("events.db", isolation_level=None, timeout=5.0)
try:
checkpoint = con.execute("PRAGMA wal_checkpoint(TRUNCATE)").fetchone()
print("checkpoint=", checkpoint)
if checkpoint[0] != 0:
raise RuntimeError("Checkpoint busy; close other connections and retry")
mode = con.execute("PRAGMA journal_mode=DELETE").fetchone()[0]
print("journal_mode=", mode)
if mode != "delete":
raise RuntimeError("Failed to return to DELETE mode")
finally:
con.close()성공 신호는 체크포인트의 첫 값이 0이며 journal_mode= delete가 출력되는 것입니다. 다른 연결이 남아 있으면 먼저 닫고 재시도합니다. 학습 폴더를 제거할 때도 모든 프로그램이 종료되고 백업이 필요 없는지 확인한 뒤 학습 파일만 삭제합니다. WAL은 분명 유용하지만, 효과를 결정하는 것은 설정 한 줄보다 짧은 트랜잭션·연결 수명·백업 습관입니다.
