[SQLite Error] SQLITE_BUSY 예외와 WAL 모드

SQLite를 로컬 DB로 사용하고 있는 프로젝트에서 잊을만하면 마주치는 에러가 있어서 정리한다. 프로젝트에서는 Hibernate 버전 6을 사용하고 있다. 

 

상황

로그인 API 이후 사용자 정보 수정 API를 시도했을 때 아래와 같은 예외가 발생했다. 

org.sqlite.SQLiteException: [SQLITE_BUSY] The database file is locked (database is locked)

 

`application.yml`에서 `datasource` 설정은 아래와 같다.

  spring.datasource:
    url: jdbc:sqlite:project.db?busy_timeout=5000
    driver-class-name: org.sqlite.JDBC

 

 

추가적으로 알아야 할 정보가 있다. 

• 로그인 API는 정상 응답으로 현재 로그인한 사용자의 정보를 반환한다. (transaction: read-only)
• 사용자 정보 수정 API 또한 로그인 API가 접근하는 엔티티에 접근하며, 이를 수정해야 한다. (transaction: not read-only)

 

원인

이 문제는 SQLite가 동시 액세스를 처리하는 방식와 Hibernate가 연결을 관리하는 방식에서 비롯된다. 

 

1. SQLite Locking Mechanism

• SQLite는 대부분의 엔터프라이즈 데이터베이스가 사용하는 행 수준(row-level)이 아닌, 파일 수준(file-level) lock을 사용한다.
• 읽기 작업에 대해서, 한 트랜잭션이 시작하면 SQLite는 파일에 대해 `shared lock`을 건다.
  • 다른 프로세스가 읽기 작업은 가능하지만, 쓰기 작업은 할 수 없다.
• 쓰기 작업에 대해서, 한 트랜잭션이 시작하면 SQLite는 파일에 대해 `exclusive lock`을 건다. 
  • 다른 프로세스는 쓰기 작업을 할 수 없다.
• 이러한 잠금은 예상보다 길어질 수 있다. 

2. Hibernate Connection Management

• Hibernate는 데이터베이스 커넥션을 트랜잭션 완료 이후에 바로 해제하지 않는다.
• 커넥션은 커넥션 풀에 반환되지만, SQLite 파일 lock은 지속될 수 있다. 
• 수정 API가 `exclusive lock`을 획득하려고 할 때, 로그인 API의 read-only 트랜잭션은 여전히 `shared lock`을 유지하고 있을 수 있다.

3. Transaction Isolation 

• 두 API의 트랜잭션이 분리되어 있어도, 시간적으로는 겹칠 수 있다.
• SQLite의 기본 `journal mode`인 `DELETE`는 이러한 잠금 문제가 발생하기 쉽다. 

읽기 시작 → `shared lock` 획득 → 모든 쓰기 작업 차단
쓰기 대기 → `SQLITE_BUSY` 에러 발생

 

즉, 수정 API를 통해 쓰기 작업(exclusive lock)을 시도했지만, 여전히 로그인 API의 읽기 작업으로 걸린 `shared lock`이 해제되지 않아서 발생한 문제이다.

 

해결

찾아본 해결 방법은 다음과 같다.

• 동시 접근을 위한 설정 추가: `application.yml` 수정
• 수정 기능에 재시도 메커니즘 추가: Spring `@Retryable` 애노테이션 사용
• 애플리케이션 수준의 동기성 제어: Java `synchronized` 사용

우선 `application.yml` 수정을 시도해보고 동일 문제가 발생하면 점진적으로 수정 단계를 높여가는 것으로 정했다.

spring:
  datasource:
    url: jdbc:sqlite:project.db?busy_timeout=30000&journal_mode=WAL&synchronous=NORMAL&cache_size=10000&temp_store=MEMORY
    driver-class-name: org.sqlite.JDBC

 

1. journal_mode=WAL

이 옵션은 `Write-Ahead Logging`을 활성화하여 더 나은 동시성을 허용한다. 메인 데이터베이스 파일을 변경하는 것이 아닌 분리된 WAL 파일에 쓰기 작업을 진행한다. 읽을 때는 메인 데이터베이스와 커밋된 WAL 엔트리를 읽게 된다. 

읽기 작업 → 읽기: 메인 DB + WAL 파일 → No blocking
쓰기 작업 → WAL 파일에 쓰기 → 동시 진행 가능

동시에 여러 읽기 작업을 할 수 있고 이때 하나의 쓰기 작업도 가능하다. (하지만 여전히 동시 여러 쓰기 작업은 지원하지 않는다)

 

2. synchronous=NORMAL (문제 해결 X, 쓰기 성능 향상 목적)

이 옵션은 SQLite가 디스크에 데이터를 동기화하는 빈도를 조절할 수 있다.

• `OFF` : 가장 빠르지만, 가장 안전하지 않은 옵션
• `NORMAL` : 균형잡힌 상태. 안전성을 유지하면서 `fsync` 호출을 줄인다. 
• `FULL` : 가장 안전하지만, 가장 느리다. 

이제 모든 쓰기 작업마다 `fsync`를 호출하지 않으며, 중요한 순간에만 동기화 된다. 다만 서버가 다운되면 데이터 소실이 일어날 수 있다. 

 

3. cache_size=10000 (문제 해결 X, 읽기 성능 향상 목적)

페이지(Page)는 SQLite에서 데이터를 다루는 기본 단위이다. 이 옵션은 페이지 캐시 사이즈를 설정한다. 기본적으로 2000 pages로 설정되어 있다.

사이즈를 높임으로써 메모리를 더 사용하게 된다. 이는 메모리에 더 많은 데이터를 유지할 수 있고, 자주 접근되는 데이터는 캐시에서 가져올 수 있음을 의미한다. 또한 무거운 읽기 작업에 대해 더 나은 성능을 제공하게 된다. 

 

4. temp_store=MEMORY (문제 해결 X, 성능 향상 목적)

기본 설정은 `FILE`로 임시 작업에 대해 디스크를 사용한다. `MEMORY`로 변경하면 임시 테이블, 인덱스, 정렬 작업에 대해 disk 대신 메모리를 사용하게 된다. 임시 파일은 file lock을 위해 경쟁하지 않게 된다(lock 경쟁 감소). 

이는 sorting, join 쿼리와 같은 곳의 성능을 높일 수 있다. 

 

5. busy_timeout=30000

데이터베이스가 잠겼을 때(lock) 포기하기 전에 얼마나 대기할지 설정

 

.db-wal, .db-shm

위 설정을 적용하고 나서 서버를 가동하고 나니 db 파일 외에 다른 파일이 추가로 생성되었다. 

 

`.db-wal` 파일은 커밋되지 않은 쓰기 작업을 담고 있다. 새로운 데이터는 처음에 여기에 쓰이고 후에 메인 데이터베이스로 이동된다(Write-Ahead Log). 처음 쓰기 작업이 발생할 때 생성되며, 체크포인트될 때까지 유지된다. 

 

`.db-shm` 파일은 더욱 빠른 접근을 위해 WAL 파일을 인덱싱하는 목적을 갖고 있다. WAL 파일과 함께 생성되고 WAL이 체크포인트되면 삭제된다.   

 

이 두 파일은 런타임 시 발생하는 것이므로 굳이 버전 관리 저장소에 올리거나 배포할 때 포함시키지 않는다. 

 

 

 

 

🔖

• https://junstar17.github.io/db/2020/11/16/DB-SQLite-Lock-%EB%A7%A4%EC%BB%A4%EB%8B%88%EC%A6%98.html
• https://gywn.net/2013/08/let-me-intorduce-sqlite/
• https://yeon-kr.tistory.com/213