[Database] Transaction Isolation level

Introduction

이전 글

1. [Database] Transaction ACID

Transaction ACID에 이어서 이번에는 Transaction Isolation Level에 대해서 다뤄보려고 합니다. Isolation Level에 대한 정의는 SQL-92를 기준으로 작성했습니다.

Transaction Isolation Level

트랜잭션 격리 수준(Transaction isolation level)의 정의는 2021/11/03에 작성된 Microsoft ODBC문서를 참고했습니다.

Transaction isolation levels are a measure of the extent to which transaction isolation succeeds. In particular, transaction isolation levels are defined by the presence or absence of the following phenomena: Dirty Read, Nonrepeatable Reads, Phantoms

- Microsoft ODBC -

Transaction Isolation Levels (ODBC) - ODBC API Reference

 

 

간단하게 해석하면, "트랜잭션 격리수준은 트랜잭션 격리가 성공하는 정도에 따라 나뉘며, 주로 Dirty Read, Nonrepeatable Read, Phantoms과 같은 현상의 유무에 따라서 결정됨" 정도로 해석 할 수 있을 것 같습니다.

다만 조금 더 이해를 쉽게 하기 위해서, 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있도록 허용할지 말지를 결정한다고 생각하셔도 좋습니다.

 

ACID 원칙이랑 위배되는 것 같은데....?

ACID원칙을 실제 상황에서 만족시키기 위해서는 동시성이 너무 떨어지기 때문에, 이를 일부 무시하여 동시성을 확보하기 위한 현실적인 대안이 Transaction Isolation Level이라고 생각할 수 있습니다. 격리 수준이 낮을수록 더 높은 동시성을 보장하고, 높을수록 더 동시성이 낮아지지만 데이터의 정합성을 보장합니다.

그러면 이제 Dirty Read와 같은 현상이 무엇이며, 트랜잭션의 격리 수준에는 무엇이 있는지 알아보겠습니다.

 

 

🚫주의사항

트랜잭션 격리 수준에 대한 문서는 오로지 Microsoft의 ODBC문서만 참고할 생각입니다. 자료를 이것 저것 찾다보니, MySQL이나 PSQL같은 실제 사용되고 있는 DB에서 쓰이는 방식이 섞이면 Transaction isolation level과 격리 수준에 따라서 발생하는 현상이 달라집니다.

따라서 MySQL이나 PSQL의 transaction isolation 동작 방식은 다른 포스터에서 다룰 생각입니다.

🔒 Read Lock & Write Lock

시작하기에 앞서서 먼저 Read Lock과 Wirte Lock에 대해서 알아야 합니다.

Read Lock의 경우 특정 행에 Read Lock이 걸리면 Read 요청은 통과시키고 Write 요청은 대기 시킵니다. 만약 Read Lock이 걸려서 Write 요청이 블럭되고 있다면, 그 뒤에 들어온 Read 요청은 먼저 들어온 Write 요청이 처리되기 전까지 대기시킵니다.

** Write Lock**의 경우 특정 행을 수정하거나 지울 경우 그 행에 대해서 걸리는 Lock이다. 이 경우 모든 요청을 블럭시게 된다.

🔒[Lv.0] Read Uncommitted

• Read Uncommitted 혹은 Level 0 의 격리 수준이라고 합니다.
• 각 트랜잭션에서 변경내용이 COMMIT 혹은 ROLLBACK된 여부에 상관없이, 다른 트랜잭션에서 값을 조회할 수 있습니다.
• Dirty Read 현상이 발생합니다.

위의 그림과 같이 Transaction-1에서 Busan이라는 데이터를 넣었고 아직 COMMIT이 발생하지 않았음에도 불구하고 다른 트랜잭션인 Trnascation-2에서 Busan이라는 데이터를 조회할 수 있습니다.

Dirty Read

위와 같이 하나의 트랜잭션이 완료되기도 전에 다른 트랜잭션에서 그 값을 볼 수 있게 되는 현상을 Dirty Read라고 합니다.

🔒[Lv.1] Read Committed

• Read Committed 혹은 Level 1 의 격리 수준이라고 합니다.
• 주로 대부분의 RDB에서 Default로 사용되고 있는 트랜잭션 격리 수준입니다. (하지만 구현 방식은 모두 제각각 다릅니다.)
• ODBC에서는 Read Committed의 구현을 다음과 같이 설명하고 있습니다.

[수정 & 삭제] Update & Delete the row
특정 행을 수정하고 삭제하는 경우에는 수정 및 삭제하는 행에 Write Lock을 겁니다. 그리고 트랜잭션이 COMMIT 혹은 ROLLBACK되기 전까지 계속 Write Lock을 유지해서 다른 트랜잭션에 해당 행에 접근할 수 없도록 만듭니다.

[조회] Only Read the row
조회만 하는 트랜잭션일 경우, 현재 읽고 있는 행에 Read Lock을 건다. 그 후에 현재 행에 대한 조회가 끝나면 다음 행에 Read Lock을 걸고 현재 Read Lock이 걸린 행의 잠금을 해제합니다.
즉 현재 행에 대한 조회가 끝나면 다른 트랜잭션에서 조회가 끝난 행을 수정 혹은 삭제 할 수 있습니다.

 

ODBC와 같은 방식을 사용할 경우 UNDO영역에 저장하거나 DB의 Sanpshot을 찍는 등과 같은 방법은 사용하지 않습니다. 이와 같은 방법들은 MySQL과 같은 DB에서 새롭게 구현한 방식입니다.

Nonrepeatable Read

이와 같은 격리수준에서는 Notrepatable Read 현상이 나타날 수 있다. 이는 한 트랜잭션에서 조회를 두 번 했을 때, 행의 데이터가 달라짐을 의미합니다.

위의 사진과 같이 조회 중에 Read Lock을 걸고 조회를 마치고 Read Lock을 해재한 사이에 다른 트랜잭션에서 특정 행에 Update를 하고 완료를 했다면, 그 행을 다시 읽을 때 값이 다른 현상이 나타나게 됩니다.

이는 한 트랜잭션에서 같은 조회를 했을 때 값이 일정해야 한다는 것에 어긋나게 됩니다.

🔒[Lv.2] Repeatable Read

• Repeatable Read 혹은 Level 2 의 격리 수준이라고 합니다.
• ODBC에서는 Repeatable Read의 구현을 다음과 같이 설명하고 있습니다.

[수정 & 삭제] Update & Delete the row
수정하고 삭제하는 경우에 대해서는 접근하는 모든 행에 대해서 Write Lock을 겁니다. 이를 걸기 때문에 다른 어떤 요청도 트랜잭션이 완료되기 전까지 행에 접근할 수 없습니다.

[조회] Only Read the row
프로그램으로부터 반환될 모든 행에 대해서 Read Lock을 걸고 유지합니다. 즉 조회하는 트랜잭션이 끝날 때 까지 Read하는 요청을 제외하고 행에 접근할 수 없습니다

위와 같은 방식으로 Read Lock 자체를 유지하기 때문에, Nonrepeatable Read 현상을 방지할 수 있습니다.

Phantom Read

Repeatable Read의 문제는 이와 같이 row에 대해서만 잠금을 거는데서 시작합니다. 잡금을 거는 범위가 행에 국한되기 때문에 새로운 행이 Instert 되는데 대응하지 못합니다.

사진과 같이 Transaction-1이 다시 한 번 조회를 했을 때, 보이지 않던 행이 보이게 되는 현상이 일어납니다.

🔒[Lv.3] Serializable

• Serializable 혹은 Level 3 의 격리 수준이라고 합니다.
• ODBC에서는 Serializable의 구현을 다음과 같이 설명하고 있습니다.

[수정 & 삭제] Update & Delete the row
수정이나 삭제할 때도 마찬가지로 영향을 미칠수 있는 모든 범위에 Wirite Lock을 겁니다.

[조회] Only Read the row
조회가 될 가능성이 있는 행 범위 전체에 대해서 Read Lock을 걸고 유지합니다. 예를 들어서 SELECT * FROM Orders 구문이 있다면, Orders 테이블 전체에 걸쳐서 Read Lock을 걸어서 다른 트랜잭션에서 행에 접근해서 내용을 수정할 수 없게 막어버립니다.

이와 같은 방법을 이용하면 확실하게 데이터의 정합성을 보장할 수 있지만, 동시성을 매우 떨어트립니다.

Conclusion

현재 작성한 문서는 ODBC문서를 기반으로 해석한 글에 불과합니다. 실제 MySQL이나 PostgreSQL 그리고 실제로 사용되는 다양한 Database들은 더 발전된 방식을 사용하고 있는 것으로 알고 있습니다. 그렇기 때문에 이와 같은 방식의 Database의 작동방식으로 설명하기에는 Phantom Read와 같은 문제가 왜 발생하는지 설명하기 어려웠습니다.

그래서 각 데이터베이스들에 대한 트랜잭션 격리 수준에 대해서는 다음 포스팅에서 알아보기로 하고, 이번에는 조금 개념적으로 접근해서 정리해 보았습니다.

Reference

https://learn.microsoft.com/en-us/sql/odbc/reference/develop-app/transaction-isolation-levels?view=sql-server-ver16

https://csharp-video-tutorials.blogspot.com/2015/08/non-repeatable-read-example-in-sql.html