잠금에 관한 고찰(2) - 격리 수준(Transaction Isolation Level)에 대하여

격리 수준(Transaction Isolation Level)이란 SQL Server에서 잠금(Lock)을 어떤 식으로 적용할 것인가에 관한 얘기이다. 주로 SELECT시의 Lock을 어떻게 걸 것이냐에 따라 격리 수준이 구분지어진다.

1. READ COMMITTED

  글자 그대로 해석하면 "커밋된 데이터만 읽을 수 있는" 격리수준이다. 

  SQL Server의 기본 격리수준으로서 SELECT 실행시 공유잠금을 건다. 이 격리수준에서는 SELECT를 시도하려는 DATA에 다른 트랜잭션에서 업데이트를 진행한 경우, 배타적 잠금(X-Lock)이 걸린 데이터에 공유잠금(S-Lock)을 걸려고 시도하므로 업데이트의 트랜잭션이 종료될 때까지 SELECT는 Block된다. Block된 SELECT는 트랜잭션이 종료되면 자동으로 실행된다.

  예를 들어 다음과 같은 SQL을 순서대로 실행시킬 경우, 세션 2는 블럭된다.

-- 세션 1 BEGIN TRAN UPDATE TABLENAME SET VAL=1 WHERE PK = 10 -- 세션 2 : 위의 SQL 실행 후 실행 SELECT * FROM TABLENAME WHERE PK = 10 -- 블럭된다!!!

2. READ UNCOMMITTED

  말 그대로 "커밋되지 않은 데이터도 읽을 수 있는" 격리수준이다.

  업데이트가 커밋되지 않은 데이터에는 배타적 잠금(X-Lock)이 걸려있다. 배타적 잠금이 걸린 데이터를 SELECT할 수 있다는 것은 SELECT시 어떠한 잠금도 걸지 않는다는 것을 의미한다.

업데이트되지 않은 데이터를 읽을 수 있다는 것은 무결성을 깨트릴 위험성이 있다는 의미이다. 예를 들어 SELECT한 결과가 실제로 DB에 INSERT되지 않고 ROLLBACK될 경우, 존재하지 않는 DATA를 SELECT한 결과가 된다. 하지만 그런 경우가 매우 적고, READ UNCOMMITTED 수준에서는 동시성이 향상되는 효과가 있기 때문에 자주 사용되는 수준이다.

  예를 들어 다음과 같은 SQL을 순서대로 실행시킬 경우, 세션 2에서는 1 값이 SELECT된다..

-- 세션 1 UPDATE TABLENAME SET VAL=2 WHERE PK = 10 BEGIN TRAN UPDATE TABLENAME SET VAL=1 WHERE PK = 10 -- 세션 2 : 위의 SQL 실행 후 실행 SELECT * FROM TABLENAME WITH (READUNCOMMITTED) WHERE PK = 10 -- 1 값이 SELECT된다.

READ UNCOMMITTED 격리수준은 다음과 같이 설정할 수 있다.

SELECT * FROM TABLE1 WITH (READUNCOMMITTED) WHERE ... -- 해당 SELECT문장에만 적용 SELECT * FROM TABLE1 WITH (NOLOCK) WHERE ... -- 해당 SELECT문장에만 적용 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED -- 해당 세션에 적용

3. REPEATABLE READ

  직역하면 "반복가능한 읽기" 정도?

  REPEATABLE READ 수준에서는 동일 트랜잭션 내에서 한번 SELECT했던 ROW에 대해서는 UPDATE나 DELETE가 불가능하며 트랜잭션 종료시까지 다시 SELECT했을 때 같은 값이 SELECT된다는 것이 보장된다.

  내부적으로는 SELECT했을 때 해당 ROW에 걸리는 S-LOCK이 즉시 해제되지 않고 트랜잭션 종료시까지 유지된다는 것을 의미한다.

다음의 예를 보자

-- 세션 1 BEGIN TRAN SELECT VAL FROM TABLENAME WITH (REPEATABLEREAD) WHERE PK = 10 -- VAL의 값이 1인 RECORD 1개가 출력되었다고 치자. -- 세션 2 : 위의 SQL 실행 후 실행 UPDATE TABLENAME SET VAL = 2 WHERE PK = 10 -- 세션1 트랜잭션 종료시까지 BLOCK된다.

이 상태에서 세션 1에서 다시 SELECT해본다 -- 세션 1 SELECT VAL FROM TABLENAME WITH (REPEATABLEREAD) WHERE PK = 10 -- 조금 전과 동일하게 1 값이 1개 출력된다.

그러나 이 격리수준은 SELECT된 ROW의 데이터에 UPDATE할 수 없다는 의미이지, 이 테이블에 INSERT 까지 차단된다는 의미는 아니어서 SELECT했던 행의 값들은 동일하게 유지되지만, SELECT의 결과가 항상 동일하다는 의미는 아니다. (즉 SELECT 결과 RecordSet의 변경은 되지 않지만 추가는 발생할 수 있다.)

다음과 같이 해보자. -- 세션 1 BEGIN TRAN SELECT VAL FROM TABLENAME WITH (REPEATABLEREAD) WHERE VAL = 1 -- VAL의 값이 1인 RECORD 1개가 출력 -- 세션 2 : 위의 SQL 실행 후 실행 INSERT INTO TABLENAME (PK, VAL) VALUES (11, 1) -- 잘 실행된다.

이 상태에서 세션 1에서 다시 SELECT해본다.  -- 세션 1 SELECT VAL FROM TABLENAME WITH (REPEATABLEREAD) WHERE VAL = 1 -- VAL 값이 1인 레코드가 2개 출력된다.

SELECT 결과가 동일하게 보장되지는 않음을 확인할 수 있다.

REPEATABLE READ 격리수준은 다음과 같이 설정할 수 있다.

SELECT * FROM TABLE1 WITH (REPEATABLEREAD) WHERE ... -- 해당 SELECT문장에만 적용 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ -- 해당 세션에 적용

4. SERIALIZABLE

  우리말로는 "직렬화"이다. 직렬화란 한줄로 서서 순서대로 처리되는 상황을 의미하며 동시성은 최악이 된다.

  이 경우에는 SELECT 했을 때 SELECT된 ROW에 RANGED S-LOCK이 걸리고 트랜잭션 종료시까지 유지되기 때문에, 트랜잭션 종료시까지 해당 범위에 대해서 일체의 UPDATE, INSERT, DELETE가 차단되고, 따라서 동일한 SELECT 결과 재현이 보장된다. 이것은 SQL Server에서 지원하는 가장 높은 격리수준이며 가장 높은 데이터 무결성이 제공되지만, 성능은 최악이다. COM+의 기본 수준이라고 한다.

다음의 예를 보자 -- 세션 1 BEGIN TRAN SELECT * FROM TABLENAME WITH (SERIALIZABLE) WHERE VAL < 5 -- VAL값이 5이하인 RECORD에 RANGED S-LOCK이 걸린다. -- 세션 2 : 위의 SQL 실행 후 실행 INSERT INTO TABLENAME (PK, VAL) VALUES (11, 1) -- 이 SQL은 BLOCK된다!! INSERT INTO TABLENAME (PK, VAL) VALUES (12, 7) -- 이 SQL은 잘 실행된다.

첫번째 INSERT문이 BLOCK되는 것은 INSERT하려는 VAL값이 세션1에서 RANGE LOCK을 건 범위에 포함되기 때문이다. 그러나 두번째 INSERT문은 RANGE LOCK의 범위 밖의 값이므로 INSERT가 가능하다.

여기서 RANGE-LOCK이 걸리는 것은 해당 컬럼에 인덱스가 있을 경우에 한한다. 위의 예제에서 VAL 컬럼에 인덱스가 없다면 테이블 전체에 LOCK이 걸리기 때문에 첫번째 INSERT문은 물론이고 두번째 INSERT문도 BLOCK된다.

VAL 컬럼에 인덱스가 있을 경우 위의 SELECT를 실행한 후 SP_LOCK 프로시져를 실행해 LOCK 상태를 살펴보면 아래와 같다.

위의 이미지를 보면 Range Lock (RangeS-S)이 걸려있는 것을 볼 수 있다.

이 상태에서 인덱스만 삭제한 후 동일한 SELECT문을 실행시키면 다음과 같이 된다.

전체 테이블에 S-Lock이 걸린 것을 볼 수 있다. (인덱스의 유무는 잠금의 크기에 이정도로 큰 영향을 준다!!!)

SERIALIZABLE 격리수준은 다음과 같이 설정할 수 있다.

SELECT * FROM TABLE1 WITH (SERIALIZABLE) WHERE ... -- 해당 SELECT문장에만 적용 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE -- 해당 세션에 적용