2007~2011/SQL Server2009.11.17 01:03

Commit 되지 않은 Dirty page 를 읽을 수 있는 READ UNCOMMITTED 격리 수준과 commit 된 데이터만 읽을 수 있는 READ COMMITTED 차이를 확인할 수 있습니다.

/* ---------- 세션 1  ---------- */
USE LAIGO
GO

SELECT * FROM TblUser
/*
name       age
---------- -----------
라이고        20
*/

BEGIN TRAN
 UPDATE TblUser SET age = 23


/* ---------- 세션 2  ---------- */

SELECT * FROM TblUser
-- LCK_M_X 0x05 Exclusive 잠금 상태

SELECT * FROM TblUser WITH (NOLOCK)
/*
name       age
---------- -----------
라이고        23
*/
-- WITH (NOLOCK) 을 사용하여 READ UNCOMMITTED 허용, Dirty page 읽기 가능


위의 상황에서 세션 1의 트랜잭션을 rollback 한다면 세션2에서는 결과적으로 잘못된 데이터를 조회하게 된 것이며 일관성(Consistency)이 떨어지는 문제가 나타나게 됩니다. 반면에 동시성은 좋아지기 때문에 wait 이 발생하지 않기에 목적에 따라 blocking 을 방지하기 위해 사용할 수도 있습니다.

기본 Transaction Isolation level 은 READ COMMITTED 이며 쿼리를 사용하여 현재 세션의 격리 수준을 변경할 수 있습니다. 아래 예제는 현재 설정된 격리 수준을 확인하고 READ UNCOMMITTED 로 변경하는 방법입니다.

DBCC USEROPTIONS

/*
Set Option                                                  Value
-----------------------------------------------------------
textsize                                                      2147483647
language                                                   us_english
dateformat                                                  mdy
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 row(s) affected)
*/

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
/* WITH (NOLOCK)을 사용하지 않아도 commit 되지 않은 값을 액세스 할 수 있습니다.



현재 세션 뿐만 아니라 SSMS 에서 수행되는 모든 세션에서 변경된 ISOLATION LEVEL 을 적용하기 위해서는 아래와 같이 옵션을 변경해서 사용할 수 있습니다.

Tools - Options - Query Excution - SQL Server - Advanced - SET TRANSACTION ISOLATION LEVEL





[참고자료]
SET TRANSACTION ISOLATION LEVEL(Transact-SQL)
http://msdn.microsoft.com/ko-kr/library/ms173763.aspx

Description of the waittype and lastwaittype columns in the master.dbo.sysprocesses table in SQL Server 2000 and SQL Server 2005
http://support.microsoft.com/kb/822101/en-us


작성자 : Lai Go / 작성일자 : 2009.11.17

Posted by 사용자 Lai Go

댓글을 달아 주세요