누락된 공유 잠금 (Missing Shared Locks)

누락된 공유 잠금 (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 :

http://sqlblog.com/blogs/paul_white/archive/2010/11/01/read-committed-shared-locks-and-rollbacks.aspx

 

 

2014-01-06 / 강성욱 / http://sqlmvp.kr