Transaction Isolation Levels (MySQL 8.4)

https://dev.mysql.com/doc/refman/8.4/en/innodb-transaction-isolation-levels.html

Transactions

• ACID에서 I를 의미한다.(Isolation)
• 여러 개의 트랜잭션이 동시에 발생하기 위해서 고려되는 성능과 신뢰성, 일관성, 결과 재현성은 트레이드 오프 관계이다.
• InnoDB는 기본적으로 REPEATABLE READ로 설정된다.
• SET TRANSACTION을 통해서, Statement의 격리 수준을 설정할 수 있다.

REPEATABLE READ

• InnoDB의 기본적인 모드
• 동일한 트랜잭션내에서는 첫번째 읽기에서 획득한 스냅샷을 사용한다.
• 기본적인 SELECT문에 대해서 비잠금 읽기(Nonlocking reads)가 적용된다.
• 잠금 읽기(SELECT WITH FOR UPDATE OR FOR SHARE)는 인덱스의 종류에 따라서 동작이 달라질 수 있다.
  • Unique Index는 해당 레코드만 락한다. 다른 레코드들에 대해서 처리할 수 있도록 하여 성능을 보장할 수 있다.
  • 인덱스의 특징에 따라서, Range나 Next-Key에 대해서도 락할 수 있다.
  • Range, Next-Key 잠금 읽기를 통해서, Phantom Read가 발생하지 않는 것처럼 느껴질 수도 있어보인다.
• 잠금 읽기(locking reads, SELECT with FOR UPDATE or FOR SHARE)와 비잠금 읽기를 사용하는 것은 권장되지 않는다, 비잠금 읽기는 스냅샷이지만, 잠금 읽기는 최신의 스냅샷을 사용하므로 두 개의 다른 테이블을 바라보고 있다. 또한 잠금 읽기를 사용하는 것은 대부분 SERIALIZABLE인 격리성을 고려해야 한다.
• 트랜잭션내 전체 레코드에 대해서 잠금 읽기하는 상황

// Session A
x-lock(1,2); retain x-lock
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); retain x-lock
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); retain x-lock

// Session B
x-lock(1,2); block and wait for first UPDATE to commit or roll back

READ COMMITTED

• 가장 최신의 스냅샷(fresh snapshot)을 획득한다.
• 잠금 읽기에 대해서는 오직 해당 레코드만 가능하고, 갭 락킹이 불가능하므로 팬텀 로우 문제(Phantom Rows)가 발생할 수 있다.
  • Phantom Rows : 트랜잭션 내에서 특정 쿼리를 여러번 했을 때, 시점에 따라 다른 결과가 나타나는 현상

  SELECT * FROM child WHERE id > 100 FOR UPDATE;

• 오직 해당 레코드만 잠금 읽기하는 상황

x-lock(1,2); unlock(1,2)
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); unlock(3,2)
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); unlock(5,2)

x-lock(1,2); update(1,2) to (1,4); retain x-lock
x-lock(2,3); unlock(2,3)
x-lock(3,2); update(3,2) to (3,4); retain x-lock
x-lock(4,3); unlock(4,3)
x-lock(5,2); update(5,2) to (5,4); retain x-lock

READ UNCOMMITTED

• Dirty Read가 발생할 수 있음
  • 다른 트랜잭션에 의해 커밋되지 않은 데이터들이 노출되는 상황
• ACID의 원칙을 준수하지 않으므로, 매우 위험하다.

  커밋까지 시간이 오래 걸리는 경우, 데이터들을 추적하는 용도로 사용할 수 있어보인다.

SERIALIZABLE

• REPEATABLE_READ보다 더 엄격한 수준의 격리성
• AUTOCOMMIT : ON
  • SELECT 1개가 자신의 트랜잭션을 가지고 있으므로, 트랜잭션내에서 Phantom Read가 발생하지 않는다.
• AUTOCOMMIT : OFF
  - SELECT ... FOR SHARE가 적용된다.
  • 잠금 읽기가 발생한다.