[MSSQL] 트랜잭션 격리수준(TRANSACTION ISOLATION LEVEL)

 

최근에 에러를 분석하면서 트랜잭션 격리수준에 대해 더 자세하게 공부하게 되었는데 그 내용을 정리했다.

 

먼저 트랜잭션 격리수준이란?

여러 트랜잭션이 처리될 때 특정 트랜잭션이 참조하고 있는 데이터를 다른 트랜잭션에서 볼 수 있을지 여부를 결정하는 것을 말한다.

즉, 먼저 선점하고 있는 데이터를 다른 트랜잭션이 접근해서 볼 수 있는지 결정하는 역할을 하는 것을 말한다.

 

이게 왜 필요할까

격리수준은 데이터의 일관성을 유지하고 안정적으로 데이터를 복구할 수 있다는 점에서 필요하다.

간단하게 말하자만, 만약 '과일'이라는 컬럼에 '사과'를 추가한다고 하자. 여기서 다른 사람은 '과일'에 어떤 종류가 있는지 알고 싶어한다면, '사과'라는 값이 들어간 데이터를 보여줄지 아니면 그 전까지의 데이터만을 보여줄지를 결정할 수 있게 되는 셈이다.

 

MSSQL의 트랜잭션 격리수준

-- Syntax for SQL Server and Azure SQL Database

SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }

 

MSSQL에서는 다음과 같이 격리 수준을 설정하는데 각각의 격리수준의 의미를 살펴보자.

 

// 뒤로 갈수록 동시성이 낮아지고 격리성이 높아진다
READ UNCOMMITTED < READ COMMITTED < REPEATABLE READ < SERIALIZABLE

Isolation level	Dirty Read	Non-Repeatable Read	Phantom read
Read uncommitted	Yes	Yes	Yes
Read committed	No	Yes	Yes
Repeatable read	No	No	Yes
Serializable	No	No	No

 

READ UNCOMMITTED

다른 트랜잭션에 의해 커밋되지 않은 내용도 읽을 수 있는 수준을 의미한다. 이 사항은 모든 SELECT문에 NOLOCK을 설정하는 것과 같은 수준으로 볼 수 있다. 하지만 커밋을 안한 내용도 읽기 때문에 정확하게 UPDATE 된 값을 가지고 오는 것이 아니라는 점에서 일관성이 떨어지며, DIRTY READ가 발생한다.

 

READ COMMITED

커밋된 내용만 읽기 때문에 DIRTY READ를 방지할 수 있다. 기본적으로 RDB에서 적용되고 있는 격리수준으로 다른 트랜잭션이 업데이트하고 커밋하지 않은 내역은 볼 수가 없다. 따라서 다른 세션에서는 SELECT 시, 커밋되어져 있던 기존의 UNDO 영역의 레코드 값을 가지고 온다.

하지만 이 경우, 동일한 쿼리를 실행한다고 해도 다른 트랜잭션의 커밋여부에 따라 다른 값을 가지고 오기 때문에 정합성에 어긋난다. 

 

REPEATABLE READ

하나의 스냅샷 버전으로 동작하기 때문에 SELECT 쿼리를 다시 실행했을 때 커밋여부로 값이 달라지던 READ COMMITTED와 달리 동일한 값을 유지한다. 이름처럼 여러 번 반복해서 READ하더라도 같은 값을 가지고 올 정도의 격리수준을 보장한다.

대신 모든 READ가 끝나기 전까지 UPDATE 트랜잭션을 할 수 없다. 반대로 UPDATE가 진행된다면 READ 할 수 없다는 것은 READ COMMITTED LEVEL과 유사하다.

 

SERIALIZABLE

REPEATABLE READ와 유사하나 SELECT 쿼리가 모두 SELECT ... FOR SHARE와 같이 자동으로 변경된다.

 

해보지 않으면 모르니까

CREATE TABLE [dbo].[TestTab](
[idx] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
[Height] [int] NOT NULL,
[Age] [int] NOT NULL,
CONSTRAINT [PK_TestTab] PRIMARY KEY CLUSTERED
(
[idx] ASC
) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO [TestTab] VALUES('하나',150,10)
INSERT INTO [TestTab] VALUES('두울',166,20)
INSERT INTO [TestTab] VALUES('세엣',163,30)
INSERT INTO [TestTab] VALUES('네엣',180,35)

 

현재 Isolation Level 찾기

여기서 transction_isolation_level 값이 숫자로 주어지는데 의미는 다음 표와 같다.

SELECT transaction_isolation_level       
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID

0	Unspecified
1	ReadUncommitted
2	ReadCommitted
3	Repeatable
4	Serializable
5	Snapshot

 

READ UNCOMMITTED

'하나'의 나이를 10살에서 70살로 변경했을 세션1에서 READ UNCOMMITTED 격리 수준을 두고 커밋을 하지 않았지만, 세션 2에서 조회했을 때 70살의 나이로 변해있는 것을 볼 수 있었다.

 

READ COMMITTED

두 개 창을 띄어서 실행했을 때, No.2까지 실행한 결과는 No.1에서 '네엣'이라는 사람의 나이를 변경한 사항을 반영하지 못한 상태로 SELECT 결과 값을 가지고 온다는 것을 확인할 수 있다.

그 이후에 왼쪽 트랜잭션을 커밋한 경우에 다시 오른쪽 트랜잭션에서 SELECT하면, 그 결과 값이 변경된 것을 볼 수 있다.

 

REPEATABLE READ

동일하게 나이를 변경하고 오른쪽 트랜잭션을 실행시켰을 때는 조회되지 않고 계속 쿼리를 실행중인 것을 볼 수 있었다. 왼쪽 커밋을 하기 전까지 SELECT 작업이 락이 걸려있음을 알 수 있다.

락여부 조회

EXEC SP_LOCK
EXEC SP_WHO2

 

SERIALIZABLE

SERIALIZAVBLE의 경우에도 캡쳐는 하지 않았지만, 위와 동일한 방식으로 실행했을 때 락이 걸려 조회가 안되는 걸 확인할 수 있었다.