sys.dm_tran_locks (Transact-SQL)

적용 대상: Microsoft Fabric의 SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics AnalyticsPlatform System(PDW) Warehouse

SQL Server에서 현재 활성 잠금 관리자 리소스에 대한 정보를 반환합니다. 각 행은 부여되었거나 부여되기를 기다리는 잠금에 대한 잠금 관리자에 대한 현재 활성 요청을 나타냅니다.

결과 집합의 열은 리소스와 요청이라는 두 개의 기본 그룹으로 나뉩니다. 리소스 그룹은 잠금이 요청된 리소스를 설명하고 요청 그룹은 잠금 요청을 설명합니다.

사용 권한

SQL Server 및 SQL Managed Instance에서는 VIEW SERVER STATE 권한이 필요합니다.

SQL Database Basic, S0 및 S1 서비스 목표 및 탄력적 풀의 데이터베이스에는 서버 관리자 계정, Microsoft Entra 관리자 계정 또는 서버 역할의 ##MS_ServerStateReader##멤버 자격이 필요합니다. 다른 모든 SQL Database 서비스 목표에 대해서는 데이터베이스에 대한 VIEW DATABASE STATE 권한 또는 ##MS_ServerStateReader## 서버 역할의 멤버 자격이 필요합니다.

SQL Server 2022 이상에 대한 권한

서버에 대한 VIEW SERVER PERFORMANCE STATE 권한이 필요합니다.

설명

부여된 요청 상태 요청자에게 리소스에 대한 잠금이 부여되었음을 나타냅니다. 대기 요청은 요청이 아직 부여되지 않음을 나타냅니다. 다음 대기 요청 형식은 열에서 request_status 반환됩니다.

• 변환 요청 상태 요청자에게 리소스에 대한 요청이 이미 부여되었으며 현재 초기 요청으로의 업그레이드가 부여되기를 기다리고 있음을 나타냅니다.

• 대기 요청 상태 요청자가 현재 리소스에 대해 부여된 요청을 보유하지 않음을 나타냅니다.

내부 잠금 관리자 데이터 구조에서 채워지므로 sys.dm_tran_locks 이 정보를 기본 일반 처리에 추가 오버헤드가 추가되지는 않습니다. 뷰를 구체화하려면 잠금 관리자 내부 데이터 구조에 액세스해야 하므로 이는 서버의 일반 처리에 사소한 영향을 미칠 수 있습니다. 이러한 효과는 눈에 띄지 않아야 하며 많이 사용되는 리소스에만 영향을 주어야 합니다. 이 보기의 데이터는 라이브 잠금 관리자 상태에 해당하므로 데이터는 언제든지 변경될 수 있으며 잠금을 획득하고 해제할 때 행이 추가되고 제거됩니다. 이 보기를 쿼리하는 애플리케이션은 잠금 관리자 구조의 무결성을 보호하는 특성으로 인해 예측할 수 없는 성능을 경험할 수 있습니다. 이 보기에는 기록 정보가 없습니다.

두 요청은 모든 리소스 그룹 열이 동일한 경우에만 동일한 리소스에서 작동합니다.

다음 도구를 사용하여 읽기 작업의 잠금을 제어할 수 있습니다.

• SET TRANSACTION ISOLATION LEVEL - 세션에 대한 잠금 수준을 지정합니다. 자세한 내용은 SET TRANSACTION ISOLATION LEVEL(Transact-SQL)을 참조하세요.

• FROM 절에서 테이블의 개별 참조에 대한 잠금 수준을 지정하는 잠금 테이블 힌트입니다. 구문 및 제한 사항은 테이블 힌트(Transact-SQL)를 참조하세요.

하나 session_id 아래에서 실행되는 리소스는 둘 이상의 잠금을 부여할 수 있습니다. 한 세션에서 실행되는 다른 엔터티는 각각 동일한 리소스에 대한 잠금을 소유할 수 있으며, 정보는 반환되는 열 및 request_owner_id 열에 request_owner_typesys.dm_tran_locks표시됩니다. 동일한 request_owner_type 인스턴스가 여러 개 있는 request_owner_id 경우 열은 각 인스턴스를 구분하는 데 사용됩니다. 분산 트랜잭션의 request_owner_type 경우 열과 열은 request_owner_guid 서로 다른 엔터티 정보를 표시합니다.

예를 들어 세션 S1은 공유 잠금을 Table1소유하고, 세션 S1에서 실행되는 트랜잭션 T1도 공유 잠금을 소유합니다 Table1. 이 경우 resource_description 반환 sys.dm_tran_locks 되는 열에는 동일한 리소스의 두 인스턴스가 표시됩니다. 열은 request_owner_type 하나의 인스턴스를 세션으로 표시하고 다른 인스턴스는 트랜잭션으로 표시합니다. 또한 열에 resource_owner_id 다른 값이 있습니다.

한 세션에서 실행되는 여러 커서는 구별할 수 없으며 하나의 엔터티로 처리됩니다.

값과 session_id 연결되지 않은 분산 트랜잭션은 분리된 트랜잭션이며 값이 session_id-2할당됩니다. 자세한 내용은 KILL(Transact-SQL)을 참조 하세요.

잠금

트랜잭션 중에 읽거나 수정한 행과 같은 SQL Server 리소스에 잠금이 유지되어 서로 다른 트랜잭션에서 리소스를 동시에 사용할 수 없습니다. 예를 들어 트랜잭션에 의해 테이블 내의 행에 배타적(X) 잠금이 유지되는 경우 잠금이 해제될 때까지 다른 트랜잭션은 해당 행을 수정할 수 없습니다. 잠금을 최소화하면 동시성이 향상되어 성능이 향상될 수 있습니다.

리소스 세부 정보

다음 표에서는 열에 표시되는 리소스를 resource_associated_entity_id 나열합니다.

다음 표에서는 각 리소스 유형에 연결된 하위 유형을 나열합니다.

다음 표에서는 각 리소스 종류에 resource_description 대한 열 형식을 제공합니다.

예제

A. 다른 도구와 함께 sys.dm_tran_locks 사용

다음 예제에서는 업데이트 작업이 다른 트랜잭션에 의해 차단되는 시나리오에서 작동합니다. 사용 및 기타 도구를 사용하여 sys.dm_tran_locks 리소스 잠금에 대한 정보가 제공됩니다.

USE tempdb;
GO

-- Create test table and index.
CREATE TABLE t_lock
    (
    c1 int, c2 int
    );
GO

CREATE INDEX t_lock_ci on t_lock(c1);
GO

-- Insert values into test table
INSERT INTO t_lock VALUES (1, 1);
INSERT INTO t_lock VALUES (2, 2);
INSERT INTO t_lock VALUES (3, 3);
INSERT INTO t_lock VALUES (4, 4);
INSERT INTO t_lock VALUES (5, 5);
INSERT INTO t_lock VALUES (6, 6);
GO

-- Session 1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN TRAN
    SELECT c1
        FROM t_lock
        WITH(holdlock, rowlock);

-- Session 2
BEGIN TRAN
    UPDATE t_lock SET c1 = 10;

다음 쿼리는 잠금 정보를 표시합니다. 값을 <dbid> fromsys.databases으로 database_id 바꿔야 합니다.

SELECT resource_type, resource_associated_entity_id,
    request_status, request_mode,request_session_id,
    resource_description
    FROM sys.dm_tran_locks
    WHERE resource_database_id = <dbid>;

다음 쿼리는 이전 쿼리를 사용하여 resource_associated_entity_id 개체 정보를 반환합니다. 개체가 포함된 데이터베이스에 연결된 동안 이 쿼리를 실행해야 합니다.

SELECT object_name(object_id), *
    FROM sys.partitions
    WHERE hobt_id=<resource_associated_entity_id> ;

다음 쿼리는 차단 정보를 보여줍니다.

SELECT
    t1.resource_type,
    t1.resource_database_id,
    t1.resource_associated_entity_id,
    t1.request_mode,
    t1.request_session_id,
    t2.blocking_session_id
FROM sys.dm_tran_locks as t1
INNER JOIN sys.dm_os_waiting_tasks as t2
    ON t1.lock_owner_address = t2.resource_address;

트랜잭션을 롤백하여 리소스를 해제합니다.

-- Session 1
ROLLBACK;
GO

-- Session 2
ROLLBACK;
GO

B. 운영 체제 스레드에 세션 정보 연결

다음 예제에서는 Windows 스레드 ID와 연결하는 session_id 정보를 반환합니다. 스레드의 성능은 Windows 성능 모니터 모니터링할 수 있습니다. 이 쿼리는 현재 절전 모드인 쿼리를 session_id 반환하지 않습니다.

SELECT STasks.session_id, SThreads.os_thread_id
FROM sys.dm_os_tasks AS STasks
INNER JOIN sys.dm_os_threads AS SThreads
    ON STasks.worker_address = SThreads.worker_address
WHERE STasks.session_id IS NOT NULL
ORDER BY STasks.session_id;
GO

관련 콘텐츠

• sys.dm_tran_database_transactions(Transact-SQL)
• 트랜잭션 잠금 및 행 버전 관리 지침
• 시스템 동적 관리 뷰
• 트랜잭션 관련 동적 관리 뷰 및 함수(Transact-SQL)
• SQL Server, Locks 개체