-
SQL Server 트랜잭션 격리 수준SQL Server/Transaction 2011. 4. 21. 18:35
디자인은 라이고님 디자인을 가져다 썻습니다 ^^; 감사합니다!
SQL Server 는 총 6가지 격리 수준을 제공하고 있습니다.
하지만 오늘은 1~4번에 해당하는 격리 수준에 대해서 알아보려고 합니다.
[트랜잭션 격리 수준]
1. READ UNCOMMITTED
2. READ COMMITTED
3. REPEATABLE READ
4. SERIALIZABLE
SQL Server 2005부터 추가된 두가지
5. READ COMMITTED WITH SNAPSHOT6. SNAPSHOT
[선행 필요 Lock에 대한 지식]
1. S Lock: 공유 잠금(Shared Lock)
2. X Lock: 단독 잠금(Exclusive Lock)
3. Row, Page, Extent에 대한 기본 개념 및 잠금 범위
4. 암시적과 명시적 트랜잭션에 대한 이해
S Lock은.. 데이터를 읽어올때 주로 걸리는 Lock 입니다.
X Lock은.. 데이터를 입력/수정/삭제 할 때 걸리는 Lock 입니다.
X Lock은 혼자만 잠글 수 있기 떄문에.. 다른 어떤 Lock이랑도 호환되지 않습니다. 하지만.. S Lock은... 다른 S Lock과 호환이 되기 때문에.. 서로 공유할 수 있습니다.
이 외에도 업데이트락, 인텐트 락등이 있지만.. 이번에 설명드릴 부분외라 따로 설명드리진 않겠습니다!
[테스트를 위해 임시 테이블 생성]
IF OBJECT_ID('tblOnlyHeap') IS NOT NULL
DROP TABLE tblOnlyHeap
GO
IF OBJECT_ID('tblClusteredIndex') IS NOT NULL
DROP TABLE tblClusteredIndex
GO
CREATE TABLE tblOnlyHeap(IDX INT NOT NULL, DATA VARCHAR(2) NULL) --//인덱스가 없는 테이블
GO
CREATE TABLE tblClusteredIndex(IDX INT NOT NULL, DATA VARCHAR(2) NULL) --//인덱스가 있는 테이블
GO
INSERT INTO tblOnlyHeap SELECT 1, '가'
INSERT INTO tblOnlyHeap SELECT 2, '나'
INSERT INTO tblOnlyHeap SELECT 3, '다'
INSERT INTO tblOnlyHeap SELECT 4, '라'
INSERT INTO tblOnlyHeap SELECT 5, '마'
INSERT INTO tblOnlyHeap SELECT 6, '바'
INSERT INTO tblOnlyHeap SELECT 7, '사'
INSERT INTO tblClusteredIndex SELECT 1, '가'
INSERT INTO tblClusteredIndex SELECT 2, '나'
INSERT INTO tblClusteredIndex SELECT 3, '다'
INSERT INTO tblClusteredIndex SELECT 4, '라'
INSERT INTO tblClusteredIndex SELECT 5, '마'
INSERT INTO tblClusteredIndex SELECT 6, '바'
INSERT INTO tblClusteredIndex SELECT 7, '사'
GO
--//tblClusteredIndex 에는 IDX Column에 PRIMARY KEY 생성
ALTER TABLE tblClusteredIndex
ADD CONSTRAINT tblClusteredIndex_PK PRIMARY KEY(IDX)
GO
1. READ UNCOMMITED
READ UNCOMMTED 를 해석을 하게되면 => COMMIT 되지 않은 데이터를 읽을 수 있다.
이 말은.. 다른 세션에서 트랜잭션을 걸어서 데이터를 INSERT 나 UPDATE 할 경우... COMMIT 이 되지 않았는데도 불구하고, 데이터를 읽을 수 있다는 말입니다.
마치 남자친구의 맘속을 훤희 모두 들여다보는..변심도 미리 알아채는 여친님의 마음이라 볼 수 있죠~!
예제를 통해 살펴보도록 하겠습니다.
#1번 세션
BEGIN TRAN
UPDATE tblClusteredIndex
SET DATA = '하'
#2번 세션
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM tblClusteredIndex
GO
--결과
IDX DATA
1 하
2 하
3 하
4 하
5 하
6 하
7 하
데이터를 SELECT 할때 S Lock으로 잠근다고 미리 언급을 했습니다.
하지만.. READ UNCOMMITTED 에서는 S Lock 조차 걸지 않기 때문에..
다른 세션에서 커밋되지 않은 수정하는 (X Lock 데이터) 데이터를 볼 수 있습니다.
바로 변심한 남친의 마음을 즉각 알아채는 여친의 마음!
이걸 Dirty Read 라고 합니다.
2. READ COMMITTED
READ COMMITTED 를 해석을 하게되면 => COMMIT 된 데이터만 읽을 수 있다.
READ UNCOMMITTED와 다른 점은 실제 SELECT 할때 S Lock을 소유 합니다. 하지만!!! SELECT 가 끝나는 동시에 S Lock을 해제 합니다.
이말은..... 트랜잭션 내에서 SELECT가 끝나더라도(커밋되기전) 다른 세션에서 INSERT나 UPDATE가 가능하다는 말이 됩니다.
남친의 변한 마음만 확인할 수 있는 여친의 마음... 훗~~ 속마음까진 확인할 방법이.........없다!
예제를 통해 살펴보도록 하겠습니다.
1번 예제
#1번 세션
IF(@@TRANCOUNT > 0) ROLLBACK
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
SELECT * FROM tblClusteredIndex
GO
--결과
IDX DATA
1 가
2 나
3 다
4 라
5 마
6 바
7 사
--트랜잭션이 커밋되지 않았지만... SELECT로 이미 결과를 반환했습니다. (S Lock에 해제 된 상태)
#2번 세션
IF(@@TRANCOUNT > 0) ROLLBACK
GO
BEGIN TRAN
UPDATE tblClusteredIndex
SET DATA = '가'
WHERE IDX = 7
COMMIT --//수정한 데이터를 COMMIT(1번 세션 S Lock이 해제 되었기 때문에 UPDATE 가 가능)
#다시 1번 세션
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT * FROM tblClusteredIndex
GO
--결과
IDX DATA
1 가
2 나
3 다
4 라
5 마
6 바
7 가
--//2번 세션에서 이미 커밋을 했기 때문에 변경된 내용을 불러옵니다.
만약 SELECT 하더라도 COMMIT 되기 전까지 S Lock을 소유 한다면...
다른 세션에선 업데이트가 불가능 하겠죠..
(사실 이게 REPEATABLE READ 입니다. 뒤에서 설명드리겠습니다.)
SELECT가 아닌 업데이트는 어떻게 다른지 보도록 하겠습니다.
2번 예제
#1번 세션
IF(@@TRANCOUNT > 0) ROLLBACK
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
UPDATE tblClusteredIndex
SET DATA = '가'
WHERE IDX = 7
GO
#2번 세션
IF(@@TRANCOUNT > 0) ROLLBACK
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT * FROM tblClusteredIndex WHERE IDX = 7
--대기 상태...............
READ COMMITTED의 해석 기억하십니까? 커밋된 데이터만 읽기가 가능하다는 얘기죠!
1번 세션에서 아직 COMMIT을 하지 않았기 때문에.. 2번 세션에선 IDX = 7을 읽지 못하고 1번 세션의 COMMIT을 기다리게 됩니다.
그렇다면 2번 세션에서 아래와 같은 쿼리를 날리면 어떻게 될까요?
#2번 세션IF(@@TRANCOUNT > 0) ROLLBACK
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT * FROM tblClusteredIndex WHERE IDX = 1
--결과
IDX DATA
1 가
1번 세션에서 수정으로 인해 IDX = 7 인 row를 X Lock을 걸은 상태기 때문에.. 다른 컬럼들은 가지고 나오는데 문제는 없습니다.
그렇다면 .. 아래도 충분히 가능하겠죠~!
#2번 세션
IF(@@TRANCOUNT > 0) ROLLBACK
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
UPDATE tblClusteredIndex
SET DATA = '가'
WHERE IDX = 1
GO
ROLLBACK
다른 ROW에 대해서 X 락을 걸었으니까요~ 이 증거가 SQL Server가 ROW Lock이 가능한 이유입니다.
3. REPEATABLE READ
REPEATABLE READ 를 해석을 하게되면 => 반복 읽기
한 트랜잭션 안에서 내가 읽었던 녀석은 절대 변하면 안되~~!
마치.. 간절한 여자친구처럼 오빠 마음 절대 변하면 안되!! 이렇게 Lock을 걸어두는 것이죠...
만날때부터 헤어질때까지~~~ㅎㅎ
이미 위에서 한번 설명을 드렸죠!! 트랜잭션내에서 읽은 녀석은.. 다른 세션에서 변경조차 안됩니다.
물론 S Lock이기 때문에.. 읽을 순 있겠죠 ^^;
1번 예제
#1번 세션
IF(@@TRANCOUNT > 0) ROLLBACK
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM tblClusteredIndex
GO
--결과
IDX DATA
1 가
2 나
3 다
4 라
5 마
6 바
7 사
--트랜잭션이 커밋되지 않았지만... S Lock은 계속 유지 됩니다.
#2번 세션
IF(@@TRANCOUNT > 0) ROLLBACK
GO
BEGIN TRAN
UPDATE tblClusteredIndex
SET DATA = '가'
WHERE IDX = 7
---------- 대기 상태
이런 경우 1번세션에서 이미 S Lock을 걸고 있기 때문에..수정할 수가 없게 됩니다.
하지만 읽을 순 있겠죠~
#2번 세션 을 중지하고..
IF(@@TRANCOUNT > 0) ROLLBACK
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM tblClusteredIndex
GO
--결과
IDX DATA
1 가
2 나
3 다
4 라
5 마
6 바
7 사
--읽는건 정상적으로 됩니다. 당근 S Lock은 S Lock 끼리 공유할 수 있으니깐요 ^^
2번 예제를 위해서 데이터를 1,3,5만 남기고 지워봅시다.
2번 세션에 ROLLBACK을 하지 않았기 때문에.. 우선 ROLLBACK을 시킵니다.
#2번 세션(혹시나 모를 롤백 시키기)
IF(@@TRANCOUNT > 0) ROLLBACK
GO
#1번 세션
IF(@@TRANCOUNT > 0) ROLLBACK
GO
DELETE FROM tblClusteredIndex WHERE IDX NOT IN (1,3,5)
GO
2번 예제
REPEATABLE READ는 처음 읽은놈은 끝까지 유지한다고 했습니다.
그럼 중간에 낑겨 드는 놈들은 어떻게 될까요?
#1번 세션
IF(@@TRANCOUNT > 0) ROLLBACK
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM tblClusteredIndex
--결과
IDX DATA
1 가
3 다
5 마
#2번 세션
INSERT INTO tblClusteredIndex SELECT 2,'나'
--오~~ 입력이 됩니다.
#1번 세션(커밋되기전..)
SELECT * FROM tblClusteredIndex
--결과
IDX DATA
1 가
2 나
3 다
5 마
REPEATABLE READ는 처음 읽었을 당시에 존재하는 데이터에 대해서만 Lock을 걸게 됩니다.
따라서 2번째 세션에서 입력된 IDX = 2의 값은.. 첫번째 세션에서 SELECT 할 당시엔 없었기 때문에.. LOCK이 걸리지 않았으므로.. INSERT가 되는것이죠
오빠 마음이 변하진 않지만.... 다른 미모의 여인이 나타나면.. 여친몰래 마음속에 INSERT 되듯이 말이죠!!
물론 여친은 알아채지 못하겠죠 ㅋㅋㅋ..
이 외도의 주인공!! 바로 팬텀이라고 부릅니다. ^^
그렇다면 미모의 여인도 접근 못하게 확실히 틀어 막는 방법은 없을까요???...
4. SERIALIZABLE
SERIALIZABLE => 직렬화......
마치.. 일거수 일투족을 감시하는 여자친구처럼 옆에 찰싹 붙어서 다른 어떤 여자도 들어오지 못하게 막는게 바로!
SERIALIZABLE 입니다.
예제를 바로 보시죠!
1번 예제
1번 예제를 위해서 데이터를 1,3,5만 남기고 다시 지워봅시다. 그리고 7을 넣습니다.
#1번 세션
IF(@@TRANCOUNT > 0) ROLLBACK
GO
DELETE FROM tblClusteredIndex WHERE IDX NOT IN (1,3,5)
GO
INSERT INTO tblClusteredIndex SELECT 7,'사'
GO
#1번 세션
IF(@@TRANCOUNT > 0) ROLLBACK
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT * FROM tblClusteredIndex WHERE IDX BETWEEN 3 AND 5
GO
--결과
IDX DATA
3 다
5 마
-- RangeS-S Lock이라는 녀석으로 범위로 S Lock을 걸어버립니다.
이때 4가 들어가려 한다면??? REPATABLE READ와 다르게 분명 막힐 것입니다.
#2번 세션
IF(@@TRANCOUNT > 0) ROLLBACK
GO
INSERT INTO tblClusteredIndex SELECT 4, '나'
---------- 대기 상태
하지만 여기서 3~5 범위에 상관없는 2가 들어가려고 하면 어떻게 될까요??
#2번 세션
IF(@@TRANCOUNT > 0) ROLLBACK
GO
INSERT INTO tblClusteredIndex SELECT 2, '나'
---------- 대기 상태
똑같이 대기 상태입니다.
SERIALIZABLE은.. 3 AND 5 로 SELECT로 잠궜다고 하더라도... 3~5 까지 잠그는 것이 아닙니다.
3 이전행인 1 부터... 5 다음행인 7까지 잠그게 됩니다.
결국... 1 <= IDX <=7인 값은 다른 세션에서 INSERT가 되지 않습니다.
#2번 세션
IF(@@TRANCOUNT > 0) ROLLBACK
GO
INSERT INTO tblClusteredIndex SELECT 100, '나'
--성공!
만약... 1 AND 3으로 잠궜다면??? 테이블에 1보다 작은 값은 존재하지 않고, 3보다 큰 값은 5기 때문에..
IDX >= 6인 값만 INSERT 가 가능하게 됩니다. 물론 0보다 작은 값은 절대 들어갈 수 없습니다.
물론 다른 세션에서 말이죠!
SERIALIZABLE에서 테이블에 인덱스가 없으면 어떻게 될까요?
#1번 세션
IF(@@TRANCOUNT > 0) ROLLBACK
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT * FROM tblOnlyHeap WHERE IDX BETWEEN 3 AND 5
GO
--결과
IDX DATA
3 다
5 마
#2번 세션
IF(@@TRANCOUNT > 0) ROLLBACK
GO
INSERT INTO tblClusteredIndex SELECT 0, '나'
---------- 대기 상태
INSERT INTO tblClusteredIndex SELECT 100000, '나'
---------- 대기 상태
테이블에 인덱스가 없기 때문에.. 범위를 알 수 없습니다. 그래서 모두 잠그게 됩니다.
혹시 잘못된 부분있으면 지적해주세요! 수정하겠습니다 ^^
감사합니다.. ^^