Transaction이란?

Database Transaction이란 데이터베이스의 상태를 변화시키기 위해 수행하는 작업의 단위를 뜻한다. Transaction에 대한 자세한 설명과 Transaction의 특징인 ACID(Atomicity, Consistency, Isolation, Durability)에 대해서는 요기 블로그를 읽어보면 좋을 것 같다!

Transaction Type

Transaction에는 크게 System Transaction과 User Transaction이 있다.

System Transaction

System Transaction은 autocommit mode를 말한다. BEGIN COMMIT/ROLLBACK 명령어를 명시하지 않더라도 내부적으로 모든 SQL 문을 하나의 트랜잭션 내에서 수행되도록 하는 것을 의미한다. (참고)
SELECT 구문인 경우 read transaction을 생성하고, SELECT를 제외한 다른 구문에 대해서는 read transaction을 생성한 후, write transaction으로 바꿔준다.

User Transaction

autocommit을 특징으로 하는 System transaction은 비용이 비싸다는 단점이 있다.
따라서 BEGIN COMMIT/ROLLBACK을 명시적으로 사용하여 autocommit mode를 비활성화하는 것을 user transaction이라고 한다.

Savepoint

Savepoint를 사용하면 중첩된 트랜잭션을 생성할 수 있다.

• SAVEPOINT [savepoint_name]
• ROLLBACK TO [savepoint_name] : 수행 중인 트랜잭션을 미리 표시해둔 savepoint_name 위치로 복원
• RELEASE [savepoint_name] : 해당 savepoint_name의 트랜잭션을 데이터베이스에 반영하는 동시에 savepoint_name을 삭제한다.

예를 들어 아래와 같이 save_point_1과 save_point_2를 만들어 준 다음

create table test (a int);
BEGIN;
insert into test values(1);
SAVEPOINT save_point_1;
insert into test values(2);
SAVEPOINT save_point_2;
insert into test values(3);

test table을 보면 1,2,3이 있지만,

ROLLBACK TO save_point_2;

를 실행한 후의 test table을 보면 1, 2가 출력된다.

Lock Management

Lock Management는 ACID의 Isolation과 관련된다.
만약 다수의 프로세스가 동시에 데이터베이스 파일에 접속해서 읽거나 쓰게 되면 무결성(Integretiy)이 깨지게 되는데, 데이터베이스를 읽거나 쓰기 위해서 해당 프로세스가 적절한 잠금(LOCK) 권한을 획득하는 것을 Lock Management라고 한다.

Lock Type

NOLOCK, SHARED, EXCLUSIVE, RESERVED, PENDING 총 5가지가 있다.

NOLOCK

어떠한 Operation Type도 가지고 있지 않은 상태를 말한다. Transaction이 시작되었을 때 Default로 가지는 상태를 말한다.

SHARED

프로세스에서 파일을 읽고 있는 상태, 즉 READ Operation Type을 가지고 있는 상태이다. 다른 프로세스에서 동시에 읽기는 가능하지만, 쓰기는 불가능하다.

RESERVED

프로세스가 해당 파일에 쓰기를 할 예정인 상태이다. 다른 프로세스가 해당 파일을 읽기 위해 SHARED LOCK을 획득할 수 있지만, 오직 하나의 프로세스만 RESERVED LOCK을 가져야 한다.

EXCLUSIVE

프로세스가 파일을 쓰고 있는 상태, 즉 WRITE와 READ Operation Type을 가지고 있는 상태이다. 시스템 전체에서 오로지 하나의 프로세스만이 EXCLUSIVE LOCK을 획득 가능하다.

PENDING

EXCLUISVE LOCK을 획득하기 직전의 임시적인 상태를 말한다. 다른 프로세스의 SHARED LOCK이 모두 해제될 때까지 이 상태를 유지하며, 만약 한 프로세스가 PENDING LOCK을 가지고 있다면, 다른 프로세스는 새롭게 SHARED LOCK을 획득할 수 없다.

정리하면 아래 표와 같다.
행은 Existing Lock Mode를, 열은 Request Lock Mode이며, O는 새로운 요청이 받아들여질 수 있고, X는 받아들여질 수 없음을 의미한다.

	SHARED	RESERVED	PENDING	EXCLUSIVE
SHARED	O	O	O	X
RESERVED	O	X	X	X
PENDING	X	X	X	X
EXCLUSIVE	X	X	X	X

예를 들어 현재 시스템에서 RESERVED LOCK이 존재할 때, 다른 프로세스에서 SHARED LOCK을 획득할 수 있지만, RESERVED, PENDING, EXCLUSIVE LOCK은 획득이 불가능하다.

Locking state transition diagram

• Read transaction의 경우 : NOLOCK -> SHARED -> NOLOCK
• Write transaction의 경우 : NOLOCK -> SHARED -> RESERVED -> PENDING -> EXCLUSIVE -> NOLOCK

Explicit Locking

트랜잭션 시작과 동시에 해당 프로세스는 NOLOCK 상태를 획득하게 되는데, BEGIN 옵션을 통해 다른 상태를 명시해줄 수 있으며, 이를 Explicit Locking이라고 한다.

BEGIN [EXCLUSIVE | IMMEDIATE | DEFERED] TRANSACTION

EXCLUSIVE

트랜잭션 시작과 동시에 EXCLUSIVE LOCK을 획득하게 되며, 다른 프로세스에서는 읽기와 쓰기 동작이 불가능하다.

IMMEDIATE

트랜잭션 시작과 동시에 RESERVED LOCK을 획득한다. 다른 프로세스에서는 BEGIN IMMEDIATE나 BEGIN EXCLUSIVE이 불가능하며, 기존에 SHARED LOCK획득 세션은 읽기를 계속할 수 있으나, 새로운 세션에서는 읽지 못한다.

DEFERRED

BEGIN과 동일하며, 읽기/쓰기 동작 수행 전까지 어떠한 LOCK도 획득하지 않는다.

DeadLock

아래 그림에서 Transaction1과 Transaction2가 SHARED LOCK을 획득한 상태에서, Transaction1이 RESERVED LOCK을 획득한 후에, Transaction2가 RESERVED LOCK을 획득하기 위해 WAIT하게 된다. 이후, Transaction1이 EXCLUISVE LOCK을 획득하기 위해서는 다른 SHARED LOCK을 해제해야 하지만, Transaction2는 WAIT하고 있는 상태이기에, SHARED LOCK이 제거되지 않기에 DEADLOCK이 발생하게 된다.

SQLite에서는 이를 방지하기 위해 Deadlock Prevention을 제공한다.
(구체적으로 어떻게 제공하는지에 대해서는 조금 더 공부를 하고 적어보도록 하겠다)

Journal Management

journaling이란 스토리지에 데이터를 저장하기 전에 journal 영역에 데이터의 변경 이력을 저장하고, 스토리지에 데이터 변경 내역을 저장하는 활동을 의미한다고 한다.
journal 파일은 "파일이름-journal"을 사용하며, rollback 방식을 통해 트랜잭션을 구현한다.

Journal mode

• DELETE : rollback journal 파일을 매번 생성/삭제
• MEMORY : rollback journal을 파일이 아닌 메모리에 유지. DISK I/O를 줄일 수 있음.
• OFF : rollback journal을 사용하지 않고 DB 파일에 직접 기록한다.(rollback 사용 불가)
• PERSIST : rollback journal을 지우지 않는다. 파일의 header를 0으로 만든다.
• TRUNCATE : rollback journal 파일을 매번 생성/삭제하지 않고, 파일 사이즈만 0으로 설정 후 재활용. DELETE보다 훨씬 빠름
• WAL : write-ahead-logging.

보고서를 작성하다가 DELETE가 TRUNCATE보다 빠른 결과가 나와서 구글링을 하다가 다음과 같은 사실을 발견했다.

'TRUNCATE' is a constant time operation while 'DELETE' complexity depends on the number of rows in the table. 'TRUNCATE' is a fast operation to quickly clear out huge tables, but 'DELETE' is much faster when a table contains just a handful of rows.

즉, TRUNCATE가 DELETE보다 항상 빠른 것은 아니고, 테이블의 크기가 작은 경우에는 DELETE가 속도가 훨씬 빠르게 나온다고 한다.

Logging Protocol

1. 원본 데이터를 별도의 파일에 저장
2. 데이터 변경
3. 백업 데이터 제거

Commit Protocol

SQLite는 flush-log-at-commit과 flush-database-at-commit을 제공한다고 한다.
전자는 commit이 되었을 때 log record를 flush하는 것, 후자는 commit이 되었을 때 database page를 flush하는 것이라고 한다.

만약 위의 과정 중 1번과 2번 사이에서 트랜잭션이 실패를 하게 된다면, 업데이트 사항을 무시한다.
2번에서 실패를 하게 되면, before image를 journal file에서 DB로 복사한다.
2번과 3번 사이에서 실패하게 되면, DB 파일에서의 모든 변경사항은 disk로 flush된다.

참조

• Lock Management
  • https://thinking-jmini.tistory.com/23
  • https://mindgear.tistory.com/188
• Journal Management
  • https://clear-sky-sun.tistory.com/59
  • https://aroundck.tistory.com/5948
  • https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=sysganda&logNo=30180103718
  • https://www.lob.com/blog/truncate-vs-delete-efficiently-clearing-data-from-a-postgres-table