[SQL] 트랜잭션 격리 수준(isolation level)

트랜잭션 격리 수준

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

 

READ UNCOMMITTED(트렌젝션 레벨 0) : 다른 트랜잭션에서 커밋되지 않은 내용도 참조할 수 있음

READ COMMITTED(트렌젝션 레벨 1) : 다른 트랜잭션에서 커밋되 내용만 참조할 수 있음 (ORACLE)

REPETABLE READ(트렌젝션 레벨 2) : 트랜잭션에 진입하기 이전에 커밋된 내용만 참조할 수 있음 (MySQL)

SERIALIZABLE(트렌젝션 레벨 3) : 트랜잭션에 진입하면 락을 걸어 다른 트랜잭션이 접근하지 못하게 함 (성능이 매우 떨어짐)

아래로 내려갈수록 트랜잭션 간의 고립도가 높아지고 성능이 떨어지는게 일반적임

 

---

트랜잭션 격리 수준이 필요한 이유

트랜잭션 수준 읽기 일관성(Transaction-Level Read Consistency)을 지키기 위해서 필요(동시성 제어 문제 해결을 위해서)

❓ 트랜잭션 수준 읽기 일관성

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

 

---

READ UNCOMMITTED (트렌젝션 레벨 0)

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

🚫 Dirty Read, Non-Repeatable Read, Phantom Read 현상이 발생

(위 3가지 현상은 밑에 설명하겠다)

 

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

예시

• 트랜잭션A는 테이블의 데이터를 수정중인 상태이고 아직 COMMIT 전 임
• 트랜잭션B는 트랜잭션A가 수정중인 데이터를 조회(이를 Dirty Read라고 함)
• 트랜잭션A는 문제가 발생해 ROLLBACK 함
• 하지만 트랜잭션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='nana';

START TRANSACTION; -- transaction id : 2
SELECT * FROM member WHERE name = 'nana';
UPDATE member SET name = 'pie' WHERE name = 'nana';
COMMIT;

UPDATE member SET name = 'jerry' WHERE name = 'nana'; -- 0 row(s) affected
COMMIT;

위 sql 결과는 name=jerry가 아니라 name=pie임

• 2번 트랜잭션
  name=pie으로 UPDATE한 뒤 COMMIT
  REPEATABLE READ 격리수준에서는 1번 트랜잭션이 일관된 데이터를 보는 것을 보장해주기 위해서
  변경되기 전 내용인 name=nana 을 UNDO 세그먼트에 남겨둬야 함
• 1번 트랜잭션
  1번 트랜잭션은 UPDATE 구문을 실행
  1번 트랜잭션이 바라보는 name=nana 데이터는 레코드의 데이터가 아닌 UNDO 세그먼트 영역의 데이터이고,
  UNDO 세그먼트 영역의 데이터는 쓰기 잠금(write lock)을 걸 수 없음 (UPDATE 구문의 경우 변경을 수행할 ROW에 대해 LOCK을 걸어야함.)
  1번 트랜잭션은 레코드 데이터에 대해 쓰기 잠금을 시도하지만 name=nana인 레코드가 존재하지 않으므로
  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, 'nana', 01012345678); --  INSERT 하면 문제발생
COMMIT;

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

• 2번 트랜잭션
  member 테이블에 id=1인 데이터를 INSERT 한 뒤 COMMIT 
  REPEATABLE READ 격리수준에서는 1번 트랜잭션이 일관된 데이터를 보는 것을 보장해주기 위해서
  변경되기 전 내용인 name=nana 을 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 = 'jerry' 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://velog.io/@guswns3371/%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4-%ED%8A%B8%EB%9E%9C%EC%9E%AD%EC%85%98-%EA%B2%A9%EB%A6%AC%EC%88%98%EC%A4%80