실행 가능한 SQLite 문서: 트랜잭션

Q: SQLite에서 트랜잭션은 어떻게 시작하나요?

BEGIN; (또는 BEGIN TRANSACTION;)으로 시작해서 작업을 진행한 뒤, 결과를 저장하려면 COMMIT;, 되돌리려면 ROLLBACK;을 실행하면 됩니다. BEGIN을 명시적으로 쓰지 않으면 모든 SQL 문이 각각 자동 커밋되는 별개의 트랜잭션으로 실행돼요.

Q: BEGIN, BEGIN IMMEDIATE, BEGIN EXCLUSIVE는 뭐가 다른가요?

BEGIN은 BEGIN DEFERRED와 같고, 실제로 쓰기를 시도하는 순간까지 쓰기 락을 잡지 않습니다. 그래서 다른 연결이 먼저 락을 가져가 버리면 그때 가서 SQLITE_BUSY가 터질 수 있어요. BEGIN IMMEDIATE는 시작하자마자 쓰기 락을 선점합니다. BEGIN EXCLUSIVE는 한 발 더 나가서 다른 리더까지 막는데, WAL 모드에서는 사실상 의미가 없습니다.

Q: SQLite에서 중첩 트랜잭션도 가능한가요?

직접적으로는 안 됩니다. BEGIN 안에서 또 BEGIN을 호출할 수는 없어요. 중첩이 필요하면 SAVEPOINT와 RELEASE / ROLLBACK TO를 쓰면 됩니다. 하나의 트랜잭션 안에서 부분 롤백을 할 수 있게 해주는데, 이건 다음 페이지에서 자세히 다룹니다.

트랜잭션은 전부 아니면 전무(All-or-Nothing) 단위입니다

SQLite 트랜잭션은 여러 SQL 문을 하나의 묶음으로 처리해서, 전부 반영되거나 아니면 하나도 반영되지 않도록 보장합니다. 중간에 무언가 잘못되더라도 롤백만 해주면 데이터베이스는 시작 시점 그대로 돌아옵니다.

가장 흔히 드는 예가 계좌 이체입니다:

CREATE TABLE accounts (
    id    INTEGER PRIMARY KEY,
    owner TEXT,
    cents INTEGER NOT NULL CHECK (cents >= 0)
);

INSERT INTO accounts (owner, cents) VALUES ('Ada', 10000), ('Boris', 500);

BEGIN;
UPDATE accounts SET cents = cents - 2000 WHERE owner = 'Ada';
UPDATE accounts SET cents = cents + 2000 WHERE owner = 'Boris';
COMMIT;

SELECT owner, cents FROM accounts;

두 개의 UPDATE는 한 묶음입니다. 만약 둘 사이에서 데이터베이스가 죽어버리면 Ada는 2000센트만 잃고 Boris는 아무것도 못 받게 되죠. 이 두 문장을 BEGIN ... COMMIT으로 감싸면 한 쌍이 원자적으로 처리됩니다 — 둘 다 반영되거나, 아무것도 반영되지 않거나 둘 중 하나입니다.

자동 커밋(Autocommit): 이미 쓰고 있던 기본 모드

지금까지 실행한 모든 SQL 문장은 사실 하나하나가 트랜잭션이었습니다. SQLite는 기본적으로 자동 커밋 모드(autocommit mode) 로 동작하기 때문에, 각 문장마다 암묵적으로 BEGIN과 COMMIT이 자동으로 감싸집니다.

CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);

INSERT INTO notes (body) VALUES ('첫 번째');
INSERT INTO notes (body) VALUES ('두 번째');
INSERT INTO notes (body) VALUES ('세 번째');

SELECT * FROM notes;

INSERT를 세 번 하면 트랜잭션도 세 개, 디스크에 fsync 호출도 세 번 일어납니다. 한두 번 쓰는 정도라면 문제없지만, 대량으로 데이터를 넣을 때는 너무 느립니다. 게다가 여러 문장을 하나로 묶어서 되돌릴 수도 없죠. BEGIN을 쓰면 다음 COMMIT이나 ROLLBACK이 나올 때까지 자동 커밋이 꺼집니다.

ROLLBACK: 없던 일로 되돌리기

ROLLBACK은 짝이 되는 BEGIN 이후에 한 작업을 전부 취소합니다. 데이터베이스는 트랜잭션을 시작하기 전 상태로 되돌아갑니다.

CREATE TABLE inventory (item TEXT PRIMARY KEY, qty INTEGER);
INSERT INTO inventory VALUES ('apple', 10), ('pear', 5);

BEGIN;
UPDATE inventory SET qty = 0 WHERE item = 'apple';
DELETE FROM inventory WHERE item = 'pear';
ROLLBACK;

SELECT * FROM inventory;

UPDATE도 DELETE도 모두 사라져서 테이블은 BEGIN 직전 상태로 되돌아갑니다. 여러 문장으로 이뤄진 작업 도중에 에러가 났을 때 애플리케이션이 깔끔하게 중단할 수 있도록 해주는 안전장치인 셈이죠.

참고로, 트랜잭션 안에서 제약 조건 위반(constraint violation)이 발생해도 트랜잭션 전체가 자동으로 롤백되지는 않습니다. 문제가 된 그 문장만 롤백되고, 트랜잭션은 그대로 열린 채 여러분의 판단을 기다립니다. "모두 성공 아니면 전부 취소"로 처리하고 싶다면, 애플리케이션이 에러를 감지했을 때 직접 ROLLBACK을 실행해줘야 합니다.

대량 INSERT 속도 끌어올리기

자동 커밋 모드에서는 문장 하나하나마다 fsync가 일어나기 때문에, 여러 INSERT를 하나의 트랜잭션으로 묶으면 보통 100배 가까이 빨라집니다:

CREATE TABLE events (id INTEGER PRIMARY KEY, payload TEXT);

BEGIN;
INSERT INTO events (payload) VALUES ('a');
INSERT INTO events (payload) VALUES ('b');
INSERT INTO events (payload) VALUES ('c');
INSERT INTO events (payload) VALUES ('d');
INSERT INTO events (payload) VALUES ('e');
COMMIT;

SELECT count(*) FROM events;

COMMIT 시점에 디스크 동기화가 한 번만 일어나는 거죠. 행마다 매번 쓰는 게 아니라요. 수천 건을 임포트하는데 속도가 기어가듯 느리다면, 십중팔구 이게 원인입니다.

DEFERRED, IMMEDIATE, EXCLUSIVE

BEGIN에는 SQLite가 언제 잠금을 잡을지 결정하는 모드를 지정할 수 있습니다:

BEGIN DEFERRED (기본값) — 읽거나 쓰기 전까지는 아예 잠금을 잡지 않습니다. 쓰기 잠금은 첫 쓰기 문장이 실행되는 순간에야 지연 획득됩니다.
BEGIN IMMEDIATE — 시작하자마자 쓰기 잠금을 확보합니다. 다른 연결에서 읽기는 가능하지만, 다른 연결이 쓰기를 시작할 수는 없습니다.
BEGIN EXCLUSIVE — IMMEDIATE와 같은데 다른 연결의 읽기까지 막습니다. WAL 모드에서는 사실상 IMMEDIATE와 동일하게 동작하고, 차이가 의미를 갖는 건 예전 방식인 롤백 저널 모드뿐입니다.

BEGIN DEFERRED;     -- 普通のBEGINと同じ
BEGIN IMMEDIATE;    -- 今すぐ書き込みロックを予約
BEGIN EXCLUSIVE;    -- すべてを予約(ロールバックジャーナルモード)

어떤 모드를 고르느냐에 따라 동시성이 크게 달라집니다. 그냥 BEGIN으로 시작하면, 두 연결이 동시에 트랜잭션을 열고 둘 다 신나게 읽다가 정작 쓰려는 순간 충돌이 납니다. 쓰기 잠금을 늦게 요청한 쪽이 SQLITE_BUSY 에러를 받게 되는데, 더 큰 문제는 그때까지 해 둔 읽기 작업을 전부 버려야 한다는 점이죠.

BEGIN IMMEDIATE는 바로 이 문제를 해결합니다. 어차피 쓸 거라는 걸 알고 있다면, 시작하자마자 쓰기 잠금부터 잡아 두는 겁니다. 그러면 두 번째 연결은 의미 없는 작업을 시작하기 전에 곧바로 대기하거나 빠르게 실패합니다.

기억하기 쉬운 원칙: 트랜잭션 안에서 쓰기가 일어난다면 BEGIN IMMEDIATE를 쓰세요.

트랜잭션 안의 읽기는 스냅샷을 본다

트랜잭션이 열려 있는 동안 읽기 작업은 데이터베이스의 일관된 스냅샷을 보게 됩니다. WAL 모드라면 트랜잭션이 시작된 시점, 롤백 저널 모드라면 처음으로 읽기를 수행한 시점의 상태죠. 그 사이 다른 연결이 변경 사항을 커밋하더라도, 내 쿼리 결과에는 갑자기 끼어들지 않습니다.

CREATE TABLE counter (n INTEGER);
INSERT INTO counter VALUES (1);

BEGIN;
SELECT n FROM counter;   -- 1
UPDATE counter SET n = 99;
SELECT n FROM counter;   -- 99, 자신의 쓰기는 볼 수 있다
COMMIT;

SELECT n FROM counter;

자기 자신이 만든 커밋되지 않은 변경은 본인 연결에서만 보이고, 다른 연결에서는 보이지 않습니다. COMMIT을 하는 순간 새 값이 모두에게 노출되죠. 사람들이 SQLite를 직렬화 가능(serializable) 이라고 말할 때 의미하는 게 바로 이겁니다. READ COMMITTED 같은 옵션을 따로 만질 필요가 없어요. 기본 동작이 이미 가장 강력한 격리 수준이거든요.

애플리케이션 코드에서 트랜잭션 다루기

실제 프로그램에서는 보통 본문을 try/except(언어에 따라서는 try/catch)로 감싸고, 에러가 발생했을 때 ROLLBACK을 호출하는 패턴을 씁니다.

-- 任意のクライアントライブラリの擬似コード
BEGIN IMMEDIATE;
try:
    UPDATE accounts SET cents = cents - 2000 WHERE owner = 'Ada';
    UPDATE accounts SET cents = cents + 2000 WHERE owner = 'Boris';
    COMMIT;
except:
    ROLLBACK;
    raise;

대부분의 클라이언트 라이브러리(파이썬의 sqlite3, better-sqlite3 등)는 with 블록이나 transaction() 헬퍼로 이 과정을 알아서 감싸 줍니다. 다만 라이브러리마다 기본 동작이 예상과 다를 수 있으니 문서를 한 번씩 확인해 보는 게 좋아요. 특히 파이썬의 sqlite3는 예전부터 자동 커밋 동작이 좀 특이하기로 유명했는데, 최신 버전에서는 이 문제를 해결하려고 별도의 autocommit 파라미터가 추가됐습니다.

자주 헷갈리는 포인트

DDL도 트랜잭션 안에서 동작합니다. CREATE TABLE, ALTER TABLE, 심지어 DROP TABLE까지 롤백할 수 있습니다. 사실 SQLite의 독특한 점인데, 다른 DB들은 대부분 DDL을 자동 커밋해 버리거든요.
VACUUM은 트랜잭션 안에서 실행할 수 없습니다. 몇몇 유지보수용 명령들도 마찬가지입니다. 이런 명령은 자동 커밋 모드에서 돌려야 합니다.
COMMIT 실패도 엄연한 실패입니다. 드물긴 하지만 COMMIT이 SQLITE_BUSY를 반환할 수 있는데, 이 경우 트랜잭션은 커밋되지 않은 상태입니다. 보통은 재시도 로직으로 처리해야 합니다.
트랜잭션을 오래 열어두면 다른 쓰기가 막힙니다. 트랜잭션이 몇 분간 열려 있으면 그 시간 동안 다른 writer들도 모두 대기하게 됩니다. 가능한 한 늦게 열고, 빠르게 커밋하세요.

다음 주제: 세이브포인트

BEGIN과 COMMIT은 전부 아니면 전무 방식입니다. 그런데 가끔은 트랜잭션의 일부분만 롤백하고 싶을 때가 있죠. 위험한 단계 하나만 취소하고 나머지는 그대로 살리는 식으로요. 바로 이럴 때 쓰는 게 세이브포인트인데, 다음 글에서 이어서 다뤄 보겠습니다.

자주 묻는 질문

SQLite에서 트랜잭션은 어떻게 시작하나요?

BEGIN; (또는 BEGIN TRANSACTION;)으로 시작해서 작업을 진행한 뒤, 결과를 저장하려면 COMMIT;, 되돌리려면 ROLLBACK;을 실행하면 됩니다. BEGIN을 명시적으로 쓰지 않으면 모든 SQL 문이 각각 자동 커밋되는 별개의 트랜잭션으로 실행돼요.

BEGIN, BEGIN IMMEDIATE, BEGIN EXCLUSIVE는 뭐가 다른가요?

BEGIN은 BEGIN DEFERRED와 같고, 실제로 쓰기를 시도하는 순간까지 쓰기 락을 잡지 않습니다. 그래서 다른 연결이 먼저 락을 가져가 버리면 그때 가서 SQLITE_BUSY가 터질 수 있어요. BEGIN IMMEDIATE는 시작하자마자 쓰기 락을 선점합니다. BEGIN EXCLUSIVE는 한 발 더 나가서 다른 리더까지 막는데, WAL 모드에서는 사실상 의미가 없습니다.

SQLite에도 트랜잭션 격리 수준(isolation level)이 있나요?

SQL 표준이 말하는 의미의 격리 수준은 없습니다. SQLite는 사실상 SERIALIZABLE로 동작해요. 트랜잭션은 일관된 스냅샷을 보고, 쓰기는 직렬화됩니다. READ COMMITTED나 REPEATABLE READ 같은 옵션은 없고, 우리가 고를 수 있는 건 DEFERRED/IMMEDIATE/EXCLUSIVE 정도인데, 이건 뭘 볼 수 있느냐가 아니라 락을 언제 잡느냐를 정하는 옵션입니다.

SQLite에서 중첩 트랜잭션도 가능한가요?

직접적으로는 안 됩니다. BEGIN 안에서 또 BEGIN을 호출할 수는 없어요. 중첩이 필요하면 SAVEPOINT와 RELEASE / ROLLBACK TO를 쓰면 됩니다. 하나의 트랜잭션 안에서 부분 롤백을 할 수 있게 해주는데, 이건 다음 페이지에서 자세히 다룹니다.

SQLite 트랜잭션: BEGIN, COMMIT, ROLLBACK 완벽 정리