MSSQL Server] 2. Lock, 트랜잭션 격리 수준

지난 post에서 SQL Server에서 사용하는 주요 lock types에 대해 살펴보았다. 이번 post에서는 트랜잭션 격리 수준과 locking 동작에 어떤 영향을 주는지 살펴보자. 

 

트랜잭션이란?

transaction이란 작업단위(unit of work)이다. 계좌이체를 할때, system은 하나의 계좌에서 인출해서 다른 계좌에 입금하는 하나의 단위작업을 수행한다. 인출과 입금이 2개의 독립적인 작업이라도, 중간에 멈추면 안되기 때문에 하나의 작업처럼 실행된다. 

 

transaction에 대해서 잘 알려주는 ACID라는 유용한 용어가 있다. 

• (A) - Atomicity 또는 'all or nothing'. 모든 변경이 저장되든지 아무것도 변경되지 않든지 둘 중 하나이다.
• (C) - Consistency. 데이터는 항상  일관된 상태를 유지한다. 
• (I) - Isolation. 다른 session은 transaction이 완료될때까지 변경사항을 볼 수 없다. 
• (D) - Durability. DB에 영속적으로 저장된다. 

SQL Server의 transaction에 대해 다음과 같은 몇가지 미신이 있다. 

• begin tran/commit 없이 insert/update/delete를 실행하면 transaction이 없다. 이는 사실이 아니다. 이런 경우, SQL Server는 내부 transaction을 실행한다. 
• select 문구에는 transaction이 없다. 이는 사실이 아니다. SQL Server는 select문구에 대해 경량 transaction을 사용한다. 
• (NOLOCK) hint가 있으면 transaction이 없다. 이는 사실이 아니다. (NOLOCK) hint는 reader를 read uncommitted isolation으로 downgrades를 하며, transactions은 여전히 실행중이다. 

각 transaction은 특정 트랜잭션 격리 수준으로 시작한다.

4가지 pessimistic isolation level

• Read Uncommitted : 트랜잭션에서 처리 중인 아직 커밋되지 않은 데이터를 다른 트랜잭션이 읽는 것을 허용한다.
• Read Committed : 트랜잭션이 커밋되어 확정된 데이터만 다른 트랜잭션이 읽도록 허용함으로써 Dirty Read를 방지해준다.
• Repeatable Read : 트랜잭션 내에서 쿼리를 두 번 이상 수행할 때, 첫 번째 쿼리에 있던 레코드가 사라지거나 값이 바뀌는 현상을 방지해 준다. 다른 session이 읽혀진 data를 수정하는 것을 막는다. 하지만, 첫 번째 쿼리에서 없던 새로운 레코드가 나타날 수 있다.
• Serializable : 트랜잭션 내에서 쿼리를 두 번 이상 수행할 때, 첫 번째 쿼리에 있던 레코드가 사라지거나 값이 바뀌지 않음은 물론 새로운 레코드가 나타나지도 않는다. lock이 range of rows에 획득되기 때문에, 다른 session에서 읽혀진 data 사이에 다른 data가 insert되는 것을 막는다. 

isolation level에 상관없이 배타적 Lock은 transaction 끝까지 유지되며, 다른점은 SQL Server가 공유 Lock을 어떻게 다루느냐이다. 

격리수준	공유 잠금 행동	테이블 힌트
Read Uncommitted	공유 잠금은 획득되지 않는다.	NOLOCK
Read Committed	획득되고 즉시 풀린다.	READCOMMITTED
Repeatable Read	트랜잭션 끝까지 유지된다.	REPEATABLEREAD
Serializable	트랜잭션 끝까지 Range locks을 유지한다.	HOLDLOCK

read uncommitted mode에서는 공유잠금이 획득되지 않는다. 따라서, select문은 다른 uncommitted transactions(배타 잠금을 갖고 있어도)에 의해 수정된 데이터를 조회할 수 있다. writers는 여전히 서로 block된다. 

그 외 격리수준은 공유잠금을 획득하고, 다른 session에서 X lock과 함께 커밋되지 않은 row를 읽으려 하는 시도가 block 된다. 

 

2가지 optimistic isolation level

• Snapshot
• read committed snapshot

 

locking behavior는 'set transaction isolation level' 구문으로 설정할 수 있다. 테이블 수준의 테이블 힌드를 사용해서 할 수 있다. 

select ...
from 
	dbo.Table t1 with (NOLOCK) join
    dbo.Table t2 with (HOLDLOCK) on

Table1에는 uncommitted isolation level로 접근할 수 있고, Table2에는 serializable isolation level로 접근할 수 있다.