데이터의 무결성을 보장하기 위해 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에
SELECT * FROM TEST_TAB WHERE ID=3을 시도하면 BLOCK되겠지만
SELECT * FROM TEST_TAB WITH (READUNCOMMITTED) WHERE ID=3를 시도하면 Data를 읽을 수 있게 된다. (WITH (READUNCOMMITTED) 라는 잠금 힌트는 Select할 때 S-Lock을 걸지 말라는 의미이다.)
현재 걸려있는 Lock을 확인하려면
EXEC SP_LOCK
을 실행하면 된다. 만약 특정 세션의 Lock 상태를 확인하려면 세션 ID를 인자로 주면 된다.
EXEC SP_LOCK @@spid
잠금의 "세기" 라는 것은 여러가지 종류의 Lock들 간의 "호환 관계"라고 정리할 수 있다. S-Lock과 U-Lock이 "호환된다"라 함은 "S-Lock"이 걸려있는 상태에서 제 3의 세션에서 그 데이터에 "U-Lock"을 걸 수 있다는 의미이다.
SELECT ColA, ColB FROM TAB_NAME WITH (UPDLOCK) WHERE ColA = 'AA'
BEGIN TRAN UPDATE test SET val = 2 WHERE pk = 100 EXEC SP_LOCK @@spid
위의 잠금들 간의 호환 관계는 다음과 같은 간단한 테이블로 정리된다.
위에서 언급한 바와 같이 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 |
'SQL Server' 카테고리의 다른 글
잠금에 관한 고찰(3) - 교착상태(데드락,DeadLock)에 대하여 (8) | 2009.02.06 |
---|---|
잠금에 관한 고찰(2) - 격리 수준(Transaction Isolation Level)에 대하여 (7) | 2009.02.05 |
악성 쿼리 찾아내기(4) - 사용자 정의함수의 함정 (0) | 2009.01.08 |
TempDB 관련 성능 이슈 (0) | 2009.01.07 |
Database를 새로운 서버에서 복원했을 때 로그인이 안되는 문제 (0) | 2009.01.07 |