1. 아무설정 안했을때
READ COMMITTED 스넵샷 설정여부 : False
스냅샷 격리 허용 : False
SELECT NAME
, SNAPSHOT_ISOLATION_STATE
, SNAPSHOT_ISOLATION_STATE_DESC AS DESCRIPTION
, IS_READ_COMMITTED_SNAPSHOT_ON
FROM SYS.DATABASES;
NAME SNAPSHOT_ISOLATION_STATE DESCRIPTION IS_READ_COMMITTED_SNAPSHOT_ON
----------------------------------- ------------------------------- ----------------------- -----------------------------
master 1 ON 0
tempdb 0 OFF 0
model 0 OFF 0
msdb 1 ON 0
neogeo.pe.kr 0 OFF 0
(5개 행이 영향을 받음)
USE [neogeo.pe.kr]
DBCC useroptions
Set Option Value
----------------------------------- ----------------------------------------------
textsize 2147483647
language 한국어
dateformat ymd
datefirst 7
lock_timeout -1
quoted_identifier SET
arithabort SET
ansi_null_dflt_on SET
ansi_warnings SET
ansi_padding SET
ansi_nulls SET
concat_null_yields_null SET
isolation level read committed
(13개 행이 영향을 받음)
CREATE TABLE TEST
(NO INTEGER NOT NULL
,AMT INTEGER);
ALTER TABLE TEST ADD CONSTRAINT TEST_PK PRIMARY KEY(NO);
INSERT INTO TEST VALUES (1, 1000);
INSERT INTO TEST VALUES (2, 1000);
INSERT INTO TEST VALUES (3, 1000);
INSERT INTO TEST VALUES (4, 1000);
INSERT INTO TEST VALUES (5, 1000);
INSERT INTO TEST VALUES (6, 1000);
INSERT INTO TEST VALUES (7, 1000);
INSERT INTO TEST VALUES (8, 1000);
INSERT INTO TEST VALUES (9, 1000);
INSERT INTO TEST VALUES (10, 1000);
SELECT * FROM TEST;
NO AMT
----------- -----------
1 1000
2 1000
3 1000
4 1000
5 1000
6 1000
7 1000
8 1000
9 1000
10 1000
(10개 행이 영향을 받음)
TX1 TX2
---------------------------------------------------- ----------------------------------------------------
-- 1.
BEGIN TRAN
UPDATE TEST SET AMT = AMT + 100 WHERE NO = 1;
-- 2. Blocking 된다.
SELECT * FROM TEST;
-- 3.
COMMIT TRAN;
-- 4.
NO AMT
----------- -----------
1 1100
2 1000
3 1000
4 1000
5 1000
6 1000
7 1000
8 1000
9 1000
10 1000
(10개 행이 영향을 받음)
-- 5. 데이터 원복
UPDATE TEST SET AMT = 1000 WHERE NO = 1;
-- 6.
BEGIN TRAN
UPDATE TEST SET AMT = AMT + 100 WHERE NO = 1;
-- 7. WITH (NOLOCK) Hint로 Blocking 없이 읽을 수 있으나, Dirty Read이다.
SELECT * FROM TEST WITH (NOLOCK);
NO AMT
----------- -----------
1 1100
2 1000
3 1000
4 1000
5 1000
6 1000
7 1000
8 1000
9 1000
10 1000
(10개 행이 영향을 받음)
-- 7. Transaction 끝나지 않았다.
SELECT @@TRANCOUNT
-----------
1
(1개 행 적용됨)
-- 8. Rollback을 하면
ROLLBACK TRAN;
-- 8. 다시조회하면 데이터가 원복되어 있다. 즉 일관성 없는 읽기가 된다.
SELECT * FROM TEST WITH (NOLOCK);
NO AMT
----------- -----------
1 1000
2 1000
3 1000
4 1000
5 1000
6 1000
7 1000
8 1000
9 1000
10 1000
(10개 행이 영향을 받음)- Oracle에서는 이런 현상없다. 왜? Consistent read라는 읽기모드로 undo tablespace에서 committed된 데이터를 읽기 때문이다.
- SQLSERVER는 current read로 항상 원본데이터를 읽기시도한다.
이런 문제를 해결하기 위해 SQLSERVER에서도 2005이후 버전부터 tempdb에서 데이터의 이전버전을 관리하는 option을 제공한다.
2. READ COMMITTED 스넵샷 설정여부 : False
스냅샷 격리 허용 : True
USE master
GO
ALTER DATABASE [neogeo.pe.kr] SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
SELECT NAME
, SNAPSHOT_ISOLATION_STATE
, SNAPSHOT_ISOLATION_STATE_DESC AS DESCRIPTION
, IS_READ_COMMITTED_SNAPSHOT_ON
FROM SYS.DATABASES;
NAME SNAPSHOT_ISOLATION_STATE DESCRIPTION IS_READ_COMMITTED_SNAPSHOT_ON
----------------------------------- ------------------------------- ----------------------- -----------------------------
master 1 ON 0
tempdb 0 OFF 0
model 0 OFF 0
msdb 1 ON 0
neogeo.pe.kr 1 ON 0
(5개 행이 영향을 받음)
TX1 TX2
---------------------------------------------------- ----------------------------------------------------
-- 1.
BEGIN TRAN
UPDATE TEST SET AMT = AMT + 100 WHERE NO = 1;
-- 2. 트랜젝션 격리레벨 변화없다.
DBCC USEROPTIONS;
Set Option Value
----------------------------------- ----------------------------------------------
textsize 2147483647
language 한국어
dateformat ymd
datefirst 7
lock_timeout -1
quoted_identifier SET
arithabort SET
ansi_null_dflt_on SET
ansi_warnings SET
ansi_padding SET
ansi_nulls SET
concat_null_yields_null SET
isolation level read committed
(13개 행이 영향을 받음)
-- 3. 여전히 Blocking 된다.
SELECT * FROM TEST;
-- 4.
ROLLBACK TRAN;
-- 5. Lock이 풀리고 나서야 읽혀진다.
NO AMT
----------- -----------
1 1000
2 1000
3 1000
4 1000
5 1000
6 1000
7 1000
8 1000
9 1000
10 1000
(10개 행이 영향을 받음)
-- 6. 다시 Transaction 시작
BEGIN TRAN
UPDATE TEST SET AMT = AMT + 100 WHERE NO = 1;
-- 7. 이번에는 격리레벨을 snapshot으로변경해보자.
-- ALTER DATABASE로 "스냅샷 격리 허용"이 True로 먼저 설정되어 있어야 한다.
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
DBCC USEROPTIONS;
Set Option Value
----------------------------------- ----------------------------------------------
textsize 2147483647
language 한국어
dateformat ymd
datefirst 7
lock_timeout -1
quoted_identifier SET
arithabort SET
ansi_null_dflt_on SET
ansi_warnings SET
ansi_padding SET
ansi_nulls SET
concat_null_yields_null SET
isolation level snapshot
(13개 행이 영향을 받음)
-- 8. Blocking 안되고 Dirty read 하지 않는다.
SELECT * FROM TEST;
NO AMT
----------- -----------
1 1000
2 1000
3 1000
4 1000
5 1000
6 1000
7 1000
8 1000
9 1000
10 1000
(10개 행이 영향을 받음)
-- 9. Transaction 끝나지 않았다.
SELECT @@TRANCOUNT
-----------
1
(1개 행 적용됨)
-- 10.
COMMIT TRAN;
-- 11. COMMIT된 정보 읽는다.
SELECT * FROM TEST;
NO AMT
----------- -----------
1 1100
2 1000
3 1000
4 1000
5 1000
6 1000
7 1000
8 1000
9 1000
10 1000
(10개 행이 영향을 받음)
-- 12. 격리수준 원복
ALTER DATABASE [neogeo.pe.kr] SET ALLOW_SNAPSHOT_ISOLATION OFF;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
DBCC USEROPTIONS;
Set Option Value
----------------------------------- ----------------------------------------------
textsize 2147483647
language 한국어
dateformat ymd
datefirst 7
lock_timeout -1
quoted_identifier SET
arithabort SET
ansi_null_dflt_on SET
ansi_warnings SET
ansi_padding SET
ansi_nulls SET
concat_null_yields_null SET
isolation level read committed
(13개 행이 영향을 받음)이 옵션은 DB 레벨에서 '스냅샷 격리 허용'을 True로 해도 세션레벨에서 SET TRANSACTION ISOLATION LEVEL SNAPSHOT 명령으로 스냅샷읽기를 지정해야 Blocking없이 읽기가 가능해진다.
이것을 해당 DB에 접속하는 모든 세션에 적용되게하는 방법이 아래의 방법이다.
3. READ COMMITTED 스넵샷 설정여부 : True
스냅샷 격리 허용 : False
-- 먼저 ALTER하는 데이터베이스에 접속된 세션이 없어야 된다.
USE master
GO
ALTER DATABASE [neogeo.pe.kr] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT
GO
SELECT NAME
, SNAPSHOT_ISOLATION_STATE
, SNAPSHOT_ISOLATION_STATE_DESC AS DESCRIPTION
, IS_READ_COMMITTED_SNAPSHOT_ON
FROM SYS.DATABASES;
NAME SNAPSHOT_ISOLATION_STATE DESCRIPTION IS_READ_COMMITTED_SNAPSHOT_ON
----------------------------------- ------------------------------- ----------------------- -----------------------------
master 1 ON 0
tempdb 0 OFF 0
model 0 OFF 0
msdb 1 ON 0
neogeo.pe.kr 0 OFF 1
(5개 행이 영향을 받음)
TX1 TX2
---------------------------------------------------- ----------------------------------------------------
-- 1. READ COMMITTED 스냅샷 설정 여부 확인
DBCC USEROPTIONS;
Set Option Value
----------------------------------- ----------------------------------------------
textsize 2147483647
language 한국어
dateformat ymd
datefirst 7
lock_timeout -1
quoted_identifier SET
arithabort SET
ansi_null_dflt_on SET
ansi_warnings SET
ansi_padding SET
ansi_nulls SET
concat_null_yields_null SET
isolation level read committed snapshot
(13개 행이 영향을 받음)
-- 2.
BEGIN TRAN
UPDATE TEST SET AMT = AMT + 100 WHERE NO = 1;
-- 3. Blocking 안되고 Dirty read 하지 않는다.
SELECT * FROM TEST;
NO AMT
----------- -----------
1 1000
2 1000
3 1000
4 1000
5 1000
6 1000
7 1000
8 1000
9 1000
10 1000
(10개 행이 영향을 받음)
-- 4. Transaction 끝나지 않았다.
SELECT @@TRANCOUNT
-----------
1
(1개 행 적용됨)
-- 5.
COMMIT TRAN;
-- 6. COMMIT된 정보 읽는다.
SELECT * FROM TEST;
NO AMT
----------- -----------
1 1100
2 1000
3 1000
4 1000
5 1000
6 1000
7 1000
8 1000
9 1000
10 1000
(10개 행이 영향을 받음)
* SQLSERVER에서는 원래 한 Transaction에서 Transaction이 종료되지 않은상태에서 동일 Table을 다른 Transaction에서 Select할때 Blocking이 걸린다.
But
'READ COMMITTED 스넵샷 설정여부'를 True로 하면 DB레벨에서,
'스냅샷 격리 허용'을 True로 하면 세션레벨에서,
Oracle처럼 같은 Table을 다른 세션에서 Transaction이 종료되지 않은 상태에서도 해당Table을 접근해도 Blocking이나 Dirty read없이 Select 할 수 있다.
Oracle은 select 할때 다른transcation에서 lock을 걸어도 select가 가능하다. 그 이유는 Oracle의 특징인 consistent read때문인데(Tibero도 지원된다 확인했다),
다른 DB는 current read만을 지원하기 때문이다. Oracle은 current read도 된다.
current read는 row를 하나하나읽어내려가는 그 순간의 데이터를 읽지만
consistent read는 row를 읽어가면서 읽고자 하는 row가 lock이 걸려 있다면 undo tablespace에 저장된 최종 commit된 데이터를 읽어온다.
즉 데이터의 이력을 관리하고 있는 것이다. 그에 반해 다른 DB는 항상 원본 데이터만 관리한다.
그래서 Oracle은 select할때 다른 transaction의 lock으로 인한 blocking 대기가 없다.
참고서적
오라클 성능 고도화 원리와 해법
관련Post : http://blog.daonelab.com/post/26/344/