Isolation Level - 트랜잭션 격리 수준 깊게 이해하기

InnoDB 스토리지 엔진을 사용하는 mysql 기준으로 정리한다.

 

●  먼저 MVCC와 undo log, read view에 대한 이해가 필요하다.

MVCC 깊게 이해하기: Undo Log 기반 다중 버전 동시 제어의 원리

 

트랜잭션의 Isolation Level 왜 중요한가?

●  동시에 실행되는 트랜잭션들은 서로 영향을 주지 않고 독립적으로 실행되어야 한다.

 

●  하지만 완벽하게 격리시키면, 모든 트랜잭션이 직렬로 실행되는 것과 동일한 효과를 가지게 되어 동시 처리 성능이 크게 떨어진다.

 

●  일관성을 강화하면 성능이 떨어지고, 성능을 높이면 일관성이 떨어지는 딜레마를 해결하기 위해, 격리 수준은 일관성과 성능 사이의 균형을 조절한다.

 

 

격리 수준이 없으면 발생하는 동시성 이슈

Dirty Read

●  다른 트랜잭션이 아직 커밋하지 않은 변경 내용을 읽는다.

 

●  나중에 그 트랜잭션이 롤백되면 잘못된 데이터를 읽게 된 셈이다.

Non-Repeatable Read

●  동일한 트랜잭션 내에서 같은 데이터를 두 번 읽었는데, 다른 값이 반환 된다.

 

●  트랜잭션 A가 데이터를 읽고 있는 동안 트랜잭션 B가 데이터를 수정하여 다시 조회하면 값이 바뀐다.

Phantom Read

●  동일한 트랜잭션 내에서 동일한 조건으로 조회했을 때, 이전에는 없던 새로운 데이터가 나타난다.

 

●  트랜잭션 A가 특정 조건으로 조회했을 때는 보이지 않았던 데이터가, 트랜잭션 B가 INSERT 후 조회된다.

 

격리 수준은 이런 문제를 얼마나 허용할지 정하는 전략이다.

 

---

Transaction Isolation Levels

READ UNCOMMITED

●  트랜잭션들이 서로 커밋되지 않은 값까지 읽을 수 있다.

 

●  Dirty Read가 발생한다.

 

●  성능은 좋지만 가장 위험하다.

# T1
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;
-> commit 안함

# T2
START TRANSACTION;
SELECT balance FROM account WHERE id = 1;
-> -100 적용된 값을 읽음

# T1
ROLLBACK;
-> T2는 실제 DB에 반영되지 않은 잘못된 값을 읽어서 잔고가 줄었다.

---

READ COMMITTED

●  커밋이 완료된 데이터만 읽기 때문에 Dirty Read가 방지된다.

 

●  트랜잭션에서 SELECT 할 때마다 가장 최신 커밋된 버전을 읽는다.

 

●  SELECT마다 새롭게 생성된 ReadView 기준으로 데이터를 읽는다.

# T1
START TRANSACTION;
SELECT balance FROM account WHERE id = 1;  -> 5000

# T2
START TRANSACTION;
UPDATE account SET balance = 7000 WHERE id = 1;
# commit 안함

# T1 다시 SELECT
SELECT balance FROM account WHERE id = 1;  -> 5000
COMMIT;

 

●  T2에서 balance=7000으로 변경

 

●  undo log에 기존 값 balance=5000 기록

 

●  T1이 다시 SELECT 했을 때 ReadView를 새롭게 생성

 

●  ReadView 기준 T2는 아직 commit을 하지 않아 활성 트랜잭션이므로 다른 트랜잭션이 볼 수 없다.

 

●  따라서 undo log로 복원하여 balance=5000

 

Dirty Read는 해결되었지만, 일관성을 보장하지 못하는 경우

Non-Repeatable Read 이슈

# T1
START TRANSACTION;
SELECT balance FROM account WHERE id = 1;  -> 5000

# T2
START TRANSACTION;
UPDATE account SET balance = 7000 WHERE id = 1;
COMMIT;

# T1 다시 SELECT
SELECT balance FROM account WHERE id = 1;  -> 7000 (값이 바뀜!)
COMMIT;

 

●  T1이 다시 SELECT 할 경우 ReadView를 새롭게 생성

 

●  T2가 commit하였으므로 ReadView 기준에 맞다면 다른 트랜잭션이 볼 수 있다.

 

●  결과적으로 동일한 Row를 두 번 조회할 때 결과가 달라질 수 있다.

 

Phantom Read 이슈

# T1
START TRANSACTION;
SELECT * FROM orders WHERE price > 100;  -> 결과: 2건

# T2
START TRANSACTION;
INSERT INTO orders (price) VALUES (200);
COMMIT;

# T1 다시 SELECT
SELECT * FROM orders WHERE price > 100;  -> 결과: 3건 (팬텀 등장)
COMMIT;

 

●  T1이 다시 SELECT 할 경우 ReadView를 새롭게 생성한다.

 

●  이 때, T2가 커밋된 상태이기 때문에, T1의 새로운 ReadView에서는 T2가 삽입한 데이터를 볼 수 있다.

 

●  즉 같은 조건으로 조회 했는데도 불구하고 결과 갯수가 달라지게 된다.

 

●  위에서 조회한 결과로 집계를 낸다고 할 때 한 트랜잭션 안에서 일관된 결과가 나오지 않기 때문에 문제가 발생할 수 있다.

---

REPEATABLE READ

●  트랜잭션 내에서 같은 row를 여러 번 읽어도 항상 같은 값이 반환 된다.

 

●  트랜잭션 내에서 처음 SELECT를 실행할 때, 단 한 번의 ReadView를 생성하고, 트랜잭션이 종료될 때까지 계속해서 이 ReadView를 사용

 

●  트랜잭션 중간에 다른 트랜잭션이 데이터를 수정하거나 추가해도, 처음 조회 시점에 생성된 ReadView를 기준으로 조회하기 때문에 영향을 받지 않는다.

 

Non-Repeatable Read 방지

# 초기 row 상태
[ id = 1, balance = 5000, db_trx_id = 99, db_roll_ptr = null ]

# T1
START TRANSACTION; (trx_id=100)
SELECT balance FROM account WHERE id = 1;  -> 5000

 

●  T1에서 처음 SELECT 할 때 ReadView를 생성

 

●  T1은 트랜잭션이 종료할 때 까지 이 ReadView를 사용

 

# T2
START TRANSACTION; (trx_id=101)
UPDATE account SET balance = 7000 WHERE id = 1;
COMMIT;

# undo log
[ trx_id = 99, balance = 5000 ]

#Buffer Pool row 상태
[ id = 1, balance = 7000, db_trx_id = 101, db_roll_ptr = undo$1 ]

 

●  T2에서 balance=7000으로 업데이트하면 다음 과정을 거친다.

 

●  undo log에 기존 값 balance=5000 기록

 

●  Buffer Pool에서 데이터 수정. balance=7000

 

●  row를 마지막에 수정한 트랜잭션 아이디를 반영하여 db_trx_id는 101로 변경

 

# undo log
[ trx_id = 99, balance = 5000 ]

# T1 다시 SELECT
SELECT balance FROM account WHERE id = 1;  -> 5000
COMMIT;

 

●  ReadView 기준으로 보면 T2가 변경한 내용은 T1 ReadView 생성 이후 시점에 만들었으므로 볼 수 없다.

 

●  따라서 undo log에서 이전 값 5000을 복원해서 보여준다.

 

●  commit 후 트랜잭션이 종료되면서 ReadView는 폐기된다.

 

Phantom Read 방지

# T1 (trx_id=100)
START TRANSACTION;
SELECT * FROM orders WHERE price > 100;  -> 결과: 2건 (ReadView 생성됨)

# T2 (trx_id=101)
START TRANSACTION;
INSERT INTO orders (price) VALUES (200);
COMMIT;

# T1 다시 조회
SELECT * FROM orders WHERE price > 100;  -> 결과: 2건 (ReadView로 인해 INSERT된 데이터 무시)
COMMIT;

 

●  T1에서 SELECT할 때 ReadView 생성

 

●  T2의 trx_id는 T1의 ReadView 기준에 부합하지 않으므로 T1이 다시 조회할 때 같은 결과가 나온다.

 

Phantom Read는 위와 같이 MVCC로 인해 REPEATABLE READ 레벨에서 발생하지 않는다. 

그렇다면 어떤 경우에 발생할까?

 

# T1
START TRANSACTION;
SELECT * FROM member WHERE balance >= 9000;

# T2
START TRANSACTION;
INSERT INTO member(balance) VALUES(10000);
COMMIT;

# T1
SELECT * FROM member WHERE balance >= 9000 FOR UPDATE;  
-> 잔고가 10000인 멤버가 추가로 보임

 

●  T1이 다시 SELECT 할 때 X-Lock을 걸면 ReadView를 사용하지 않고, 최신 데이터를 직접 조회하기 때문에 팬텀 리드가 발생한다.

 

●  조회 시 X-Lock을 걸 경우 이 명령은 단순 조회가 아닌, 수정을 위해 보는 것이기 때문에 테이블의 현재 버전을 바라본다.

 

●  데이터를 변경하기 전, 다른 트랜잭션이 해당 데이터를 수정하거나 삭제하지 못하도록 하는 것이 목적이다.

 

●  따라서 일관된 과거 버전이 아니라 최신 상태를 보아야 한다.

 

위의 경우 어떻게 팬텀리드를 막을까?

# T1
START TRANSACTION;
SELECT * FROM member WHERE balance >= 9000 FOR UPDATE;

# T2
START TRANSACTION;
INSERT INTO member(balance) VALUES(10000); -> 락이 걸려 있기 때문에 INSERT 불가
COMMIT; 

# T1
SELECT * FROM member WHERE balance >= 9000 FOR UPDATE;

 

●  T1에서 첫번째 조회할 때부터 X-Lock을 건다.

 

●  mysql은 Next-Key Lock을 사용하여 레코드 자체뿐만아니라 인접한 Gap도 함께 Lock을 건다.

 

●  즉 balance= 9000 레코드, balance>9000 범위 공간 모두 Lock이 걸리기 때문에 T2에서 INSERT를 못한다.

 

●  T1에서 Commit이나 Rollback을 해야만 T2에서 INSERT를 할 수 있기 때문에 팬텀 리드가 발생할 수 없다.

---

SERIALIZABLE

●  가장 강력한 격리 수준으로, 트랜잭션 간의 완벽한 분리를 보장한다.

 

●  다른 격리 수준들과 달리, 모든 트랜잭션이 직렬적으로 (하나씩 순서대로) 실행되는 것처럼 보이게 힌다.

 

●  따라서 성능이 제일 낮다.

 

●  Dirty Read / Non-Repeatable / Phantom 전부 방지

 

●  모든 읽기 / 쓰기 작업에 Lock을 건다.

 

●  범위 조회 시 해당 범위 전체에 락을 걸어 INSERT를 차단한다.

 

●  데이터 무결성이 최우선일 때, 예를 들어, 은행 시스템, 금융 거래, 중요한 데이터 분석 사용한다.

 

DB별 기본 Isolation Level

●  DBMS 기본 격리 수준

DBMS	기본 격리 수준
MySQL (InnoDB)	REPEATABLE READ
PostgreSQL	READ COMMITTED
Oracle	READ COMMITTED
SQL Server	READ COMMITTED