sys.dm_tran_locks (Transact-SQL)

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Entrepôt dans Microsoft FabricBase de données SQL dans Microsoft Fabric

Retourne des informations sur les ressources actives du gestionnaire de verrous dans SQL Server. Chaque ligne représente une demande active au gestionnaire de verrous pour un verrou autorisé ou en attente d'autorisation.

Les colonnes du jeu de résultats sont réparties en deux groupes principaux : ressource et demande. Le groupe ressource décrit la ressource sur laquelle la demande de verrou a lieu ; le groupe demande décrit la demande de verrou.

Autorisations

Sur SQL Server et SQL Managed Instance, sys.dm_tran_locks cela nécessite VIEW SERVER STATE une autorisation.

Sur les objectifs de service SQL Database Basic, S0 et S1, et pour les bases de données dans les pools élastiques, le compte d’administration du serveur, le compte d’administration Microsoft Entra ou l’appartenance dans ##MS_ServerStateReader## au rôle de serveur est requis. Sur tous les autres objectifs de service SQL Database, l’autorisation VIEW DATABASE STATE sur la base de données ou l’appartenance au rôle serveur ##MS_ServerStateReader## est requise.

Dans SQL Analytics endpoint et Warehouse dans Microsoft Fabric, l’appartenance au rôle Viewer workspace est la permission minimale nécessaire pour visualiser sys.dm_tran_locks.

Autorisations pour SQL Server 2022 (et versions plus récentes)

Dans SQL Server 2022 et versions ultérieures, il sys.dm_tran_locks faut VIEW SERVER PERFORMANCE STATE obtenir une autorisation sur le serveur.

Notes

Un état de demande autorisée indique qu'un verrou a été accordé au demandeur sur une ressource. Une demande en attente indique que la demande n'est pas encore autorisée. Les types de requêtes en attente suivants sont retournés par la request_status colonne :

• Une demande de conversion indique que le demandeur a déjà reçu l'autorisation pour la ressource et attend l'autorisation de mise à jour de la demande initiale.

• Une demande en attente indique que le demandeur n'a pas reçu l'autorisation pour la ressource.

Étant donné qu’elles sys.dm_tran_locks sont remplies à partir de structures de données du gestionnaire de verrous internes, la maintenance de ces informations n’ajoute pas de surcharge supplémentaire au traitement normal. La matérialisation de la vue ne nécessite pas l'accès aux structures des données internes du gestionnaire de verrous. Ceci minimise les effets sur le traitement normal du serveur. Ces effets doivent être imperceptibles et affecter uniquement les ressources utilisées intensivement. Du fait que les données de cette vue correspondent à l'état actif du gestionnaire de verrous, elles peuvent changer à tout moment ; des lignes sont ajoutées et supprimées lorsque des verrous sont acquis et libérés. Les applications qui interrogent cette vue peuvent rencontrer des performances imprévisibles en raison de la nature de la protection de l’intégrité des structures du gestionnaire de verrous. Cette vue ne comporte pas d'informations historiques.

Deux demandes agissent sur la même ressource uniquement si toutes les colonnes du groupe de ressources sont égales.

Vous pouvez contrôler le verrouillage des opérations de lecture à l'aide des outils suivants :

• SET TRANSACTION ISOLATION LEVEL pour spécifier le niveau de verrouillage pour une session. Pour plus d’informations, consultez SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

• La table de verrouillage suggère de spécifier le niveau de verrouillage pour une référence individuelle d’une table dans une FROM proposition. Pour connaître la syntaxe et les restrictions, consultez Indicateurs de table (Transact-SQL).

Une ressource en cours d’exécution sous un seul session_id peut avoir plusieurs verrous accordés. Différentes entités qui s’exécutent sous une session peuvent chacune posséder un verrou sur la même ressource, et les informations sont affichées dans les request_owner_type colonnes request_owner_id retournées par sys.dm_tran_locks. Si plusieurs instances de la même request_owner_type instance existent, la request_owner_id colonne est utilisée pour distinguer chaque instance. Pour les transactions distribuées, les request_owner_type colonnes et les request_owner_guid colonnes affichent les différentes informations d’entité.

Par exemple, session S1 possède un verrou partagé sur Table1; et la transaction T1, qui s’exécute sous session S1, possède également un verrou partagé sur Table1. Dans ce cas, la resource_description colonne retournée par sys.dm_tran_locks deux instances de la même ressource. La request_owner_type colonne affiche une instance en tant que session et l’autre en tant que transaction. En outre, la resource_owner_id colonne a des valeurs différentes.

Il n'est pas possible de distinguer plusieurs curseurs d'une même session qui sont traités comme une seule entité.

Les transactions distribuées qui ne sont pas associées à une session_id valeur sont des transactions orphelines et sont affectées à la session_id valeur de -2. Pour plus d’informations, consultez KILL (Transact-SQL).

Verrous

Des verrous sont placés sur les ressources SQL Server , telles que les lignes lues ou modifiées lors d'une transaction, pour empêcher d'autres transactions d'utiliser simultanément les ressources. Par exemple, si un verrou exclusif (X) est mis en place dans une ligne de table par une transaction, aucune autre transaction ne peut modifier cette ligne jusqu'à ce que le verrou soit débloqué. Un nombre minimal de verrous favorise la concurrence, ce qui peut améliorer les performances.

Détails de la ressource

Le tableau suivant répertorie les ressources représentées dans la resource_associated_entity_id colonne.

Le tableau suivant répertorie les sous-types qui sont associés à chaque type de ressource.

Le tableau suivant fournit le format de la resource_description colonne pour chaque type de ressource.

Exemples

R. Utiliser sys.dm_tran_locks avec d’autres outils

L'exemple suivant fonctionne dans un scénario dans lequel une mise à jour est bloquée par une autre transaction. En utilisant sys.dm_tran_locks et d’autres outils, des informations sur le verrouillage des ressources sont fournies.

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;

La requête suivante affiche les informations de verrouillage. La valeur pour <dbid> laquelle doit être remplacée par la valeur de database_id.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>;

La requête suivante retourne des informations d'objet à l'aide de la valeur resource_associated_entity_id issue de la requête précédente. Cette requête doit être exécutée alors que vous êtes connecté à la base de données qui contient l'objet.

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

La requête suivante montre les informations bloquantes.

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;

Libérez les ressources en annulant les transactions.

-- Session 1
ROLLBACK;
GO

-- Session 2
ROLLBACK;
GO

B. Lier des informations de session aux threads du système d’exploitation

L’exemple suivant retourne des informations qui associent un session_id ID de thread Windows. Il est possible de surveiller les performances d'un thread dans l'Analyseur de performances Windows. Cette requête ne retourne pas une session_id requête actuellement en veille.

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

Contenu connexe

• sys.dm_tran_database_transactions (Transact-SQL)
• Guide du verrouillage des transactions et du versioning des lignes
• Vues de gestion dynamique système
• Fonctions et vues de gestion dynamique relatives aux transactions (Transact-SQL)
• SQL Server, objet Locks