Guida ai deadlock

Si applica a: SQL Server Database SQL di Azure Istanza gestita di SQL di Azure Azure Synapse Analytics Piattaforma 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 può verificare 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 riduce la concorrenza forzando gli aggiornamenti di grandi dimensioni in una partizione ad 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 uno o più task e ogni task può acquisire o attendere di acquisire risorse. 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) in SQL Server Native Client.

  • 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 Risorse che possono generare un deadlock 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, viene 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ò 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 Common Language Runtime (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.

Evento deadlock esteso

In SQL Server 2012 (11.x) e versioni successive, in Analisi SQL o SQL Profiler è consigliabile usare l'evento esteso (XEvent) xml_deadlock_report, anziché la classe di evento Deadlock Graph.

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 è abilitata per impostazione predefinita, non è necessario configurare una sessione XEvent separata per acquisire 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 delle 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;

Il set di risultati è il seguente.

L'esempio seguente illustra l'output dopo aver selezionato il collegamento Event_Data nella prima riga del risultato:

<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 Utilizzare 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.

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

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.

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 grafici deadlock (SQL Server Profiler).

Per informazioni sugli equivalenti per le classi di evento di Analisi SQL presenti in Eventi estesi, vedere Vedere gli 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 l’applicazione non intercetta l’errore, l'applicazione può continuare 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 rinvio automatico della query, 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.

Gestire con TRY... CATCH

È possibile usare TRY... CATCH per gestire i deadlock. L'errore della vittima del deadlock 1205 può essere rilevato dal blocco CATCH e può essere eseguito il rollback della transazione fino a quando i thread non vengono sbloccati.

Per altre informazioni, vedere Gestione dei deadlock.

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 scrittura codice. 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. 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.

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.

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

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

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
     INNER JOIN SalesLT.ProductModelProductDescription AS pmpd
         ON pd.ProductDescriptionID = pmpd.ProductDescriptionID
     INNER JOIN SalesLT.ProductModel AS pm
         ON pmpd.ProductModelID = pm.ProductModelID
     INNER 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
     INNER JOIN SalesLT.ProductModelProductDescription AS pmpd
         ON pd.ProductDescriptionID = pmpd.ProductDescriptionID
     INNER JOIN SalesLT.ProductModel AS pm
         ON pmpd.ProductModelID = pm.ProductModelID
     INNER JOIN SalesLT.Product AS p
         ON pm.ProductModelID = p.ProductModelID
WHERE p.Color = 'Red';

Il secondo rapporto di aggiornamento nella sessione A viene 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 identifica 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 viene completata correttamente. Nella sessione A viene visualizzato un messaggio di errore con testo simile al seguente esempio:

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 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 CAST (target_data AS XML) AS [Deadlock_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 x.Graph.query('(event/data/value/deadlock)[1]') AS Deadlock_XML,
       x.Graph.value('(event/data/value/deadlock/process-list/process/@lastbatchstarted)[1]', 'datetime2(3)') AS when_occurred,
       DB_Name(x.Graph.value('(event/data/value/deadlock/process-list/process/@currentdb)[1]', 'int')) AS DB --Current database of the first listed process
FROM (SELECT Graph.query('.') AS Graph
      FROM cteDeadLocks AS 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 viene 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 Transact-SQL seguenti creano un deadlock nella tabella t2in due sessioni:

Nella sessione 1:

--session 1
BEGIN TRANSACTION foo;

UPDATE t2
    SET b = b + 10
WHERE a = 1;

Nella sessione 2:

--session 2:
BEGIN TRANSACTION 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

• Panoramica degli eventi estesi
• sys.dm_tran_locks (Transact-SQL)
• Classe di evento Deadlock Graph
• Deadlock con livello di isolamento di lettura ripetibile
• 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)