sys.dm_tran_locks (Transact-SQL)

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL verwaltete Instanz Azure Synapse Analytics Platform System (PDW) Warehouse in Microsoft Fabric

Gibt Informationen zu derzeit aktiven Sperren-Manager-Ressourcen in SQL Server zurück. Jede Zeile stellt eine derzeit aktive Anforderung des Sperren-Managers für eine erteilte oder zu erteilende Sperre dar.

Die Spalten im Resultset sind in zwei Hauptgruppen unterteilt: Ressourcen und Anforderungen. Die Ressourcengruppe beschreibt die Ressource, für die die Sperranforderung erfolgt. Die Anforderungsgruppe beschreibt die Sperranforderung.

Berechtigungen

Für SQL Server und SQL Managed Instance ist die VIEW SERVER STATE-Berechtigung erforderlich.

Für die SQL-Datenbank-Ziele Basic, S0 und S1 sowie für Datenbanken in Pools für elastische Datenbanken ist das Konto des Serveradministrators oder des Microsoft Entra-Administratorkontos oder die Mitgliedschaft in der ##MS_ServerStateReader## Serverrolle erforderlich. Für alle anderen SQL-Datenbank-Dienstziele ist entweder die VIEW DATABASE STATE-Berechtigung für die Datenbank oder die Mitgliedschaft in der ##MS_ServerStateReader##-Serverrolle erforderlich.

Berechtigungen für SQL Server 2022 und höher

Erfordert die VIEW SERVER PERFORMANCE STATE-Berechtigung auf dem Server.

Bemerkungen

Der Anforderungsstatus GRANTED gibt an, dass dem Anforderer für eine Ressource eine Sperre erteilt wurde. Der Anforderungsstatus WAITING gibt an, dass die Anforderung noch nicht erteilt wurde. Die folgenden Anforderungstypen werden von der request_status Spalte zurückgegeben:

• Der Anforderungsstatus CONVERT gibt an, dass dem Anforderer bereits eine Anforderung für die Ressource erteilt wurde und dass er derzeit darauf wartet, dass ein Upgrade für die ursprüngliche Anforderung erteilt wird.

• Der Anforderungsstatus WAIT gibt an, dass der Anforderer derzeit keine erteilte Anforderung für die Ressource besitzt.

Da sys.dm_tran_locks diese Datenstrukturen aus internen Sperr-Manager-Datenstrukturen aufgefüllt werden, wird durch die Aufrechterhaltung dieser Informationen kein zusätzlicher Aufwand für die normale Verarbeitung hinzugefügt. Durch das Materialisieren der Sicht ist der Zugriff auf die internen Strukturen des Sperren-Managers erforderlich. Dies kann die reguläre Verarbeitung auf dem Server geringfügig beeinträchtigen. Diese Auswirkungen sollten nicht bemerkbar sein und sollten nur stark ausgelastete Ressourcen betreffen. Die Daten in dieser Sicht entsprechen dem Livestatus des Sperren-Managers. Deshalb können sie jederzeit geändert werden, und Zeilen werden hinzugefügt und entfernt, wenn Sperren eingerichtet und aufgehoben werden. Anwendungen, die diese Sicht abfragen, stellen aufgrund der Art des Integritätsschutzes für Sperr-Manager-Strukturen möglicherweise eine unvorhersehbare Leistung fest. Diese Sicht weist keine Verlaufsinformationen auf.

Zwei Anforderungen verwenden nur dann dieselbe Ressource, wenn alle Ressourcengruppenspalten identisch sind.

Sie können das Sperren von Lesevorgängen mit folgenden Tools steuern:

• SET TRANSACTION ISOLATION LEVEL, um die Ebene von Sperren für eine Sitzung anzugeben. Weitere Informationen finden Sie unter TRANSAKTIONSISOLATIONSSTUFE EINSTELLEN (Transact-SQL).

• Sperren von Tabellenhinweisen, um die Ebene von Sperren für einen einzelnen Verweis einer Tabelle in einer FROM-Klausel anzugeben. Syntax und Einschränkungen finden Sie unter Tabellenhinweise (Transact-SQL).

Eine Ressource, die unter einer session_id ausgeführt wird, kann mehr als eine gewährte Sperre aufweisen. Unterschiedliche Entitäten, die unter einer Sitzung ausgeführt werden, können jeweils eine Sperre für dieselbe Ressource besitzen, und die Informationen werden in den request_owner_type von ihnen zurückgegebenen sys.dm_tran_locksSpalten angezeigtrequest_owner_id. Wenn mehrere Instanzen desselben request_owner_type vorhanden sind, wird die request_owner_id Spalte verwendet, um jede Instanz zu unterscheiden. Bei verteilten Transaktionen zeigen die Spalten request_owner_guid die request_owner_type verschiedenen Entitätsinformationen an.

Beispielsweise besitzt Session S1 eine freigegebene Sperre Table1; und Transaktion T1, die unter Sitzung S1 ausgeführt wird, besitzt auch eine freigegebene Sperre Table1. In diesem Fall zeigt die resource_description Spalte, die von sys.dm_tran_locks zwei Instanzen derselben Ressource zurückgegeben wird. In der request_owner_type Spalte wird eine Instanz als Sitzung und die andere als Transaktion angezeigt. Außerdem weist die resource_owner_id Spalte unterschiedliche Werte auf.

Mehrere Cursor, die unter einer Sitzung ausgeführt werden, können nicht unterschieden werden und werden als eine Einheit behandelt.

Verteilte Transaktionen, die keinem session_id Wert zugeordnet sind, sind verwaiste Transaktionen und werden dem session_id Wert zugewiesen -2. Weitere Informationen finden Sie unter KILL (Transact-SQL).

Locks

SQL Server -Ressourcen, wie etwa Zeilen, die während einer Transaktion gelesen oder geändert werden, werden mit Sperren belegt, um die zeitgleiche Verwendung der Ressourcen durch verschiedene Transaktionen zu verhindern. Wenn beispielsweise eine Zeile in einer Tabelle von einer Transaktion mit einer exklusiven Sperre (X) belegt wird, kann diese Zeile erst dann von einer anderen Transaktion geändert werden, wenn die Sperre aufgehoben wird. Durch die Reduzierung der Anzahl von Sperren kann die Parallelität erhöht werden, wodurch sich die Leistung verbessert.

Ressourcendetails

In der folgenden Tabelle sind die Ressourcen aufgeführt, die in der resource_associated_entity_id Spalte dargestellt werden.

In der folgenden Tabelle sind die Untertypen für jeden Ressourcentyp aufgelistet.

Die folgende Tabelle enthält das Format der resource_description Spalte für jeden Ressourcentyp.

Beispiele

A. Verwenden von sys.dm_tran_locks mit anderen Tools

Im folgenden Beispiel wird ein Updatevorgang durch eine andere Transaktion blockiert. sys.dm_tran_locks Mithilfe von und anderen Tools werden Informationen zum Sperren von Ressourcen bereitgestellt.

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;

Die folgende Abfrage zeigt Sperrinformationen an. Der Wert für <dbid> den Wert sollte durch den database_id Wert von .sys.databases

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>;

Die folgende Abfrage gibt Objektinformationen mithilfe des Wertes von resource_associated_entity_id aus der vorherigen Abfrage zurück. Die Abfrage muss ausgeführt werden, während Sie mit der Datenbank verbunden sind, die das Objekt enthält.

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

Die folgende Abfrage zeigt Blockierungsinformationen.

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;

Die Ressourcen werden freigegeben, indem ein Rollback für die Transaktionen ausgeführt wird.

-- Session 1
ROLLBACK;
GO

-- Session 2
ROLLBACK;
GO

B. Verknüpfen von Sitzungsinformationen mit Betriebssystemthreads

Im folgenden Beispiel werden Informationen zurückgegeben, die einer session_id Windows-Thread-ID zugeordnet werden. Die Leistung des Threads kann im Windows-Systemmonitor überwacht werden. Diese Abfrage gibt keinen Ruhezustand zurück 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

Zugehöriger Inhalt

• sys.dm_tran_database_transactions (Transact-SQL)
• Handbuch zu Transaktionssperren und Zeilenversionsverwaltung
• Dynamische Systemverwaltungssichten
• Dynamische Verwaltungssichten und -funktionen im Zusammenhang mit Transaktionen (Transact-SQL)
• SQL Server- und Locks-Objekt