SQL 트랜잭션과 WAL: database is locked 의 세 가지 원인

시리즈: SQL 강좌 5/6 (고급). 쿼리와 인덱스는 다룰 줄 알고, 이제 데이터베이스를 동시에 둘 이상이 건드리는 코드를 운영하는 분을 위한 글입니다. 워커가 여러 개인 웹 앱, API 옆에서 도는 배치, 백그라운드 동기화가 있는 모바일 앱 같은 경우입니다. 다 읽으면 트랜잭션이 보장하는 것과 보장하지 않는 것을 알고, 대량 쓰기를 100배 넘게 빠르게 만들고, 마주치는 모든 database is locked 를 원인별로 가를 수 있습니다.

트랜잭션 버그 대부분은 프로세스 하나만 DB 를 쓰는 개발 환경에서는 보이지 않습니다. 운영에 나가서야 "한 달에 한 번 포인트 잔액이 안 맞는다", "로그에 database is locked 가 찍히는데 재현이 안 된다"는 모습으로 나타납니다. 쿠폰 사용과 포인트 차감, 재고 차감과 주문 생성처럼 두 개의 변경을 반드시 함께 처리해야 하는 코드를 작성한다면 바로 해당됩니다. 이 강좌에서는 sqlite3 프로세스 두 개와 몇 줄의 Python 으로 일부러 재현해 정확한 메시지를 보여 주고, 트랜잭션이 쓰기 속도에 미치는 영향을 잽니다.

실행 환경: SQLite 3.51.0 CLI 와 Python 3.9.6 의 sqlite3 모듈(포함된 SQLite 도 3.51.0), Apple Silicon Mac (M1 Pro), macOS, APFS SSD. MySQL 과 PostgreSQL 은 쓸 수 없었으므로 그에 관한 내용에는 "여기서는 실행하지 않음"이라고 적었습니다.

트랜잭션이란, 그리고 처음 만나는 오류

트랜잭션은 DB 가 "전부 한꺼번에 반영하거나, 아무것도 반영하지 않거나" 둘 중 하나로 다루는 문장 묶음입니다. BEGIN 으로 열고, COMMIT 으로 확정하고, ROLLBACK 으로 버립니다. BEGIN 밖에서는 SQLite 가 모든 문장을 각각 작은 트랜잭션으로 실행합니다. 이것이 자동 커밋(autocommit) 모드입니다.

예제는 두 계좌 사이의 이체이고, 잔액은 절대 음수가 될 수 없다는 규칙을 둡니다.

CREATE TABLE accounts (
  id      INTEGER PRIMARY KEY,
  owner   TEXT    NOT NULL,
  balance INTEGER NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts VALUES (1, 'alice', 100), (2, 'bob', 50);

누구나 첫 주에 보는 두 오류입니다.

sqlite> COMMIT;
Runtime error near line 1: cannot commit - no transaction is active
sqlite> BEGIN;
sqlite> BEGIN;
Runtime error near line 3: cannot start a transaction within a transaction

트랜잭션은 중첩되지 않습니다. "트랜잭션 안의 트랜잭션"이 필요하면 이름 붙은 되돌림 지점인 세이브포인트를 씁니다.

SAVEPOINT transfer;
UPDATE accounts SET balance = balance + 150 WHERE id = 2;
UPDATE accounts SET balance = balance - 150 WHERE id = 1;  -- CHECK fails
ROLLBACK TO transfer;   -- undoes the first UPDATE too
RELEASE transfer;

Runtime error near line 3: CHECK constraint failed: balance >= 0 (19)
1|alice|100
2|bob|50

흔한 실수: 오류가 나면 롤백될 거라고 믿기

실수. alice 잔액은 100 인데 150 을 보내려 합니다. 코드는 두 UPDATE 를 BEGIN … COMMIT 으로 감싸고 CHECK 제약이 지켜 줄 거라고 생각합니다.

BEGIN;
UPDATE accounts SET balance = balance + 150 WHERE id = 2;
UPDATE accounts SET balance = balance - 150 WHERE id = 1;
COMMIT;
SELECT * FROM accounts;
SELECT 'total', sum(balance) FROM accounts;

실제로 일어나는 일. 실패한 문장만 취소되고 트랜잭션은 열린 채로 남으며, COMMIT 이 성공한 문장을 확정합니다.

Runtime error near line 6: CHECK constraint failed: balance >= 0 (19)
1|alice|100
2|bob|200
total|300

총액이 150 늘었습니다. 돈이 허공에서 생겼습니다. (PostgreSQL 은 다르게 동작해서, 오류 뒤에는 롤백할 때까지 "current transaction is aborted" 로 모든 문장을 거부합니다. 여기서는 실행하지 않았습니다.)

해결. 문장 하나라도 실패하면 직접 롤백합니다. 스크립트라면 sqlite3 -bail 이 첫 오류에서 멈추고, 트랜잭션이 열린 채 프로세스가 끝나므로 SQLite 가 롤백합니다.

$ sqlite3 -bail bank.db < transfer.sql
Runtime error near line 3: CHECK constraint failed: balance >= 0 (19)
$ echo $?
1
$ sqlite3 bank.db "SELECT * FROM accounts"
1|alice|100
2|bob|50

애플리케이션 코드에서는 언어의 트랜잭션 도우미를 씁니다. Python 의 with con: 은 성공하면 커밋, 예외가 나면 롤백합니다. 같은 파일에서 Python 특유의 함정도 보여 줍니다.

import sqlite3
print("SQLite", sqlite3.sqlite_version)

# Trap 1: Python 3.9's sqlite3 opens a transaction for you before INSERT/UPDATE/DELETE
con = sqlite3.connect("bank.db")
con.execute("UPDATE accounts SET balance = 999 WHERE id = 1")
print("in_transaction:", con.in_transaction)
con.close()                                   # no commit() -> rolled back
print("after close:", sqlite3.connect("bank.db").execute("SELECT balance FROM accounts WHERE id=1").fetchone())

# Fix: one transfer = one transaction; `with con:` commits, or rolls back on exception
def transfer(con, src, dst, amount):
    with con:
        con.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", (amount, dst))
        con.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", (amount, src))

con = sqlite3.connect("bank.db")
try:
    transfer(con, 1, 2, 150)
except sqlite3.IntegrityError as e:
    print("IntegrityError:", e)
print(con.execute("SELECT id, balance FROM accounts").fetchall())
transfer(con, 1, 2, 30)
print(con.execute("SELECT id, balance FROM accounts").fetchall())

SQLite 3.51.0
in_transaction: True
after close: (100,)
IntegrityError: CHECK constraint failed: balance >= 0
[(1, 100), (2, 50)]
[(1, 70), (2, 80)]

앞부분은 반대 방향의 놀라움입니다. Python 이 몰래 트랜잭션을 열었고 아무도 commit() 을 부르지 않았기 때문에 변경이 "사라졌습니다".

실측: INSERT 1만 건, 자동 커밋 대 트랜잭션 하나

같은 10,000개의 INSERT 문(random.Random(42) 로 생성)을 설정을 바꿔 가며 sqlite3 에 흘려 넣었습니다. 설정마다 새 DB 에서 5회 실행했고, 시간에는 sqlite3 프로세스 시작이 포함되며 시작만으로 6ms 였습니다.

교훈은 세 가지입니다. 트랜잭션 하나로 묶자 약 195배 빨라졌습니다. 동기화를 꺼도(synchronous=OFF, 정전 시 손상 위험) 자동 커밋은 절반밖에 줄지 않았는데, DELETE 모드에서는 커밋마다 -journal 파일을 만들고 지우기 때문입니다. 그리고 묶을 수 없는 경우(요청마다 한 행을 커밋하는 웹 앱 등)에는 WAL + synchronous=NORMAL 이 가장 좋았습니다.

첫 줄의 편차는 실제 값입니다. 기계에서 다른 작업도 돌고 있었고, 파일 생성이 그 영향을 가장 많이 받는 작업입니다.

롤백 저널과 WAL: 누가 누구를 기다리게 하나

SQLite 가 트랜잭션을 지키는 방식은 두 가지입니다. 기본인 롤백 저널(journal_mode=DELETE)은 원래 페이지를 -journal 파일에 복사한 뒤 DB 파일 자체에 씁니다. WAL(write-ahead log, journal_mode=WAL)은 변경을 -wal 파일에 덧붙이고, 체크포인트가 되돌려 쓸 때까지 DB 파일을 건드리지 않습니다.

w.db 8192          <- after 10,000 inserts in WAL mode
w.db-wal 189552
w.db-shm 32768
(0, 0, 0)          <- PRAGMA wal_checkpoint(TRUNCATE)
w.db 188416
w.db-wal 0

실무에서의 차이는 동시 실행입니다. 다음 테스트에서 프로세스 A 가 읽기 트랜잭션을 열고 3초 동안 유지하고, 1초 뒤 프로세스 B 가 쓰려고 합니다.

( echo "BEGIN;"; echo "SELECT 'A sees', balance FROM accounts WHERE id = 1;"; sleep 3
  echo "SELECT 'A still sees', balance FROM accounts WHERE id = 1;"; echo "COMMIT;"
  echo "SELECT 'A after COMMIT', balance FROM accounts WHERE id = 1;" ) | sqlite3 bank.db &
sleep 1
printf "UPDATE accounts SET balance = 0 WHERE id = 1;\nSELECT 'B wrote', balance FROM accounts WHERE id = 1;\n" | sqlite3 bank.db
wait

--- delete mode
A sees|100
Runtime error near line 1: database is locked (5)
B wrote|100
A still sees|100
A after COMMIT|100
--- wal mode
A sees|100
B wrote|0
A still sees|100
A after COMMIT|0

DELETE 모드에서는 읽기가 쓰기의 커밋을 막습니다. WAL 모드에서는 둘 다 진행되고, A 는 자기 트랜잭션이 끝날 때까지 100 을 계속 봅니다. A 는 스냅숏을 읽고 있습니다. 스냅숏 격리가 세 줄로 보입니다.

"database is locked" 의 세 가지 원인

1. 다른 연결이 쓰는 중인데 기다리지 않았다. CLI 의 busy timeout 은 0 이라서, 쓰기 도중의 쓰기는 즉시 실패합니다.

$ ( echo "BEGIN IMMEDIATE;"; echo "UPDATE accounts SET balance = balance - 10 WHERE id = 1;"; sleep 3; echo "COMMIT;" ) | sqlite3 bank.db &
$ sleep 1; sqlite3 bank.db "UPDATE accounts SET balance = balance + 10 WHERE id = 2;"; echo "exit=$?"
Error: stepping, database is locked (5)
exit=5

해결: busy timeout. .timeout 5000 을 주자 B 는 A 의 커밋을 2.05초 기다린 뒤 성공했습니다. Python 의 sqlite3.connect() 는 처음부터 5초를 설정하므로(PRAGMA busy_timeout 이 5000 을 돌려줬습니다), 같은 작업이 CLI 에서는 실패하고 Python 에서는 통과하는 이유가 이것입니다.

2. 롤백 저널 모드의 긴 읽기가 쓰기의 커밋을 막는다. 위에서 본 그대로입니다. 해결: WAL 모드로 바꾸거나 읽기 트랜잭션을 짧게 합니다.

3. WAL 모드의 "승격" 함정. timeout 이 소용없어서 경험자도 놀라는 경우입니다. 일반 BEGIN(지연 트랜잭션)으로 시작해 먼저 읽고, 다른 연결이 커밋한 뒤에 쓰려고 하면 일어납니다.

( echo ".timeout 5000"; echo "BEGIN;"; echo "SELECT 'A read', balance FROM accounts WHERE id = 1;"; sleep 2
  echo ".timer on"; echo "UPDATE accounts SET balance = balance - 30 WHERE id = 1;"; echo "COMMIT;" ) | sqlite3 bank.db &
sleep 1
sqlite3 bank.db ".timeout 5000" "UPDATE accounts SET balance = balance - 50 WHERE id = 1;"
wait

A read|100
Runtime error near line 5: database is locked (5)
Run Time: real 0.000 user 0.000044 sys 0.000030
Run Time: real 0.000 user 0.000005 sys 0.000005

(두 번째 Run Time 은 COMMIT 의 것으로, A 의 읽기 트랜잭션을 닫았을 뿐입니다.)

5초 timeout 이 있는데도 A 는 1ms 도 안 돼 실패했습니다. A 의 스냅숏은 B 의 커밋보다 오래되었으므로, A 에게 쓰게 하면 보지 못한 데이터 위에 쓰는 셈입니다. 기다려도 해결되지 않으니 SQLite 는 SQLITE_BUSY(확장 코드는 SQLITE_BUSY_SNAPSHOT)를 즉시 돌려줍니다. Python 에서는 같은 것이 sqlite3.OperationalError: database is locked 로 나옵니다. BEGIN IMMEDIATE 로 시작하면 A 가 처음부터 쓰기 잠금을 잡고, B 는 A 를 기다리며, 두 변경이 모두 반영되었습니다. 최종 잔액은 20(100 − 30 − 50)입니다.

갱신 손실: 애플리케이션 코드 속 경쟁 상태

잠금 오류는 하나도 안 나는데 돈이 사라집니다. 두 워커가 잔액을 읽고, 앱에서 계산하고, 다시 씁니다(Python, 연결 2개, 순서 고정).

import sqlite3
a = sqlite3.connect("bank.db", isolation_level=None)   # two app workers, autocommit mode
b = sqlite3.connect("bank.db", isolation_level=None)
q = "SELECT balance FROM accounts WHERE id = 1"
ba = a.execute(q).fetchone()[0]      # worker A reads 100
bb = b.execute(q).fetchone()[0]      # worker B reads 100
a.execute("UPDATE accounts SET balance = ? WHERE id = 1", (ba - 30,))
b.execute("UPDATE accounts SET balance = ? WHERE id = 1", (bb - 50,))
print("read-modify-write in app:", a.execute(q).fetchone()[0], "(expected 20)")

a.execute("UPDATE accounts SET balance = 100 WHERE id = 1")
a.execute("UPDATE accounts SET balance = balance - 30 WHERE id = 1")
b.execute("UPDATE accounts SET balance = balance - 50 WHERE id = 1")
print("atomic UPDATE          :", a.execute(q).fetchone()[0])

read-modify-write in app: 50 (expected 20)
atomic UPDATE          : 20

해결(우선순위 순): 계산을 SQL 안에서 한다(SET balance = balance - ?). 꼭 먼저 읽어야 한다면 읽기와 쓰기를 BEGIN IMMEDIATE … COMMIT 안에서 한다. 다른 DB 라면 SELECT … FOR UPDATE(SQLite 에는 이 구문이 없습니다). 선착순 쿠폰이 수량보다 많이 발급되는 사고도 같은 구조입니다.

격리 수준: SQLite 와 다른 DB

격리 수준은 동시에 도는 트랜잭션이 서로의 변경을 어디까지 볼 수 있는지의 정도입니다. SQL 표준은 허용하는 이상 현상에 따라 네 단계를 정의합니다. SQLite 는 단순해서 쓰기가 항상 하나뿐이므로 사실상 serializable 이고, WAL 모드의 읽기는 위에서 본 것처럼 스냅숏을 읽습니다. (PRAGMA read_uncommitted 는 거의 쓰지 않는 공유 캐시 모드에만 적용됩니다.)

다른 DB 행은 각 문서에 근거한 것이지 실행 결과가 아닙니다. 실무상 결론: 위의 갱신 손실은 어느 DB 든 Read Committed 에서 일어나므로, 원자적 UPDATE 라는 해결책은 어디서나 통합니다.

어떤 상황에 어떤 설정을

연습 문제

1. 야간 배치가 Python 3.9 에서 한 행씩 execute() 로 5만 행을 INSERT 하고 마지막에 commit() 을 부릅니다. 이것은 자동 커밋으로 돌고 있나요? 4만 번째 행에서 죽으면 어떻게 되나요?
2. WAL 모드 앱이 busy_timeout 이 5000 인데도 몇 밀리초 만에 database is locked 로 실패할 때가 있습니다. 원인으로 의심되는 패턴과 한 단어짜리 해결책은?

정답

1. 아닙니다. Python 3.9 의 sqlite3(기본 isolation_level="")는 첫 INSERT 전에 트랜잭션을 열므로 5만 행 전체가 트랜잭션 하나입니다. 빠르고, 4만 번째 행에서 죽으면 열린 트랜잭션이 롤백되어 아무것도 남지 않습니다. os._exit(1) 로 4만 번째 행에서의 비정상 종료를 재현했더니 in_transaction 은 True, 이후 SELECT count(*) 는 0 이었습니다. 대개 바라는 동작이지만 많은 사람이 생각하는 것과는 반대입니다.
2. 먼저 읽고 나중에 쓰는 지연 BEGIN 에서, 그 사이 다른 연결이 커밋한 경우(위의 원인 3). BEGIN IMMEDIATE 를 씁니다.

솔직한 한계와 버전 메모

• 실행한 것은 SQLite 3.51.0(CLI 와 Python 3.9.6 포함 라이브러리)뿐입니다. WAL 모드는 SQLite 3.7.0 부터, 세이브포인트는 3.6.8 부터 있습니다.
• 이 macOS 빌드는 기본이 synchronous=FULL 이고, 컴파일 옵션(DEFAULT_WAL_SYNCHRONOUS=1)에 따라 WAL DB 에서는 NORMAL 입니다. 표준 빌드는 둘 다 FULL 입니다. PRAGMA fullfsync 가 0 이었으므로 동기화는 일반 fsync() 였고, macOS 에서는 이것이 드라이브 자체 캐시까지 비우게 하지 않습니다. Linux 나 fullfsync=ON 에서는 자동 커밋 수치가 달라지며 아마 더 커질 것입니다.
• Python 3.12 에서 Connection.autocommit 이 추가되어 트랜잭션 제어 방식이 바뀌었습니다. 실행한 것은 3.9 뿐입니다.
• WAL 은 프로세스 간 공유 메모리를 쓰므로 네트워크 파일 시스템에서는 동작하지 않습니다.
• PostgreSQL·MySQL·SQL Server·Oracle 격리 수준 표는 문서 기반이며 실행하지 않았습니다.

WHERE 없는 UPDATE 가 왜 위험한지는 2강에서, 학습 전체 경로는 프로그래밍 학습 로드맵에서 확인하세요.

이전: 4강: 인덱스와 EXPLAIN QUERY PLAN · 다음: 6강: 윈도 함수와 CTE