[SQLite] 락 매커니즘 & 트랜잭션

SQLite3는 기본적으로 다수의 프로세스가 하나의 데이터베이스 파일을 읽고 쓸수 있다. 그런데 만약 다수의 프로세스가 동시에 동일 데이터 베이스 파일에 접속해서 읽거나 쓰게 되면 데이터베이스 무결성이 깨지게 되는 문제가 발생하게 된다. 이러한 문제가 발생하는 것을 막기 위해 데이터베이스를 읽거나 쓰기 위해서는 해당 프로세스가 적절한 잠금(LOCK)권한을 획득하도록 하고 있다.

아래는 프로세스 관점에서 바라 본 데이터베이스 파일의 잠금 상태(Locking states)들이다.

잠금 상태	내용
UNLOCKED	프로세스가 파일을 열기(Open)만 한 상태이며 읽기/쓰기는 하지 않은 상태   : 다른 프로세스에서 읽기/쓰기 가능
SHARED	프로세스에서 파일을 읽고 있는 상태   : 다른 프로세스에서 동시 읽기 가능. 그러나 쓰기는 불가능
RESERVED	프로세스가 해당 파일에 쓰기를 할 예정인 상태   : 다른 프로세스가 해당 파일 읽기 가능(SHARED LOCK 획득). 하지만 시스템 전체에서는 오직 하나의 프로세스만 RESERVED LOCK을 가짐
PENDING	EXCLUSIVE LOCK을 획득하기 직전의 임시적인 상태   : 다른 프로세스의 SHARED LOCK이 모두 해제될 때(읽기 동작 완료)까지 이 상태 유지, 만약 한 프로세스가 PENDING LOCK을 가지고 있다면 다른 프로세스는 새롭게 SHARED LOCK 획득 불가능
EXCLUSIVE	프로세스가 파일을 쓰는 상태   : 시스템 전체에서 오로지 하나의 프로세스만이 EXCLUSIVE LOCK을 획득 가능

아래는 프로세스 A와 B가 동시에 하나의 데이터베이스를 공유하여 읽기/쓰기를 하는 가상의 시나리오이다.

하나씩 순차적으로 각가의 프로세스가 어떤 작업을 했는 지 살펴보고 그에 따른 잠금 상태에 대해서도 보도록 하겠다.

프로세스 A	프로세스 B
Sqlite> BEGIN;  --> UNLOCKED
	Sqlite> BEGIN; --> UNLOCKED Sqlite> INSERT INTO test VALUES(1) -->RESERVED
Sqlite>SELECT * FROM test; -->SHARED Sqlite>INSERT INTO test VALUES(2); -->RESERVED =>SHARED SQL error: database is locked
	Sqlite>COMMIT; --> PENDING => RESERVED SQL error: database is locked
Sqlite>ROLLBACK; --> UNLOCKED	Sqlite>COMMIT; -->RESERVED => UNLOCKED

먼저 프로세스 A에서 먼저 BEGIN을 통해 UNLOCKED 상태가 되었다. 그리고 뒤이어 프로세스 B또한 BEGIN을 통해 UNLOCKED 상태가 되고 그 뒤에 바로 특정 레코드를 삽입함으로써 RESERVED 잠금 상태가 된다. 이 상태에서 프로세스 A는 test 테이브를 조회(읽기)를 하게 되는 데 이 때 프로세스 A는 SHARED 상태가 된다. 프로세스 B가 RESERVED 상태를 가지고 있을 때 프로세스 A가 SHARED LOCK 획득하는 데 어떠한 문제도 없다는 것을 확인할 수 있다. 그러나 뒤이어 바로 프로세스 A가 레코드 삽입 (쓰기) 작업을 실행하려고 하지만 database is locked라는 에러 메세지와 함께 해당 작업이 실행되지 않음을 알 수 있다. 이는 이미 프로세스 B가 RESERVED LOCK을 획득하고 있기 때문이다. RESERVED LOCK은 단 하나의 프로세스만 가질 수 있기 에 프로세스 A는 RESERVED LOCK을 획득할 수 없고 이에 레코드 삽입 작업에 실패하게 되는 것이다. 작업 실패 후 프로세스 A는 다시 SHARED LOCK 상태로 돌아가게 된다. 이 때 프로세스 B가 COMMIT 명령어를 명시적으로 호출하지만 이 작업 또한 실패하게 된다. 그 이유는 프로세스 A가 SHARED LOCK인 상태이기 때문에 프로세스 B가 PENDING LOCK 권한을 획득하지 못하기 때문이다. 프로세스 B의 COMMIT 작업은 프로세스 A가 ROLLBACK 동작 이후 UNLOCKED 권한을 획득하면서 가능해진다. 프로세스 B 또한 COMMIT 이후 RESERVED에서 UNLOCKED 상태로 변경된다.

위 예시를 본 것처럼 트랜잭션 시작과 동시에 해당 프로세스는 UNLOCKED 상태를 획득하게 되는데 BEGIN 옵션을 통해 다른 락 상태를 획득할 수 있다.

BEGIN 옵션	내용
BEGIN DEFERRED	트랜잭션의 기본 동작(BEGIN과 동일)   : 읽기/쓰기 동작 수행 전까지 어떠한 락도 획득하지 않음
BEGIN IMMEDIATE	트랜잭션 시작과 동시에 RESERVED LOCK 획득   : 다른 프로세스에서는 IMMEDIATE/EXCLUSIVE LOCK 획득 불가능
BEGIN EXCLUSIVE	트랜잭션 시작과 동시에 EXCLUSIVE LOCK 획득   : 다른 프로세스에서는 일기/쓰기 동작 불가능

http://www.sqlite.org/lockingv3.html