[mssql] 잠금에 관한 고찰(1) - 잠금(Lock) 매커니즘에 대하여

출처 : http://kuaaan.tistory.com/97

데이터의 무결성을 보장하기 위해 SQL Server에서는 데이터에 Lock을 건다. 사흘에 걸쳐 책을 읽고 테스트를 하면서 공부한 끝에 이 Lock의 매커니즘을 어느정도 이해할 수 있게 되었다. 이에 이해한 내용을 정리해보고자 한다.

1. 잠금(Lock)의 개념

  데이터에 잠금(Lock)을 건다고 하면 언뜻 생각하기엔 데이터가 들어있는 방에 들어가지 못하게 방문을 걸어 잠근다는 느낌이 들지만, 사실은 방문에 "이 방에는 U-Lock이 걸려있음" 이라고 써 붙이는 개념에 가깝다. 

  그 방에 누군가 SELECT를 시도한다면, 시도하는 사람은 그 방에 또 "S-Lock이 걸려있음"이라고 써붙이게 되는데 이때 이전에 걸려있던 "U-Lock"과 지금 걸려고 시도하는 "S-Lock"의 호환성을 비교하게 된다. 다행히도 "U-Lock"은 "S-Lock"과 호환되므로 "S-Lock"을 걸 수 있게 되고, SELECT에 성공하게 된다.

  만약 누군가가 그 방에 UPDATE를 시도한다면 그 사람은 그 방에 U-Lock을 걸려고 시도하겠지만 U-Lock은 U-Lock과 호환되지 않으므로 Lock을 걸지 못하고 기다리게 된다. 말하자면 이 Update시도는 기존 트랜잭션이 끝날때까지 "Block"되게 되는 것이다.

  걸어잠그는 것과 걸어잠갔다고 써붙이는 것이 무슨 차이가 있냐고?

  이 방에 들어있는 Data에 접근할 때는 서로간의 약속이 되어 있다. 예를 들면 "Select를 시도할 때는 S-Lock을 걸어야 한다"라던지... "X-Lock이 걸려있을 때는 S-Lock을 걸 수 없다"라던지... 뭐 이런 약속들이다. 그런데 만약 이 약속을 어기는 사람이 있다면 어떻게 될까? 위에서 언급했듯이 실제로 방 문을 걸어잠그는 것이 아니기 때문에 약속을 무시하는 사람은 방에 마음대로 들어갈 수 있게 된다. 예를 들어서... 이미 X-Lock이 걸려있는 Row에

view plaincopy to clipboardprint?

1. SELECT * FROM TEST_TAB WHERE ID=3  

을 시도하면 BLOCK되겠지만

view plaincopy to clipboardprint?

1. SELECT * FROM TEST_TAB WITH (READUNCOMMITTED) WHERE ID=3  

를 시도하면 Data를 읽을 수 있게 된다. (WITH (READUNCOMMITTED) 라는 잠금 힌트는 Select할 때 S-Lock을 걸지 말라는 의미이다.)

현재 걸려있는 Lock을 확인하려면

view plaincopy to clipboardprint?

1. EXEC SP_LOCK  

 

을 실행하면 된다. 만약 특정 세션의 Lock 상태를 확인하려면 세션 ID를 인자로 주면 된다.

view plaincopy to clipboardprint?

1. EXEC SP_LOCK @@spid  

 

2. 잠금의 세기

  잠금의 "세기" 라는 것은 여러가지 종류의 Lock들 간의 "호환 관계"라고 정리할 수 있다. S-Lock과 U-Lock이 "호환된다"라 함은 "S-Lock"이 걸려있는 상태에서 제 3의 세션에서 그 데이터에 "U-Lock"을 걸 수 있다는 의미이다.

2.1 공유 잠금 (Shared-Lock, S-Lock)

  공유잠금은 가장 낮은 강도의 잠금으로서, 일반적으로 Select를 할 때 공유잠금이 발생하며, Select가 완료되는 즉시 공유잠금은 해제된다. (트랜잭션이 완료되기 전이더라도 Select 완료 시점에서 잠금이 해제된다는 것이 중요하다.) 공유잠금은 서로 다른 공유잠금과 호환된다. 이 말은 바로 동일한 데이터를 서로 다른 세션에서 동시에 Select할 수 있다는 의미이다. 반면에 공유 잠금은 배타적잠금과는 호환되지 않는데 이 의미는 다른 트랜잭션에서 Update를 수행한 레코드(Uncommitted Data)에 대해 Select를 할 수 없다는 의미로 해석하면 된다.

 

2.2 배타적 잠금 (Exclusive-Lock, X-Lock)

  배타적잠금은 가장 높은 강도의 잠금으로서, Update가 행해진 시점부터 그 트랜잭션이 Commit될 때까지 배타적 잠금이 걸린다. 배타적 잠금은 다른 모든 종류의 잠금과 호환되지 않는다. 이 의미는 어떠한 약한 잠금이라도 걸려있는 레코드에 대해서는 Update가 불가능하며, 반대로 Update가 진행중인 레코드에 대해서는 Select를 포함한 어떠한 작업도 불가능하다는 의미가 된다.

 

2.3 업데이트 잠금 (Update-Lock, U-Lock)

  업데이트잠금은 공유잠금과 배타적잠금의 중간 강도의 잠금이다. 공유잠금과는 호환되지만 다른 업데이트잠금이나 배타적 잠금과는 호환되지 않는다. 일반적으로는 Update의 Filter(Where절)가 수행되는 단계에서 업데이트 잠금이 걸리며, Filter된 결과에 대해 실제로 Update를 시도할 때 업데이트잠금은 배타적 잠금으로 전환된다. (만약 테이블에 인덱스가 없거나 Where절이 인덱스를 탈 수 없게 되어 있다면, 테이블을 풀스캔하면서 모든 레코드에 업데이트잠금을 걸 것이다.)

  업데이트잠금은 잠금힌트를 통해 업데이트문이 아닌 Select문에도 걸 수 있다. 보통 컨버젼 데드락을 방지하기 위해 Select문에 업데이트 잠금을 거는 경우가 많다.

view plaincopy to clipboardprint?

1. SELECT ColA, ColB FROM TAB_NAME WITH (UPDLOCK) WHERE ColA = 'AA'  

 

 

3. 잠금의 크기

3.1 잠금의 크기

  잠금의 크기라 함은 어느 정도의 범위를 잠글 것인가에 관한 이야기이다. 대체로 Row Lock (Key Lock), Page Lock, Table Lock 정도가 있다.

  "잠금 비용"이란 잠금을 거는 과정에서 발생하는 성능 손실을 말한다. 만약 Lock을 걸어야 할 페이지가 너무 많다면, 차라리 Table 전체에 Lock을 거는 것이 "잠금 비용"이 훨씬 낮을 것이다.

  "동시성 비용"이란 잠금을 걸면서 동시성이 낮아져서 발생하는 성능 손실을 의미한다. Page Lock 여러개를 Table 하나로 대체하였다면 "잠금 비용"은 낮아지겠지만 대신 "동시성 비용"은 높아질 것이다.

  SQL Server는 "잠금 비용"과 "동시성 비용" 간에 균형을 적절히 고려하여 잠금의 범위를 결정하게 된다. 일반적으로 약 40%의 페이지에 Lock을 걸어야 한다면 테이블 Lock으로 대체된다고 한다

  잠금의 크기를 줄이기 위해서는 적절한 인덱스를 사용하는 것이 중요한데, 인덱스를 타지 못하여 Table Full Scan이 발생한다면 업데이트할 데이터를 찾는 과정에서 테이블 전체에 Lock을 걸게 되기 때문이다. 

 

3.2 내재된 잠금 (Intent-Lock)

  내재된 잠금은 앞의 세가지 잠금과는 약간 다른 차원의 이야기이다. 내재된 잠금을 이해하기 위해서는 "잠금의 크기"를 먼저 이해해야 한다. 

  만약 세션1에서 어떤 Row에 잠금을 걸었다고 가정하자. 그 상태에서 세션2에서 그 Row가 속한 테이블 전체에 테이블잠금을 걸려고 시도하면 어떻게 될까? 당연히 테이블잠금이 걸리면 안된다. (만약 테이블락을 걸 수 있다면 세션1은 레코드잠금을 건 상태에서 다음 작업을 못한 채 꼼짝도 못하게 될 것이다. )

  그렇다면, 세션2는 어떻게 이 테이블의 Row 중 하나에 락이 걸려있다는 것을 알 수 있을까? 테이블에 락을 걸기 전에 모든 페이지와 모든 Row를 다 조사해야 할까? 실제로 SQL Server는 그 반대로 구현되어 있다. 즉, 세션 1이 그 Row에 업데이트 잠금을 걸 때, 해당 레코드가 속한 상위 페이지와 상위 테이블에 내재된 잠금을 함께 걸게 된다. 이렇게 함으로서 세션 2는 테이블에 락을 걸기 전에 해당 테이블만 확인해보면 락을 걸어도 될지를 결정할 수 있게 된다.

내재된 잠금은 IX, IU 와 같이 표기한다. (Intent Exclusive Lock, Intent Update Lock)

 

다음과 같이 테스트해보자.

view plaincopy to clipboardprint?

1. BEGIN TRAN  
2.   
3. UPDATE test  
4. SET val = 2  
5. WHERE pk = 100  
6.   
7. EXEC SP_LOCK @@spid  

SP_LOCK 프로시져로 살펴보았을 때 내재된 잠금은 다음과 같이 확인된다.

 

위의 결과는 52번 세션에서 인덱스(KEY)에 배타적잠금(Mode = X)를, 그것이 속한 페이지와 테이블에는 내재된 배타적잠금을 (Mode = IX)를 걸었다는 의미가 되겠다.

 

 

4. 잠금의 호환성 (Lock Compatibility)

  위의 잠금들 간의 호환 관계는 다음과 같은 간단한 테이블로 정리된다.

  위에서 언급한 바와 같이 Update Lock과 Shared Lock이 호환된다 함은 Update Lock이 걸려있는 페이지에 Shared Lock을 또 걸수 있다는 의미가 된다.

 

Requested mode	Existing granted mode
	IS	S	U	IX	SIX	X
Intent shared (IS)	Yes	Yes	Yes	Yes	Yes	No
Shared (S)	Yes	Yes	Yes	No	No	No
Update (U)	Yes	Yes	No	No	No	No
Intent exclusive (IX)	Yes	No	No	Yes	No	No
Shared with intent exclusive (SIX)	Yes	No	No	No	No	No
Exclusive (X)	No	No	No	No	No	No

(테이블 출처 : http://msdn.microsoft.com/en-us/library/ms172925.aspx)

 

 

 

5. 잠금의 길이

  잠금의 길이란 잠금이 지속되는 시간을 의미한다. 

  일반적인 공유잠금(S-Lock)은 SELECT 문이 끝나면 자동으로 풀린다. 반면에 단독잠금(X-Lock)과 업데이트잠금(UPD-Lock)은 트랜잭션 종료시까지 지속된다.

  공유잠금(S-Lock)의 길이는 경우에 따라 달라질 수 있어, 격리 수준이 Serializable이나 Repeatable Read인 경우 S-Lock도 트랜잭션 종료시까지 지속된다.