[MSSQL] 격리수준 - 비관적 동시성
SQL Server에서는 기본적으로 "비관적 동시성(pessimistic concurrency)"을 기반으로 하는
네 가지 형태의 격리 수준을 제공한다
1) READ UNCOMMITED
2) READ COMMITED(On-premises SQL Server의 기본값 )
3) REPEATABLE READ
4) SERIALIZABLE
1) READ UNCOMMITED
READ UNCOMMITTED은 가장 낮은 수준의 격리 수준이다.
이 격리 수준에서는 읽기 작업은 공유 잠금(S-LOCK)을 요청하지 않는다.
읽기 작업에서 공유 잠금(S-LOCK)을 요청하지 않기 때문에
배타적 잠금(X-LOCK)을 유지하고 있는 쓰기 작업과 충돌이 발생하지는 않는다.
이는 읽기 작업에서는 커밋되지 않는 변경사항(dirty reads)을 읽을 수 있다는 것을 의미한다.
또한, 읽기 작업이 쓰기 작업에서 요청하는 배타적 잠금(X-LOCK)을 막지도 않는다.
다시말해, 읽기 작업에서 READ UNCOMMITTED 격리 수준으로 데이터를 읽는 동안에도
쓰기 작업에서는 데이터를 변경할 수 있다는 것을 의미한다.
실습을 위해서 두개의 쿼리창을 준비한다.
쿼리창1)
BEGIN TRAN
UPDATE dbo.TBLINSA SET name = N'김길동' WHERE num = 1001
SELECT * FROM dbo.TBLINSA WHERE num = 1001
SELECT @@trancount AS trancount
쿼리창2)
SELECT * FROM dbo.TBLINSA WHERE num = 1001
현재 기본 격리수준은 READ COMMITTED 상태이고
TBLINSA 의 홍길동의 이름을 가진 행은 배타적잠금(X-LOCK) 상태이므로
위와 같이 쿼리하게 되면 읽기 작업에서 공유잠금(S-LOCK)을 요청하므로
블로킹이 발생하는것을 볼 수 있다.
다시 쿼리창2로 돌아가서 세션단위에서 격리수준을 낮춰줘보자.
READ COMMITTED -> READ UNCOMMITTED
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED -- 격리수준을 낮춤
SELECT * FROM dbo.TBLINSA WHERE num = 1001
위처럼 격리수준을 READ UNCOMMITED로 낮춘다면
아직 트랜잭션이 끝나지 않은 행에 대해서도 읽어올 수가 있다.
그리고 위처럼 아직 COMMIT 되지 않은 상태의 데이터를 읽어오는 "dirty read" 가 발생한다.
2) READ COMMITED
읽기작업에서 커밋되지 않은 데이터를 읽는 것을 방지하려면,
더 강력한 격리 수준을 사용해야 한다.
"dirty read" 를 방지하는 가장 낮은 수준의 격리 수준을 READ COMMITTED 이며,
이는 On-premises SQL Server의 기본 격리 수준이기도 하다.
이 격리 수준은 커밋된 변경사항에 대해서만 읽는 것을 허용한다.
커밋되지 않은 읽기 작업은 읽기 작업에서 공유잠금(S-LOCK)을 얻도록 함으로써 방지하게 된다.
이는 쓰기 작업에서 배타적 잠금(X-LOCK)을 유지하고 있으면,
읽기 작업의 공유 잠금(S-LOCK) 요청이
쓰기 작업과 충돌을 하기 때문에 기다려야 한다는 것을 의미한다.
쓰기 작업에서 트랜잭션을 커밋하자마자 읽기 작업에서는 공유 잠금(S-LOCK)을 얻게 되며,
커밋된 변경사항만 읽게된다.
쿼리창1)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
UPDATE dbo.TBLINSA SET name = N'이순재' WHERE num = 1002
위의 결과에서 봤지만, 현재 트랜잭션이 열린상태로 UPDATE 즉 쓰기작업을 진행하고 있으므로
배타적 잠금(X-LOCK)을 유지한 상태이다.
READ COMMITTED 격리수준을 적용하고 있으므로 현재 num = 1002 행은
배타적 잠금(X-LOCK)이 걸려있는 상태이며,
읽기 작업의 공유 잠금(S-LOCK) 요청 또한 거부된다.
쿼리창2)
SELECT * FROM dbo.TBLINSA WHERE num = 1002
쿼리창1)
COMMT TRAN쿼리창1 에서 커밋을 하게 되면 아래의 쿼리가 문제없이 작동되게 된다.
(X-LOCK이 풀리며 S-LOCK 요청에 대한 승인이 가능해지므로)
쿼리창2)
SELECT * FROM dbo.TBLINSA WHERE num = 1002
잠금의 지속 시간 관점에서 보면, READ COMMITTED 격리 수준에서는
읽기 작업에서 리소스를 읽어가는 동안에만
공유된 잠금(S-LOCK)을 유지하게 된다.
**트랜잭션이 끝날 때까지 잠금을 유지하지 않는다.**
즉, 하나의 트랜잭션 내에 동일한 데이터 리소스를 참조하는 두 개의 읽기 작업이 있더라도,
이 작업들 간에는 리소스에 대한 잠금이 유지되지 않는다는 것을 의미한다.
따라서 이러한 두 개의 읽기 작업이 수행되는 시점 사이에 다른 트랜잭션에서 리소스 값을 변경할 수도 있다.
이 경우 두 개의 읽기 작업은 서로 다른 값을 얻게 된다.
이러한 현상을 반복적이지 않은 읽기(non-repeatable reads) 또는 일관되지 않은 분석이라고 한다.
3) REPEATABLE READ
동일한 트랜잭션 내에서 발생하는 읽기 작업들 사이에
다른 트랜잭션에서 값을 변경하지 못하도록 하고자 한다면,
격리수준을 REPEATABLE READ로 상승시켜야 한다.
이 격리 수준에서는 읽기 작업에서 데이터를 읽기 위해 공유 잠금을 요청할 뿐만 아니라.
트랜잭션이 끝날때까지 공유잠금(S-LOCK)을 유지하게 된다.
이는 읽기 작업이 데이터 리소스를 읽기 위해 공유 잠금(S-LOCK)을 얻으면,
다른 작업에서는 이 읽기 작업에서 트랜잭션을 종료시킬 때까지
리소스를 변경하기 위한 배타적 잠금(X-LOCK)을 얻을 수 없다는 의미가 된다.
이와 같은 방식을 이용하면, 반복해서 읽더라도 동일한 값을 얻을 수 있다는 점을 보장할 수 있다.
READ COMMITTED 격리 수준에서는 아래와 같은 현상이 발생할 수 있다.
쿼리창1)
BEGIN TRAN
SELECT * FROM dbo.TBLINSA
위의 쿼리는 트랜잭션을 열어놓은 상태로 데이터를 읽어오고 있다.
데이터를 읽어오기 때문에 S-LOCK이 걸린것을 알 수 있다.
하지만 여기서의 S-LOCK은 트랜잭션의 종료 여부와 상관없이
SELECT 가 끝나자마자 S-LOCK 이 해제된 상태이다.
즉 아래의 쿼리문처럼 다른 쿼리창에서 UPDATE 를 하여 데이터쓰기를 발생시키면
X-LOCK에 대한 요청이 바로 승인되는 형태라고 볼 수 있다.
쿼리창2)
UPDATE dbo.TBLINSA SET name = N'훈길동' WHERE num = 1001
SELECT * FROM dbo.TBLINSA WHERE num = 1001
트랜잭션 동안 S-LOCK을 유지시키지 않으므로 잠금이 해제되고 X-LOCK 에 대해 승인 되어
위와 같이 정상적으로 UPDATE가 되어있는걸 볼 수 있다.
즉, 반복적이지 않은 읽기(non-repeatable reads)가 발생한다.
위와 같은 반복적이지 않은 읽기 발생을 피하려면
격리수준을 REPEATABLE READ 로 바꿔줘야 한다.
쿼리창1)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM dbo.TBLINSA
현재 위의 쿼리창1) 의 구문을 보면 트랜잭션이 열린상태이고, 읽기 작업을 수행하고 있다.
격리수준이 REPEATABLE READ 로 격상됨에 따라
해당 테이블을 읽어오는 과정에서 S-LOCK 이 걸리게 되는데,
SELECT 가 끝나면서 잠금이 풀리는것이 아닌 트랜잭션이 끝날때까지 S-LOCK을 유지해주게 된다.
쿼리창2)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
UPDATE dbo.TBLINSA SET name = N'구길동' WHERE num = 1001
만약 격리수준이 READ UNCOMMITTED나 READ COMMITTED 였으면 이미 데이터가 변경되었겠지만,
REPEATABLE READ 격리수준에서는 트랜잭션이 끝날때까지 S-LOCK을 유지하므로
X-LOCK 에 대한 승인이 거부되어 데이터를 변경하지 못하는 모습을 볼 수 있다.
데이터를 변경하고자하는 쿼리를 적용하려면 쿼리 1)에서
COMMIT TRAN 또는 ROLLBACK TRAN 으로 트랜잭션이 정상적으로 종료하면,
자연스럽게 S-LOCK 이 해제되므로 X-LOCK 에 대해 승인이 되고 데이터를 변경할 수 있게 된다.
4) SERIALIZABLE
REPEATABLE READ 격리수준 내에서는 읽기 작업의 트랜잭션이 끝날 때까지
공유 잠금(S-LOCK)을 유지한다.
따라서, 트랜잭션에서 처음 읽은 행의 값은 반복해서 읽더라도 같은 값임을 보장할 수 있다.
하지만, 트랜잭션에서는 처음 실행했을때
쿼리에서 접근하지 않은 다른행들에 대해서는 잠금을 설정하지 않는다.
따라서 동일한 트랜잭션 내에서 두 번 읽기 작업을 수행하게 되면, 새롭게 추가된 행이 반환될 수도 있다.
이러한 새로운 행들을 팬텀(phantom)이라 하며,
이러한 행들에 대한 읽기 작업을 팬텀읽기(phantom-reads)라고 한다.
이러한 현상은 다른 트랜잭션에서 새로운 행들이 추가된 후,
새롭게 추가된 행들이 현재 읽기 작업의 쿼리 필터의 대상에 속하는 경우에 발생한다.
쿼리창1)
BEGIN TRAN
SELECT * FROM dbo.TBLINSA현재 쿼리창에서 격리수준은 REPEATABLE READ 이다.
또한 트랜잭션을 열어놓기만 하고 데이터를 읽고 있으니
현재로써는 공유잠금(S-LOCK)이 걸려있다고 볼 수 있다.
하지만 해당 테이블에 새로운 행을 추가하는 작업에 대한것은 제약이 없다.
쿼리창2)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
INSERT INTO dbo.TBLINSA VALUES (1061,N'테스트','123-123','2001-01-13',N'서울','010-7777-8888',N'개발부',N'대리',1000000,100000)
SELECT * FROM dbo.TBLINSA
쿼리한 결과를 보면 팬텀읽기(phantom-reads)를 한것을 알 수 있다.
이와 같은 팬텀읽기를 방지하기 위해서는 격리수준을 SERIALIZABLE로 상승시켜야 한다.
SERIALIZABLE 의 대부분 동작은 REAPEATABLE READ 와 비슷하지만, 다른점이 하나있다.
논리적으로, 이 격리 수준은 쿼리필터의 대상이 되는 키 값들의 모든 범위에 대해 잠금을 유지한다는 점이다.
즉, 읽기 작업에는 쿼리 필터의 대상이 되는 기존 행 뿐만 아니라, 앞으로 사용될 행에 대해서도 잠금을 설정한다.
또한, 다른 트랜잭션에서 추가하는 행도 읽기 작업의 쿼리 필터의 대상이 될 수 있기 때문에 차단된다.
쿼리창1)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT * FROM dbo.TBLINSA이번에는 격리수준을 높여서 위의 예제와 같은 상황을 만들어보자.
격리수준이 REAPEATABLE READ -> SERIALIZABLE 로 상향 되었으므로
읽기 작업에서도 기존 행 뿐만 아니라,
앞으로 사용될 행에서도 잠금을 설정해줬다고 보면된다.
쿼리창2)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
INSERT INTO dbo.TBLINSA VALUES (1061,N'테스트','123-123','2001-01-13',N'서울','010-7777-8888',N'개발부',N'대리',1000000,100000)
SELECT * FROM dbo.TBLINSA
앞으로 적용될 행에 X-LOCK 을 시도하고 있는데,
격리수준으로 인해서 현재 트랜잭션에는 앞으로 추가될 행에도
S-LOCK 이 설정되어있으므로 호환되지 않는 잠금이 발생하여
위의 INSERT 문 자체가 블로킹 되는것을 볼 수 있다.