[SQLite] Sqlite transaction 처리문 - BEGIN TRANSACTION

[SQLite 사용법] Sqlite transaction 처리문 - BEGIN TRANSACTION

 

SQLite 사용법 관련  목차 

1. [SQLite]-sqlite 소개 -Serverless-Database
2. [SQLite]-SQLite_관리-Commands
3. [SQLite] sqlite 테이블 생성 - create table
4. [SQLite] Sqlite transaction - ACID개념
5. [SQLite] [SQLite] Sqlite transaction 처리문 작성 - BEGIN TRANSACTION
6.[SQLite] date형식 timestamp를 이용하여 날짜 표현하기
7.[SQLite] SQLite 윈도우-리눅스 설치하기

 

데이터 베이스는 동시에 입력, 수정,삭제가 일어나는 경우에도 데이터를 안전하게 처리하여 완전한 데이터 정합성을 갖추도록 결과를 저장하고 관리하는 기능을 제공합니다. 흔히 데이터베이스의 중요한 기능으로 ACID (atomic, consistent, isolated, and durable)를라고 도 하며 데이터의 동시 입력과 수정이 발생하는경우 데드락이나, 데이터 중복을 방지 하기 위한 기술입니다.

여러사람이 동시에 데이터를 입력 하더라도 일관성을 유지 하기위해 각 데이터베이스마다 특성을 가진 Transaction 처리 방법들이 있습니다. SqlLite도 그러한 기능을 갖추고 있습니다.

ACID에 대하여 알아보고 데이터의 일관성을 유지하는데 필요한 Transaction을 알아 보도록 하겠습니다.

 

1. SQLite 와 ACID 개요

SQLite는 모든 변경 사항과 쿼리가 원자성(Atomic), 일관성(consistent), 격리(isolation) 및 내구성 (durable) 등 ACID를 제공하는 트랜잭션 데이터베이스입니다.


SQLite는 프로그램 충돌, 운영 체제 덤프 또는 컴퓨터 정전으로 인해 트랜잭션이 중단된 경우에도 모든 트랜잭션이 ACID를 준수함을 보장합니다.

Atomic(원자성): 트랜잭션은 원자적이어야 합니다.
즉 변화를 더 작은 것으로 나눌 수 없다는 의미입니다.
트랜잭션은 모두 성공하거나 또는 실패하는 데이터베이스 운용의 집합입니다.
트랜잭션을 커밋하면 전체 트랜잭션이 적용되거나 일부만 적용되지 않는것이 아니라 commit이 필요한 대상은 모두 성공하거나 모두 실패 해야 하는 대상 범위라고 이해 하면 좋을듯 합니다.

Consistent(일관성): 트랜잭션이 실행을 성공적으로 완료하면 언제나 일관성 있는 데이터베이스 상태로 유지하는 것을 의미합니다. 무결성 제약으로 모든 계좌는 잔고가 있어야 한다면 이를 위반하는 트랜잭션은 중단되도록 합니다. (제약 조건을 통해서 null값이 오거나 너무 적은 값, 숫자 영역에 문자가 있다는등의 데이터 오류를 방지도 할 수 있습니다)
또 트랜잭션이 시작되고 데이터를 수정하는 여러 사람이 명령문을 실행하여 커밋되거나 롤백될 때 트랜잭션이 데이터베이스 일관성을 유지해야 하는 것은 더욱 중요합니다.
동시 여러 사람이 수정을 하더라도 데이터 값이 가져야 하는 경우를 데이터의 값을 기대하는 상황에 맞게 관리하는 기능은 데이터 베이스의 기본 역할 입니다.

Isolation (격리) : SQL이 수행되는 세션에서 보류 중인 트랜잭션은 다른 세션과 격리되어야 합니다. 세션이 트랜잭션을 시작하고 INSERT또는 UPDATE문을 실행하여 데이터를 변경하면 이러한 변경 사항은 현재 세션에만 적용되고 다른 세션에는 적용되지 않도록 합니다. 또 트랜잭션이 시작된 후 다른 세션에서 커밋된 변경 사항은 현재 세션에서 볼 수 없어야 합니다.

Durable (지속성): 트랜잭션이 성공적으로 커밋되면 정전이나 프로그램 충돌과 같은 조건에 관계없이 변경 사항이 데이터베이스에 영구적이어야 합니다. 반대로 트랜잭션이 커밋되기 전에 프로그램이 충돌하면 변경 사항이 지속되지 않아야 합니다.

 

2. SQLite 트랜잭션 문

트랜잭션은 BEGIN ... COMMIT TRANSACTION 문을 이용하여 DATA를 DB에 저장되는 시점을 수동으로 조정합니다. 주로 데이터를 읽고 쓰는 과정에서 여러 사용자가 동시에 데이터를 읽고 쓰게 되면 데이터를 입력하는 시점에 데드락이나 참조값이 변화하여 데이터 값이 의도한 상태로 저장되지 않는 상황이 있기 때문입니다. 일반적으로 은행의 계좌에서 동시에 입금과 출금이 이루어지는 경우로 예를 많이 들고 있습니다.

이러한 트랜잭션은 일반적으로 BEGIN TRANSACTION 부터 다음 COMMIT 또는 ROLLBACK 명령까지 처리가 성공하면 DB에 저장(Commit)하든지 실패하면 저장을 취소(Rollback) 하도록 작업을 하나의 단위로 지정할 수 있습니다. ROLLBACK 충돌 해결 알고리즘은 데이터베이스가 닫히거나 오류가 발생하여 지정된 경우 트랜잭션을 ROLLBACK 하도록 합니다.  

 

1.1. Transaction Control Syntax

begin-stmt: 

트랜잭션은 DEFERRED, IMMEDIATE 또는 EXCLUSIVE일 수 있습니다. 

1) 기본 트랜잭션 동작은 DEFERRED입니다.

2) DEFERRED는 데이터베이스에 처음 액세스할 때까지 트랜잭션이 실제로 시작되지 않음을 의미합니다. IMMEDIATE는 데이터베이스 연결이 쓰기 문을 기다리지 않고 즉시 새 쓰기를 시작하도록 합니다.

3) EXCLUSIVE는 쓰기 트랜잭션이 즉시 시작된다는 점에서 IMMEDIATE와 유사합니다. EXCLUSIVE 및 IMMEDIATE는 WAL 모드 에서 동일하지만 다른 저널링 모드에서 EXCLUSIVE는 트랜잭션이 진행 중인 동안 다른 데이터베이스 연결이 데이터베이스를 읽는 것을 방지합니다.

 

commit-stmt: 

rollback-stmt: 

다른 스레드 또는 프로세스에 열린 읽기 연결이 있는 경우 COMMIT를 실행하려고 시도하면 SQLITE_BUSY 반환 코드가 발생할 수도 있습니다. 이러한 방식으로 COMMIT가 실패하면 트랜잭션이 활성 상태로 유지되고 삭제 가능 시점 기회를 얻은 후에 COMMIT를 나중에 재시도할 수 있습니다.

명시적 COMMIT 명령은 보류 중인 SELECT 문 이 있는 경우에도 즉시 실행됩니다 . 그러나 보류 중인 쓰기 작업이 있는 경우 COMMIT 명령은 오류 코드 SQLITE_BUSY 와 함께 실패합니다 .

PRAGMA journal_mode 가 OFF로 설정 되면 ( 롤백 저널 파일이 비활성화)  ROLLBACK 명령의 동작은 작동(정의) 되지 않습니다.

 

2.2. Transactions 처리 예시

 

1) BEGIN TRANSACTION먼저 명령 을 실행하여 트랜잭션을 엽니다 .

 -  BEGIN TRANSACTION 명령문을 실행한 후 트랜잭션은 명시적으로 커밋되거나 롤백될 때까지 열려 있습니다.

BEGIN TRANSACTION;

 

2) SQL 문을 실행하여 데이터베이스의 데이터를 선택하거나 업데이트합니다.

 - 변경 사항은 현재 세션(또는 클라이언트)에만 표시됩니다.

3) COMMIT 또는 COMMIT TRANSACTION문 을 사용하여 데이터베이스에 변경 사항을 커밋합니다 .

COMMIT;  
or 
ROLLBACK;

 

4) Transaction  기본 예제 

BEGIN TRANSACTION;

UPDATE accounts
   SET balance = balance - 1000
 WHERE account_no = 100;

UPDATE accounts
   SET balance = balance + 1000
 WHERE account_no = 200;
 
INSERT INTO account_changes(account_no,flag,amount,changed_at) 
VALUES(100,'-',1000,datetime('now'));

INSERT INTO account_changes(account_no,flag,amount,changed_at) 
VALUES(200,'+',1000,datetime('now'));

COMMIT;

 

5) Transaction에서 Rollback 이 되지 않을 가능성

트랜잭션 내에서 특정 종류의 오류가 발생하면 트랜잭션이 자동으로 롤백되거나 롤백되지 않을 수 있습니다. 자동 롤백을 유발할 수 있는 오류는 다음과 같습니다.

• SQLITE_FULL : 데이터베이스 또는 디스크 가득 참
• SQLITE_IOERR : 디스크 I/O 오류
• SQLITE_BUSY : 다른 프로세스에서 사용 중인 데이터베이스
• SQLITE_NOMEM : 메모리 부족

 

이러한 모든 오류에 대해 SQLite는 작업 중이던 하나의 명령문만 실행 취소하고 동일한 트랜잭션 내에서 이전 명령문의 변경 사항을 그대로 두고 트랜잭션을 계속합니다. 그러나 수행되는 명령문과 오류가 발생한 지점에 따라 SQLite가 전체 트랜잭션을 롤백하고 취소해야 할 수도 있습니다.

 

SQLite 사용법 관련  목차 

1. [SQLite]-sqlite 소개 -Serverless-Database
2. [SQLite]-SQLite_관리-Commands
3. [SQLite] sqlite 테이블 생성 - create table
4. [SQLite] Sqlite transaction - ACID개념
5. [SQLite] [SQLite] Sqlite transaction 처리문 작성 - BEGIN TRANSACTION
6.[SQLite] date형식 timestamp를 이용하여 날짜 표현하기
7.[SQLite] SQLite 윈도우-리눅스 설치하기