SQL - PostgreSQL, Transaction, Save Point, Isolation Level, Phenomena

PostgreSQL

 

 

✅ Transaction이란?

---

Transaction = 여러 작업을 “하나의 묶음”으로 처리하는 단위

 

여러 SQL 쿼리에 걸쳐 데이터 무결성(integrity)을 강제한다.

은행, 항공사, 온라인 티켓 예매 시스템 등 중요한 데이터를 다루는 산업들에서 사용하는 개념이다.

 

Commit; 되기 전까지 한 Transaction에서 만든 변경사항은 다른 Transaction에서 보이지 않는다.

(SELECT, UPDATE, DELETE 같은 문들은 사실 하나의 작은 Transaction들이다.)

Transaction의 4가지 성질

 

Transaction 사용법

BEGIN;

UPDATE accounts
SET
	balance = balance - 500
WHERE
	account_holder = 'nico';

SELECT
	*
FROM
	accounts;

UPDATE accounts
SET
	balance = balance + 500
WHERE
	account_holder = 'lynn';

COMMIT;

BEGIN;으로 Transaction을 열고 COMMIT;으로 닫는다.

 

Save point

BEGIN;

UPDATE accounts
SET
	balance = balance + 1500
WHERE
	account_holder = 'lynn';

SAVEPOINT transfer_one;
RELEASE SAVEPOINT transfer_one;

SELECT
	*
FROM
	accounts;

UPDATE accounts
SET
	account_holder = 'rich lynn'
WHERE
	account_holder = 'lynn';

ROLLBACK TO SAVEPOINT transfer_one;

UPDATE accounts
SET
	balance = balance - 1500
WHERE
	account_holder = 'nico';

COMMIT;

SAVEPOINT를 생성해놓으면 ROLLBACK할 때 해당 SAVEPOINT를 ROLLBACK TO SAVEPOINT로 지정해서 되돌릴 수 있다.

그냥 ROLLBACK을 하게 되면 BEGIN 이후의 모든 변경사항이 취소된다.

 

Isolation level

Transaction에서 발생하는 현상(Transaction Phenomena)을 막아주기도 한다.

 

1. Read Uncommited

아직 commit이 되지 않은 데이터까지 읽을 수 있는 수준

 

2. Read Commited

commit된 데이터만 읽는다

 

3. Repeatable Read

transaction 동안 같은 데이터를 항상 동일하게 보장

스냅샷은 non-transaction 명령문이 실행될 때 찍힌다. (SELECT, UPDATE 등 처음으로 데이터를 읽는 순간)

 

4. Serializable

여러 transaction을 동시에 실행해도 '순차적으로 실행한 것처럼' 보이게 만드는 수준

 

 

Phenomena

1. dirty read

transaction이 commit 되지 않은 transaction이 작성한 데이터를 동시에 읽을 때 발생한다.

(PostgreSQL에서는 기본적으로 Read Commited라 dirty read가 발생하지 않는다.)

 

2. nonrepeatable read

같은 row를 두 번 읽었는데 값이 달라지는 현상

 

3. phantom read

같은 조건으로 조회했는데 '없던 row가 생기거나 사라지는 현상'

 

4. serialization anomaly

동시에 실행된 transaction 결과가 '순차적으로 실행한 결과와 달라지는 문제'

 

 

https://www.postgresql.org/docs/current/transaction-iso.html

13.2. Transaction Isolation

 

SELECT FOR UPDATE, SELECT FOR SHARE

commit 할 때까지 아무도 row를 건드릴 수 없다.(Lock 생성)

읽기는 가능(SELECT할 때 lock을 걸지 않는다면)

 

UPDATE

BEGIN;
SELECT
	balance
FROM
	accounts
WHERE
	account_holder = 'lynn'
FOR UPDATE;
COMMIT;

UPDATE는 exclusive lock을 생성한다.(아무도 수정이나 삭제 불가, 또 다른 lock을 생성도 불가)

 

SHARE

BEGIN;
SELECT
	balance
FROM
	accounts
WHERE
	account_holder = 'lynn'
FOR SHARE;
COMMIT;

SHARE는 아무도 수정이나 삭제는 불가능한 shared lock을 생성, 하지만 다른 transaction에서 또 다른 lock을 생성할 수는 있다.