sys.dm_tran_locks (Transact-SQL)

適用対象: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) Microsoft Fabric のウェアハウス

SQL Server で現在アクティブなロック マネージャー リソースに関する情報を返します。 各行は、ロック マネージャーに対して現在アクティブになっている要求を示しています。この要求は、許可されたロックまたは許可を待機しているロックに対するものです。

結果セットの列は、リソースと要求の 2 つの主要グループに分けられます。 リソース グループは、ロック要求が出されているリソースを示し、要求グループはロック要求を示します。

アクセス許可

SQL Server と SQL Managed Instance では、VIEW SERVER STATE アクセス許可が必要です。

SQL Database の Basic、S0、S1 サービス対象、および Elastic Pool のデータベースの場合、サーバー管理者アカウント、Microsoft Entra 管理者アカウント、または ##MS_ServerStateReader##サーバー ロールのメンバーシップが必要です。 他のすべての SQL Database サービス目標では、データベースに対する VIEW DATABASE STATE アクセス許可または ##MS_ServerStateReader## サーバー ロールのメンバーシップのいずれかが必要です。

SQL Server 2022 以降でのアクセス許可

サーバーに対する VIEW SERVER PERFORMANCE STATE アクセス許可が必要です。

解説

要求が許可された状態とは、要求元に対してリソースのロックが許可されたことを示します。 要求を待機している状態とは、その要求がまだ許可されていないことを示します。 次の待機要求の種類は、 request_status 列によって返されます。

• 要求変換の状態とは、要求元がリソース要求を既に許可されており、最初の要求からさらに上の段階の許可を現在待機中であることを示します。

• 要求待機の状態とは、要求元が現在、リソースに対して許可された要求を保持していないことを示します。

sys.dm_tran_locksは内部ロック マネージャーのデータ構造から設定されるため、この情報を維持しても、通常の処理に余分なオーバーヘッドは発生しません。 ビューを具現化するには、ロック マネージャーの内部データ構造にアクセスする必要があります。 これによって、サーバーでの通常の処理にわずかな影響が生じることがありますが、 このような影響は重要ではなく、頻繁に使用されるリソースのみに影響します。 このビューのデータはアクティブなロック マネージャーの状態に対応しているので、データはいつでも変更される可能性があります。ロックが取得または解放されるときに、行が追加または削除されます。 このビューのクエリを実行するアプリケーションでは、ロック マネージャー構造の整合性を保護する性質のため、予期しないパフォーマンスが発生する可能性があります。 このビューには、履歴情報はありません。

2 つの要求は、すべてのリソース グループの行が等しい場合のみ、同じリソースに実行されます。

次のツールを使用すると、読み取り操作のロックを制御できます。

• SET TRANSACTION ISOLATION LEVEL を使用すると、セッションに対するロックのレベルを指定できます。 詳細については、「SET TRANSACTION ISOLATION LEVEL (Transact-SQL)」を参照してください。

• テーブル ヒントをロックすると、FROM 句内にあるテーブルの個別の参照に対してロックのレベルを指定できます。 構文と制限については、「テーブル ヒント (Transact-SQL)」をご覧ください。

1 つの session_id で実行されているリソースに、複数のロックが付与されている場合があります。 1 つのセッションで実行されている異なるエンティティは、それぞれ同じリソースに対するロックを所有でき、sys.dm_tran_locksによって返されるrequest_owner_type列とrequest_owner_id列に情報が表示されます。 同じ request_owner_type の複数のインスタンスが存在する場合は、各インスタンスを区別するために request_owner_id 列が使用されます。 分散トランザクションの場合、 request_owner_type 列と request_owner_guid 列には、さまざまなエンティティ情報が表示されます。

たとえば、セッション S1 は Table1の共有ロックを所有し、セッション S1 で実行されているトランザクション T1 は、 Table1の共有ロックも所有します。 この場合、sys.dm_tran_locksによって返されるresource_description列には、同じリソースの 2 つのインスタンスが表示されます。 request_owner_type列には、1 つのインスタンスがセッションとして、もう 1 つのインスタンスがトランザクションとして表示されます。 また、 resource_owner_id 列の値も異なります。

1 つのセッションで実行する複数のカーソルは区別できないため、1 つのエンティティとして扱われます。

session_id値に関連付けられていない分散トランザクションは孤立したトランザクションであり、-2のsession_id値が割り当てられます。 詳細については、「KILL (Transact-SQL)」を参照してください。

Locks

ロックは、複数のトランザクションで同じ 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>の値は、sys.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. セッション情報をオペレーティング システム スレッドにリンクする

次の例では、 session_id を Windows スレッド 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 オブジェクト