데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

NOLOCK HINT 이해

jevida(강성욱) 2016.10.07 03:27 Views : 7811

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

 



강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1850 프로파일러를 이용한 중첩된 프로시저 디버깅 jevida(강성욱) 2016.10.07 1557
1849 SAN 스토리지 성능 모니터 - SAN 스토리지를 사용하는 경우 성능 카운터를 어떻게 모니터링 할까? jevida(강성욱) 2016.10.07 1731
1848 저장된 Plan Cache 확인 및 활용 jevida(강성욱) 2016.10.07 4572
1847 Xp_fixeddrives 세부 정보 확인하기 jevida(강성욱) 2016.10.07 1730
1846 강제 매개변수화로 인한 성능 저하 사례 jevida(강성욱) 2016.10.07 1559
1845 파라메터 스니핑과 데이터 스큐 jevida(강성욱) 2016.10.07 1528
1844 DBCC CHECKDB 버그 및 해결 방법 jevida(강성욱) 2016.10.07 1539
» NOLOCK HINT 이해 jevida(강성욱) 2016.10.07 7811
1842 인증으로부터 분리된 사용자 방지 jevida(강성욱) 2016.10.07 2179
1841 비관리자 계정으로 쿼리 계획 보기 jevida(강성욱) 2016.10.07 1242
1840 SSMS 폴링 간격 구성 jevida(강성욱) 2016.10.07 1771
1839 Deadlock 감지하여 알림하기 jevida(강성욱) 2016.10.07 1777
1838 Suspect_pages 테이블 이해 및 관리 jevida(강성욱) 2016.10.07 1371
1837 SSRS SocketException jevida(강성욱) 2016.10.07 1472
1836 파티션 분할 시 I/O 최소화 하기 jevida(강성욱) 2016.10.07 1858
1835 대량 BCP 작업 시 발생하는 오류 (665, 1450, 33) jevida(강성욱) 2016.10.07 1648
1834 기본 추적(default tace) 활성화 및 로그 확인 jevida(강성욱) 2016.10.07 1392
1833 SQL Server ALTER TABLE syntax diagrams jevida(강성욱) 2016.10.07 1161
1832 SQL Server Performance Counter Guidance jevida(강성욱) 2016.09.30 2507
1831 SQL Server CREATE TABLE syntax diagrams jevida(강성욱) 2016.09.30 1421





XE Login