SQLite 트랜잭션

SQLite에서는 특별히 지시하지 않아도 어떤 작업이든 거의 항상 트랜잭션 안에서 일어나요. 데이터베이스에 접근하는 SQL 명령은 몇몇 PRAGMA 문을 제외하고는, 이미 트랜잭션이 진행 중이 아니라면 자동으로 트랜잭션을 시작하고 마지막 문장이 끝나는 시점에 자동으로 커밋해요. 손으로 트랜잭션을 조절하고 싶을 때는 BEGIN으로 시작해서 COMMIT이나 ROLLBACK으로 마무리하면 돼요.

출처: SQLite Transaction Documentation

트랜잭션 제어 문법

트랜잭션은 BEGIN으로 시작하고 COMMIT으로 확정하거나 ROLLBACK으로 되돌려요. END TRANSACTION은 COMMIT의 별칭이에요. 트랜잭션은 닫힐 때 자동으로 되돌려질 수도 있고, 오류가 발생했을 때 ROLLBACK 충돌 해석 규칙이 지정되어 있으면 역시 되돌려져요.

BEGIN ... COMMIT으로 만든 트랜잭션은 중첩되지 않아요. 중첩이 필요하면 SAVEPOINT와 RELEASE 명령을 사용해야 해요. 트랜잭션 안에서 다시 BEGIN을 호출하면 오류가 나는데, 그 트랜잭션이 SAVEPOINT로 시작됐는지 이전 BEGIN으로 시작됐는지와는 무관해요. ROLLBACK의 TO SAVEPOINT name 절은 SAVEPOINT 트랜잭션에만 적용돼요.

읽기 트랜잭션과 쓰기 트랜잭션

SQLite는 서로 다른 연결(스레드나 프로세스가 달라도)에서 들어오는 동시 읽기 트랜잭션을 여러 개 지원하지만, 쓰기 트랜잭션은 동시에 하나만 허용해요.

읽기 트랜잭션은 읽기 전용이고, SELECT 문장으로 시작돼요.
쓰기 트랜잭션은 읽기와 쓰기를 모두 허용하고, CREATE·DELETE·DROP·INSERT·UPDATE 같은 쓰기 문장으로 시작돼요.

읽기 트랜잭션이 활성화된 상태에서 쓰기 문장이 나오면 가능할 때 쓰기 트랜잭션으로 승격돼요. 그런데 다른 연결이 이미 데이터베이스를 수정했거나 수정하는 중이라면 승격이 불가능하고, 그 쓰기 문장은 SQLITE_BUSY 오류와 함께 실패해요.

읽기 트랜잭션이 열려 있는 동안에는 다른 연결이 반영한 변경이 보이지 않아요. 다른 연결이 내용을 바꿔도 그 트랜잭션이 끝나기 전까지는 변경 전의 과거 스냅샷만 보는 거죠.

DEFERRED, IMMEDIATE, EXCLUSIVE

트랜잭션은 DEFERRED, IMMEDIATE, EXCLUSIVE 셋 중 하나로 시작할 수 있고, 기본값은 DEFERRED예요.

DEFERRED: 실제로는 데이터베이스에 처음 접근할 때까지 트랜잭션이 시작되지 않아요. 내부적으로는 BEGIN DEFERRED가 자동 커밋을 끄는 플래그만 설정해요. 첫 문장이 SELECT면 읽기 트랜잭션이, 쓰기 문장이면 쓰기 트랜잭션이 시작돼요.
IMMEDIATE: 쓰기 문장을 기다리지 않고 바로 쓰기 트랜잭션을 시작해요. 다른 연결에 쓰기 트랜잭션이 이미 있으면 SQLITE_BUSY로 실패할 수 있어요.
EXCLUSIVE: IMMEDIATE처럼 바로 쓰기 트랜잭션을 시작하지만, WAL 모드에서는 IMMEDIATE와 동일해요. 다른 저널링 모드에서는 트랜잭션이 진행되는 동안 다른 연결이 데이터베이스를 읽지 못하게 해요.

암시적 트랜잭션과 명시적 트랜잭션

BEGIN 없이 자동으로 시작된 암시적 트랜잭션은 마지막 활성 문장이 끝나면 자동으로 커밋돼요. 문장은 커서가 닫힐 때 끝나는데, 준비된 문장을 sqlite3_reset()이나 sqlite3_finalize()로 호출하면 보장되어요. 명시적 COMMIT은 즉시 실행되지만, 보류 중인 쓰기 작업이 있으면 SQLITE_BUSY로 실패할 수 있어요.

트랜잭션 안에서 오류에 대응하기

트랜잭션 도중 특정 오류가 발생하면 자동으로 롤백될 수도 있고 아닐 수도 있어요. 자동 롤백을 유발할 수 있는 오류는 다음과 같아요:

SQLITE_FULL: 데이터베이스나 디스크가 가득 참
SQLITE_IOERR: 디스크 I/O 오류
SQLITE_INTERRUPT: sqlite3_interrupt() 등으로 작업이 중단됨
SQLITE_NOMEM: 메모리 부족

이 오류들에 대해 SQLite는 우선 작업 중이던 그 문장 하나만 되돌리고, 같은 트랜잭션 안의 이전 문장 변경은 유지하며 트랜잭션을 계속 진행하려고 해요. 다만 문장과 오류가 발생한 시점에 따라 트랜잭션 전체를 롤백·취소해야 할 수도 있어요. 애플리케이션은 sqlite3_get_autocommit()이나 sqlite3_txn_state() C 인터페이스로 어떤 방식으로 진행됐는지 알 수 있어요.

SQLite 트랜잭션