MSSQL WITH(NOLOCK) , SELECT시 잠금무시

 

 

MSSQL 은 기본적으로 SELECT 시 공유잠금(S Lock) 이 걸린다.

이는 MSSQL 의 기본 격리수준(Isolation Level) 이 Read Committed 이기 때문이다.

즉, DML(INSERT, UPDATE, DELETE) 작업중인 ROW 또는 TABLE 에 SELECT 할 경우,

해당 작업이 끝나야 SELECT 할 수 있다는 의미이다.  

 

만일 이렇게 수행된다면 DB의 성능은 떨어지고, 데드락(Deadlock) 이 발생될 수 있다.

 

이를 방지하기 위해 WITH(NOLOCK) 을 사용할 수 있다.

WITH(NOLOCK) 의 격리수준(Isolation Level)은 Read Uncommitted 와 같다고 보면 된다.

 

즉, SELECT 할 ROW 또는 TABLE 이 잠겨있어도(작업중이여도) 기다리지 않고 조회 (Dirty Read) 하겠다라는 의미이다.

이를 통해 조회 성능이 올라가고, 데드락(Deadlock) 을 방지할 수 있다.

 

그러나 Committed 되지 않은 데이터를 읽기 때문에 트랜잭션이 Rollback 될 경우,

잘못된 데이터를 읽게 될 수도 있다. (정합성이 떨어진다.)

 

고로, WITH(NOLOCK) 은 정확성이 필요한 경우에는 사용하지 말아야한다.

 

 

 

1. WITH(NOLOCK) 사용법

---

사용법은 간단하다.

아래와 같이 테이블명 뒤에 WITH(NOLOCK) 을 붙여주면 된다.

1

SELECT * FROM tbl_product WITH(NOLOCK);

cs

 

 

만약 JOIN 으로 이루어진 쿼리의 경우에는

아래와 같이 각 테이블명 뒤에 WITH(NOLOCK) 을 붙여주면 된다.

1 2 3

SELECT * FROM tbl_product p WITH(NOLOCK) INNER JOIN tbl_product_price pp WITH(NOLOCK) ON p.product_id = pp.product_id

cs

 

 

 

2. WITH(NOLOCK) 없는 SELECT

---

아래와 같은 '상품가격' 테이블이 있다고 하자.

1

SELECT * FROM tbl_product_price;

cs

 

 

 

'라면'의 가격이 5,000 으로 인상되어 세션1 에서 가격을 UPDATE 해주었다.

1 2 3 4 5 6

--session 1 BEGIN TRAN   UPDATE tbl_product_price SET product_price = 5000 WHERE product_id = 1

cs

 

 

 

 

그리고 세션2 에서 이 테이블을 WITH(NOLOCK) 이 없는 SELECT 해보았다.

아래와 같이 UPDATE 작업이 완료되기까지 SELECT 작업은 대기하게 된다.

1 2

--session 2 SELECT * FROM tbl_product_price;

cs

 

 

 

 

sp_who2 를 통해 대기상태를 확인할 수 있다.

SELECT 명령어가 SUSPENDED(대기) 상태인 것을 볼 수 있다.

1 2

--session 3 sp_who2

cs

 

 

 

 

 

3. WITH(NOLOCK) 있는 SELECT

---

위와 동일하게 세션1에서 라면 상품의 가격을 5,000 으로 UPDATE 했다.

 

1 2 3 4 5 6

--session 1 BEGIN TRAN   UPDATE tbl_product_price SET product_price = 5000 WHERE product_id = 1

cs

 

 

그리고 이번에는 세션2 에서 WITH(NOLOCK) 이 있는 SELECT 를 해보았다.

1 2

--session 2 SELECT * FROM tbl_product_price WITH(NOLOCK);

cs

 

 

 

session1 의 작업 진행여부와 상관 없이 Dirty Read 데이터를 읽어 조회가 정상적으로 되는 것을 볼 수 있다.

 

하지만, 여기서 주의해서 봐야 할 점이 있다.

session2 의 결과를 보면 라면의 가격이 5,000 으로 UPDATE 되어있다.

여기서 session1 의 UPDATE 작업을 Rollback 하면 어떻게 될까?

 

라면의 가격은 UPDATE 되지 않아 그대로 4500 일 것이며,

결국 WITH(NOLOCK) 으로 조회한 SELECT 문은 잘못된  값이 될 수 있다는 것이다.

 

고로, 정확성이 필요할 경우 WITH(NOLOCK) 을 사용해선 안된다.

 

 

 

 

4. SELECT의 공유 잠금

---

반대로 WITH(NOLOCK) 이 없는 SELECT 작업 중 해당 테이블을 변경하려고 하면 어떻게 될까?

session1 에서 WITH(NOLOCK) 없는 SELECT 문을 실행하였다.

1 2

--session 1 SELECT * FROM tbl_product_price;

cs

 

 

 

그리고 session2 에서 해당 테이블을 UPDATE 하였다.

1 2 3 4 5 6

--session 2 BEGIN TRAN   UPDATE tbl_product_price SET product_price = 5000 WHERE product_id = 1

cs

 

 

정상적으로 UPDATE 되는 것을 볼 수 있다.

 

SELECT 문을 실행 시 공유잠금(S Lock) 이 걸리고,

공유잠금은 다른 공유잠금(S Lock) 과만 허용이되며 다른 배타적 잠금(X Lock) 은 허용하지 않는다.

 

그런데도 위 session2 에서 해당 테이블이 UPDATE 되는 이유는 뭘까?

 

기본 SQL Server 데이터베이스 엔진에서는 트랜잭션이 끝날 때까지 배타적 잠금이 유지 되지만,

공유 잠금은 SELECT 작업이 수행되는 즉시 해제되기때문에 트랜잭션이 종료되지 않아도 UPDATE 가 가능하다.

 

 

출처:

https://ryean.tistory.com/32

[SQL Server / MS-SQL] WITH(NOLOCK) 사용법, 주의할 점, 공유잠금