누락된 공유 잠금 (Missing Shared Locks)
- Version : SQL Server 2005, 2008, 2008R2, 2012
특정 행에 대해 베타적 잠금(exclusive lock)을 보유한 경우 다른 트랜잭션에서 read committed isolation 레벨에서 읽을 수 있을까?
대부분의 사람들은 베타적 잠금을 보유한 경우 읽을 수 없다고 답할 것이다. 일부 사람들은 READ_COMMITTED_SNAPSHOT 데이터베이스 옵션의 적용 여부에 달려 있다고 할 것이다.
READ_COMMITTED_SNAPSHOT 경우가 아니라고 가정하면 read committed 격리 수준으로 읽을 수 있다.
실습을 위해 테이블과 데이터를 생성한다.
use tempdb go
CREATE TABLE dbo.Demo ( some_key INTEGER PRIMARY KEY, some_value INTEGER NOT NULL ); GO
INSERT INTO dbo.Demo (some_key, some_value) VALUES (1, 100); GO
IF DB_NAME() = N'tempdb' CHECKPOINT; |
아래 스크립트는 트랜잭션을 시작하고 베타적 잠금을 획득한다. 그리고 보유한 잠금 정보를 보여준다. 예상대로 베타적 키 잠금을 보여주고 페이지와 테이블에는 의도적 독점이 사용되었다.
BEGIN TRANSACTION
SELECT D.some_key, D.some_value FROM dbo.Demo D WITH (XLOCK);
SELECT L.resource_type, L.request_mode, L.request_status, L.resource_description, L.resource_associated_entity_id FROM sys.dm_tran_current_transaction as T JOIN sys.dm_tran_locks as L ON L.request_owner_id = T.transaction_id; |
다른 세션에서 해당 테이블을 조회하여 보자. 베타적 잠금에도 불구하고 쿼리가 차단되지 않았다.
SELECT D.some_key, D.some_value FROM dbo.Demo D WITH (ROWLOCK, READCOMMITTEDLOCK); |
[Locking Optimization]
SQL Server 옵티마이저는 특정 상황에서 행 수준 공유 잠금(row-level shared locks)을 사용하지 않는다. 커밋되지 않은 데이터를 판독할 할 때 위험성이 없다고 판단될 경우 공유 잠금 스킵한다.
프로파일러를 사용하여 잠금을 획득하는지 여부를 확인 할 수 있다. 프로파일러 결과를 살펴 보면 테이블 수준에서 의도적 공유 잠금(IS)이 사용되었지만 기존의 단독 잠금과 행 수준 공유 잠금이 충돌하지 않아 차단이 발생 하지 않았다.
[Row-Level Shared Locks Only]
다음 스크립트는 페이지 단위 잠금을 사용하였다. 결과를 살펴보면 테이블 수준의 IS 잠금을 획득하였다. 그리고 페이지 수준의 잠금을 요청 하였지만 기존의 잠금과 충돌하여 잠금을 획득 할 수 없었다.
SELECT D.some_key, D.some_value FROM dbo.Demo D WITH (PAGLOCK, READCOMMITTEDLOCK); |
[No Uncommitted Changes]
커밋되지 않은 변경 사항이 있을 경우 SQL Server는 잠금 최적화를 사용 할 수 없다. 공유 잠금이 수행되지 않은 경우 페이지에 커밋되지 않은 변경으로 인해 dirty read가 발생 할 수 있다. Dirty read는 커밋 된 읽기 격리 수준에서 허용되지 않는다.
SQL Server는 페이지 단위의 최적화를 적용하여 일부 페이지에 공유 행 수준의 잠금을 획득한다.
기존 트랜잭션이 열려 있던 세션에서 다음 스크립트를 사용하여 행을 추가한다.
INSERT INTO dbo.Demo (some_key, some_value) VALUES (2, 200); |
페이지에는 커밋되지 않은 변경 사항이 적용되어 있다.
다음 스크립트는 SELECT를 요청 할 때 READPAST 힌트를 사용하였다. 공유 잠금을 획득하였지만 힌트로 인하여 쿼리는 완료 되고 행은 반환 되지 않았다.
SELECT D.some_key, D.some_value FROM dbo.Demo D WITH (ROWLOCK, READCOMMITTEDLOCK, READPAST); |
명시적인 XLOCK힌트와 입력된 행에 대한 베타적 잠금 보호로 두 행을 건너 뛰었다. 프로파일러 결과를 살펴 보면 행 수준 공유 잠금은 타임아웃을 보여준다.
[Committed Changes Are Fine]
트랜잭션을 커밋하는 경우 행 수준 잠금을 다시 건너뛰는 것을 발견 할 수 있다. 기존 트랜잭션일 열려 있는 세션을 커밋하고 (이전에 입력한 sumeky = 2 데이터 커밋) 해당 테이블에 대한 XLOCK을 요청 한다.
commit tran
checkpoint
BEGIN TRANSACTION SELECT D.some_key, D.some_value FROM dbo.Demo D WITH (XLOCK); |
다른 세션에서 다음의 SELECT 쿼리를 실행 한다. 공유 잠금을 요청 하지 않고 결과가 반환된다.
SELECT D.some_key, D.some_value FROM dbo.Demo D WITH (ROWLOCK, READCOMMITTEDLOCK); |
[참고자료]
The Case of the Missing Shared Locks :
강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp
