[데이터베이스] 격리성 관련 문제와 격리성 수준 (Isolation Level)

격리성 관련 문제점

1. Dirty Read

한 트랜잭션(T1)이 데이터에 접근하여 값을 'A'에서 'B'로 변경했고 아직 커밋을 하지 않았을때, 다른 트랜잭션(T2)이 해당 데이터를 Read 하면?

T2가 읽은 데이터는 B가 될 것이다. 하지만 T1이 최종 커밋을 하지 않고 종료된다면, T2가 가진 데이터는 꼬이게 된다.

트랜잭션이 rollback했는데, 다른 트랜잭션이 rollback 이전에 수정되었던 데이터를 갖고 있어서 문제가 되는 경우

2. Non-Repeatable Read

한 트랜잭션(T1)이 데이터를 Read 하고 있다. 이때 다른 트랜잭션(T2)가 데이터에 접근하여 값을 변경 또는, 데이터를 삭제하고 커밋을 때려버리면?

그 후 T1이 다시 해당 데이터를 Read하고자 하면 변경된 데이타 혹은 사라진 데이터를 찾게 된다.

트랜잭션을 하는 동안 계속해서 데이터를 동일하게 읽을 수 있는 것을 보장하지 않는 문제. UPDATE/DELETE가 일어날 수 있는 경우

3. Phantom Read

트랜잭션(T1) 중에 특정 조건으로 데이터를 검색하여 결과를 얻었다. 이때 다른 트랜잭션(T2)가 접근해 해당 조건의 데이터 일부를 삭제 또는 추가 했을때, 아직 끝나지 않은 T1이 다시 한번 해당 조건으로 데이터를 조회 하면 T2에서 추가/삭제된 데이터가 함께 조회/누락 된다. 그리고 T2가 롤백을 하면? 데이터가 꼬인다

어떤 조건으로 검색해서 데이터를 읽었는데, 그 사이에 다른 트랜잭션에서 INSERT/DELETE가 일어나서 다시 조회하면 결과가 다르고, 그 사이에 INSERT/DELETE는 rollback 되었다면 데이터가 꼬임

 

트랜잭션 격리 수준 Isolation Level

isolation level이란 트랜잭션에서 일관성이 없는 데이터를 허용하도록 하는 수준을 의미합니다.

ANSI에서 작성된 SQL-92 표준은 네 종류의 Isolation Level을 정의하고 있으며 SQL Server 7.0은 이 표준을 준수합니다. Isolation Level을 조정하는 경우 동시성이 증가되는데 반해 데이터의 무결성에 문제가 발생할 수 있거나, 데이터의 무결성을 완벽하게 유지하는 데 반하여 동시성이 떨어질 수 있으므로 그 기능을 완벽하게 이해한 후에 사용해야 합니다.

Mysql 의 InnoDB 스토리지 엔진의 기본 Isolation Level이 REPEATABLE-READ 이고 Oracle 은 READ-COMMITED 입니다. 
Oracle 은 READ-COMMITED 와 SERIALIZABLE 만 지원하며 나머지 두가지 isolation level 은 지원하지 않습니다.

참고로, 트랜잭션이 발생하면 락(Lock)이 걸리는데,
SELECT 시에는 공유 락, CREATE/INSERT/DELETE 시에는 배타적 락이 걸립니다.

 

1. Read Uncommitted
   • SELECT 문장을 수행하는 경우 해당 데이터에 Shared Lock이 걸리지 않는 Level입니다.
   • 따라서, 어떤 사용자가 A라는 데이터를 B라는 데이터로 변경하는 동안 다른 사용자는 B라는 아직 완료되지 않은(Uncommitted 혹은 Dirty) 데이터 B를 읽을 수 있습니다.
   • 변경하는 동안, 즉 commit하지 않은 데이터를 읽을 수 있는 것
   • 발생할 수 있는 문제점
     • Dirty Read
     • Non-Repeatable Read
     • Phantom Read
2. Read Committed
   • SQL Server가 Default로 사용하는 Isolation Level입니다.
   • 이 Level에선 SELECT 문장이 수행되는 동안 해당 데이터에 Shared Lock이 걸립니다.
   • 그러므로, 어떠한 사용자가 A라는 데이터를 B라는 데이터로 변경하는 동안 다른 사용자는 해당 데이터에 접근할 수 없습니다.
   • 변경하는 동안에 읽을 수 없는 것 즉, 커밋이 완료된 데이터만 읽을 수 있는 것
   • 발생할 수 있는 문제점
     • Non-Repeatable Read
     • Phantom Read
   • 오라클의 default 값
3. Repeatable Read
   • 트랜잭션이 완료될 때까지 SELECT 문장이 사용하는 모든 데이터에 Shared Lock이 걸리므로 다른 사용자는 그 영역에 해당되는 데이터에 대한 수정이 불가능합니다.
   • 가령, Select col1 from A where {condition}을 수행하였고 이 조건에 해당하는 데이터가 2건이 있는 경우(col1=1과 5) 다른 사용자가 col1이 1이나 5인 Row에 대한 UPDATE이 불가능합니다. 하지만, 이 조건에 해당하는 Row를 INSERT하는 것은 가능합니다. 따라서 동일 트랜잭션에서 Read되는 데이터 수가 달라질 수 있습니다.
   • 어떤 데이터가 조회되는 동안, 그 데이터 조건에 맞는 insert는 가능. update/delete는 불가능
   • 트랜잭션 중에 한번 조회한 데이터를 반복해서 조회해도 같은 데이터가 조회됨. 근데 추가되어 조회될 수도 있음
   • 발생할 수 있는 문제점
     • Phantom Read
   • InnoDB의 default값
4. Serializable
   • 트랜잭션이 완료될 때까지 SELECT 문장이 사용하는 모든 데이터에 Shared Lock이 걸리므로 다른 사용자는 그 영역에 해당되는 데이터에 대한 수정 및 입력이 불가능합니다.
   • 예를 들어, Repeatable Read의 경우 조건을 만족하는 데이터가 INSERT는 가능하였습니다. 하지만 이 Level에서는 INSERT 작업도 허용하지 않습니다.
   • insert도 불가능
   • 가장 엄격한 격리 수준.
   • 발생할 수 있는 문제점 없음

 

 

 

---

References

• https://nesoy.github.io/articles/2019-05/Database-Transaction-isolation
• https://feco.tistory.com/45