sys.dm_tran_locks (Transact-SQL)

Si applica a: SQL Server database SQL di Azure Istanza gestita di SQL di Azure Azure Synapse Analytics Analytics Platform System (PDW) Warehouse in Microsoft Fabric

Restituisce informazioni sulle risorse di Gestione blocchi attualmente attive in SQL Server. Ogni riga rappresenta una richiesta attualmente attiva di un blocco concesso o in attesa di essere concesso, effettuata a Gestione blocchi.

Le colonne nel set di risultati sono divise in due gruppi principali: risorsa e richiesta. Nel gruppo relativo alle risorse viene descritta la risorsa per cui viene effettuata la richiesta, mentre nel gruppo relativo alle richieste viene descritta la richiesta di blocco.

Autorizzazioni

In SQL Server e Istanza gestita di SQL è richiesta l'autorizzazione VIEW SERVER STATE.

Negli obiettivi del servizio Basic del Database SQL, S0 e S1 e per i database nei pool elastici, è richiesto l'account amministratore del server, l'account amministratore di Microsoft Entra o l’adesione nel ##MS_ServerStateReader## al ruolo del server. Per tutti gli altri obiettivi di servizio database SQL, è necessaria l'autorizzazione VIEW DATABASE STATE per il database o l'adesione ruolo del server ##MS_ServerStateReader##.

Autorizzazioni per SQL Server 2022 e versioni successive

È richiesta l'autorizzazione VIEW SERVER PERFORMANCE STATE per il server.

Osservazioni:

Uno stato di richiesta concessa indica che un blocco è stato concesso per una risorsa al richiedente. Una richiesta in attesa indica che la richiesta non è stata ancora concessa. I tipi di richiesta in attesa seguenti vengono restituiti dalla request_status colonna :

• Uno stato di richiesta di conversione indica che il richiedente ha già ottenuto una richiesta per la risorsa ed è attualmente in attesa di un aggiornamento alla richiesta iniziale.

• Uno stato di richiesta in attesa indica che il richiedente non possiede una richiesta concessa per la risorsa.

Poiché sys.dm_tran_locks viene popolato da strutture di dati di gestione blocchi interne, la gestione di queste informazioni non comporta un sovraccarico aggiuntivo per l'elaborazione regolare. Per la materializzazione di questa vista è richiesto l'accesso alle strutture di dati interne di Gestione blocchi. Ciò può produrre effetti minimi sulla normale elaborazione nel server, che interesseranno solo le risorse con carichi elevati e non saranno altrimenti rilevati. Poiché i dati nella vista corrispondono allo stato di Gestione blocchi in tempo reale, tali dati sono soggetti a modifiche in qualsiasi momento e le righe vengono aggiunte e rimosse con l'acquisizione e il rilascio di blocchi. Le applicazioni che eseguono query su questa vista potrebbero riscontrare prestazioni imprevedibili a causa della natura della protezione dell'integrità delle strutture di gestione blocchi. La vista non contiene informazioni cronologiche.

Due richieste operano sulla stessa risorsa solo se tutte le colonne del gruppo relativo alle risorse sono uguali.

È possibile controllare il blocco delle operazioni di lettura tramite gli strumenti seguenti:

• SET TRANSACTION ISOLATION LEVEL per specificare il livello di blocco per una sessione. Per ulteriori informazioni, vedere SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

• Hint di tabelle di blocco per specificare il livello di blocco per un riferimento individuale di una tabella in una clausola FROM. Per la sintassi e le restrizioni, vedere Hint di tabella (Transact-SQL).

Una risorsa in esecuzione in uno session_id può avere più di un blocco concesso. Entità diverse in esecuzione in una sessione possono essere ognuna proprietaria di un blocco sulla stessa risorsa e le informazioni vengono visualizzate nelle request_owner_type colonne e request_owner_id restituite da sys.dm_tran_locks. Se esistono più istanze della stessa request_owner_type istanza, la request_owner_id colonna viene usata per distinguere ogni istanza. Per le transazioni distribuite, le request_owner_type colonne e request_owner_guid mostrano le diverse informazioni sull'entità.

Ad esempio, Session S1 è proprietario di un blocco condiviso su Table1; e la transazione T1, in esecuzione nella sessione S1, possiede anche un blocco condiviso su Table1. In questo caso, la resource_description colonna restituita da sys.dm_tran_locks mostra due istanze della stessa risorsa. La request_owner_type colonna mostra un'istanza come sessione e l'altra come transazione. Inoltre, la resource_owner_id colonna ha valori diversi.

Più cursori in esecuzione in una sessione non sono distinguibili e vengono considerati come un'unica entità.

Le transazioni distribuite non associate a un session_id valore sono transazioni orfane e a cui viene assegnato il session_id valore di -2. Per altre informazioni, vedere KILL (Transact-SQL).

Locks

I blocchi vengono mantenuti nelle risorse di SQL Server, ad esempio righe lette o modificate durante una transazione, per impedire l'uso simultaneo di risorse da transazioni diverse. Ad esempio, se una transazione mantiene attivo un blocco esclusivo (X) su una riga all'interno di una tabella, nessun'altra transazione potrà modificare la riga fino a quando il blocco non viene rilasciato. La riduzione dei blocchi aumenta la concorrenza e, di conseguenza, potrebbe migliorare le prestazioni.

Dettagli della risorsa

Nella tabella seguente sono elencate le risorse rappresentate nella resource_associated_entity_id colonna .

Nella tabella seguente sono elencati i sottotipi associati a ogni tipo di risorsa.

Nella tabella seguente viene fornito il formato della resource_description colonna per ogni tipo di risorsa.

Esempi

R. Usare sys.dm_tran_locks con altri strumenti

L'esempio seguente illustra uno scenario in cui un'operazione di aggiornamento è bloccata da un'altra transazione. sys.dm_tran_locks Usando e altri strumenti, vengono fornite informazioni sul blocco delle risorse.

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;

Nella query seguente vengono visualizzate le informazioni di blocco. Il valore per <dbid> deve essere sostituito con da 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 query seguente restituisce informazioni sugli oggetti utilizzando resource_associated_entity_id dalla query precedente. Questa query deve essere eseguita durante la connessione al database contenente l'oggetto.

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

La query seguente mostra le informazioni di blocco.

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;

Rilasciare le risorse eseguendo il rollback delle transazioni.

-- Session 1
ROLLBACK;
GO

-- Session 2
ROLLBACK;
GO

B. Collegare le informazioni della sessione ai thread del sistema operativo

Nell'esempio seguente vengono restituite informazioni che associano un oggetto session_id a un ID thread di Windows. È possibile utilizzare Performance Monitor di Windows per monitorare le prestazioni del thread. Questa query non restituisce un oggetto session_id attualmente in sospensione.

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

Contenuto correlato

• sys.dm_tran_database_transactions (Transact-SQL)
• Guida per il controllo delle versioni delle righe e il blocco delle transazioni
• Viste a gestione dinamica (DMV) di sistema
• Funzioni e viste DMV relative alle transazioni (Transact-SQL)
• Oggetto Locks di SQL Server