Transaction

💡 이 내용 이후로 모든 SQL 관련 내용은 NomadCoder 강의를 듣고 정리한 글입니다.
아래의 링크에 SQL 학습에 필요한 데이터가 존재합니다.
https://nomadcoders.co/sql-masterclass

아래의 글과 함께 보시면 좋습니다.

2025.07.16 - [Programming/DataBase] - Transaction

Transaction

DB에서 사용되는 연산의 최소 단위

하나의 transaction 내에서 에러가 발생하면 모든 연산은 취소되어야 한다.

SELECT와 같은 명령어도 1개의 transaction이다

---

Transaction이 왜 필요한가?

아래처럼 은행 계좌 DB가 있다고 가정하자.

CREATE TABLE accounts (
    account_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    account_holder VARCHAR(100) NOT NULL,
    balance DECIMAL(10,2) NOT NULL CHECK ( balance >= 0 )
);

INSERT INTO accounts (account_holder, balance)
VALUES
    ('nico', 1000.00),
    ('lynn', 2000.00);

nico가 lynn에게 500원을 송금하려면 아래처럼 연산이 이뤄져야 하지만

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

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

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

이 코드가 실행되기 전 DB에 문제가 생겨 실행이 안되면 nico의 돈만 줄어들고 lynn은 돈을 받지 못한다.

---

Transaction 설정

• PostgreSQL에서는 BEGIN ~ COMMIT으로 감싸주면 된다.
• MySQL에서는 START TRANSACTION ~ COMMIT으로 감싸주면 된다.

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

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

위 SQL문에서 nico의 잔금은 1000원 이므로 -1500 하면 CHECK ( balance >= 0 )에 의해 막힌다.

그러면 Transaction에 의해 연산이 취소되고 실행 전 상태로 롤백된다.

---

Transaction의 특징

ACID

구분	설명
Atomic	모든 작업이 성공하거나, 그렇게 될 수 없을 경우 전부 실행되서는 안된다.
Consistent	실행 전 상태와 실행 후 상태가 항상 constraint, cascade 같은 조건을 충족해야 한다.
Isolated	transaction이 commit 되기 전까지는 다른 transation에선 확인이 불가능하다.
Durable	Transaction이 commit이 되면 DB를 재시작해도 변경 사항이 그대로 유지되어야 한다.

---

Save Point

transaction 작업을 수행하다 실패하면 완전 롤백되지 않고 save point까지 롤백된다.

BEGIN;  -- MySQL -> start transaction
    UPDATE accounts SET balance = balance + 1000 WHERE account_holder = 'nico';
    SAVEPOINT step_one;

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

    UPDATE accounts SET balance = balance - 1000 WHERE account_holder = 'lynn';

    ROLLBACK TO SAVEPOINT step_one;
COMMIT;

ROLLBACK TO SAVEPOINT step_one; 에 의해 lynn → Lynn과 Lynn의 잔금이 -1000 이 된 부분만 롤백된다.

---

Isolation Level

transaction 현상을 막기 위한 방법

💡 PostgreSQL의 기본 설정은 Read Uncommited MySQL의 기본 설정은 Repeatable read이다.

transaction 현상

구분	설명
Dirty Read	transaction이 아직 commit 되지 않은 데이터를 동시에 읽을 때 발생하는 현상
Nonrepeatable Read	이미 한번 읽은 데이터가 다른 transaction에 의해 수정되고 commit 되었을 때 다시 읽는 현상 주로 update에 의해 발생한다.
Phantom Read	한번 읽었던 row들을 다시 읽는 연산을 수행할 때 다른 transaction에 의해 변경된 데이터가 존재할 경우 값이 바뀌는 현상 주로 insert, delete에 의해 발생한다.
Serialization Anomaly	여러 transaction의 성공적인 커밋 결과가 가능한 모든 순서로 트랜잭션을 순차적으로 실행한 결과와 일치하지 않는 현상 여러 transaction을 동시에 실행했을 때의 결과와 여러 transaction을 순차적으로 실행했을 때의 결과가 달라지는 현상

•  

---

Isolation Level 종류

Isolation Level	Dirty Read	Nonrepeatable Read	Read Phantom	Serialization Anomaly
Read uncommitted	허가 PostgreSQL에서는 불가	가능	가능	가능
Read committed	불가능	가능	가능	가능
Repeatable read	불가능	불가능	허가 PostgreSQL에서는 불가	가능
Serializable	불가능	불가능	불가능	불가능

---

• Read uncommitted
  • 아직 commit 되지 않은 데이터를 다른 transaction에서 볼 수 있다.
• Read committed
  • 아직 commit 되지 않은 데이터를 다른 transaction에서 볼 수 없다.
  • PostgreSQL에서는 Read uncommitted와 Read committed 두 레벨이 같다.
• Repeatable read
  • A에서 데이터를 변경하고 아직 commit을 하지 않은 상태일 때, B(Repeatable read 모드)에서는 변경 전 데이터를 읽는다.
  • 하지만 A가 commit이 된 후라도 B는 해당 transaction을 닫지 않는 한 계속 이전 데이터를 읽는다.
  • 이때, transaction이 시작할 때 데이터를 읽는 것이 아닌 쿼리가 실행될 때 데이터를 읽는다
    • B가 transaction 열림 (BEGIN) → A 작업 완료 (commit) → B 쿼리 실행
    • 이 순서일 경우 B에서는 A에서 작업 완료 후의 데이터를 읽게 된다.

    BEGIN
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    SELECT * FROM accounts;  -- 이 쿼리가 실행된 시점의 데이터로 고정된다.
    COMMIT;

---

Isolation Level 변경

• MySQl
  • SET SESSION TRANSACTION ISOLATION LEVEL {isolation_level};
  • session 설정 변경이므로 이후 모든 transaction에 적용된다.

  SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
  START TRANSACTION;
  --
  COMMIT;

• PostgreSQL
  • SET TRANSACTION ISOLATION LEVEL {isolation_level};
  • transaction 내부에 작성하므로 해당 transaction만 적용된다.

  BEGIN;
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
  --
  COMMIT;

---

Locks

상황	설명
Read Committed 일 때	A transaction에서 데이터에 접근할 때, B transaction에서 같은 데이터에 접근할 경우 먼저 실행한 transaction 작업이 완료되어야 B의 작업이 실행된다.
Repeatable Read 일 때	A transaction에서 데이터에 접근할 때, B transaction에서 같은 데이터에 접근할 경우 먼저 실행한 transaction 작업이 commit 되어도 B의 작업이 실패한다. Repeatable Read에 의해 B에서 읽은 데이터가 A에 의해 변경되었으므로 오류가 발생한다. 따라서 A 작업이 롤백되어야 B 작업이 완료된다.

•  

---

Lock Share

SELECT FOR UPDATE

그 어떤 transaction도 해당 row의 lock을 걸 수 없으며 데이터 변경도 하지 못한다.

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

BEGIN;
SELECT * FROM accounts;
    UPDATE accounts SET account_holder = 'Lynn' WHERE account_holder = 'lynn';
COMMIT;

2 종류의 transaction이 있을 때 첫 번째 쿼리의 FOR UPDATE에 의해 데이터가 update가 되지 않음에도 두 번째 transaction은 첫 번째 transaction 작업이 완료될 때까지 대기해야 한다.

SELECT FOR UPDATE를 사용하면 다른 transaction은 해당 row에 lock조차 걸 수 없다.

---

SELECT FOR SHARE

그 어떤 transaction도 해당 row의 데이터 변경은 할 수 없으나 lock은 걸 수 있다.

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

BEGIN;
SELECT * FROM accounts;
    UPDATE accounts SET account_holder = 'Lynn' WHERE account_holder = 'lynn';
COMMIT;

SELECT FOR UPDATE와 같은 현상이 발생한다.

하지만 SELECT FOR UPDATE와는 다르게 여러 transaction이 같은 row에 lock을 걸 수 있다.

만약 다른 transaction이 해당 row를 변경하고자 하면 기존의 transaction들의 작업을 모두 완료해야 한다.