[MSSQL] WITH(NOLOCK)

 

 

실무에서 WITH(NOLOCK) 에 대해서 잘 모르고 사용을 하는 경우들을 종종 본다. WITH(NOLOCK) 은 현재 내가 조회하는 쿼리와 상황에 따라 선택적으로 사용을 해야한다. 과거 로그 데이터를 확인하는데 WITH(NOLOCK) 을 붙이지 않는다거나 금액 집계 등 현재 변경되는 데이터에 따라 정확하게 조회해야하는데 WITH(NOLOCK) 을 붙이는 등 정확하게 알지 못하고 붙이는 경우들이 있다. 오늘은 WITH(NOLOCK)이 어떤 것인지에 대해 알아보고 사용해야 하는 상황과 사용하지 않아야 하는 상황에 대해 정리하려고 한다.

CREATE DADA

CREATE TABLE #temp_order
(
    order_id    INT,
    product_nm  VARCHAR(20),
    order_qty   INT,
    status      VARCHAR(10)
)

INSERT INTO #temp_order VALUES (1, '사과', 10, '완료')
INSERT INTO #temp_order VALUES (2, '배',   5,  '완료')
INSERT INTO #temp_order VALUES (3, '메론', 3,  '처리중')

SELECT * FROM #temp_order

 

#temp_order 결과 조회

 

WITH(NOLOCK)

• 테이블 조회 시 잠금(Lock) 을 걸지 않고 조회하겠다는 힌트
• 다른 트랜잭션이 데이터를 수정 중이더라도 기다리지 않고 바로 조회

-- NOLOCK 없이 조회
SELECT * FROM #temp_order

-- NOLOCK 적용 조회
SELECT * FROM #temp_order WITH (NOLOCK)

 

데이터 양이 작거나 실시간으로 데이터가 계속 생성되는 곳이 아니라면 WITH(NOLOCK) 을 이용하여 조회하나, 이용하지 않으나 사실 속도 차이는 없다. 하지만 테이블의 데이터가 양이 많거나 현재 지속적으로 데이터가 생성, 수정, 삭제가 되는 테이블의 경우 NOLOCK이 없으면 Lock 해제를 기다리고 조회하기 위해 지연되는 경우가 있어 WITH(NOLOCK)이 단순히 조회의 속도에 영향을 주어 빠르게 처리된다고 인식하기 쉽다.

#1. 커밋되지 않은 데이터를 조회

• NOLOCK을 설정하면 커밋(COMMIT) 되지 않은 데이터를 읽는다
• 아래처람 다른 세션에서 BEGIN TRAN 만 하고 COMMIT을 하지 않은 상태일 때.

BEGIN TRAN
UPDATE #temp_order
   SET order_qty = 9999
 WHERE order_id = 1
 
 SELECT * FROM #temp_order WITH (NOLOCK)

 

commit 되지 않은 상태를 조회

 

BEGIN TRAN 으로 처리하여서 아직 COMMIT 되지 않았지만 조회가 되는 상황이다. ROLLBACK 이 처리되면 실제로 존재한 적이 없는 데이터를 읽은 상황이 되게 된다.

이에 같은 쿼리임에도 결과가 달라지는 상황이 발생할 수 있다. 

 

그럼 언제 써도 괜찮을까?

• 다소 부정확해도 괜찮은 통계성 자료 조회 ( 대시보드 현황판 등 )
• INSERT / UPDATE / DELETE 가 거의 없는 읽기 전용에 가까운 테이블
• 정확한 수치보다 속도가 중요한 단순 로그 등 파악용 쿼리

그럼 쓰지 말아야 하는 상황은 어떨때일까?

• 금액이나 정산 등 정확한 수치가 계산되어야 하는 자료 조회
• 재고나 수량 집계 누락이나 중복 집계가 되면 안되는 자료 조회

WITH(NOLOCK) 은 정확성을 포기하는 대신 속도를 얻을 수는 있지만 정확도에서 차이가 발생할 수 있기 때문에 단순히 속도 개선목적만으로는 적합하지 않다. 이에 상황에 따라 WITH(NOLOCK) 을 선택하여 쓸 수 있도록 하자.

 

[MS-SQL] COMMIT, ROLLBACK, BEGIN TRAN

 

[MS-SQL] 인덱스 단편화 조회 및 리빌드