데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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

 

 



강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1913 SQL Server 시작 옵션 사용 jevida(강성욱) 2016.10.13 1808
1912 Ring buffer를 활용한 External Memory Pressure 확인 jevida(강성욱) 2016.10.12 1288
1911 Memory Pressure jevida(강성욱) 2016.10.12 1123
1910 64비트 버전의 SQL Server 버퍼 풀 메모리 페이지 수 줄이는 방법 jevida(강성욱) 2016.10.12 2480
» 누락된 공유 잠금 (Missing Shared Locks) jevida(강성욱) 2016.10.12 1565
1908 Ad Hoc Distributed Queries 옵션 jevida(강성욱) 2016.10.12 3113
1907 높은 MAXDOP은 쿼리를 느리게 만들 수 있는가? jevida(강성욱) 2016.10.12 1315
1906 변경된 테이블 이름 복구하기 jevida(강성욱) 2016.10.12 1310
1905 Sys,dm_exec_connections jevida(강성욱) 2016.10.12 1939
1904 SQL Server 시작 매개 변수 설정 jevida(강성욱) 2016.10.12 2499
1903 AFTER 트리거를 INSTEAD OF 트리거로 변경 후 효율성 향상 jevida(강성욱) 2016.10.12 1798
1902 쿼리 대기 옵션 jevida(강성욱) 2016.10.12 1575
1901 Index create memory 설정 jevida(강성욱) 2016.10.12 1238
1900 Min memory per query 옵션 jevida(강성욱) 2016.10.12 1691
1899 SQL Server 에디션 다운그레이드와 제한된 기능 확인 jevida(강성욱) 2016.10.12 2127
1898 Downgrade from SQL Server Ent to Std Edition jevida(강성욱) 2016.10.12 1349
1897 트랜잭션 로그 여유 공간 모니터링 jevida(강성욱) 2016.10.12 1359
1896 SQL Server가 서비스 격리를 처리하는 방법 jevida(강성욱) 2016.10.12 1145
1895 쿼리 매개변수화 확인하기 jevida(강성욱) 2016.10.12 1435
1894 SQL Server에서 Drop 및 Delete 사용자 찾기 jevida(강성욱) 2016.10.12 1630





XE Login