Guida ai deadlock

Si applica a:SQL Serverdatabase SQL di AzureIstanza gestita di SQL di AzureAzure Synapse AnalyticsPiattaforma di strumenti analitici (PDW)

Questo articolo illustra in modo approfondito i deadlock nel motore di database di SQL Server. I deadlock sono generati da blocchi concorrenti e simultanei nel database, spesso presenti in transazioni con più passaggi. Per altre informazioni sui blocchi delle transazioni, vedere Guida ai blocchi delle transazioni e al controllo delle versioni delle righe.

Per informazioni più specifiche sull'identificazione e la prevenzione dei deadlock nel database SQL di Azure, vedere la sezione Analizzare e prevenire deadlock nel database SQL di Azure.

Riconoscere i deadlock

Un deadlock si verifica quando due o più task si bloccano reciprocamente in modo permanente, in quanto ognuno dei task prevede un blocco su una risorsa che gli altri task stanno cercando di bloccare. Ad esempio:

• La transazione A acquisisce un blocco condiviso sulla riga 1.
• La transazione B acquisisce un blocco condiviso sulla riga 2.
• La transazione A richiede ora un blocco esclusivo sulla riga 2 ed è bloccata fino al completamento della transazione B e del relativo rilascio del blocco condiviso sulla riga 2.
• La transazione B richiede ora un blocco esclusivo sulla riga 1 ed è bloccata fino al completamento della transazione A e del relativo rilascio del blocco condiviso sulla riga 1.

La transazione A non può essere completata fino al completamento della transazione B, ma la transazione B è bloccata dalla transazione A. Questa condizione viene anche denominata dipendenza ciclica: la transazione A è dipendente dalla transazione B e la transazione B chiude il cerchio con una dipendenza rispetto alla transazione A.

Entrambe le transazioni in un deadlock restano in attesa per sempre fino a quando il deadlock non viene interrotto da un processo esterno. La funzionalità di monitoraggio dei deadlock del motore di database di SQL Server ricerca periodicamente i task interessati da un deadlock. Se viene rilevata una dipendenza ciclica, una delle attività viene scelta come vittima e le relative transazioni vengono terminate con un errore. In questo modo l'altra attività potrà completare la propria transazione. L'applicazione la cui transazione è stata terminata con un errore può eseguire un nuovo tentativo di transazione, che viene in genere completato al termine dell'altra transazione bloccata dal deadlock.

Spesso la condizione di deadlock viene confusa con il blocco normale. Quando una transazione richiede un blocco in una risorsa bloccata da un'altra transazione, la transazione che ha eseguito la richiesta resta in attesa fino quando il blocco non viene rilasciato. Per impostazione predefinita, le transazioni di SQL Server non prevedono timeout, a meno che non sia stata impostata l'opzione LOCK_TIMEOUT. La transazione che ha eseguito la richiesta viene bloccata, ma non tramite un deadlock, in quanto non ha tentato di bloccare la transazione proprietaria del blocco. La transazione proprietaria del blocco completa e rilascia il blocco e quindi il blocco viene assegnato alla transazione che ha eseguito la richiesta, che può procedere. I deadlock vengono risolti quasi immediatamente, mentre il bloccaggio può, in teoria, rimanere indefinito. I deadlock sono a volte definiti anche blocchi critici.

Un deadlock si verifica in qualsiasi sistema con più thread e non soltanto in un sistema di gestione di database relazionali, e può interessare anche risorse diverse dai blocchi negli oggetti di database. Un thread, ad esempio, in un sistema operativo a thread multipli può acquisire una o più risorse, ad esempio blocchi di memoria. Se la risorsa che viene acquisita è già utilizzata da un altro thread, il primo thread deve aspettare che la risorsa target venga rilasciata dal thread di appartenenza. Il thread in attesa è considerato dipendente dal thread proprietario della risorsa richiesta. In un'istanza del motore di database di SQL Server le sessioni possono causare un deadlock durante l'acquisizione di risorse non di database, ad esempio la memoria o i thread.

Nell'illustrazione la transazione T1 è dipendente dalla transazione T2 per la risorsa di blocco della tabella Part. In modo analogo, la transazione T2 presenta una dipendenza dalla transazione T1 per la risorsa di blocco della tabella Supplier. Poiché queste dipendenze creano un ciclo, si verifica un deadlock tra le transazioni T1 e T2.

I deadlock possono verificarsi anche quando una tabella è partizionata e l'impostazione LOCK_ESCALATION di ALTER TABLE è impostata su AUTO. Quando LOCK_ESCALATION è impostato su AUTO, la concorrenza aumenta consentendo al motore di database di SQL Server di bloccare le partizioni della tabella a livello HoBT anziché a livello di tabella. Tuttavia, quando transazioni separate contengono blocchi di partizioni in una tabella e richiedono un blocco in un punto nella partizione delle altre transazioni, si verifica un deadlock. Questo tipo di deadlock può essere evitato impostando LOCK_ESCALATION su TABLE. Tuttavia, questa impostazione ridurrà la concorrenza forzando aggiornamenti di grandi dimensioni a una partizione attendere un blocco di tabella.

Rilevare e interrompere i deadlock

Un deadlock si verifica quando due o più task si bloccano reciprocamente in modo permanente, in quanto ognuno dei task prevede un blocco su una risorsa che gli altri task stanno cercando di bloccare. Nel grafico seguente viene illustrata una vista di alto livello di uno stato di deadlock in cui:

• L'attività T1 prevede un blocco sulla risorsa R1, indicato dalla freccia da R1 a T1, e ha richiesto un blocco sulla risorsa R2, indicato dalla freccia da T1 a R2.
• L'attività T2 prevede un blocco sulla risorsa R2, indicato dalla freccia da R2 a T2, e ha richiesto un blocco sulla risorsa R1, indicato dalla freccia da T2 a R1.
• Poiché nessuna attività può continuare fino a quando una risorsa diventa disponibile e nessuna risorsa può essere rilasciata fino a quando l'attività continua, si verifica una stato di deadlock.

Il motore di database di SQL Server rileva automaticamente i cicli di deadlock all'interno di SQL Server. Il motore di database di SQL Server sceglie una delle sessioni come vittima del deadlock e per interrompere il deadlock la transazione attuale viene terminata con un errore.

Risorse che possono generare un deadlock

Per conto di ogni sessione utente possono essere in esecuzione una o più attività e ogni attività può acquisire o attendere di acquisire risorse diverse. Di seguito sono elencati i tipi di risorse che possono causare blocchi che potrebbero provocare un deadlock.

• Blocchi. L'attesa di acquisizione di blocchi sulle risorse, ad esempio oggetti, pagine, righe, metadati e applicazioni, può generare un deadlock. La transazione T1, ad esempio, prevede un blocco condiviso (S) sulla riga r1 ed è in attesa di ottenere un blocco esclusivo (X) su r2. La transazione T2 prevede un blocco condiviso (S) su r2 ed è in attesa di ottenere un blocco esclusivo (X) sulla riga r1. Il risultato è un ciclo di blocco in cui T1 e T2 attendono che le risorse bloccate vengano rilasciate dall'altra attività.

• Thread di lavoro. Un'attività in coda in attesa di un thread di lavoro disponibile può generare un deadlock. Se l'attività in coda è proprietaria di risorse che bloccano tutti i thread di lavoro, si verifica un deadlock. La sessione S1 avvia ad esempio una transazione e acquisisce un blocco condiviso (S) sulla riga r1 e quindi va in sospensione. Le sessioni attive in esecuzione in tutti i thread di lavoro disponibili cercano di acquisire blocchi esclusivi (X) sulla riga r1. Poiché la sessione S1 non riesce ad acquisire un thread di lavoro, non può eseguire la transazione e rilasciare il blocco sulla riga r1. Di conseguenza, si verifica un deadlock.

• Memoria. Quando richieste simultanee sono in attesa di concessioni di memoria che non possono essere soddisfatte con la memoria disponibile, può verificarsi un deadlock. Due query simultanee, Q1 e Q2, vengono ad esempio eseguite come funzioni definite dall'utente che acquisiscono rispettivamente 10 MB e 20 MB di memoria. Se per ogni query sono necessari 30 MB e la memoria disponibile totale è di 20 MB, Q1 e Q2 devono attendere che ognuna rilasci memoria e di conseguenza si verifica un deadlock.

• Risorse per l'esecuzione di query in parallelo. Thread coordinator, producer o utente associati a una porta di scambio possono bloccarsi a vicenda generando un deadlock, di solito quando almeno un altro processo che non fa parte della query parallela è incluso. Quando inoltre viene avviata l'esecuzione di una query parallela, SQL Server determina il grado di parallelismo o il numero di thread di lavoro sulla base del carico di lavoro attuale. Se il carico di lavoro del sistema cambia inaspettatamente, ad esempio per l'avvio di nuove query o per l'esaurimento dei thread di lavoro, è possibile che si verifichi un deadlock.

• Risorse MARS (Multiple Active Result Sets). Queste risorse sono utilizzate per controllare in che modo più richieste attive vengono intercalate con il servizio MARS. Per ulteriori informazioni. Per altre informazioni, vedere Uso di MARS (Multiple Active Result Set).

  • Risorsa utente. Quando un thread è in attesa di una risorsa che è potenzialmente controllata da un'applicazione utente, la risorsa viene considerata risorsa esterna o utente e viene trattata come un blocco.

  • Mutex della sessione. Le attività in esecuzione in una sessione vengono intercalate, ovvero solo un'attività può essere eseguita in una sessione in un determinato momento. Per poter essere eseguita, l'attività deve disporre di accesso esclusivo al mutex della sessione.

  • Mutex della transazione. Tutte le attività in esecuzione in una transazione vengono intercalate, ovvero solo un'attività può essere eseguita in una transazione in un determinato momento. Per poter essere eseguita, l'attività deve disporre di accesso esclusivo al mutex della transazione.

  Per essere eseguita in un servizio MARS, l'attività deve acquisire il mutex della sessione. Se l'attività è in esecuzione in una transazione, deve acquisire il mutex della transazione. Questo consente di garantire che sia attiva una sola attività per volta in una determinata sessione e in una determinata transazione. Dopo che i mutex richiesti sono stati acquisiti, l'attività può essere eseguita. Quando l'attività termine, oppure restituisce il risultato a metà della richiesta, viene rilasciato prima il mutex della transazione, seguito da quello della sessione, in ordine inverso rispetto a quello di acquisizione. In queste risorse, tuttavia, possono verificarsi deadlock. Il seguente esempio di pseudocodice mostra due task, richiesta utente U1 e richiesta utente U2, in esecuzione nella stessa sessione.

  U1:    Rs1=Command1.Execute("insert sometable EXEC usp_someproc");  
  U2:    Rs2=Command2.Execute("select colA from sometable");  
  

  La stored procedure in esecuzione dall'attività richiesta utente U1 ha acquisito il mutex della sessione. Se per l'esecuzione della stored procedure è necessario molto tempo, nel motore di database di SQL Server viene considerato che la stored procedure è in attesa dell'input dell'utente. La richiesta utente U2 è in attesa del mutex della sessione mentre l'utente è in attesa del set di risultati da U2 e U1 è in attesa di una risorsa utente. La rappresentazione logica di tale condizione di deadlock è la seguente:

  

Rilevamento di deadlock

Tutte le risorse elencate nella sezione precedente fanno parte dello schema di rilevamento dei deadlock di motore di database di SQL Server. Il rilevamento dei deadlock viene eseguito da un thread di monitoraggio dei blocchi tramite il quale viene periodicamente avviata una ricerca su tutti i task in un'istanza del motore di database di SQL Server. Il processo di ricerca è descritto dai punti seguenti:

• L'intervallo predefinito è 5 secondi.
• Se tramite il thread di monitoraggio dei blocchi vengono individuati deadlock, l'intervallo di rilevamento dei deadlock scende da 5 secondi a un minimo di 100 millisecondi, in base alla frequenza dei deadlock.
• Se tramite il thread di monitoraggio dei blocchi non vengono trovati ulteriori deadlock, nel motore di database di SQL Server gli intervalli tra le ricerche vengono aumentati a 5 secondi.
• Se viene rilevato un deadlock, si presuppone che i thread successivi che devono attendete un blocco stiano entrando nel ciclo di deadlock. La prima coppia di attese di blocco dopo il rilevamento di un deadlock genera immediatamente una ricerca di deadlock senza che venga atteso l'intervallo successivo di rilevamento dei deadlock. Se, ad esempio, l'intervallo attuale è di 5 secondi ed è appena stato rilevato un deadlock, l'attesa di blocco successiva provoca l'avvio immediato della funzionalità di rilevamento di deadlock. Se l'attesa di blocco fa parte di un deadlock, verrà rilevata immediatamente e non nel corso della ricerca di deadlock successiva.

In genere nel motore di database di SQL Server vengono eseguite solo attività di rilevamento di deadlock periodiche. Poiché il numero di deadlock di un sistema in genere è ridotto, il rilevamento periodico consente di ridurre l'overhead associato all'operazione di ricerca.

Dopo l'avvio di una ricerca di deadlock per un thread specifico, viene identificata la risorsa di cui il thread è in attesa e vengono individuati il proprietario o i proprietari della risorsa. La ricerca di deadlock viene quindi ripetuta in modo ricorsivo per gli stessi thread fino all'individuazione di un ciclo. Un ciclo identificato in questo modo crea un deadlock.

Dopo essere stato rilevato, un deadlock viene terminato dal motore di database di SQL Server scegliendo uno dei thread come vittima del deadlock. Tramite motore di database di SQL Server viene terminato il batch attualmente in esecuzione per il thread, viene eseguito il rollback della transazione della vittima del deadlock e all'applicazione viene restituito un errore 1205. Tramite il rollback della transazione per la vittima del deadlock vengono rilasciati tutti i blocchi della transazione. In questo modo, le transazioni degli altri thread vengono sbloccate e possono continuare. Tramite l'errore 1205 relativo alla vittima del deadlock vengono registrate nel log degli errori le informazioni sulle risorse e i thread coinvolti.

Per impostazione predefinita, tramite motore di database di SQL Server viene scelta come vittima del deadlock la sessione in cui è in esecuzione la transazione il cui rollback è meno costoso. In alternativa, è possibile specificare la priorità delle sessioni in una situazione di deadlock usando l'istruzione SET DEADLOCK_PRIORITY. Il valore di DEADLOCK_PRIORITY può essere impostato su LOW, NORMAL o HIGH oppure è possibile utilizzare qualsiasi valore intero compreso tra -10 e 10. L'impostazione predefinita per priorità di deadlock è NORMAL. Se le priorità di deadlock di due sessioni sono diverse, come vittima del deadlock verrà scelta la sessione con la priorità inferiore. Se entrambe le sessioni hanno la stessa priorità di deadlock, verrà scelta la sessione con la transazione il cui rollback è meno costoso. Se le sessioni coinvolte nel ciclo di deadlock hanno la stessa priorità di deadlock e lo stesso costo, la vittima viene scelta in modo casuale.

Quando si utilizza l'ambiente CLR, tramite la funzionalità di monitoraggio vengono automaticamente rilevati i deadlock per le risorse di sincronizzazione, ovvero monitor, blocchi di lettura/scrittura e join di thread, a cui viene eseguito l'accesso all'interno di procedure gestite. Il deadlock viene tuttavia risolto generando un'eccezione nella procedura selezionata come vittima del deadlock. È importante comprendere che l'eccezione non comporta il rilascio automatico delle risorse attualmente di proprietà della vittima, ma le risorse devono essere rilasciate esplicitamente. In modo coerente con il comportamento dell'eccezione, l'eccezione utilizzata per identificare una vittima del deadlock può essere intercettata e ignorata.

Strumenti di informazione sui deadlock

Per la visualizzazione di informazioni sui deadlock, il motore di database di SQL Server offre strumenti di monitoraggio costituiti dalla sessione xEvent system_health, da due flag di traccia e dall'evento Deadlock Graph in SQL Profiler.

Nota

Questa sezione contiene informazioni su eventi estesi, flag di traccia e tracce, tuttavia il metodo consigliato per l'acquisizione di informazioni sui deadlock è l'evento deadlock esteso.

Evento deadlock esteso

A partire da SQL Server 2012 (11.x), in Analisi SQL o SQL Profiler è consigliabile usare l'evento esteso (xEvent) xml_deadlock_report, anziché la classe di evento Deadlock Graph.

Anche a partire da SQL Server 2012 (11.x), quando si verificano deadlock, la sessione system_health acquisisce già tutti gli xEvent xml_deadlock_report che contengono il grafico del deadlock. Poiché la sessione system_health è attivata per impostazione predefinita, non è necessario configurare una sessione xEvent separata per l'acquisizione delle informazioni sul deadlock. Non sono necessarie altre azioni per acquisire informazioni sul deadlock con xEvent xml_deadlock_report.

L'evento Deadlock Graph acquisito include in genere tre nodi distinti:

• victim-list. Identificatore di processo della vittima del deadlock.
• process-list. Informazioni su tutti i processi coinvolti nel deadlock.
• resource-list. Informazioni sulle risorse coinvolte nel deadlock.

All'apertura del file o del buffer circolare della sessione system_health, se viene registrato xEvent xml_deadlock_report, Management Studio presenta una rappresentazione grafica dei task e delle risorse interessate da un deadlock, come illustrato nell'esempio seguente:

La query seguente può visualizzare tutti gli eventi di deadlock acquisiti dal buffer circolare della sessione system_health:

SELECT xdr.value('@timestamp', 'datetime') AS [Date],
    xdr.query('.') AS [Event_Data]
FROM (SELECT CAST([target_data] AS XML) AS Target_Data
            FROM sys.dm_xe_session_targets AS xt
            INNER JOIN sys.dm_xe_sessions AS xs ON xs.address = xt.event_session_address
            WHERE xs.name = N'system_health'
              AND xt.target_name = N'ring_buffer'
    ) AS XML_Data
CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xdr)
ORDER BY [Date] DESC;

Questo è il set di risultati.

L'esempio seguente illustra l'output dopo aver selezionato il primo collegamento del risultato precedente:

<event name="xml_deadlock_report" package="sqlserver" timestamp="2022-02-18T08:26:24.698Z">
  <data name="xml_report">
    <type name="xml" package="package0" />
    <value>
      <deadlock>
        <victim-list>
          <victimProcess id="process27b9b0b9848" />
        </victim-list>
        <process-list>
          <process id="process27b9b0b9848" taskpriority="0" logused="0" waitresource="KEY: 5:72057594214350848 (1a39e6095155)" waittime="1631" ownerId="11088595" transactionname="SELECT" lasttranstarted="2022-02-18T00:26:23.073" XDES="0x27b9f79fac0" lockMode="S" schedulerid="9" kpid="15336" status="suspended" spid="62" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2022-02-18T00:26:22.893" lastbatchcompleted="2022-02-18T00:26:22.890" lastattention="1900-01-01T00:00:00.890" clientapp="SQLCMD" hostname="ContosoServer" hostpid="7908" loginname="CONTOSO\user" isolationlevel="read committed (2)" xactid="11088595" currentdb="5" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
            <executionStack>
              <frame procname="AdventureWorks2022.dbo.p1" line="3" stmtstart="78" stmtend="180" sqlhandle="0x0300050020766505ca3e07008ba8000001000000000000000000000000000000000000000000000000000000">
SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+    </frame>
              <frame procname="adhoc" line="4" stmtstart="82" stmtend="98" sqlhandle="0x020000006263ec01ebb919c335024a072a2699958d3fcce60000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
SET NOCOUNT ON
WHILE (1=1) 
BEGIN
    EXEC p1 4
END
   </inputbuf>
          </process>
          <process id="process27b9ee33c28" taskpriority="0" logused="252" waitresource="KEY: 5:72057594214416384 (e5b3d7e750dd)" waittime="1631" ownerId="11088593" transactionname="UPDATE" lasttranstarted="2022-02-18T00:26:23.073" XDES="0x27ba15a4490" lockMode="X" schedulerid="6" kpid="5584" status="suspended" spid="58" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-02-18T00:26:22.890" lastbatchcompleted="2022-02-18T00:26:22.890" lastattention="1900-01-01T00:00:00.890" clientapp="SQLCMD" hostname="ContosoServer" hostpid="15316" loginname="CONTOSO\user" isolationlevel="read committed (2)" xactid="11088593" currentdb="5" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
            <executionStack>
              <frame procname="AdventureWorks2022.dbo.p2" line="3" stmtstart="76" stmtend="150" sqlhandle="0x03000500599a5906ce3e07008ba8000001000000000000000000000000000000000000000000000000000000">
UPDATE t1 SET c2 = c2+1 WHERE c1 = @p    </frame>
              <frame procname="adhoc" line="4" stmtstart="82" stmtend="98" sqlhandle="0x02000000008fe521e5fb1099410048c5743ff7da04b2047b0000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
SET NOCOUNT ON
WHILE (1=1) 
BEGIN
    EXEC p2 4
END
   </inputbuf>
          </process>
        </process-list>
        <resource-list>
          <keylock hobtid="72057594214350848" dbid="5" objectname="AdventureWorks2022.dbo.t1" indexname="cidx" id="lock27b9dd26a00" mode="X" associatedObjectId="72057594214350848">
            <owner-list>
              <owner id="process27b9ee33c28" mode="X" />
            </owner-list>
            <waiter-list>
              <waiter id="process27b9b0b9848" mode="S" requestType="wait" />
            </waiter-list>
          </keylock>
          <keylock hobtid="72057594214416384" dbid="5" objectname="AdventureWorks2022.dbo.t1" indexname="idx1" id="lock27afa392600" mode="S" associatedObjectId="72057594214416384">
            <owner-list>
              <owner id="process27b9b0b9848" mode="S" />
            </owner-list>
            <waiter-list>
              <waiter id="process27b9ee33c28" mode="X" requestType="wait" />
            </waiter-list>
          </keylock>
        </resource-list>
      </deadlock>
    </value>
  </data>
</event>

Per altre informazioni, vedere Usare la sessione system_health

Flag di traccia 1204 e flag di traccia 1222

In caso di deadlock, il flag di traccia 1204 e il flag di traccia 1222 restituiscono le informazioni acquisite nel log degli errori di SQL Server. Il flag di traccia 1204 riporta informazioni sui deadlock formattate da ogni nodo interessato dal deadlock. Il flag di traccia 1222 formatta le informazioni sui deadlock, prima per processi e poi per risorse. È possibile attivare entrambi i flag di traccia per ottenere due diverse rappresentazioni dello stesso evento di deadlock.

Importante

Evitare di usare i flag di traccia 1204 e 1222 in sistemi con carichi di lavoro intensivi interessati da deadlock. L'uso di questi flag di traccia può comportare problemi di prestazioni. Usare invece l'evento deadlock esteso per acquisire le informazioni necessarie.

Nella tabella seguente vengono illustrate le proprietà dei flag di traccia 1204 e 1222, nonché le relative similitudini e differenze.

Proprietà	Flag di traccia 1204 e flag di traccia 1222	Solo flag di traccia 1204	Solo flag di traccia 1222
Formato di output	L'output viene acquisito nel log degli errori di SQL Server.	Mirato ai nodi interessati dal deadlock. Ogni nodo ha una sezione dedicata e la sezione finale descrive la vittima del deadlock.	Restituisce informazioni in un formato simile all'XML ma non conforme a uno schema XSD (XML Schema Definition). Il formato presenta tre sezioni principali. Nella prima sezione viene dichiarata la vittima del deadlock. Nella seconda sezione viene descritto ogni processo interessato dal deadlock. Nella terza sezione vengono descritte le risorse che rappresentano un sinonimo dei nodi indicati nel flag di traccia 1204.
Identificazione degli attributi	SPID:<x> ECID:<x>. Identifica il thread dell'ID del processo di sistema in presenza di processi paralleli. La voce SPID:<x> ECID:0, dove <x> viene sostituita dal valore di SPID, rappresenta il thread principale. La voce SPID:<x> ECID:<y>, dove <x> viene sostituita dal valore di SPID e <y> è maggiore di 0, rappresenta i sottothread dello stesso SPID. BatchID (sbid per il flag di traccia 1222). Identifica il batch da cui l'esecuzione del codice richiede o mantiene un blocco. Quando MARS (Multiple Active Result Set) è disabilitato, il valore di BatchID è 0. Quando MARS è abilitato, il valore per i batch attivi è compreso tra 1 e n. Se la sessione non contiene batch attivi, BatchID è 0. Mode. Specifica il tipo di blocco per una determinata risorsa richiesta, concessa o attesa da un thread. Mode può essere IS (Preventivo condiviso), S (Condiviso), U (Aggiornamento), IX (Preventivo esclusivo), SIX (Condiviso preventivo esclusivo) e X (Esclusivo). Line # (line per il flag di traccia 1222). Elenca il numero di riga del batch di istruzioni corrente che era in esecuzione quando si è verificato il deadlock. Input Buf (inputbuf per il flag di traccia 1222). Elenca tutte le istruzioni del batch corrente.	Node. Rappresenta il numero di voce nella catena del deadlock. Lists. Il proprietario del blocco può essere parte degli elenchi seguenti: Grant List. Enumera i proprietari correnti della risorsa. Convert List. Enumera i proprietari correnti che stanno tentando di convertire i propri blocchi a un livello superiore. Wait List. Enumera le nuove richieste di blocco correnti per la risorsa. Statement Type. Descrive il tipo di istruzione DML (SELECT, INSERT, UPDATE o DELETE) su cui i thread hanno autorizzazioni. Victim Resource Owner. Specifica il thread partecipante che SQL Server sceglie come vittima per interrompere il ciclo di deadlock. Il thread scelto e tutti i sottothread esistenti vengono terminati. Next Branch. Rappresenta i due o più sottothread legati allo stesso SPID che sono interessati dal ciclo di deadlock.	deadlock victim. Rappresenta l'indirizzo di memoria fisica del task (vedere sys.dm_os_tasks (Transact-SQL)) selezionato come vittima del deadlock. In caso di deadlock risolto, il valore può essere 0 (zero). Un'attività in cui è in corso l'esecuzione del rollback non può essere scelta come vittima del deadlock. executionstack. Rappresenta il codice di Transact-SQL in esecuzione al momento del deadlock. priority. Rappresenta la priorità di deadlock. In alcuni casi, la priorità di deadlock potrebbe essere modificata dal motore di database di SQL Server per un breve intervallo di tempo, per ottenere una concorrenza migliore. logused. Spazio del log utilizzato dall'attività. ID proprietario ID della transazione che ha il controllo della richiesta. status. Stato dell'attività. I possibili valori sono i seguenti: >>pending. in attesa di un thread di lavoro. >>runnable. Pronto per l'esecuzione ma in attesa di un quantum. >>running. in esecuzione nell'utilità di pianificazione. >>suspended. Esecuzione sospesa. >>done. Attività completata. >>spinloop. In attesa che venga liberato uno spinlock. waitresource. Risorsa necessaria per l'attività. waittime. Tempo, in millisecondi, di attesa per la risorsa. schedulerid. Utilità di pianificazione associata all'attività. Vedere sys.dm_os_schedulers (Transact-SQL). hostname. Nome della workstation. isolationlevel. Livello di isolamento delle transazioni corrente. Xactid. ID della transazione che ha il controllo della richiesta. currentdb. ID del database. lastbatchstarted. Ultima volta in cui un processo client ha avviato un'esecuzione del batch. lastbatchcompleted. Ultima volta in cui un processo client ha completato un'esecuzione del batch. clientoption1 e clientoption2. Opzioni SET nella connessione client. Si tratta di una maschera di bit che include informazioni sulle opzioni generalmente controllate da istruzioni SET, ad esempio SET NOCOUNT e SET XACTABORT. associatedObjectId. Rappresenta l'ID di HoBT (heap o albero B).
Attributi risorsa	RID. Identifica la singola riga di una tabella su cui un blocco viene mantenuto o richiesto. RID è rappresentato come RID: db_id:file_id:page_no:row_no. Ad esempio, RID: 6:1:20789:0. OBJECT. Identifica la tabella su cui un blocco viene mantenuto o richiesto. OBJECT è rappresentato come OBJECT: db_id:object_id. Ad esempio, TAB: 6:2009058193. KEY. Identifica l'intervallo di chiavi di un indice su cui un blocco viene mantenuto o richiesto. KEY è rappresentato come KEY: db_id:hobt_id (valore hash della chiave di indice). Ad esempio, KEY: 6:72057594057457664 (350007a4d329). PAG. Identifica la risorsa di pagina su cui un blocco viene mantenuto o richiesto. PAG è rappresentato come PAG: db_id:file_id:page_no. Ad esempio, PAG: 6:1:20789. EXT. Identifica la struttura extent. EXT è rappresentato come EXT: db_id:file_id:extent_no. Ad esempio, EXT: 6:1:9. DB. Identifica il blocco del database. DB è rappresentato in uno dei modi seguenti: DB: db_id DB: db_id[BULK-OP-DB], che identifica il blocco di database acquisito dal database di backup. DB: db_id[BULK-OP-LOG], che identifica il blocco acquisito dal log di backup per un particolare database. APP. Identifica il blocco applicato da una risorsa di un'applicazione. APP è rappresentata come APP: lock_resource. Ad esempio, APP: Formf370f478. METADATA. Rappresenta le risorse di metadati interessate da un deadlock. Poiché METADATA ha molte sottorisorse, il valore restituito dipende dalla sottorisorsa interessata dal deadlock. Ad esempio METADATA.USER_TYPE restituisce user_type_id = *integer_value*. Per altre informazioni sulle risorse e le sottorisorse METADATA, vedere sys.dm_tran_locks (Transact-SQL). HOBT. Rappresenta un heap o albero B interessato da un deadlock.	Nessuna esclusiva di questo flag di traccia.	Nessuna esclusiva di questo flag di traccia.

Esempio di flag di traccia 1204

L'esempio seguente illustra l'output che si ottiene quando il flag di traccia 1204 è attivo. In questo caso, la tabella in Node 1 è un heap senza indici e la tabella in Node 2 è un heap con un indice non cluster. La chiave di indice in Node 2 è in corso di aggiornamento quando si verifica il deadlock.

Deadlock encountered .... Printing deadlock information  
Wait-for graph  
  
Node:1  
  
RID: 6:1:20789:0               CleanCnt:3 Mode:X Flags: 0x2  
 Grant List 0:  
   Owner:0x0315D6A0 Mode: X          
     Flg:0x0 Ref:0 Life:02000000 SPID:55 ECID:0 XactLockInfo: 0x04D9E27C  
   SPID: 55 ECID: 0 Statement Type: UPDATE Line #: 6  
   Input Buf: Language Event:   
BEGIN TRANSACTION  
   EXEC usp_p2  
 Requested By:   
   ResType:LockOwner Stype:'OR'Xdes:0x03A3DAD0   
     Mode: U SPID:54 BatchID:0 ECID:0 TaskProxy:(0x04976374) Value:0x315d200 Cost:(0/868)  
  
Node:2  
  
KEY: 6:72057594057457664 (350007a4d329) CleanCnt:2 Mode:X Flags: 0x0  
 Grant List 0:  
   Owner:0x0315D140 Mode: X          
     Flg:0x0 Ref:0 Life:02000000 SPID:54 ECID:0 XactLockInfo: 0x03A3DAF4  
   SPID: 54 ECID: 0 Statement Type: UPDATE Line #: 6  
   Input Buf: Language Event:   
     BEGIN TRANSACTION  
       EXEC usp_p1  
 Requested By:   
   ResType:LockOwner Stype:'OR'Xdes:0x04D9E258   
     Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380)  
  
Victim Resource Owner:  
 ResType:LockOwner Stype:'OR'Xdes:0x04D9E258   
     Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380)  

Esempio di flag di traccia 1222

Nell'esempio seguente viene illustrato l'output che si ottiene quando il flag di traccia 1222 è attivo. In questo caso, una tabella è un heap senza indici e l'altra tabella è un heap con un indice non cluster. Nella seconda tabella, la chiave di indice è in corso di aggiornamento quando si verifica il deadlock.

deadlock-list  
 deadlock victim=process689978  
  process-list  
   process id=process6891f8 taskpriority=0 logused=868   
   waitresource=RID: 6:1:20789:0 waittime=1359 ownerId=310444   
   transactionname=user_transaction   
   lasttranstarted=2022-02-05T11:22:42.733 XDES=0x3a3dad0   
   lockMode=U schedulerid=1 kpid=1952 status=suspended spid=54   
   sbid=0 ecid=0 priority=0 transcount=2   
   lastbatchstarted=2022-02-05T11:22:42.733   
   lastbatchcompleted=2022-02-05T11:22:42.733   
   clientapp=Microsoft SQL Server Management Studio - Query   
   hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user   
   isolationlevel=read committed (2) xactid=310444 currentdb=6   
   lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200  
    executionStack  
     frame procname=AdventureWorks2022.dbo.usp_p1 line=6 stmtstart=202   
     sqlhandle=0x0300060013e6446b027cbb00c69600000100000000000000  
     UPDATE T2 SET COL1 = 3 WHERE COL1 = 1;       
     frame procname=adhoc line=3 stmtstart=44   
     sqlhandle=0x01000600856aa70f503b8104000000000000000000000000  
     EXEC usp_p1       
    inputbuf  
      BEGIN TRANSACTION  
       EXEC usp_p1  
   process id=process689978 taskpriority=0 logused=380   
   waitresource=KEY: 6:72057594057457664 (350007a4d329)     
   waittime=5015 ownerId=310462 transactionname=user_transaction   
   lasttranstarted=2022-02-05T11:22:44.077 XDES=0x4d9e258 lockMode=U   
   schedulerid=1 kpid=3024 status=suspended spid=55 sbid=0 ecid=0   
   priority=0 transcount=2 lastbatchstarted=2022-02-05T11:22:44.077   
   lastbatchcompleted=2022-02-05T11:22:44.077   
   clientapp=Microsoft SQL Server Management Studio - Query   
   hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user   
   isolationlevel=read committed (2) xactid=310462 currentdb=6   
   lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200  
    executionStack  
     frame procname=AdventureWorks2022.dbo.usp_p2 line=6 stmtstart=200   
     sqlhandle=0x030006004c0a396c027cbb00c69600000100000000000000  
     UPDATE T1 SET COL1 = 4 WHERE COL1 = 1;       
     frame procname=adhoc line=3 stmtstart=44   
     sqlhandle=0x01000600d688e709b85f8904000000000000000000000000  
     EXEC usp_p2       
    inputbuf  
      BEGIN TRANSACTION  
        EXEC usp_p2      
  resource-list  
   ridlock fileid=1 pageid=20789 dbid=6 objectname=AdventureWorks2022.dbo.T2   
   id=lock3136940 mode=X associatedObjectId=72057594057392128  
    owner-list  
     owner id=process689978 mode=X  
    waiter-list  
     waiter id=process6891f8 mode=U requestType=wait  
   keylock hobtid=72057594057457664 dbid=6 objectname=AdventureWorks2022.dbo.T1   
   indexname=nci_T1_COL1 id=lock3136fc0 mode=X   
   associatedObjectId=72057594057457664  
    owner-list  
     owner id=process6891f8 mode=X  
    waiter-list  
     waiter id=process689978 mode=U requestType=wait  

Evento Deadlock Graph di Profiler

Si tratta di un evento in SQL Profiler che presenta una descrizione grafica delle attività e delle risorse coinvolte in un deadlock. L'esempio seguente illustra l'output prodotto da SQL Profiler quando l'evento Deadlock Graph è attivo.

Importante

SQL Profiler crea tracce deprecate nel 2016 e sostituite da eventi estesi. Gli eventi estesi hanno un sovraccarico di prestazioni di gran lunga inferiore e sono molto più configurabili rispetto alle tracce. È consigliabile usare l'Evento deadlock degli eventi estesi anziché le tracce.

Per altre informazioni sull'evento deadlock, vedere Classe di evento Lock:Deadlock. Per altre informazioni sull'esecuzione dell'evento Deadlock Graph di SQL Profiler, vedere Salvare Deadlock Graph (SQL Server Profiler).

Per informazioni sugli equivalenti per le classi di evento di Analisi SQL presenti in Eventi estesi, vedere Equivalenti di eventi estesi per le classi di evento di Analisi SQL. Sono maggiormente consigliati gli eventi estesi rispetto alle Analisi SQL.

Gestire i deadlock

Quando un'istanza del motore di database di SQL Server sceglie una transazione come vittima di un deadlock, viene terminato il batch corrente, viene eseguito il rollback della transazione e all'applicazione viene restituito il messaggio di errore 1205:

Your transaction (process ID #52) was deadlocked on {lock | communication buffer | thread} resources with another process and has been chosen as the deadlock victim. Rerun your transaction.

Tutte le applicazioni che inviano query di Transact SQL, poiché possono essere potenziali vittime del deadlock, devono includere un gestore degli errori in grado di intercettare il messaggio di errore 1205. Se questo errore non viene intercettato, l'applicazione continua a essere eseguita come se il rollback della transazione non avesse avuto luogo, con la conseguente generazione di errori.

Tramite l'implementazione di un gestore degli errori in grado di intercettare il messaggio di errore 1205, è possibile gestire la condizione di deadlock in un'applicazione ed eseguire gli interventi di correzione necessari, ad esempio il reinvio della query coinvolta nel deadlock. Tramite il reinvio automatico della query l'utente non è necessario che l'utente sia a conoscenza del verificarsi del deadlock.

È consigliabile sospendere brevemente l'applicazione prima di reinviarne la query. In questo modo, la transazione interessata dal deadlock può completare e rilasciare i relativi blocchi che formano parte del ciclo di deadlock. Ciò riduce la probabilità che il deadlock si verifichi di nuovo quando la query reinviata ne richiede i blocchi.

Ridurre i deadlock

I deadlock non possono essere evitati completamente. È tuttavia possibile ridurre il rischio di insorgenza di un deadlock attenendosi a determinate convenzioni di codifica. La riduzione del numero di deadlock comporta un aumento della velocità effettiva delle transazioni e una diminuzione dell'overhead del sistema, in quanto il numero di transazioni su cui è necessario eseguire le operazioni seguenti risulta minimo:

• Rollback, con il conseguente annullamento del lavoro eseguito.
• Riesecuzione tramite l'applicazione, in quanto in corrispondenza del deadlock è stato eseguito il rollback.

Per ridurre il numero di deadlock, è possibile:

• Accedere sempre agli oggetti in base allo stesso ordine.
• Escludere l'interazione dell'utente nelle transazioni. - Ridurre la lunghezza delle transazioni e inserirle in un solo batch.
• Utilizzare un livello di isolamento basso.
• Utilizzare un livello di isolamento basato sul controllo delle versioni delle righe.
  • Impostare l'opzione di database READ_COMMITTED_SNAPSHOT su ON affinché le transazioni Read Committed possano usare il controllo delle versioni delle righe.
  • Utilizzare l'isolamento dello snapshot.
• Usare connessioni legate.

Accedere agli oggetti in base allo stesso ordine

Se tutte le transazioni simultanee accedono agli oggetti nello stesso ordine, la possibilità che si verifichi un deadlock risulta notevolmente ridotta. Se, ad esempio, due transazioni simultanee ottengono un blocco prima nella tabella Supplier, quindi nella tabella Part, una transazione rimane bloccata sulla tabella Supplier fino al completamento dell'altra transazione. Dopo il commit o il rollback della prima transazione, l'esecuzione della seconda continua e non si verifica alcun deadlock. L'utilizzo di stored procedure per tutte le modifiche ai dati consente di standardizzare l'ordine di accesso agli oggetti.

Escludere l'interazione dell'utente nelle transazioni

È consigliabile evitare la creazione di transazioni che prevedono l'interazione dell'utente. I batch eseguiti senza alcun intervento da parte dell'utente risultano infatti molto più veloci rispetto ai tempi di risposta di un utente a una query, ad esempio per rispondere alla richiesta di un parametro richiesto da un'applicazione. Si supponga, ad esempio, che una transazione sia in attesa dell'input dell'utente e che l'utente sia a pranzo o abbia lasciato l'ufficio per il fine settimana. In questo caso la transazione non può essere completata. Questa situazione comporta una riduzione della velocità effettiva del sistema, in quanto i blocchi mantenuti attivi dalla transazione vengono rilasciati solo in corrispondenza del commit o del rollback della transazione. Anche se non si verifica una situazione di deadlock, le altre transazioni che tentano di accedere alle stesse risorse vengono bloccate, in attesa del completamento della prima transazione.

Ridurre la lunghezza delle transazioni e inserirle in un solo batch.

Il deadlock si verifica in genere quando nello stesso database vengono eseguite contemporaneamente numerose transazioni estese. La lunghezza della transazione è direttamente proporzionale alla durata dei blocchi esclusivi o di aggiornamento che bloccano qualsiasi altra attività e che possono generare una situazione di deadlock.

Se le transazioni vengono inserite in un singolo batch, è possibile minimizzare il tempo di round trip in rete durante l'esecuzione delle transazioni, con la conseguente riduzione di possibili ritardi nel completamento della transazione e nel rilascio dei blocchi.

Per altre informazioni sui blocchi di aggiornamento, vedere Guida per il controllo delle versioni delle righe e il blocco della transazione.

Utilizzare un livello di isolamento inferiore

È importante determinare se una transazione è eseguibile a un livello di isolamento inferiore. Il livello di isolamento Read Committed per una transazione consente la lettura di dati letti in precedenza (ma non modificati) da un'altra transazione senza attendere che tale transazione venga completata. Quando si imposta un livello di isolamento basso, quale Read Committed, i blocchi condivisi vengono mantenuti attivi per un periodo più breve rispetto a quello richiesto da un livello di isolamento più alto, quale Serializable, con la conseguente riduzione della contesa tra blocchi.

Utilizzare un livello di isolamento basato sul controllo delle versioni delle righe

Quando l'opzione di database READ_COMMITTED_SNAPSHOT è impostata su ON, durante le operazioni di lettura le transazioni eseguite con il livello di isolamento Read Committed usano il controllo delle versioni delle righe anziché i blocchi condivisi.

Nota

Alcune applicazioni si avvalgono della funzione di blocco del livello di isolamento Read Committed. Per tali applicazioni sono necessarie alcune modifiche prima di abilitare questa opzione.

Anche il livello di isolamento dello snapshot utilizza il controllo delle versioni delle righe, che non si avvale dei blocchi condivisi durante le operazioni di lettura. Affinché sia possibile eseguire transazioni con il livello di isolamento dello snapshot, è necessario impostare l'opzione di database ALLOW_SNAPSHOT_ISOLATION su ON.

Implementare questi livelli di isolamento per ridurre i deadlock che si possono verificare tra operazioni di lettura e di scrittura.

Usare le connessioni associate

L'utilizzo di connessioni associate garantisce la cooperazione tra due o più connessioni aperte dalla stessa applicazione. I blocchi acquisiti dalle connessioni secondarie vengono gestiti come se fossero stati acquisiti dalla connessione primaria e viceversa. Di conseguenza non si verificano blocchi reciproci tra le connessioni.

Arrestare una transazione.

In uno scenario di deadlock, la transazione vittima viene arrestata automaticamente ed ne viene eseguito il rollback. In uno scenario di deadlock non è necessario arrestare una transazione.

Generare un deadlock

Nota

Questo esempio è valido nel database di esempio AdventureWorksLT2019 quando READ_COMMITTED_SNAPSHOT è stato abilitato con schema e dati predefiniti. Per scaricare questo esempio, visitare Database di esempio di AdventureWorks.

Per generare un deadlock, è necessario connettere due sessioni al database AdventureWorksLT2019. Si farà riferimento a queste sessioni come Sessione A e Sessione B. È possibile creare le due sessioni semplicemente generando due intervalli di query in SQL Server Management Studio (SSMS).

Nella sessione A eseguire il seguente codice di Transact-SQL. Questo codice avvia una transazione esplicita ed esegue una singola istruzione che aggiorna la tabella SalesLT.Product. A tale scopo, la transazione acquisisce un blocco di aggiornamento (U) su una riga della tabella SalesLT.Product che viene convertita in un blocco esclusivo (X). Lasciare la transazione aperta.

BEGIN TRAN

    UPDATE SalesLT.Product SET SellEndDate = SellEndDate + 1
        WHERE Color = 'Red';

Ora eseguire il seguente codice di Transact-SQL nella sessione B. Questo codice non avvia in modo esplicito una transazione. Funziona invece in modalità di transazione con commit automatico. Questa istruzione aggiorna la tabella SalesLT.ProductDescription. L'aggiornamento estrae un blocco di aggiornamento (U) su 72 righe nella tabella SalesLT.ProductDescription. La query viene aggiunta ad altre tabelle, inclusa la tabella SalesLT.Product.

UPDATE SalesLT.ProductDescription SET Description = Description
    FROM SalesLT.ProductDescription as pd
    JOIN SalesLT.ProductModelProductDescription as pmpd on
        pd.ProductDescriptionID = pmpd.ProductDescriptionID
    JOIN SalesLT.ProductModel as pm on
        pmpd.ProductModelID = pm.ProductModelID
    JOIN SalesLT.Product as p on
        pm.ProductModelID=p.ProductModelID
    WHERE p.Color = 'Silver';

Per completare questo aggiornamento, la sessione B richiede un blocco condiviso (S) sulle righe della tabella SalesLT.Product, inclusa la riga bloccata dalla sessione A. La sessione B è bloccata in SalesLT.Product.

Tornare alla sessione A. Eseguire la seguente istruzione di Transact-SQL. In questo modo viene eseguita una seconda istruzione UPDATE come parte della transazione aperta.

    UPDATE SalesLT.ProductDescription SET Description = Description
        FROM SalesLT.ProductDescription as pd
        JOIN SalesLT.ProductModelProductDescription as pmpd on
            pd.ProductDescriptionID = pmpd.ProductDescriptionID
        JOIN SalesLT.ProductModel as pm on
            pmpd.ProductModelID = pm.ProductModelID
        JOIN SalesLT.Product as p on
            pm.ProductModelID=p.ProductModelID
        WHERE p.Color = 'Red';

La seconda istruzione di aggiornamento nella sessione A sarà bloccata dalla sessione B in SalesLT.ProductDescription.

La sessione A e la sessione B si bloccano a vicenda. Nessuna delle transazioni può continuare, perché ognuna necessita di una risorsa bloccata dall'altra.

Dopo alcuni secondi, il monitoraggio dei deadlock identificherà che le transazioni nella sessione A e nella sessione B si bloccano reciprocamente e che nessuna delle due può continuare. Verrà visualizzato un deadlock, con la sessione A scelta come vittima del deadlock. La sessione B verrà completata correttamente. Nella sessione A verrà visualizzato un messaggio di errore con testo simile al seguente:

Msg 1205, Level 13, State 51, Line 7
Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Se non viene generato un deadlock, verificare che READ_COMMITTED_SNAPSHOT sia stato abilitato nel database di esempio. I deadlock possono verificarsi in qualsiasi configurazione del database, ma in questo esempio è necessario abilitare READ_COMMITTED_SNAPSHOT.

È quindi possibile visualizzare i dettagli del deadlock nella destinazione ring_buffer della sessione Eventi estesi system_health, la quale per impostazione predefinita è abilitata e attiva in SQL Server. Si consideri la query seguente:

WITH cteDeadLocks ([Deadlock_XML]) AS (
  SELECT [Deadlock_XML] = CAST(target_data AS XML) 
  FROM sys.dm_xe_sessions AS xs
  INNER JOIN sys.dm_xe_session_targets AS xst 
  ON xs.[address] = xst.event_session_address
  WHERE xs.[name] = 'system_health'
  AND xst.target_name = 'ring_buffer'
 )
SELECT 
  Deadlock_XML = x.Graph.query('(event/data/value/deadlock)[1]')  
, when_occurred = x.Graph.value('(event/data/value/deadlock/process-list/process/@lastbatchstarted)[1]', 'datetime2(3)') 
, DB = DB_Name(x.Graph.value('(event/data/value/deadlock/process-list/process/@currentdb)[1]', 'int')) --Current database of the first listed process 
FROM (
 SELECT Graph.query('.') AS Graph 
 FROM cteDeadLocks c
 CROSS APPLY c.[Deadlock_XML].nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS Deadlock_Report(Graph)
) AS x
ORDER BY when_occurred desc;

È possibile visualizzare il codice XML nella colonna Deadlock_XML all'interno di SSMS selezionando la cella che verrà visualizzata come collegamento ipertestuale. Salvare questo output come un file .xdl, chiudere, quindi riaprire il file .xdl in SSMS per un grafico visivo dei deadlock. Il grafico del deadlock dovrebbe essere simile all'immagine seguente.

Blocchi ottimizzati e deadlock

Si applica a: Database SQL di Azure

Il blocco ottimizzato ha introdotto un metodo diverso per i meccanismi di blocco che modificano la modalità di report dei deadlock che interessanoo blocchi TID esclusivi. Al di sotto di ogni risorsa del report del deadlock <resource-list> ogni elemento <xactlock> presenta le risorse sottostanti e le informazioni specifiche dei blocchi di ogni membro di un deadlock.

Si consideri l'esempio seguente in cui è abilitato il blocco ottimizzato:

CREATE TABLE t2 
(a int PRIMARY KEY not null 
,b int null); 

INSERT INTO t2 VALUES (1,10),(2,20),(3,30) 
GO 

I comandi TSQL seguenti creeranno un deadlock nella tabella t2in due sessioni:

Nella sessione 1:

--session 1
BEGIN TRAN foo;
UPDATE t2 SET b = b+ 10 WHERE a = 1; 

Nella sessione 2:

--session 2:
BEGIN TRAN bar 
UPDATE t2 SET b = b+ 10 WHERE a = 2; 

Nella sessione 1:

--session 1:
UPDATE t2 SET b = b + 100 WHERE a = 2; 

Nella sessione 2:

--session 2:
UPDATE t2 SET b = b + 20 WHERE a = 1; 

Questo scenario di istruzioni UPDATE in competizione dà luogo a un deadlock. In questo caso, una risorsa keylock, in cui ogni sessione contiene nel proprio TID un blocco X e rimane in attesa nel blocco S nell'altro TID, genera un deadlock. Il codice XML seguente, acquisito come report del deadlock, contiene elementi e attributi specifici del blocco ottimizzato:

Contenuto correlato

• Eventi estesi
• sys.dm_tran_locks (Transact-SQL)
• Classe di evento Deadlock Graph
• Servizio dati remoti ADO: deadlock con livello di isolamento ripetibile in lettura
• Classe di evento Lock:Deadlock Chain
• Classe di evento Lock:Deadlock
• SET DEADLOCK_PRIORITY (Transact-SQL)
• Analizzare e impedire deadlock nel database SQL di Azure
• Aprire, visualizzare e stampare un file deadlock in SQL Server Management Studio (SSMS)