SQLite BEGIN DEFERRED·IMMEDIATE·EXCLUSIVE: 트랜잭션 중간 SQLITE_BUSY를 다루는 법

SQLite에서 BEGIN만 쓰면 트랜잭션은 DEFERRED로 시작합니다. 이 기본값 때문에 busy_timeout을 넉넉히 잡아 두었는데도 트랜잭션 중간의 UPDATE가 곧바로 SQLITE_BUSY로 실패하는 일이 생깁니다. 이 글은 세 가지 BEGIN 형태가 실제로 무엇을 다르게 하는지, 읽기 트랜잭션이 쓰기로 바뀌는 순간에 어떤 일이 벌어지는지, 그리고 오류가 난 위치에 따라 무엇을 다시 실행해야 하는지를 다룹니다.

근거는 SQLite 공식 문서입니다. 주로 BEGIN TRANSACTION 문서, 결과 코드 문서, 격리 수준 문서, busy handler 문서를 참고했고, 2026-10-15에 확인한 내용입니다.

세 가지 BEGIN의 차이

BEGIN 문서에 따르면 기본 동작은 DEFERRED입니다.

형태	시작 시점에 하는 일	문서상 주의점
BEGIN / BEGIN DEFERRED	자동 커밋만 끄고, 실제 트랜잭션은 데이터베이스에 처음 접근할 때 시작	첫 문장이 SELECT면 읽기 트랜잭션으로 시작하고, 이후 쓰기 문장에서 쓰기 트랜잭션으로 올리거나 SQLITE_BUSY를 반환
BEGIN IMMEDIATE	쓰기 문장을 기다리지 않고 바로 쓰기 트랜잭션 시작	다른 연결이 이미 쓰기 트랜잭션 중이면 BEGIN IMMEDIATE 자체가 SQLITE_BUSY로 실패할 수 있음
BEGIN EXCLUSIVE	IMMEDIATE처럼 바로 쓰기 트랜잭션 시작	WAL 모드에서는 IMMEDIATE와 같음. 그 외 저널 모드에서는 트랜잭션 동안 다른 연결의 읽기도 막음

DEFERRED 트랜잭션의 첫 문장이 쓰기 문장이면 처음부터 쓰기 트랜잭션으로 시작합니다. 문제가 되는 것은 “먼저 읽고, 그 값을 보고 쓴다”는 흔한 패턴입니다.

읽기에서 쓰기로 올라갈 때 실패하는 이유

BEGIN 문서는 읽기 트랜잭션 중에 쓰기 문장이 오면 “가능하면” 쓰기 트랜잭션으로 올린다고 설명합니다. 다른 연결이 이미 데이터베이스를 수정했거나 수정하는 중이라면 업그레이드는 불가능하고, 그 쓰기 문장은 SQLITE_BUSY로 실패합니다. 이 실패는 저널 모드에 따라 원인이 조금 다릅니다.

WAL 모드: 스냅샷이 낡았다

격리 수준 문서에 따르면 WAL 모드의 읽기 트랜잭션은 시작 시점의 스냅샷을 계속 봅니다. 그 사이에 다른 연결이 쓰기를 커밋했다면, 이 연결의 스냅샷은 더 이상 최신이 아닙니다. 이 상태에서 쓰기로 올리려 하면 SQLITE_BUSY_SNAPSHOT(확장 코드 517)으로 실패합니다. 문서는 그대로 쓰게 두면 데이터베이스 이력이 갈라지는데 SQLite는 이를 지원하지 않는다고 설명하고, 쓰려면 먼저 ROLLBACK 등으로 스냅샷을 놓고 새 BEGIN으로 다시 시작해야 한다고 적고 있습니다.

즉 이 경우는 기다려서 풀리는 잠금 대기가 아닙니다. 상대 쓰기가 끝나도 내 스냅샷은 여전히 과거 시점이므로, 같은 트랜잭션 안에서 버티는 대신 트랜잭션을 끝내고 처음부터 다시 실행해야 합니다.

롤백 저널 모드: 교착 회피

WAL이 아닌 모드의 잠금 단계는 잠금 문서에 정리되어 있습니다. 읽는 연결은 SHARED, 쓰기를 준비하는 연결은 RESERVED(동시에 하나만 가능)를 잡고, 실제로 파일에 쓰려면 다른 SHARED가 모두 풀린 뒤 EXCLUSIVE가 필요합니다.

busy handler 문서는 바로 이 상황을 예로 듭니다. 한 프로세스는 SHARED를 RESERVED로 올리려 하고, 다른 프로세스는 RESERVED를 EXCLUSIVE로 올리려 하면 서로가 서로를 막습니다. 둘 다 busy handler로 기다리면 어느 쪽도 진행하지 못하므로, SQLite는 busy handler를 부르지 않고 첫 번째 프로세스에 SQLITE_BUSY를 바로 돌려줍니다. 문서 표현으로는 그 프로세스가 읽기 잠금을 놓게 유도하려는 것입니다.

busy_timeout은 busy handler 위에 구현되어 있습니다(sqlite3_busy_timeout, PRAGMA busy_timeout). 그래서 busy handler가 호출되지 않는 이 경우에는 타임아웃 값과 상관없이 오류가 즉시 올라옵니다. 이때도 해법은 같습니다. 읽기 잠금을 쥔 트랜잭션을 롤백하고 처음부터 다시 실행합니다.

BEGIN IMMEDIATE가 바꾸는 것

결과 코드 문서의 SQLITE_BUSY 항목은 SQLITE_BUSY가 트랜잭션 시작, 중간의 쓰기, 커밋 어느 지점에서도 날 수 있다고 설명하면서, 트랜잭션 중간의 SQLITE_BUSY를 피하려면 BEGIN 대신 BEGIN IMMEDIATE를 쓰라고 권합니다. BEGIN IMMEDIATE 자체는 SQLITE_BUSY를 반환할 수 있지만, 성공했다면 다음 COMMIT까지 같은 데이터베이스에 대한 이후 작업은 SQLITE_BUSY를 반환하지 않는다고 보장합니다. 격리 수준 문서도 나중에 쓸 것을 알고 있는 트랜잭션은 SQLITE_BUSY_SNAPSHOT을 피하기 위해 BEGIN IMMEDIATE로 시작하라고 안내합니다.

실무 관점에서 정리하면, 경합이 생기는 지점이 트랜잭션 중간에서 BEGIN 한 줄로 옮겨집니다. BEGIN IMMEDIATE가 실패한 시점에는 아직 아무것도 읽거나 쓰지 않았으므로 재시도가 단순하고, 이 지점의 대기는 일반적인 쓰기 경합이라 busy_timeout이 의도대로 작동할 여지가 있습니다.

대가도 있습니다. 쓰기 트랜잭션은 한 번에 하나만 활성화될 수 있으므로, BEGIN IMMEDIATE 뒤에서 오래 걸리는 작업을 하면 그동안 다른 쓰기 연결은 기다리거나 SQLITE_BUSY를 받습니다. 이 점은 문서에서 직접 계산된 수치가 아니라 “쓰기는 하나뿐”이라는 규칙에서 나오는 추론입니다. 읽기 전용 트랜잭션은 그대로 BEGIN(DEFERRED)으로 두고, 쓸 가능성이 있는 트랜잭션만 IMMEDIATE로 바꾸며, 그 안에서 네트워크 호출 같은 느린 작업은 빼는 편이 맞습니다.

BEGIN EXCLUSIVE는 WAL 모드에서는 IMMEDIATE와 같으므로 따로 고를 이유가 없습니다. 롤백 저널 모드에서는 다른 연결의 읽기까지 막으므로, 읽기 차단이 정말 필요한 경우가 아니라면 IMMEDIATE가 기본 선택입니다.

오류 위치별 재시도 범위

어디서 났나	상태	할 일
BEGIN IMMEDIATE	아직 아무 작업도 하지 않음	잠시 뒤 BEGIN IMMEDIATE부터 재시도
DEFERRED 트랜잭션 중간의 쓰기 문장 (SQLITE_BUSY, SQLITE_BUSY_SNAPSHOT)	앞서 읽은 값이 낡았거나 교착 회피로 거절됨	ROLLBACK 후 읽기를 포함한 트랜잭션 전체를 처음부터 재실행
COMMIT	BEGIN 문서상 다른 연결의 읽기 때문에 실패하면 트랜잭션은 활성 상태로 남음	롤백하지 말고 COMMIT만 나중에 재시도
SQLITE_LOCKED	다른 연결이 아니라 같은 연결(또는 공유 캐시) 안의 충돌	다른 연결을 기다리는 문제가 아니므로 같은 연결(또는 공유 캐시) 쪽 원인을 확인

두 번째 줄에서 실패한 쓰기 문장 하나만 다시 실행하면 안 되는 이유는 WAL 쪽 설명에서 이미 나왔습니다. 그 문장 앞의 SELECT 결과가 다른 연결의 커밋 이전 값이기 때문입니다. 롤백 저널 모드에서도 SQLite가 이 연결에 읽기 잠금을 놓으라고 요구하는 상황이므로, 트랜잭션을 유지한 채 같은 문장을 반복하는 것은 의미가 없습니다.

COMMIT에서의 SQLITE_BUSY에 대해서는 문서 사이에 결이 조금 다릅니다. 결과 코드 문서는 BEGIN IMMEDIATE가 성공하면 다음 COMMIT까지 SQLITE_BUSY가 없다고 하고, BEGIN 문서는 다른 스레드나 프로세스가 읽기 연결을 열고 있으면 COMMIT이 SQLITE_BUSY를 반환할 수 있다고 합니다. 잠금 문서가 같은 상황(다른 프로세스의 SHARED 잠금 때문에 커밋 실패)을 롤백 저널 모드 기준으로 설명하고, WAL 모드에서는 읽기가 쓰기를 막지 않으므로, 이 커밋 실패는 주로 롤백 저널 모드의 경우로 보는 것이 자연스럽습니다. 다만 이 결론은 여러 문서를 함께 읽고 내린 해석이며, 한 문서가 직접 그렇게 말하지는 않습니다. 어느 쪽이든 COMMIT에서 SQLITE_BUSY를 받았다면 트랜잭션이 아직 살아 있는지 먼저 확인한 뒤에 COMMIT만 다시 시도하는 편이 안전합니다.

sqlite3 CLI로 재현해 보기

두 개의 터미널에서 같은 파일을 엽니다. 아래 예는 WAL 모드를 기준으로 합니다. WAL은 한 번 설정하면 파일에 유지됩니다(PRAGMA journal_mode).

준비:

-- sqlite3 demo.db
PRAGMA journal_mode=WAL;
CREATE TABLE counters(name TEXT PRIMARY KEY, n INTEGER NOT NULL);
INSERT INTO counters VALUES ('a', 0);

이제 터미널 A와 B에서 각각 sqlite3 demo.db를 실행하고, 다음 순서로 입력합니다.

-- A
BEGIN;
SELECT n FROM counters WHERE name = 'a';   -- 0, 읽기 트랜잭션 시작

-- B (자동 커밋)
UPDATE counters SET n = n + 1 WHERE name = 'a';   -- WAL에서는 A의 읽기가 막지 않음

-- A
UPDATE counters SET n = 1 WHERE name = 'a';   -- SQLITE_BUSY 계열 오류
ROLLBACK;

A의 마지막 UPDATE는 A의 스냅샷이 B의 커밋보다 앞서 있으므로 쓰기로 올라갈 수 없습니다. 오류 메시지 문구는 CLI 버전에 따라 다를 수 있지만, 결과 코드 기준으로는 SQLITE_BUSY의 확장 코드인 SQLITE_BUSY_SNAPSHOT 상황입니다. A가 실패한 문장만 다시 실행하는 것이 아니라 ROLLBACK 후 SELECT부터 다시 해야 하는 이유가 여기에 있습니다.

같은 작업을 BEGIN IMMEDIATE로 바꾸면 순서가 이렇게 됩니다.

-- A
BEGIN IMMEDIATE;
SELECT n FROM counters WHERE name = 'a';

-- B
SELECT n FROM counters WHERE name = 'a';          -- WAL에서는 읽기 가능
UPDATE counters SET n = n + 1 WHERE name = 'a';   -- 쓰기는 하나뿐: 대기 설정이 없으면 SQLITE_BUSY

-- A
UPDATE counters SET n = n + 1 WHERE name = 'a';
COMMIT;

이번에는 경합에서 밀린 쪽이 B의 쓰기 시작 지점입니다. B에서 .timeout 5000처럼 대기 시간을 주면, A가 그 안에 커밋할 경우 B는 대기 후 진행할 수 있습니다. .timeout은 CLI 도움말에서 “잠긴 테이블을 MS 밀리초 동안 열기를 시도”하는 명령으로 설명됩니다(CLI 문서). 애플리케이션 코드에서는 PRAGMA busy_timeout = 5000; 또는 sqlite3_busy_timeout()이 같은 역할을 합니다. 5000은 예시 값이며 권장값이 아닙니다.

애플리케이션에 적용할 때

• 읽은 값을 근거로 쓰는 트랜잭션은 BEGIN IMMEDIATE로 시작합니다. 드라이버나 ORM이 BEGIN을 대신 발행한다면, 그것이 어떤 형태의 BEGIN인지 해당 드라이버 문서에서 확인해야 합니다.
• 연결마다 busy_timeout을 설정하되, 그것만으로 트랜잭션 중간의 SQLITE_BUSY가 사라진다고 기대하지 않습니다.
• 재시도 단위는 SQL 문장이 아니라 트랜잭션 전체를 감싼 함수로 잡습니다. 문서로 확인되는 예외는 다른 연결이 읽기를 열어 두어 COMMIT이 SQLITE_BUSY로 실패한 경우입니다. BEGIN 문서에 따르면 이때 트랜잭션은 활성 상태로 남으므로 COMMIT만 재시도하면 됩니다. 그 밖의 COMMIT 실패에서는 무엇을 재시도할지 정하기 전에 트랜잭션이 아직 열려 있는지부터 확인합니다. sqlite3_get_autocommit() 문서는 SQLITE_BUSY를 비롯한 일부 오류 뒤에 트랜잭션이 자동으로 롤백될 수 있고, 그 여부를 알 수 있는 방법은 이 함수뿐이라고 설명합니다. sqlite3_txn_state()는 연결이 트랜잭션 없음, 읽기 트랜잭션, 쓰기 트랜잭션 중 어느 상태인지 돌려줍니다. 드라이버를 거쳐 쓴다면 드라이버가 제공하는 트랜잭션 상태 확인 기능을 사용합니다. 트랜잭션이 사라졌다면 전체를 다시 실행합니다.
• WAL 모드에서도 SQLITE_BUSY가 완전히 사라지지 않습니다. WAL 문서는 다른 연결이 배타 잠금 모드로 열었을 때, 마지막 연결이 닫히며 정리하는 순간, 크래시 후 복구 중일 때를 예외로 듭니다. WAL 파일 크기와 체크포인트 쪽 이야기는 SQLite WAL 체크포인트 글에서 따로 다뤘습니다.

busy_timeout은 기다리면 풀리는 경합에만 도움이 됩니다. 읽기에서 쓰기로 올라가는 순간의 실패는 기다려도 풀리지 않는 종류라서, 그런 트랜잭션은 처음부터 BEGIN IMMEDIATE로 쓰기 의도를 밝히는 것이 SQLite 문서가 제시하는 방법입니다.