[DB] 트랜잭션 격리수준 (Isolation Level)

Q. 트랜잭션 격리 수준에 대해서 설명해주세요
트랜잭션 격리 수준(Transaction Isolation Levels)은 고립도와 성능의 Trade-off 를 조절한다.

READ UNCOMMITTED : 다른 트랜잭션에서 커밋되지 않은 내용도 참조할 수 있다
READ COMMITTED : 다른 트랜잭션에서 커밋되 내용만 참조할 수 있다.
REPETABLE READ : 트랜잭션에 진입하기 이전에 커밋된 내용만 참조할 수 있다.
SERIALIZABLE : 트랜잭션에 진입하면 락을 걸어 다른 트랜잭션이 접근하지 못하게 한다. (성능이 매우 떨어짐)

✅ 트랜잭션 격리 수준

트랜잭션 격리 수준 (Transaction Isolation Levels)이란 트랜잭션들끼리 얼마나 고립되어있는지 (잠금수준)를 나타내는 것으로 특정 트랜잭션이 다른 트랜잭션에 의해 변경된 데이터를 볼 수 있도록 허용할지 말지를 결정하는 것이다.

• 격리 수준은 크게 4가지로 나뉜다

1. READ UNCOMMITTED (트렌젝션 레벨 0)
2. READ COMMITTED (트렌젝션 레벨 1)
3. REPEATABLE READ (트렌젝션 레벨 2)
4. SERIALIZABLE (트렌젝션 레벨 3)

아래로 내려갈수록 트랜잭션 간의 고립도가 높아지고 성능이 떨어지는게 일반적이다.
트랜잭션 격리 수준이 필요한 이유
트랜잭션 수준 읽기 일관성 (Transaction-Level Read Consistency)을 지키기 위해서이다.
(다시말해 동시성 제어 문제 해결을 위해서이다)

트랜잭션 수준 읽기 일관성

트랜잭션이 시작된 시점으로부터 일관성 있게 데이터를 읽어 들이는 것을 말한다.
하나의 트랜잭션이 진행되는 동안 다른 트랜잭션에의해 변경사항이 발생하더라도 이를 무시하고 계속 일관성 있는 데이터를 보여준다. (물론 트랜잭션 자신이 발생한 변경사항은 읽을 수 있다)

📌READ UNCOMMITTED (트렌젝션 레벨 0)

트랜잭션에서 처리 중인, 아직 커밋되지 않은 데이터를 다른 트랜잭션이 읽는 것을 허용한다.

특징

Dirty Read, Non-Repeatable Read, Phantom Read 현상이 발생한다.
(위 3가지 현상은 밑에 설명하겠다)

문제점

데이터 정합성에 문제가 많다. 그렇기에 RDBMS 표준에서는 격리수준으로 인정하지 않는다.

예시

1. 트랜잭션A는 테이블의 데이터를 수정중인 상태이고 아직 COMMIT 전이다.
2. 트랜잭션B는 트랜잭션A가 수정중인 데이터를 조회한다. (이를 Dirty Read라고 한다.)
3. 트랜잭션A는 문제가 발생해 ROLLBACK한다.
4. 하지만 트랜잭션B는 COMMIT되지 않은 데이터를 가지고 로직을 수행한다. (문제 발생)

📌 READ COMMITTED (트렌젝션 레벨 1)

RDB에서 대부분 기본적으로 사용되고 있는 격리 수준으로
실제 테이블 값을 가져오는 것이 아니라 Undo 영역에 백업된 레코드에서 값을 가져온다.

특징

Dirty Read 가 발생하지 않지만
(트랜잭션이 COMMIT되어 확정된 데이터만 읽는 것을 허용한다.)
Non-Repeatable Read, Phantom Read 현상은 여전히 발생한다.
온라인 서비스에서 가장 많이 선택되는 격리수준이다.
DB2, SQL Server, Sybase의 경우 읽기, 공유 Lock을 이용하여 구현한다
Oracle은 Lock을 사용하지 않고 쿼리시작 시점의 Undo 데이터를 제공한다

문제점

NON-REPEATABLE READ 부정합 문제가 발생할 수 있다.
👉 READ COMMITTED 격리 수준에서 실행되는 SQL 문장의 결과가 무엇인지 정확히 예측하고 있어야 한다.

📌 REPEATABLE READ (트렌젝션 레벨 2)

트랜잭션이 시작되기 전에 COMMIT된 내용에 대해서만 조회할 수 있는 격리수준이다.

MySQL에서는 트랜잭션마다 트랜잭션 ID를 부여하여 트랜잭션 ID보다 작은 트랜잭션 번호에서 변경한 것만 읽게 된다.
변경되기 전 레코드는 Undo 공간에 백업해두고 실제 레코드 값을 변경한다

특징

Dirty Read와 같은 현상은 발생하지 않지만 Phantom Read 현상은 여전히 발생한다.

문제점

하나의 트랜잭션 실행시간이 길어질수록 Undo에 백업된 레코드가 많아져서 멀티 버전을 관리해야하는 단점이 있다.
(하지만 영향을 미칠정도로 트랜잭션이 오래 지속되는 경우가 없어서 READ COMMITTED와 REPEATABLE READ의 성능 차이는 거의 없다고 한다.)
또한 UPDATE 부정합와 Phantom Read가 발생할 수 있다.

UPDATE 부정합 문제

START TRANSACTION; -- transaction id : 1
SELECT * FROM Member WHERE name='hjoon';

START TRANSACTION; -- transaction id : 2
SELECT * FROM Member WHERE name = 'hjoon';
UPDATE Member SET name = 'top' WHERE name = 'hjoon';
COMMIT;

UPDATE Member SET name = 'sun' WHERE name = 'hjoon'; -- 0 row(s) affected
COMMIT;

위 sql 결과는 name=sun이 아니라 name=top이다.

2번 트랜잭션

name=top으로 UPDATE한 뒤 COMMIT하였다.
REPEATABLE READ 격리수준에서는 1번 트랜잭션이 일관된 데이터를 보는 것을 보장해주기 위해서
변경되기 전 내용인 name=hjoon 을 UNDO 세그먼트에 남겨둬야 한다.

1번 트랜잭션

1번 트랜잭션은 UPDATE 구문을 실행하게 된다.
1번 트랜잭션이 바라보는 name=hjoon 데이터는 레코드의 데이터가 아닌 UNDO 세그먼트 영역의 데이터이고,
UNDO 세그먼트 영역의 데이터는 쓰기 잠금(write lock)을 걸 수 없다. (UPDATE 구문의 경우 변경을 수행할 ROW에 대해 LOCK을 걸어야한다.)
1번 트랜잭션은 레코드 데이터에 대해 쓰기 잠금을 시도하지만 name=hjoon인 레코드가 존재하지 않으므로
0 row(s) affected가 출력되고 아무 변경도 일어나지 않는다.
👉 DML 구문은 멀티버전을 관리하지 않는다

📌 SERIALIZABLE (트렌젝션 레벨 3)

선행 트랜잭션이 특정 테이블을 읽는 경우(SELECT) 공유 잠금(shared lock) 을 걸어, 다른 트랜잭션에서 해당 테이블의 데이터를 UPDATE, DELETE, INSERT 작업을 못하도록 막는다.

특징

가장 단순한 격리 수준이지만 가장 엄격한 격리 수준으로
Phantom Read가 발생하지 않는다.

문제점

동시 처리 능력이 다른 격리수준보다 떨어지고 성능저하가 발생하여 데이터베이스에서 거의 사용되지 않는다.

✅낮은 단계의 트랜잭션 고립화 수준을 사용할 때 발생하는 세 가지 현상

📌 Dirty Read (Uncommitted Dependency)

변경 후 아직 Commit 되지 않은 값 읽고, Rollback 후의 값을 다시 읽어 최종 결과 값이 상이한 현상이다
Oracle은 다중 버전 읽기 일관성 모델을 채택하여 lock을 사용하지 않고 Dirty Read를 피해 일관성 있는 데이터 읽기가 가능하게 하였다.

📌 Non-Repeatable Read (Inconsistent Analysis)

한 트랜잭션 내에서 같은 쿼리를 두번 수행할 때, 그 사이에 다른 트랜잭션이 값을 수정 또는 삭제함으로써 두 쿼리가 상이하게 나타나는 비 일관성이 발생하는 것을 말한다.
(다시말해 하나의 트랜잭션 내에서 동일한 SELECT를 수행했을 경우 항상 같은 결과를 반환해야하는 REPEATABLE READ 정합성에 어긋나는 것이다.)

금전적 처리와 연결된 서비스에서 문제가 발생할 수 있다.

트랜잭션B에서 1번 상품의 총 투자액을 조회 👉 100만원이 조회됨
트랜잭션A에서 1번 상품의 총 투자액을 120만원으로 바꾸고 COMMIT
트랜잭션B에서 1번 상품의 총 투작액을 다시 조회 👉 120만원이 조회됨 (NON-REPEATABLE READ )

📌 Phantom Read

하나의 트랜잭션에서 같은 쿼리를 두 번 실행했을 경우, 첫 번째 쿼리에서 없던 유령(Phantom) 레코드가 두 번째 쿼리에서 나타나는 현상이다.

INSERT에 대해서만 발생하는 문제다. (SELECT, DELETE에 대해서는 발생하지 않는다)
👉 이를 방지하기 위해서는 쓰기 잠금 (write lock)을 걸어야 한다

INSERT 문제

START TRANSACTION; -- transaction id : 1 
SELECT * FROM Member; -- 0건 조회

START TRANSACTION; -- transaction id : 2
INSERT INTO MEMBER VALUES(1,'hjoon',28); --  INSERT 하면 문제발생
COMMIT;

SELECT * FROM Member; -- 여전히 0건 조회 
UPDATE Member SET name = 'zion.t' WHERE id = 1; -- 1 row(s) affected
SELECT * FROM Member; -- 1건 조회 (Phantom READ 문제발생)
COMMIT;

2번 트랜잭션

member 테이블에 id=1인 데이터를 INSERT 한 뒤 COMMIT 하였다.
REPEATABLE READ 격리수준에서는 1번 트랜잭션이 일관된 데이터를 보는 것을 보장해주기 위해서
변경되기 전 내용인 name=hjoon 을 UNDO 세그먼트에 남겨둬야 한다.

1번 트랜잭션

첫 번째 쿼리에서 member를 조회했을 때
DELETE에 대해서는 문제가 발생하지 않는다.

START TRANSACTION; -- transaction id : 1 
SELECT * FROM Member; -- 1건 조회

START TRANSACTION; -- transaction id : 2
DELETE FROM Member WHERE id = 1;
COMMIT;

SELECT * FROM Member; -- 여전히 1건 조회 
UPDATE Member SET name = 'zion.t' WHERE id = 1; -- 0 row(s) affected
SELECT * FROM Member; -- 여전히 1건 조회 (문제 없음)
COMMIT;

참고

https://joont92.github.io/db/%ED%8A%B8%EB%9E%9C%EC%9E%AD%EC%85%98-%EA%B2%A9%EB%A6%AC-%EC%88%98%EC%A4%80-isolation-level/
https://mozi.tistory.com/201
http://wiki.gurubee.net/pages/viewpage.action?pageId=21200923#:~:text=(4)%20Phantom%20Read,%EC%97%90%EC%84%9C%20%EB%82%98%ED%83%80%EB%82%98%EB%8A%94%20%ED%98%84%EC%83%81%EC%9D%84%20%EB%A7%90%ED%95%9C%EB%8B%A4.
http://blog.skby.net/dirty-read/
https://nesoy.github.io/articles/2019-05/Database-Transaction-isolation