sys.dm_tran_locks (Transact-SQL)

S’applique à : SQL Server (toutes les versions prises en charge) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Parallel Data Warehouse

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, requiert l' VIEW SERVER STATE autorisation.

sur SQL Database objectifs de service de base, S0 et S1 , et pour les bases de données dans des pools élastiques, le compte d' administrateur de serveur , le compte d' administrateur Azure Active Directory ou l’appartenance au ##MS_ServerStateReader## rôle de serveur est requis. pour tous les autres SQL Database objectifs de service, l' VIEW DATABASE STATE autorisation sur la base de données ou l’appartenance au ##MS_ServerStateReader## rôle de serveur est requise.

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. La colonne request_status retourne les types de demandes en attente suivants :

• 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é que sys.dm_tran_locks est remplie avec des structures de données internes du gestionnaire de verrous, la conservation de ces informations ne génère pas de charge supplémentaire dans le 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. 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 d'une session. Pour plus d’informations, consultez SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

• Conseils de verrouillage des tables pour spécifier le niveau de verrouillage de la référence d'une table dans une clause FROM. pour connaître la syntaxe et les restrictions, consultez indicateurs de Table ()Transact-SQL .

Une ressource qui est exécutée sous un ID de session peut comporter plusieurs verrous autorisés. Différentes entités s'exécutant dans une session peuvent avoir chacune un verrou sur la même ressource ; les informations sont affichées dans les colonnes request_owner_type et request_owner_id retournées par sys.dm_tran_locks. S'il existe plusieurs instances pour un même request_owner_type, la colonne request_owner_id est utilisée pour les distinguer. Pour les transactions distribuées, les colonnes request_owner_type et request_owner_guid affichent des informations sur les différentes entités.

Par exemple, la Session S1 possède un verrou partagé sur la Table1 ; et une transaction T1 exécutée dans la session S1 possède également un verrou partagé sur la Table1. Dans ce cas, la colonne resource_description retournée par sys.dm_tran_locks affiche deux instances de la même ressource. La colonne request_owner_type affiche une instance comme une session et l'autre comme une transaction. Les valeurs de la colonne resource_owner_id sont également 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 non associées à un ID de session sont des transactions orphelines. L'ID de session -2 leur est attribué. 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 des ressources

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

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

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

Les XEvents suivants sont liés au commutateur de partition et à la reconstruction d’index en ligne. pour plus d’informations sur la syntaxe, consultez alter TABLE (transact-SQL) et alter INDEX (transact-SQL).

• lock_request_priority_state

• process_killed_by_abort_blockers

• ddl_with_wait_at_low_priority

Le progress_report_online_index_operation existant XEvent pour les opérations d’index en ligne a été étendu en ajoutant des partition_number et des partition_id.

Exemples

R. Utilisation de 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. sys.dm_tran_locks et d'autres outils fournissent des informations sur les ressources de verrouillage.

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 des informations sur les verrous. La valeur de <dbid> doit être remplacée par la valeur database_id de 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 affiche des informations de blocage.

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. Liaison des informations de session avec des threads du système d'exploitation

L'exemple suivant retourne des informations qui associent un ID de session à l'ID d'un thread Windows. Il est possible de surveiller les performances d'un thread dans l'Analyseur de performances Windows. Cette requête ne retourne pas des ID de sessions 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  

Voir aussi

sys.dm_tran_database_transactions (Transact-SQL)
Fonctions et vues de gestion dynamique (Transact-SQL)
fonctions et vues de gestion dynamique liées aux transactions (Transact-SQL)
SQL Server, objet Locks