[MSSQL] ISOLATION (LOCK) LEVEL

아래 쿼리 실행하면 레벨에 따라 문자열로 출력

SELECT

CASE transaction_isolation_level 

WHEN 0 THEN 'Unspecified'  

WHEN 1 THEN 'ReadUncommitted'  

WHEN 2 THEN 'ReadCommitted'  

WHEN 3 THEN 'Repeatable'  

WHEN 4 THEN 'Serializable'  

WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL  

FROM sys.dm_exec_sessions;

현재쿼리에만 ISOLATION LEVEL을 적용하는 방법이

FROM TABLE WITH (ISOLATION_LEVEL) 과 같이 명시하면 됨.

1. READ COMMITED

커밋 된 데이터만 SELECT 가능, UPDATE, DELETE 등의 배타적잠금 (X LOCK)이 걸린 테이블에 SELECT를 하러 들어가면 BLOCK처리 되며 트랜잭션이 완성될때까지 기다림.

UPDATE 수행 -> LOCK -> SELECT 대기 -> UPDATE COMMIT -> SELECT 정상 수행

UPDATE 수행 -> LOCK -> UPDATE COMMIT -> SELECT 바로 수행

2. READ UNCOMMITED

커밋 되지 않은 데이터도 SELECT 가능. 실시간으로 데이터가 변경 될 수도 있음.

또한 반대로 READ COMMITED 에서 SELECT 도중 공유잠금 (S LOCK) 으로 TABLE/ROW에 UPDATE/DELETE가 BLOCK 되지만 READ UNCOMMITED 옵션에서는 SELECT 도중에도 UPDATE/DELETE가 가능.

예를들면 기존 데이터를 1 -> 2로 업데이트 하는 과정에서

1번세션에서 )

BEGIN TRAN

UPDATE TABLE SET VALUE = 2 WHERE VALUE = 1

2번세션에서 )

SELECT VALUE FROM TABLE       ==> 결과는 2

이후 1번 세션이 ROLLBACK 한다면 2번 세션이 다시 SELECT 시 결과는 1이 된다.

기본 레벨이 READ UNCOMMITED가 아닌 상태에서 현재 SELECT 쿼리에만 UNCOMMITED를 주는방법 중 하나가 FROM TABLE 절 뒤에 NOLOCK를 명시

SELECT VALUE FROM TABLE WITH (NOLOCK) 

또는 SELECT VALUE FROM TABLE (NOLOCK) 

또는 SELECT VALUE FROM TABLE (READUNCOMMITED)

3. REPEATABLE READ

트랜잭션 단위로 SELECT가 들어있는 트랜잭션이 수행될 경우 그 트랜잭션이 끝나기 전까지는 SELECT의 내용이 항상 동일함. 

-> MySQL에선 정말 그런데 MSSQL에서는 항상 동일하진 않음.

-> MSSQL에서 기존데이터는 동일하나 데이터가 추가 될 수 있음 (MYSQL은 불가능)

1번세션에서 )

BEGIN TRAN

SELECT * FROM TABLE 결과가 1,2,3,4 라고하면

2번세션에서 )

UPDATE -> 불가능

DELETE -> 불가능

INSERT -> 가능

INSERT INTO TABLE VALUES(5);

다시 1번세션에서 )

SELECT * FROM TABLE 결과는 1,2,3,4,5가 된다.

1번세션이 COMMIT/ROLLBACK 등 트랜잭션 완료를 하게 되면, 다른세션에서 UPDATE/INSERT가 가능하다.

4. SERIALIZABLE

REPEATABLE READ와 동일하나 INSERT 조차 막힘.

다만 SERIALIZABLE은 인덱스가 존재하고 PLAN 에서 S LOCK 부분이 정확히 명시가 된다면 그 범위 외의 데이터는 INSERT가 가능함.

인덱스가 존재하지 않는경우에는 TABLE 전체에 LOCK이 걸린다.

1번 세션)

SELECT * FROM TABLE WHERE ID BETWEEN 5 AND 10

이경우 정확히는 앞/뒤 데이터 인덱스인 4~11까지 LOCK이 됨.

5가 첫 ID값이라고 하면 5~11까지 LOCK이 되고, 4는 있고 10이 마지막 ID라고 한다면 4~10까지 LOCK이 됨.

이런경우 ID가 인덱스로 잡혀있고 PLAN에 정확히 INDEX를 탄다면 INSERT INTO VALUE ID 20은 가능하다.

그러나 INSERT INTO VALUE ID 8은 불가능. (LOCK 구간)

이유는 BETWEEN의 ID 값이 5 ~ 10 인 경우 5, 6, 7, 8, 9, 10이기 때문에 그 사이에 8, 9같은 값이 INSERT가 된다면 SELECT TRANSACTION이 끝나기 전에 다음번 SELECT에서 데이터가 바뀌기 때문이다.