즐겁게 배우는 SQL 7. 트랜잭션

jiffydev·2021년 7월 10일
0

즐겁게 배우는 SQL

목록 보기
7/13

본 포스트는 박재호님의 유튜브 강의(링크)를 보고 일부 발췌하여 정리한 내용입니다.
사용된 자료, 샘플 데이터 등은 모두 SQLite Tutorial에서 확인할 수 있습니다.

1. 기본 원리

관계형 데이터베이스의 꽃은 트랜잭션이라고 할 수 있다.
트랜잭션 덕분에 데이터베이스를 관리하면서 발생할 수 있는 복잡한 문제들은 DBMS에 넘기고, 엔지니어들은 비즈니스 로직에 더 집중할 수 있기 때문이다.

트랜잭션과 관련된 글을 읽다 보면 항상 등장하는 것이 ACID라는 기본 원리이다.

  • Atomicity
    트랜잭션 내에서는 모든 연산이 모두 완료 또는 모두 실패 둘 중 한 가지 상태를 보증한다.
    시도하는 변경 내역이 실패하면 전체 연산은 중단되고, 아무 일도 없었던 것처럼 보인다.

  • Consistency
    데이터베이스가 변경되면, 유효하고 일관된 상태로 유지된다.

  • Isolation
    어떤 트랜잭션 중에 이뤄진 모든 중간 상태 변경이, 다른 트랜잭션에 보이지 않게 함으로써 간섭 없이 동작할 수 있도록 한다.

  • Durability
    트랜잭션이 완료되면 시스템 오류가 발생하더라도 데이터가 손실되지 않는 것을 보장한다.

트랜잭션의 기본구성은 다음과 같다.

BEGIN TRANSACTION;
COMMIT;
ROLLBACK;

여기서 BEGIN TRANSACTIONCOMMIT 사이에 있는 작업을 하나의 단위로 삼아 COMMIT에서 그 전까지의 작업 내용을 DB에 반영하게 된다.

ROLLBACK은 무언가 문제가 생겼을 때 이전의 작업을 모두 취소하는 단계이다.

2. 정상적인 트랜잭션

트랜잭션을 구현하기 위해 우선 테이블을 만든다.

CREATE TABLE accounts ( 
	account_no INTEGER NOT NULL, 
	balance DECIMAL NOT NULL DEFAULT 0,
	PRIMARY KEY(account_no),
        CHECK(balance >= 0)
);

CREATE TABLE account_changes (
	change_no INTEGER PRIMARY KEY AUTOINCREMENT,
	account_no INTEGER NOT NULL, 
	flag TEXT NOT NULL, 
	amount DECIMAL NOT NULL, 
	changed_at TEXT NOT NULL 
);

account_changes의 내용이 웹사이트 것과 약간 다른데, 웹사이트의 예제에서는 change_no를 만들어 놓고 입력을 하지 않아, 그대로 따라하게 되면 에러가 발생하게 되므로 위의 테이블로 만들어야 한다.

위 테이블은 은행 계좌 테이블인데, accounts는 계좌에 얼마가 있는지를 보여주는 snapshot 같은 기능을 하고, account_changes는 계좌에 돈이 들어오거나 나가는 상황을 알 수 있다.

다음으로 데이터를 넣어 보도록 한다.

INSERT INTO accounts (account_no,balance)
VALUES (100,20100);

INSERT INTO accounts (account_no,balance)
VALUES (200,10100);

데이터가 들어간 것을 확인하면 트랜잭션을 만들 수 있다.

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;

account_no=100에서 1000이 빠지고 account_no=200으로 1000이 들어오는 상황이다.
만약 원자성이 없다면 내 통장에서는 돈이 빠져나갔는데 받는 사람에게는 들어오지 않는 최악의 상황이 발생할 수 있다.

3. 실패한 트랜잭션

지금까지 성공한 트랜잭션을 살펴봤다면, 이제부터는 우리 머리를 아프게 할 실패한 트랜잭션을 구현해 보자. 사용하는 데이터는 위와 동일하다.

BEGIN TRANSACTION;

INSERT INTO account_changes(account_no,flag,amount,changed_at) 
VALUES(100,'-',20000,datetime('now'));

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

기억할지 모르겠지만 테이블을 생성할 때 accounts 테이블에는 CHECK(balance >= 0) 라는 항목이 있었다.
이는 잔고는 항상 0이상이어야 한다는 뜻인데, 지금까지의 과정을 따라왔다면 accounts_no=100의 잔고는 20000보다 작다.
따라서 에러가 발생할텐데 그 전에 실행된 INSERT INTO구문은 어떻게 될까?

안타깝게도 저 상태에서는 accounts에서는 돈이 줄지 않았지만 account_changes에는 변경된 데이터가 그대로 삽입되어 버린다.

이 상황에서 롤백을 실행하면 실행 이전의 상황으로 돌아온 것을 볼 수 있다.

profile
잘 & 열심히 살고싶은 개발자

0개의 댓글