트랜잭션 격리 수준

트랜잭션 격리 수준

트랜잭션의 격리 수준은 A라는 트랜잭션이 수행되고 있을때 B라는 트랜잭션에서 A가 다루고 있는 내용을 변경하거나 조회할 수 있도록 설정하는 것을 의미한다.

트랜잭션이 보장하는 ACID 4가지 특성 중 Isolation(격리성)의 trade-off에 대해서 다루는 내용이다. 이때 트레이드오프하는 두 가지 요소는 격리성과 동시성이다.

 

동시성이 높아지면 성능은 높아지지만 데이터의 정합성이 낮아지고

격리성이 높아지면 성능은 낮아지지만 데이터의 정합성이 올라간다.

 

격리 수준에는 아래와 같이 총 4가지 단계가 존재하며 옆의 숫자는 임의로 붙인 숫자로 높을수록 해당 기능이 강화된다.

 

동시성 3 격리성 0 (READ UNCOMITTED)

WRITE-LOCK X

READ-LOCK X

CRUD 전부 가능

 

동시성을 최대로 높이고 격리성을 최대로 낮추어 A 트랜잭션의 COMMIT, ROLLBACK 여부와 관계없이 B 트랜잭션에서 항상 값을 조회할 수 있는 경우를 의미한다.

 

이때 A 트랜잭션이 COMMIT되거나 ROLLBACK 되기도 전에 값을 B 트랜잭션에서 해당 값을 읽어갔으므로 데이터 정합성의 불일치 문제가 발생할 수 있게 된다. 이런 경우를 더티 리드(DIRTY READ)라 한다.

 

해당 옵션으로 격리 수준을 설정하게 되면 각 트랜잭션은 전혀 격리되지 않으므로 주로 READ-ONLY인 경우에 사용되는 옵션이다.

 

동시성 2 격리성 1 (READ COMITTED)

WRITE-LOCK O (UPDATE와 DELETE의 경우, COMMIT or ROLLBACK까지)

READ-LOCK O (해당 ROW를 읽고 있을 경우에만)

 

대부분의 RDBMS에서 디폴트로 설정되어있는 격리 수준이며 가장 많이 사용되는 옵션이다.

 

A 트랜잭션에서 해당 값을 사용 중이라면 해당 ROW에 WRITE-LOCK이 걸리게 되고 B 트랜잭션에서는 lock이 풀릴 때까지 대기하다가 풀리고나서 값에 접근할 수 있으므로 더티 리드 문제는 발생하지 않는다.

 

하지만 격리의 기준이 쿼리이기 때문에 한 트랜잭션 내의 서로 다른 쿼리가 같은 ROW에 여러번 접근하면 서로 다른 결과가 조회되는 PHANTOM 문제가 발생할 수 있다.

 

트랜잭션은 해당 ROW에 대해 읽는 경우 READ-LOCK을 걸고 변경하거나 삭제하는 경우 WRITE-LOCK을 건다. READ-LOCK은 해당 ROW를 읽는 것이 끝나면 바로 RELEASE하지만 WRITE-LOCK은 COMMIT되거나 ROLLBACK 되기 전까지 RELEASE되지 않는다.

 

동시성 1 격리성 2 (REPEATABLE READ)

WRITE-LOCK O (UPDATE, DELETE의 경우, COMMIT or ROLLBACK까지)

READ-LOCK O (트랜잭션의 시작부터 끝까지)

 

A 트랜잭션에서 해당 값을 사용 중이라면 해당 ROW에 WRITE-LOCK이 걸리게 되고 B 트랜잭션에서는 lock이 풀릴 때까지 대기하다가 풀리고나서 값에 접근할 수 있으므로 더티 리드 문제는 발생하지 않는다.

 

하나의 트랜잭션에 포함되는 모든 쿼리가 다루려고 하는 ROW에 미리 트랜잭션을 걸고 시작하여 읽고자 하는 모든 ROW에 READ-LOCK을 걸고 수정, 삭제를 하는 모든 ROW에 대해 WRITE-LOCK을 건다.

 

B 트랜잭션에서 A 트랜잭션이 사용 중인 모든 ROW에 수정과 삭제가 불가능하므로 NON-REPEATABLE READ 문제를 문제가 발생하지 않는다.

(NON-REPEATABLE READ란 한 트랜잭션 내에서 다른 트랜잭션에 의해 값이 수정되거나 삭제되어 서로 다른 값을 가져올 수 있는 경우를 의미한다.)

 

동시성 0 격리성 3 (SERIALIZABLE)

WRITE-LOCK O (INSERT, UPDATE, DELETE의 경우, COMMIT or ROLLBACK까지)

READ-LOCK O (트랜잭션의 시작부터 끝까지)

 

A 트랜잭션에서 해당 값을 사용 중이라면 해당 ROW에 WRITE-LOCK이 걸리게 되고 B 트랜잭션에서는 lock이 풀릴 때까지 대기하다가 풀리고나서 값에 접근할 수 있으므로 더티 리드 문제는 발생하지 않는다.

 

이 레벨에서 트랜잭션은 읽어야 될 모든 범주의 ROW에 READ-LOCK을 걸고 다른 ROW에 대해 수정이나 삭제가 가능한 경우라도 WRITE-LOCK을 건다. 예를 들어, SELECT * FROM Orders라는 SQL을 날리면 Orders 테이블 전체가 READ-LOCK이 걸리는 것은 물론 새로운 값도 삽입되지 못 하도록 한다.

 

B 트랜잭션에서 A 트랜잭션이 사용 중인 모든 ROW에 수정과 삭제가 불가능하므로 NON-REPEATABLE READ 문제가 발생하지 않는다.

B 트랜잭션에서 A 트랜잭션이 사용 중인 모든 ROW에 대해 삽입이 불가능하므로 PHANTOM 문제가 발생하지 않는다.

 

레퍼런스

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

Transaction Isolation Levels (ODBC) - ODBC API Reference

https://corekms.tistory.com/entry/Read-committed-VS-Repeatable-read

Read committed VS Repeatable read