[SqlServer] SET TRANSACTION ISOLATION LEVEL

SET TRANSACTION ISOLATION LEVEL 에 대해서

 

한 연결에서 실행한 SELECT 문에 대한 기본 트랜잭션 잠금 동작을 제어함.

 

설정 구문

SET TRANSACTION ISOLATION LEVEL […]

è READ COMMITTED

è READ UNCOMMITTED

è REPEATABLE READ

è SERIALIZABLE

è SNAPSHOT

1)       READ COMMITED
DATA를 SELECT할 때 COMMIT않은 DATA를 SELECT할 수 없도록 지정됨.

실제 SHARE LOCK의 지속시간은 길지 않기 때문에 LOCK이 걸려있는지 사용자가 확인은 힘듦

2)       READ UNCOMMITED
DATA를 SELECT할 때 COMMIT 되지 않은 DATA도 SELECT할 수 있도록 지정

3)       REPEATABLE READ
DATA를 SELECT하고 있는 동안 해당되는 ROW에 대해 UPDATE할 수 없도록 지정
INSERT는 가능

4)       SERIALIZABLE
DATA를 SELECT하고 있는 동안 해당되는 ROW에 대해 UPDATE 및 INSERT할 수 없도록 지정

5)       SNAPSHOT
SERIALIZABLE과 기능이 동일하지만 다른 사용자가 수정이 가능함.
SNAPSHOT옵션은 Database의 ALLOW_SNAPSHOT_ISOLATION 옵션을 ON으로 정의해야 사용 가능

<<LOCK과 관련된 실습은 아래와 같이 진행>>

* 쿼리분석기를 두개 실행하여 테스트.

* 일단 TABLE를 하나 생성하고 1개의 값을 저장

CREATE TABLE LOCK_TEST (UNAME VARCHAR(10));

INSERT INTO LOCK_TEST (UNAME) VALUES (‘TEST1’);

* 기본적인 LOCK 테스트

순서	쿼리분석기(1)	쿼리분석기(2)
1	BEGIN TRAN
2	INSERT INTO LOCK_TEST (UNAME) VALUES (‘TEST2’)
3		SELECT * FROM LOCK_TEST
4	COMMIT TRAN
5	BEGIN TRAN
6	INSERT INTO LOCK_TEST (UNAME) VALUES (‘TEST3’)
7		SELECT * FROM LOCK_TEST (NOLOCK)
8	COMMIT TRAN

3번 쿼리를 실행한 경우 4번의 COMMIT가 되기 전까지 SELECT 결과값이 나오지 않음.

하지만 (NOLOCK)옵션이 SELECT문장에 있는 경우 7번 쿼리에서 바로 결과값이 나옴.
(NOLOCK 옵션은 트랜잭션이 COMMIT가 되지 않더라도 메모리의 값을 SELECT하도록 합니다)

 

* READ COMMITED 테스트 (기본값)

순서	쿼리분석기(1)	쿼리분석기(2)
1	SET TRANSACTION ISOLATION LEVEL READ COMMITED
2	BEGIN TRAN
3	INSERT INTO LOCK_TEST (UNAME) VALUES (‘TEST2’)
4		SET TRANSACTION ISOLATION LEVEL READ COMMITED
5		SELECT * FROM LOCK_TEST
6	COMMIT TRAN

3번의 COMMIT되지 않은 INSERT문으로 인해(UPDATE도 동일) 5번의 SELECT 문은 6번의COMMIT이 실행될 때까지 대기함.

 

* READ UNCOMMITED 테스트

순서	쿼리분석기(1)	쿼리분석기(2)
1	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED
2	BEGIN TRAN
3	INSERT INTO LOCK_TEST (UNAME) VALUES (‘TEST2’)
4		SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED
5		SELECT * FROM LOCK_TEST
6	COMMIT TRAN

INSERT문이 아직 COMMIT되지 않았지만 5번의 SELECT 문장 실행됨.

 

* REPEATABLE READ 테스트

순서	쿼리분석기(1)	쿼리분석기(2)
1	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
2	BEGIN TRAN
3	SELECT * FROM LOCK_TEST
4		UPDATE LOCK_TEST SET ‘TEST0’ WHERE UNAME=’TEST1’
5	COMMIT TRAN
6	BEGIN TRAN
7	SELECT * FROM LOCK_TEST
8		INSERT INTO LOCK_TEST (UNAME) VALUES (‘TEST9’)
9	COMMIT TRAN

3번의 SELECT에 의해 검색된 ROW들은 ROW LOCK이 걸리게 되어 UPDATE는 COMMIT 되기까지 대기함.

하지만 8번의  INSERT는 상관없이 실행됨.

 

* SERIALIZABLE테스트

순서	쿼리분석기(1)	쿼리분석기(2)
1	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
2	BEGIN TRAN
3	SELECT * FROM LOCK_TEST
4		UPDATE LOCK_TEST SET ‘TEST0’ WHERE UNAME=’TEST1’
5	COMMIT TRAN
6	BEGIN TRAN
7	SELECT * FROM LOCK_TEST
8		INSERT INTO LOCK_TEST (UNAME) VALUES (‘TEST9’)
9	COMMIT TRAN

3번의 SELECT에 의해 SHARE LOCK이 걸려 INSERT 및 UPDATE가 5,9번의 COMMIT을 실행하기 전에는 wait함.

출처 : http://blog.daum.net/itamas/22