[MSSQL & SQL Server] Dirty read, WITH (NOLOCK)

[MSSQL & SQL Server] Dirty Read, WITH (NOLOCK)

 

 

기존에 써온 Postgresql과는 다르게 MSSQL은 기본적으로 SELECT문을 실행하더라도 공유 잠금(Shared Lock)이 걸린다.

이는 Dirty Read를 방지하기 위한 MSSQL의 기본 격리수준(Isolation Level)이 Read Committed이기 때문이다.

무슨 소리일까 자세히 알아보자.

 

1. Dirty Read란?

The simplest explanation of the dirty read is the state of reading uncommitted data. In this circumstance, we are not sure about the consistency of the data that is read because we don’t know the result of the open transaction(s). 

dirty read는 커밋되지 않은 데이터를 읽는 상태를 말한다. 따라서 이 상황에서는 열린 트랜잭션의 결과를 모르기 때문에 읽는 데이터의 일관성에 대해 확신할 수 없다. 이는 ACID 원칙에 위배된다.

 

2. SQL Server의 기본 격리 수준(Isolation Level)

By default, SQL Server sets an exclusive lock for data that is being modified to ensure data consistency until the transaction is complete. So, it isolates the modified data from the other transaction.

바로 이 때문에 SQL Server에서는 트랜잭션이 완료될 때까지 데이터의 일관성을 보장하기 위해 수정 중인 데이터에 대해 배타적 잠금(Exclusive Lock)을 설정한다. 이렇게 함으로써 수정된 데이터를 다른 트랜잭션과 분리한다.

Read uncommitted is the weakest isolation level because it can read the data which are acquired exclusive lock to the resources by the other transactions. So, it might help to avoid locks and deadlock problems for the data reading operations. On the other hand, Read Committed can not read the resource that acquires an exclusive lock, and this is the default level of the SQL Server.

커밋되지 않은 읽기는 다른 트랜잭션에 의해 리소스에 대한 배타적 잠금을 획득한 데이터를 읽을 수 있기 때문에 가장 약한 격리 수준이다. 따라서 데이터 읽기 작업에 대한 잠금 및 교착 상태 문제를 방지하는데 도움이 될 수 있다. 반면 Read Committed는 배타적 잠금을 획득한 리소스를 읽을 수 없으며, 이것이 SQL Server의 기본 격리 수준이다.

 

3. Exclusive Lock(배타적 잠금)

Exclusive Lock은 특정 Row를 변경(write)하고자 할 때 사용된다. 특정 Row에 Exclusive Lock이 해제될 때까지, 다른 트랜잭션은 읽기 작업을 위해 Shared Lock을 걸거나, 쓰기 작업을 위해 Exclusive Lock을 걸 수 없다.

Exclusive Lock은 SELECT ... FOR UPDATE나 UPDATE, DELETE 등의 수정 쿼리를 날릴 때 각 Row에 걸리는 Lock이다.

 

4. WITH(NOLOCK)

실 사용시에는 수만 명이 동시에 동일한 서버에 접속하게 되므로 Insert, Update, Delete가 이루어지는 상태에서 그저 기다릴 수 없기에 Dirty Read를 실행해야 한다면?

with(nolock) 구문을 사용하면 된다. 

해당 구문을 사용하면 선행작업과 상관없이 트랜잭션이 완료되지 않아도 Select문 수행이 가능해진다.

SELECT *
  FROM [schema].[table]
  WITH(NOLOCK);

Select 문장에서 여러 테이블을 조인해서 가져오는 경우 WITH(NOLOCK)을 사용하기 위해서는 모든 테이블에 적어주어야 하지만 Procedure 내에서 Select문에 WITH(NOLOCK)을 사용하기 위해서는 각 문장마다 삽입할 필요 없이 프로시저 시작 부분에 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED를 추가해주면 된다.

CREATE PROCEDURE [procedure_name]
	AS 
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
 BEGIN
       SELECT * FROM [schema].[table] 
       ...       
   END

 

5. 결론

이를 통해 조회 성능이 올라가고 데드락(Deadlock)을 방지할 수 있다. 그러나 앞서 말했다시피 Comitted 되지 않은 데이터를 읽기 때문에 트랜잭션이 Rollback 될 경우, 데이터 정합성을 잃을 수 있다.

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

 

 

참고로 Oracle과 Postgresql은 MVCC로 Lock을 제어하기 때문에 다른 DBMS처럼 Dirty Read 개념이 필요 없다.

 

 

<참조>

https://www.sqlshack.com/dirty-reads-and-the-read-uncommitted-isolation-level/

Dirty Reads and the Read Uncommitted Isolation Level