NOLOCK HINT 이해

NOLOCK HINT 이해

 

• Version : SQL Server 2005, 2008, 2008R2, 2012

 

SQL Server에서 데이터를 조회할 때 NOLOCK 힌트를 사용한 적이 있는가? 또한 NOLOCK 힌트는 어떻게 동작 할까?

 

하나의 스테이트먼트가 아닌 세션에 대해서 NOLOCK의 옵션을 사용하고 싶다면 READUNCOMMITTED를 사용할 수 도 있다.

 

NOLOCK의 작동법을 확인하기 위해 몇 가지 예제를 살펴보자. 이번 실습은 AdventureWorks 데이터베이스의 Person.Contact 테이블을 사용 하였다.

 

다음 스크립트는 ContacID가 20보다 작은 값을 모두 반환한다. Suffix 컬럼의 값이 NULL을 포함한 다양한 값이 들어 있는 것을 확인 할 수 있다.

SELECT * FROM Person.Contact WHERE ContactID < 20

 

 

다음의 경우를 살펴 보자. 2개의 세션이 있을 때 세션1에서의 쿼리는 업데이트를 하지만 의도적으로 커밋을 하지 않아 완료 되지 않은 상태로 만들며 세션2에서는 데이터를 조회한다.

세션1	세션2
BEGIN TRAN UPDATE Person.Contact SET Suffix = 'B' WHERE ContactID < 20
	SELECT * FROM Person.Contact WHERE ContactID < 20

 

세션1에서의 데이터가 커밋되지 않았기 때문에 세션2에서는 데이터 조회를 할 수가 없다.

 

다음 스크립트를 다른 세션에서 실행해 보면 쿼리 1의 작업이 커밋 또는 롤백의 명령으로 작업이 완료되기 전까지 SELECT 문이 차단된 것을 확인 할 수 있다.

sp_who2

 

 

세션1에서 롤백을 하였을 경우 잠금이 해제되어 세션2에서 조회작업이 정상적으로 진행됨을 확인 할 수 있다.

세션1	세션2
rollback tran

 

 

 

이번에는 세션1의 쿼리는 동일하며 세션2에서 NOLOCK 힌트를 사용하여 조회하여 보자.

세션1	세션2
BEGIN TRAN UPDATE Person.Contact SET Suffix = 'B' WHERE ContactID < 20
	SELECT * FROM Person.Contact WITH (NOLOCK) WHERE ContactID < 20

 

세션1의 쿼리가 커밋 또는 롤백으로 완료되지 않아도 세션2에서 조회가 되는 것을 확인 할 수 있다.

 

쿼리결과를 보면 세션1에서 아직 커밋이 발생하지 않았는데에도 불구하고 세션2의 조회 값은 Suffix 값이 모두 'B'로 변경되어 조회된 것을 확인 할 수 있다. 즉 NOLOCK 힌트를 사용하면 잠금을 무시하고 데이터를 반환한다. UPDATE가 롤백이 되는경우 이전 값으로 돌아가기 때문에 세션1의 작업에 따라 결과가 달라지기 때문에 세션2의 값은 Dirty Read로 간주 된다. 결국 NOLOCK 힌트를 사용하면 정확하지 않은 값을 사용할 수 도 있다.

 

다음은 sp_lock 구문을 통하여 NOLOCK 힌트 없이 사용하였을 때 수행되는 잠금을 확인 할 수 있다.

세션1	세션2	세션3
BEGIN TRAN UPDATE Person.Contact SET Suffix = 'B' WHERE ContactID < 20
	SELECT * FROM Person.Contact WHERE ContactID < 20
		sp_lock

 

 

 

NOLOCK 힌트 사용시 잠금 상태를 확인하여 보자.

세션1	세션2	세션3
BEGIN TRAN UPDATE Person.Contact SET Suffix = 'B' WHERE ContactID < 20
	SELECT * FROM Person.Contact WITH (NOLOCK) WHERE ContactID < 20
		sp_lock

 

 

NOLOCK를 사용하지 않았을 때에는 읽고 있는 페이지에 대해 IS잠금을 사용한다. 또한 테이블에 대해서도 IS 잠금을 사용한다.

 

이처럼 쿼리의 순서 및 기능에 따라 잠금에 대한 수준이 달라 지므로 잠금에 관한 내용을 이해하고 데이터를 조회할 때 비즈니스에 따라 쿼리의 옵션을 사용한다면 잠금에 대한 교착 문제를 피할 수 있을 것이라 생각한다.

 

 

[잠금 유형]

• MD – 메타데이터 잠금
• DB – 데이터베이스 잠금
• TAB – 테이블 잠금
• PAG – 페이지 잠금

 

[잠금 모드]

잠금모드	설명
S(공유)	SELECT처럼 읽기 작업에 사용
U(업데이트)	업데이트 할 수 있는 리소스에 사용. 업데이트시 발생하는 교착 방지
X(베타)	INSERT, UPDATE, DELETE 와 같은 데이터 수정작업에 사용. 여러 개의 작업이 같은 리소스에 대해 동시에 이루어 지지 못하게 한다.
I(의도)	잠금 계층 구조를 만드는데 사용. 의도 잠금 종류에는 내재된 공유(IS), 의도 배타(IX), 읜도 배타 공유(SIX)가 있다.
SCH(스키마)	테이블의 스키마에 종속되는 작업이 실행 될 때 사용. 스키마 잠금에는 스키마 수정(Sch-M)과 스키마 안정성(Sch-S)잠금이 있다.
BU(대량 업데이트)	데이터를 테이블로 대량 복사하는 경우와 TABLOCK 힌트가 지정된 경우 사용
Key-range(키 범위)	직렬과 가능 트랜잭션 격리 수준을 사용할 때 쿼리가 읽는 행 범위를 보호. 쿼리가 실행되는 동안 다른 트랜잭션이 삽입할 수 없도록 한다.

 

 

 

 

 

[참고자료]

http://www.mssqltips.com/sqlservertip/2470/understanding-the-sql-server-nolock-hint/

http://technet.microsoft.com/ko-kr/library/ms175519(v=sql.105).aspx

 

 

2013-09-03 / 강성욱 / http://sqlmvp.kr