목차

들어가면서

SQLite 특징

SQLite 이럴 때 사용 O

SQLite 이럴 때 사용 X

SQLite 아키텍쳐

SQLite Lock 개념

SQLite 트랜잭션 개념

부록) Oracle WAL 처리 방식

포팅할 때 겪었던 명령어들

SQLite 용례(실사용기)

참고문헌

들어가면서

해당 문서는 실제 프로젝트에 적용하면서 정리한 내용, 보통의 이론적인 내용 SQLite DOC 및 괜찮은 블로그들을 참고 했다.

SQLite 특징

SQLite는 C언어로 개발된 embeded 데이터베이스 엔진이다. SQLite의 특징은 다음과 같다.

• 경량화: SQLite Library는 1MB ~ 0.6MB 이하이다. 또한 보통의 파일시스템 컨트롤 보다 약 30% 빠르다.
• 독립적: 표준 C 라이브러리를 통해 개발되었으며, 외부 라이브러리 또는 인터페이스에 종속적인 부분은 없으며, 단순 운영체제 위에서 독립적으로 혼자 잘 돌아간다.
• 신뢰성: SQLite는 많은 스마트폰, IoT장치, 데스트콥 어플리케이션에서 오래 사용되었다. 수치로 했을 경우, 몇억개의 사용처가 10년 넘게 유지되었으며, 아직까지 업데이트하며 유지보수(지속적인 오픈소스 개발 기여)가 되고 있다.
• 무서버: 데몬을 띄우는 보통의 RDBMS와 달리 서버없이 디스크 I/O를 통해 DB에 접근한다(Neo-Serverless, Application과의 같은 프로세스가 아닌 별도의 프로세스 지만, 그 별도의 프로세스는 Application이 관리하에 존재한다. 결국 Serverless이다. ).
• 무설정: 편리하다. 무서버의 특징으로 인해, 서버 프로세스의 설치, 설정, 초기화(가동), 관리, 트러블슈팅 등의 작업이 존재하지 않기 때문에 편리하다.
• 오픈소스: 오픈소스 라이센스는 public Domain으로 라이센스가 존재하지는 않지만, 지속적인 개발과 매 릴리즈마다 엄격한 테스트 과정이 존재한다. 최신 릴리즈 버전은 2019년 12월이다 ㄷㄷ.., 20주년 된 오픈소스 프로젝트이다.
• 1개의 데이터 파일: 말마따나 모든 데이터를 1개의 디스크 파일로 표현할 수 있다.
• 크로스 플랫폼 데이터 파일: 32bit / 64Bit, Big / Little Endian, 운영체제 상관없이 데이터 파일을 사용할 수 있다(VFM을 사용하기 때문에).

SQLite 이럴 때 사용 O

 SQLite의 경우, PostgreSQL, MariaDB와 같은 clinet/server SQL DB 엔진과의 적절한 비교대상이 아니다. 해당 RDBMS는 엔터프라이즈 데이터의 공유 저장소 구현이기 때문에 확장성, 동시성, 중앙집중화 및 제어 등이 중요 초점 사항이다.

 SQLite 엔진은 개별 프로그램의 로컬 데이터 저장소 구현이기 때문에, 경제성, 효율성, 신뢰성, 독립 및 단순성이 중요 초점 사항이다(보통 스마트폰의 어플리케이션의 DB 사용을 생각하면 상상이 좀 더 쉽다.).

각각 Serverless SQLite와 client/server RDBMS와의 장단점이 존재한다.

SQLite의 사용처는 다음과 같다.

• Embedded IoT 디바이스
• SQLite DB는 관리자가 필요 없으며, 디바이스에서 혼자 잘 돌아갈 수 있다. 예는 다음과 같다. 폰, TV, 게임 콘솔, 카메라, 시계, 부엌 장비, 자동차 등 모든 IoT 같은 장비
• Mini/Toy 프로젝트 또는 소/중규모 웹사이트, 트래픽이 적은 웹사이트에서 무리없이 잘 작동한다.
• 어플리케이션에서 엔터프라이즈 RDBMS의 데이터를 캐쉬하기 위해 사용될 수 있다.
• 프로토타입/데모 프로젝트
  
  • 배보다 배꼽이 더큰 상황을 저지해줄 수 있다. 어플리케이션의 데모 또는 프로토타입을 할 때 임시적으로 사용도 괜찮다.

SQLite 이럴 때 사용 X

• 다중 클라이언트, Database에 접근하는 많은 클라이언트가 존재할 경우(즉, 많은 동시성을 요구할 때)
• 엔터프라이즈 환경, 대규모 프로젝트
• 데이터웨어하우스, 데이터 대용량 처리의 제한, 해쉬조인을 지원하지 않는다. NL-Join(OLTP 적합)밖에 되지 않는다. 
• 빅데이터, SQLite는 사이즈 제한은 140테라바이트 이다.
• 빈도 높은 동시성, read에 대해 제한은 없으나 write은 딱 1개만 허용하기 때문에 동시성 보장을 못한다(WAL 모드를 통해 Read/Write를 동시에 수용할 수 있지만 결국 Write를 할 수 있는 프로세스는 1개이다.).
• SQLite FAQ (5) Can multiple applications or multiple instances of the same application access a single database file at the same time?
  
  1. 여러 프로세스가 동시에 DB를 접근하여 SELECT를 할 수 있다, 그러나, 한개의 프로세스만이 Write(INSERT/UPDATE/DELTE)를 할 수 있다.
  2. SQLite reader/write Lock을 사용하여 DB 접근을 제어한다.
  3. 만약 특정 프로세스가 DB Write를 원하면, 파일 Write 하는 동안, Database 파일은 Lock이 걸려있으며, 이 때 Lock은 조회도 할 수 없다.
  4. 즉, 매 Write때, DB 접속을 원하는 프로세스/쓰레드는 휴지기를 갖는다(아무것도 못한다.).         

SQLite 아키텍쳐

SQLite는 Core와 Backend로 구성이 되어있다. Core는 Interface, Tokenizer, Parser, Code generator, VM을 포함하고 있으며, Backend는 B-Tree, Pager, 파일 시스템에 접근하는 OS Interface를 포함하고 있다. Tokenizer, Parser, Code generator를 VM에 코드를 돌리기 위한 Compiler로 칭하고 있다.

(아키택쳐는 Compiler의 Parser/Code Generator 부분이 많이 어려웠다.)

(해당 내요은 공식홈페이지와 참고문헌을 참고하는게 좀더 좋다)

• Interface: 해당 인터페이스를 통해 SQL 쿼리문을 보낸다.
• Tokenzer: 요청된 SQL 쿼리를 스캔하여 각각 알맞은 토큰으로 변경한 후, 해당 토큰을 Parser에 전달한다.
• Parser: 토큰 스트림을 읽고, 해당 토큰들을 기반으로 하여 Parse Tree를 생성한다(C코드를 생성한다. 이 방식은 YACC/BISON과 같은 맥락이다.).
• Code Generator: Parse Tree 분석 후, SQL 작업을 수행할 수 있는 바이트코드를 생성한다.  
• VDBE(Virtual DataBase Engine): Code Generator가 생성한 바이트코드를 해당 VM에서 실행한다.
• BTree: 보통의 RDBMS의 경우, Index를 처리할 때 BTree를 사용한다. SQLite의 경우, 인덱스 뿐만 아니라, 데이터들 또한 BTree구조로 저장을 한다. SQLite에서 데이터를 조회하는 방식은 보통의 RDBMS에서 BTree를 통해 스캔하는 방식과 유사하다.
• Pager: SQLite에서의 데이터 처리의 최소 단위를 칭하는 것 같다(Oracle의 데이터 블록과 유사하다 생각한다.) 아래와 같이 Page안에 Data BTree, Index BTree가 존재하는 방식인 것 같다. 트리에 있는 값은 진짜 값이 있는 것 같다(오라클 index BTree의 경우, 데이터 블록이 있고 그 블록안에 여러 값이 존재함). 해당 페이지 단위로 Locking과 Transaction 작업을 진행한다(commit, rollback) 

• VFS(Virtual File System): 해당 Page의 실제 값을 조회할 때, 즉 Disk의 datafile를 조회할 때 VFS를 사용한다. 해당 VM을 사용하기 때문에 플랫폼에 상관없이 똑 같은 API의 인터페이스를 통해 파일을 조회할 수 있다.

SQLite Lock 개념

현재 설명하고자 하는 Lock 메커니즘은 SQLite3 버전의 메커니즘이다. Lock은 데이터의 무결성을 유지하기 위해 존재하는 개념이다. 1개의 프로세스가 DB에 접근할 때 Lock을 얻는다.

Lock의 종류는 아래와 같다.

• UNLOCKED: 파일을 열기만한 상태, DB에 Lock이 잡혀있지 않는 상태이다. 해당 상태에서는 읽기/쓰기가 불가능하다. 아무 프로세스가 접근하여, Locking을 하여, 데이터를 쓰거나 읽을 수 있다. 해당 상태는 기본 상태이다.
• SHARED: 파일을 읽고 있는 상태, 동시에 여러 프로세스가 Shared Lock을 설정하여, 동시에 DB 파일을 읽을 수 있다. 그러나, Shared Lock이 1개라도 잡혀있으면 DB 파일은 쓸수 없다.
• RESERVED: 파일을 쓸 예정인 상태, 프로세스가 Shared Lock을 잡았으며, DB 파일을 쓸 예정인 상태이다. Reserved Lock은 1개의 DB에 1개의 프로세스만 얻을 수 있다. Reserved Lock이 잡혔다고, Shared Lock을 못 잡는 것은 아니다. 즉, Reserved Lock이 잡혀도 DB 파일 읽기는 가능하다.
• PENDING: Exclusive Lock을 잡기 전의 상태, 즉 데이터를 쓰기 전의 상태이다. 해당 Lock은 DB 파일에 데이터를 쓰기 위해 기다리는 상태이다. 해당 Lock은 1개의 DB에 1개의 프로세스만 얻을 수 있으며, Shared Lock이 모두 풀릴 때까지 기다린다(다른 프로세스에서 읽기 완료될 때까지 대기). 해당 상태에 돌입하면, 누구도 해당 DB 파일에 접근하지 못한다. 이 때 접근하면, Database is locked라는 상태메시지가 출력된다. 만약 Shared Lock이 모두 사라지면 바로 Exclusive Lock으로 전이를 한다.
• EXCLUSIVE: 프로세스가 파일을 쓰는 상태, Exclusive Lock에 존재는 DB 파일에 데이터를 입력하기 위해서이다. 데이터의 입력은 Insert, Update, Delete가 해당한다. 동시성을 제고하기 위해서는 해당 상태가 최대한 짧아야 한다. 해당 상태 또한 누구도 DB 파일에 접근하지 못하며, 접근할 경우, Database is locked라는 상태메시지가 출력된다.

SQLite 트랜잭션 개념

Sqlite는 Rollback Journal이라는 방식을 통해 트랜잭션을 구현한다.

1개의 프로세스가 Exclusive Lock을 획득하여, 1개의 DB 파일에 데이터를 입력할 때 Journaling 기술을 사용한다. 변경되기 전의 데이터를 rollback journal에 저장한다. 저장 후, rollback journal에 DB 파일의 초기 크기 값도 저장되기 때문에, insert된 데이터도 rollback이 가능하다. 즉, 트랜잭션이 시작할 때 바구니 하나를 만들고, 변경되기 전의 값들을 바구니에 담고, 변경된 값들은 데이터 파일에 기록한다. Rollback상황에 마주치게 되면, 바구니에 값들을 갖고 Rollback을 실현한다. 트랜잭션 작업 중 시스템 크러쉬가 발생될 경우, 해당 journal은 “hot”이 되며, DB 데이터 무결성 복구를 위해 바로 사용된다.

Journal Mode는 총 6가지가 있다.

• DELETE: 기본 모드이며, 트랜잭션 종료시 파일을 삭제한다.
• TRUNCATE: 트랜잭션 종료시 파일을 0 상태로 만든다. DELETE 보다 더 빠르다.
• PERSIST: 매 트랜잭션 마다 파일을 덮어쓰기 한다. TRUNCATE보다 빠르다.
• MEMORY: Journal 데이터를 메모리에 기록한다. PERSIST 보다 빠르지만, DB Corrupt에 빠질 수 있다.
• WAL: Write-Ahead Logging 방식을 사용한다.
• NONE: Journaling을 하지 않음, 트랜잭션을 제어하지 못한다.

Journal WAL mode는 기존 전통적인 Journaling과 반대되는 방식이다. 일반 RDBMS에서 사용하는 WAL 방식을 차용하였다. OracleDBMS의 WAL 방식 설명은 아래를 참고한다. 해당 설명을 참고하고 읽으면 이해에 훨씬 도움이 될 것이다. 해당 WAL 방식을 통해 Read/Write를 동시에 할 수 있게 하여, 동시성을 증대시킨다.

SQLite에 일반 Journaling을 사용하면 파일에 대해 write/reading 상태 밖에 존재하지 않은데, WAL 방식을 사용하면, write/reading/checkpoing 상태가 존재한다. 또한 DB 파일 경로에 journal 임시 파일은 생기지 않고, wal 파일과 shm 파일 생성된다.

wal 파일에는 변경된, 변경하고 있는 데이터들을 저장한다. Commit을 할 경우에는 바로 db 파일에 적용하는 것이 아니라, wal 파일에 commit 구분자를 추가한다. 해당 wal 파일이 일정 크기만큼 커질 경우(default page size=1000) checkpoint가 발동되면서 wal 파일을 data file에 집어넣는 작업이 발생한다. shm 파일은 wal 파일에 접근할 때 공유 메모리 역할 즉, 캐쉬 역할을 한다.

이 때 궁금한 사항은 결국 WAL에 접근해야하면 기존 journal 방식과 똑같이 WAL 파일에 Lock이 걸리지 않냐 그럴 수 있다. 그러나, 다음과 같이 해결한다. 먼저, WAL의 유효한 커밋의 위치 파악을 한다. 해당 위치는 end_mark라 칭한다. WAL 모드에서는 CRUD 작업을 할 때 해당 end_mark를 통해 WAL 파일 복사 여부를 판단한다. 만약, 조회하고자 하는 데이터가 데이터 파일에만 있으면 해당 데이터 파일만 조회하고, 조회하고자 하는 데이터가 데이터 파일 + WAL 파일에도 존재하면 end_mark를 통해 필요 위치까지만 WAL 파일을 복사하여 조회한다. 이렇게 복사해서 사용하기 때문에 1Writer, N reader의 공존, 즉 writer/reader 가 같이 공존할 수 있는 동시성을 제공할 수 있다. 그러나, WAL 파일을 DB 파일에 저장하는 작업이 발생할 때는 Exclusive Lock이 발생한다. 해당 작업을 checkpointing라 한다.

부록) Oracle WAL 처리 방식

위의 사진은 오라클 공홈 DB 구조 사진을 발췌한 것이다. Oracle의 WAL 작업 아래와 같이 간단하게 설명한다.

1. 클라이언트 insert 쿼리 요청
2. 해당 insert문과 관련된 데이터의 undo 세그먼트 생성(rollback을 위한)
3. 해당 insert문과 관련된 데이터의 redo log 생성(트랜잭션 작업 복구를 위한) 및 undo 세그먼트 적재
4. 해당 redo log는 redo log buffer에 저장
5. 변경된 데이터를 buffer cache에 저장(해당 블록은 더티 블록으로 전이)
6. 클라이언트에서 Commit 요청을 할 경우, LGWR 프로세스는 rede log file에 log 파일을 저장한다. 만약 rollback을 하는 경우, redo log에 보관된 undo 세그먼트를 통해 rollback을 한다.
7. 변경된 buffer cache의 더티 블록은 CKPT 프로세스에 의해 때가 되면 데이터 파일과 동기화 작업 실행 명령을 내린다. 데이터 파일 동기화 작업은 DBWR이 진행하며, 더티 블록 데이터를 데이터 파일에 등록한다.
8. 위의 과정에서 데이터 저장 보다 로그 파일을 먼저 적재하는 것을 WAL 원칙 및 작업이라 한다. 이렇게 할 경우, DB corrupt으로부터 데이터를 무결하게 유지할 수 있다.

포팅할 때 겪었던 명령어들

1. 리눅스에서 SQLite 사용할 때 
   •  보기 편하게 설정(sqlite db 파일과 같은 경로에 .sqliterc 파일 생성)
   • vim .sqliterc( 아래 추가, PRAGMA 설정도 될 것 같음 확인은 안해봄 ㅋㅋ ) 
     
     • .header on
     • .mode colum
2. SQLite 터미널 안에서
   • .open $db_name : 데이터베이스 생성(파일이 경우) 그리고 open
   • .exit, quit : 데이터베이스 종료
   • .tables: 테이블 목록 조회
   • .schema $tbl_name : DDL 조회
   • PRAGMA foreign_keys =ON: 참조 무결설 설정 ON(SQLite 하위 호환 때문에 디폴트 설정은 OFF이다.)
3. 포팅할 때
   • Right/Full Outer Join을 지원하지 않는다. 그러므로 Right Outer Join의 경우, Left Outer Join으로 변경한다. Full Outer Join 쿼리를 잘 변경하자
   • now() 함수
     • datetime( 'now', 'localtime'): yyyy-MM-dd HH:mm:ss 포멧으로 저장
     • strftime('%s', datetime('now', 'localtime'): 유닉스 타임스탬프로 저장
     • strftime('%Y-%m-%d', datetime('now', 'localtime')): yyyy-MM-dd 포멧으로 저장
     • 주의사항: 맨위의 처럼 DB insert를 할 경우, 데이터 조회할 때 dateformat 오류가 생길 수 있다. 해당 오류는 다음과 같이 해결할 수 있다. 1)유닉스 타임스탬프로 저장해서 해결 2) PRAGMA date_string_format=yyyy-MM-dd HH:mm:ss  설정
   • 타입
     • 문자형 => varchar(n)
     • 숫자형 => integer
     • text => text
     • date => datetime
     • blob/bytea/varbinary => blob
   • auto_increment 적용 => auto_increment (DDL에 default를 붙이자)
   • date add 1 day
     • strftime('%s', datetime('now', 'localtime', '+1 day') );
   • MyBatis를 사용할 경우, sqlite의 JDBC ResultSet.getBlob()이 impelements가 되지 않았음 때문에, BlobTypeHandler를 확장하거나, TypeHandler를 impelements를 해서 ResultSet.getBytes로 변경해서 한다. 퍼시스턴스 프레임워크(SQL Mapper 또는 ORM)를 쓰지 않는경우 ResultSet에서 getBlob이 아닌 getBytes를 사용한다. 타입 핸들러 코드는 아래와 같다.

public class SqliteBlobTypeHandler extends BlobTypeHandler {

	@Override
	public byte[] getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
		return cs.getBytes( columnIndex );
	}
	
	@Override
	public byte[] getNullableResult(ResultSet rs, String columnName) throws SQLException {
		return rs.getBytes( columnName );
	}
	
	@Override
	public byte[] getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
		return rs.getBytes( columnIndex );
	}
	
}	

• primary key를 제외한 필드에 대해서 auto_incremenet가 적용되지 않는다. 다음과 같이 처리 ((SELECT IFNULL(MAX(id), 0) + 1 FROM Log)
• JDBC URL 관련
  • foreign_keys=ON;( 디폴트 참조 무결성은 off이다.)
  • journal_mode=WAL 
  • busy_timeout=60000
  • Tomcat Context.xml의 DataSource Resource는 다음과 같이 설정할 수 있다.
  • url="jdbc:sqlite:C:/Users/--/Desktop/sqlite-tools-win32-x86-3300100/test.db?foreign_keys=ON&busy_timeout=60000&journal_mode=WAL&date_string_format=yyyy-MM-dd HH:mm:ss"
  • xml 에서는 &a m p ; 를 통해 설정과 설정사이를 연결해준다.

SQLite 용례(실사용기)

1) SQlite 멀티 쓰레드 사용

• 맨처음은 파일 DB를 잘 몰라, Database Lock이 걸릴까봐, SqlSession 객체 인스턴스를 한개만 만들었다. 
• 그래서, 멀티 쓰레드라도, 1개의 객체 인스턴스를 쓰면 괜찮은 줄 알았다. 
• SqlSession 여러개 만드나, 1개 만드나 큰차이가 없는 것 같았다. 

2) SQlite 멀티 프로세서 사용(2개의 데몬에서 1개의 DB 파일 사용) - 첫 번째 문제

• 2개의 서버에서 1개의 DB 파일을 사용할 때 슬슬 문제가 올라왔다.
• Database is locked가 떴다...

• 처음에는 SQLiteException을 통해 catch 슬롯에서 다시 자기 자신을 호출하는 재귀 함수를 통해 개선했음, 근데 문제는 어플리케이션 코드의 수정과 재귀 함수에 스택오버 플로우의 문제가 발생함, 분명, Connection Timeout 같은것이 있을텐데 찾고 계속 찾다 보니 있었다.
• busy_timeout=60000
• 데이터소스 설정할 떄 해당 설정을 하면 다중 프로세스라도 기다렸다가 진행한다. 

3) SQlite 멀티 프로세서 사용(2개의 데몬에서 1개의 DB 파일 사용) - 두 번째 문제

• busy_timeout 설정을 통해 개선 그러나 특정 API 처리할 때 똑같은 현상 발발
• 해당 API 처리는 2개의 데몬이 동시에 처리하는 작업임.

• 문제는 아래와 같다.

• B 프로세스가 노란색 지점에서 BEGIN TRANSACTION을 시도하는 순간 A 프로세스의 EXCLUSIVE LOCK에 의해 LOCK 선점을 하지 못해, Database is locked가 생겼다. 

4-1) SQlite 멀티 프로세서 사용(2개의 데몬에서 2개의 DB 파일 사용) - 문제 파악

• 결국 DB 파일을 분리했다ㅡㅡ.
• 그러나, A 프로세스는 B 프로세스의 DB 파일 참조가 필요하다.
• 즉, A 프로세스는 1) A DB파일 2) B DB 파일을 사용해야한다.
• 어플리케이션 코드 수정은 불가피하다. 
• 그러나, 한번의 어플리케이션 수정과 설정 값 제어를 통해 충분히 유연하게 대처가능하다.

4-2) SQlite 멀티 프로세서 사용(2개의 데몬에서 2개의 DB 파일 사용) - Spring 프로젝트 적용

• 일단 간단하다. 
• Spring에 2개의 데이터 소스 빈 생성 및 등록을 한다.
• 그리고 DAO 객체는 해당 빈을 선택적으로 사용한다.
• 쉽게 얘기해서, 선택적인 빈 주입과 선택적인 빈의 사용으로 어플리케이션 코드의 수정을 최소화 하고 유연하게 대응할 수 있다. 

참고문헌

https://gywn.net/2013/08/let-me-intorduce-sqlite/

https://lhcsoft.blogspot.com/2019/04/sqlite-multi-thread.html

https://thinking-jmini.tistory.com/23

https://www.sqlite.org/docs.html

https://dzone.com/articles/how-sqlite-database-works

https://medium.com/technology-in-essence/how-sqlite-database-works-b10ac80e4f07

http://forensicinsight.org/wp-content/uploads/2013/07/INSIGHT-SQLite-%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4-%EA%B5%AC%EC%A1%B0.pdf

https://stackoverflow.com/questions/22673833/sqlite-wal-mode-checkpoint-on-background-thread-wal-journal-never-shrinks