트랜잭션 격리수준 - SQL 2005의 새로운 트랜잭션 격리수준

트랜잭션(Transaction) 이란 간단하게 말해서 논리적인 작업의 최소 단위라 정의 할 수 있다.
우리가 SQL Server에서 실행되는 일련의 명령 실행 과정이 트랜잭션으로 이뤄지며 이것은
명시적 또는 암시적 으로 시작(Begin Transaction)과 끝(Commit or Rollback)의 처리 범위를
지정할 수 있으며 이를 지정하지 않을경우 개별적인 트랜잭션이 자동으로 시작되고 끝이 난다.

이러한 트랜잭션은 원자성(Atomicity),일관성(Consistency),고립성(Isolation),
보존성(Durability) 등의 4가지 속성을 가지는데 이를 트랜잭션의 ACID속성이라 하며
이제부터 할려는 주제는 이중 고립성(Isolation)에 대한 내용이다.

고립성이란 하나의 트랜잭션이 발생되었을 경우 다른 트랜잭션에 대하여 얼마만큼 방해를 받지
않고 독립성을 가지느냐에 대한 트랜잭션 특성이다. 이것은 한 프로세스가 작업하는 리소스에
대해 다른 프로세스가 작업하지 못하도록 차단하는데 그 방법이 잠금(Lock)이다.
사용자는 트랜잭션 작업에 대한 일관성을 향상 시키기 위해 잠금(Lock)의 방법을 정의할수 있는데
이것을 격리수준(Isolation Level)이라 한다.

SQL Server 2000에서 지원하는 격리수준은 아래와 같다.
1. 커밋되지 않은 읽기(Read Uncommittes)
2. 커밋된 잃기(Read Committed)
3. 반복 읽기(Repeatable Read)
4. 직렬화(Serializabale)

SQL Server 2005에서는 이에 2가지를 더 지원한다.
5. 스냅샷(Snapshot)
6. 커밋된 읽기(Read Committed Snapshot)
이제 각각의 대해 알아보자.

1. 커밋되지 않은 읽기(Read Uncommittes)
이것은 말 그대로 "커밋되지 않은 데이터도 읽을 수 있다." 라는 것이다.
일반적으로 업데이트시 커밋되지 않은 데이터는 배타점 잠금(X-Lock)이 걸려있기 다른 프로세스에서
읽기 조차 되지 않는다. 하지만 이것은 커밋되지 않는 데이터를 다른 프로세스에서 SELECT시
어떠한 잠금도 걸지 않는다. 다만 업데이트 되지 않은 데이터를 읽을 수 있다는 것이 무결성을 깨트릴
위험성이 있다. 그러나 Rollback되는 데이터를 SELECT하는 경우가 흔치 않고 이 격리수준에서는
동시성의 향샹되는 효과가 있기 때문에 자주 사용되는 격리수준이다.
이제 실습을 해보자.
먼저 실습용 테이블과 데이터를 만들고

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

INSERT INTO [TestTab] VALUES('나나',171,48)
INSERT INTO [TestTab] VALUES('레이나',166,46)
INSERT INTO [TestTab] VALUES('이주연',168,46)
INSERT INTO [TestTab] VALUES('박가희',168,50)

이제 하나의 데이터를 명시적으로 트랜잭션을 지정하여 업데이트 해보자.

-- 세션1
BEGIN TRAN
UPDATE TestTab SET Weight = 55 WHERE idx = 4

잠금(Lock)이 걸리는지도 확인해보고

해당 테이블 Object ID로 배타적 잠금이 걸려있는걸 볼 수 있다.

그럼 다른 세션으로 Read Uncommittes을 이용하여 데이터를 불러보자 방법은 크게 2가지가 있다.

--세션2(해당 명령문만 적용)
SELECT * FROM TestTab WITH(READUNCOMMITTED) WHERE idx=4 
SELECT * FROM TestTab WITH(NOLOCK) WHERE idx=4

--세션2(해당 세션에 적용)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM TestTab WHERE idx=4

실행결과 트랜잭션이 종료 되지 않아도 데이터를 읽을 수 있다. 위와 같은 상태를 좀 더 깊게
설명을 하자면 일반적으로  SELECT시에도 공유잠금 이라는걸 요청을 하게 되는데 세션1에서의
배타적 잠금이 있는 상태에서 세션2에서의 공유잠금이 일어 날 수가 없다.
(베타적 잠금은 어떠한 잠금과도 호환 되지 않는다.)
그래서 해당 세션의 격리 수준을 커밋 되지 않은 읽기 수준으로 설정을 하면서 데이터를 읽는
작업이 실행되면 해당 세션은 공유잠금을 요청 하지 않는다는 것이다.
그렇지만 위에서 언급했듯이 커밋되지 않은 데이터를 읽기 때문에 일관성이 떨어진다.
다만 동시성이 좋기 때문에 OLTP환경에서 가장 흔하게 쓰이는 격리 수준이라 할 수 있다.

2. 커밋된 읽기(Read Committed)
커밋된 읽기는 SQL Server의 기본 격리 수준이다. 앞서 언급했듯이 데이터를 읽기 위해서는
기본적으로 공유잠금을 요청 하기 때문에 UPDATE를 실행중인 데이터를 SELECT시 배타적잠금이
걸려있는 데이터에 공유 잠금을 요청하기 때문에 UPDATE가 종료될 때까지 대기상태에 있으며
트랜잭션이 종료 됨과 동시에 실행이 된다.
간단하게 테스트를 해보자.

--세션1
BEGIN TRAN
UPDATE TestTab SET Weight = 55 WHERE idx = 4

이제 세션2에서 SELECT문을 실행해보자

--세션2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT * FROM TestTab WHERE idx=4

잠금상태를 확인해 보자

보시는 바와 같이 세션1(spid 62)는 해당 테이블에 배타점 잠금(Mode : X)을 걸어논 상태이며
해당 데이터에 SELECT를 시도하는 세션2(spid 61)은 공유잠금(Mode : S)을 요청한후
대기 상태(Status : Wait)에 있는 걸 확인 할 수 있다.

세션1에서 COMMIT또는 ROLLBACK을 실행하면 잠금정보는 해제되며 세션2에서는
해당 데이터를 읽어 올 수 있는걸 확인해볼수 있다.
위와 같은 격리 수준은 커밋되지 않은 데이터를 읽음으로써 일관성을 떨어뜨리는 
Dirty Read를 하지 않았지만 동시성이 떨어진다는것을 알 수 있다. 하지만 이 격리수준 역시도
반복 읽기(REPEATABLE READ)가 불가능해 일관성에 대한 문제가 있다.

3. 반복 읽기(Repeatable Read)
이것은 반복 가능한 읽기 수준이다. 위에서 언급했던 격리 수준과는 조금 다른 개념이며
SELECT시 명시적인 트랜잭션을 시작하여 공유잠금이 설정되면 해당 데이터에 대해 다른 세션에서는
UPDATE나 DELETE시 일어나는 배타적 잠금이 대기 상태에 놓이며 트랜잭션이 종료시 까지
해당 데이터를 반복적으로 SELECT시 동일한 데이터를 보장한다.

그럼 테스트를 해보자

--세션1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM TestTab WHERE idx=4

--세션2
BEGIN TRAN 
UPDATE TestTab SET Weight=55 WHERE idx=4
COMMIT

--세션1
SELECT * FROM TestTab WHERE idx=4
COMMIT

일반적인 커밋된 읽기(READ COMMITED)수준일 경우 위의 명령을 순서대로 실행 했을 경우

세션1에서 공유잠금이 요청되고 세션2에서는 배타적 잠금이 요청되며 세션1의 두번째 명령문은

실행이 안되고 대기를 하게 된다. 하지만 격리 수준을 반복 가능한 읽기수준으로 설정을 하면

세션1의 첫번째 명령문이 실행된 후 세션2의 명령은 세션1의 커밋 또는 롤백 명령으로 트랜잭션이

종료될때 까지 대기를 하게 된다. 그리고 세션2의 두번째 명령문을 실행하면 트랜잭션이 종료 되기 전
 다시 해당 데이터에 명령을 요청하면 데이터를 아무런 제약없이 읽을 수 있다.
또한 대기중인 세션2의 트랜잭션도 정상적으로 실행이 완료 된다.

이것이 반복읽기 이다.

요컨대, 한 세션의 트랜잭션이 진행 중일 때는 외부에서 데이터의 변경을 차단하고 동일한
세션에서는 동일한 데이터를 읽을수 있는 구조가 필요할때 활용 가능한 격리 수준이다.
하지만 외부로부터의 UPDATE에 대한 트랜잭션으로 부터 잠금이 획득되며 데이터의 INSERT시
까지 차단되지는 않는다. 즉 SELECT한 결과가 항상 동일 하다는 것은 아니다 라는걸 주의해야한다.

4. 직렬화(SERIALIZABLE)

직렬화는 간단히 말해 명령이 순서대로 처리 되는 것이다. 이 수준은 가장 높은 수준의 격리 수준으로

트랜잭션이 실행시 키범위 잠금(Range S-Lock)이 걸리고 트랜잭션이 종료 되기 전까지는 모든

외부로 부터의 트랜잭션으로 부터 격리된다. 또한 이것은 이미 존재하는 물리적인 데이터 뿐만이

아니라 가상의 논리적 데이터 까지도 잠금을 걸어 격리되기 때문에 동일한 SELECT결과가 보장되어

일관성이 뛰어 나지만 동시성이 현저히 떨어지므로 성능은 최악이다.

그럼 테스트를 해보자.

--세션1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
UPDATE TestTab SET Weight=45 WHERE Height=168
SELECT * FROM TestTab WHERE Height=168

--세션2
BEGIN TRAN
INSERT INTO TestTab VALUES('유이',168,50)
COMMIT

위의 명령을 실행 할 경우 위에서 언급한 반복읽기 수준일 경우에는 세션2의 데이터가 정상적으로
INSERT가 되며 다시 세션1의 SELECT문 실행시는 결과가 다르게 나온다.
하지만 직렬화(SERIALIZABLE)의 수준에서는 아래의 그림처럼 세션2가 대기 상태에 빠지게 된다.

세션1(spid 62)는 직렬화를 위한 공유키 잠금(RangeS-U)이 설정 되었으며 세션2(spid 61)은

그로인해 대기상태에 있는것을 확인 할수 있다. 위에 언급한 반복읽기(Repeatable Read)수준에서

말했던 외부로부터의 INSERT나 DELETE시에 발생하는 가상 데이터의 읽기(Panthom Read)를

차단 할 수 있다. 이처럼 데이터의 일관성이 보장되는 만큼 모든 트랜잭션에 대해서 차례차례

처리가 완료되어야 한다는 부담때문에 높은 격리수준에 비해 성능이 현저히 떨어질수 있다는

부담이 있다.

5. 스냅샷 격리수준(Snapshot Isolation)
오라클에는 이미 로우 버저닝이라는 기반의 트랜잭션 처리가 가능하다. 이것은 특정 데이터가

업데이트될 경우 Undo라는 공간에 로그를 남겨 커밋되기 전까지 이전 버젼의 데이터를 사용자에게

반환하여 읽기 일관성과 사용자 간의 동시성을 향상 시킨 방법이다. 오라클 10g버젼은 이러한

트랜잭션 처리 방식으로 TPC-C라는 OLTP벤치마크 테스트에서 32bit프로세서로 1분에 160만개의

트랜잭션을 처리하여 타 DBMS와 비해 성능이 우수 하다는 것을 검증 하였다.

이에 SQL Server 2005부터는 이와 유사항 기능인 스냅샷 격리수준을 가능케 하였는데 이것은

데이터의 변경이 일어날시 임시 DB인 TempDB에 저장하여 행 단위 버젼으로 관리함으로써 일관성과

동시성을 높이는 격리수준이다.

이것은 수많은 프로세스가 동시에 읽기/쓰기 작업으로 발생되는 공유잠금 확대(Lock Escalation)에

대한 문제에 대응할수 있다.

그럼 테스트를 해보자.

지금까지의 격리수준 설정과는 다르게 이것은 데이터베이스의 옵션을 변경을 해줘야 한다.

ALTER DATABASE TestDB SET ALLOW_SNAPSHOT_ISOLATION ON;

그리고 아래 쿼리를 실행해 보자.

--세션1
BEGIN TRAN
UPDATE TestTab SET Weight=50 WHERE Name='나나'
SELECT * FROM TestTab WHERE Name='나나'

--세션2
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN
SELECT * FROM TestTab WHERE Name='나나'

위의 쿼리를 실행하면 세션1의 결과는 Weight값이 50으로 보이며 세션2의 결과는 Weight값이

변경되지 않은 기존값으로 나올것이다. 다시 세션1의 트랜잭션을 커밋시켜본후 세션2을 실행해보자

그럼에도 데이터는 변경되지 않은 상태로 나올 것이다. 이것은 앞서 얘기했던 스냅샷 격리 수준에서는

변경된 데이터에 대해서 행 버젼 관리가 이뤄지므로 변경되기전의 버젼 데이터를 읽어오기 때문이다.

6. 커밋된 읽기 스냅샷(Read Committed Snapshot)

이 격리수준은 위의 스냅샷 격리수준(Snapshot Isolation)과 다른 개념의 격리 수준은 아니다.

다만 위에서 테스트 했던 행 버젼의 데이터를 읽을시 커밋된 데이터에 대해서는 최신 데이터 버젼으로

읽을수 있는 격리 수준이다. 
 

위의 테스트 내용을 그대로 다시 테스트 해보자.

이 격리수준 또한 데이터베이스 옵션의 변경이 필요하다.

ALTER DATABASE TestDB SET READ_COMMITTED_SNAPSHOT ON;

위의 테스트 쿼리를 다시 실행해보자.

--세션1
BEGIN TRAN
UPDATE TestTab SET Weight=50 WHERE Name='나나'
SELECT * FROM TestTab WHERE Name='나나'

--세션2
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN
SELECT * FROM TestTab WHERE Name='나나'

쿼리 실행 결과는 위의 스냅샷 격리수준(Snapshot Isolation)과 다를게 없이 세션1의 값은 변경된

상태로 데이터를 읽고 세션2는 변경되지 않은 값의 데이터를 읽게된다. 하지만 세션1을 커밋시킨후

세션2를 다시 읽어보면 결과값은 변경된 데이터가 나타나는걸 알 수 있다.

이것으로 SQL Server 2005의 트랜잭션 격리수준에 대해서 간단하게 설명과 테스트를 해보았다.

이 밖에도 트랜잭션 처리에 대해 제어할 수 있도록 하는 기능들이 있다.

대표적으로 락 타임아웃 설정(SET LOCK_TIMEOUT)으로 이것은 반복읽기나 직렬화등과 같은

격리수준에서 커밋되지 않는 데이터를 읽기 위해서 무한대기 상태에 빠지게 되는데 이를 방지하기

위해 커넥션별로 LOCK TIMEOUT을 정하여 이를 방지하는 기능이다.

잠금참고(Locking Hints) 또한 커넥션 레벨 단위의 격리수준이 아닌 쿼리 단위의 트랜잭션 격리수준

을 제어하는 기능인데 이것은 일반적인 트랜잭션 격리수준 보다 우선 적용되는 것으로써 많이

사용되는 방식이다.