Analizzare e impedire i deadlock in database SQL di Azure

Si applica a: Database SQL di Azure

Questo articolo illustra come identificare i deadlock in Database SQL di Azure, usare grafici di deadlock e Query Store per identificare le query nel deadlock, nonché pianificare e testare le modifiche per impedire deadlock ricorrenti.

Questo articolo è incentrato sull'identificazione e l'analisi dei deadlock causati da contesa dei blocchi. Altre informazioni su altri tipi di deadlock in Risorse che possono causare un deadlock.

Come si verificano i deadlock in database SQL di Azure

Per ogni nuovo database in database SQL di Azure è abilitata per impostazione predefinita l'impostazione snapshot Read Committed (RCSI) del database. Il bloccaggio tra le sessioni che leggono i dati e le sessioni di scrittura dei dati è minimizzato in RCSI, che usa il controllo delle versioni delle righe per aumentare la concorrenza. Tuttavia, il bloccaggio e i deadlock possono verificarsi comunque nei database in database SQL di Azure perché:

• Le query che modificano i dati potrebbero bloccarsi tra loro.
• Le query possono essere eseguite con livelli di isolamento che aumentano il bloccaggio. I livelli di isolamento possono essere specificati tramite metodi di libreria client, hint per la query o istruzioni SET in Transact-SQL.
• RCSI può essere disabilitato, causando l'uso dei blocchi condivisi (S) da parte del database per proteggere le istruzioni SELECT eseguite con il livello di isolamento Read Committed. Ciò può aumentare il bloccaggio e i deadlock.

Un esempio di deadlock

Un deadlock si verifica quando due o più attività si bloccano reciprocamente in modo permanente, in quanto ogni attività ha un blocco su una risorsa che l'altra attività sta cercando di bloccare. Un deadlock è anche chiamato dipendenza ciclica: nel caso di un deadlock a due attività, la transazione A ha una dipendenza dalla transazione B e la transazione B chiude il cerchio avendo una dipendenza dalla transazione A.

Ad esempio:

1. La sessione A avvia una transazione esplicita ed esegue un'istruzione di aggiornamento che acquisisce un blocco di aggiornamento (U) su una riga della tabella SalesLT.Product convertita in un blocco esclusivo (X).
2. La sessione B esegue un'istruzione di aggiornamento che modifica la tabella SalesLT.ProductDescription. L'istruzione di aggiornamento viene aggiunta alla tabella SalesLT.Product per trovare le righe corrette da aggiornare.
   • La sessione B acquisisce un blocco di aggiornamento (U) su 72 righe nella tabella SalesLT.ProductDescription.
   • La sessione B richiede un blocco condiviso sulle righe della tabella SalesLT.Product, inclusa la riga bloccata dalla sessione A. La sessione B è bloccata in SalesLT.Product.
3. La sessione A continua la transazione e ora esegue un aggiornamento sulla tabella SalesLT.ProductDescription. La sessione A è bloccata dalla sessione B in SalesLT.ProductDescription.

Tutte le transazioni in un deadlock attenderanno per un periodo illimitato, a meno che non venga eseguito il rollback di una delle transazioni partecipanti, ad esempio, perché la sessione è stata terminata.

La funzionalità di monitoraggio dei deadlock del motore di ricerca del database ricerca periodicamente le attività interessate da un deadlock. Se il monitoraggio dei deadlock rileva una dipendenza ciclica, sceglie una delle task come vittima e termina la transazione con l'errore 1205, "La transazione (ID di processo N) è stata interrotta a causa di un deadlock delle risorse bloccate con un altro processo ed è stata scelta come vittima del deadlock. Rieseguire la transazione". L'interruzione del deadlock in questo modo consente alle altre task nel deadlock di completare le transazioni.

L'applicazione con la transazione scelta come vittima del deadlock deve riprovare la transazione, che in genere viene completata al termine delle altre transazioni coinvolte nel deadlock.

La procedura consigliata è introdurre un breve ritardo casuale prima di riprovare per evitare di incontrare di nuovo lo stesso deadlock. Altre informazioni su come progettare la logica di ripetizione dei tentativi per errori temporanei.

Livello di isolamento predefinito di database SQL di Azure

Nei nuovi database in database SQL di Azure è abilitata per impostazione predefinita l'impostazione snapshot Read Committed (RCSI). RCSI modifica il comportamento del livello di isolamento Read Committed in modo da usare il controllo delle versioni delle righe per garantire la coerenza a livello di istruzione senza l'uso di blocchi condivisi (S) per le istruzioni SELECT.

Con RCSI abilitato:

• Le istruzioni che leggono i dati non bloccano le istruzioni che modificano i dati.
• Le istruzioni che modificano i dati non bloccano le istruzioni che leggono i dati.

Il livello di isolamento dello snapshot è abilitato per impostazione predefinita anche per i nuovi database in database SQL di Azure. L'isolamento dello snapshot è un livello di isolamento aggiuntivo basato su righe che garantisce coerenza a livello di transazione per i dati e che usa le versioni delle righe per selezionare le righe da aggiornare. Per usare l'isolamento dello snapshot, le query o le connessioni devono impostare in modo esplicito il livello di isolamento della transazione su SNAPSHOT. Tale operazione può essere eseguita solo quando l'isolamento dello snapshot è abilitato per il database.

È possibile identificare se l'isolamento RCSI e/o dello snapshot è abilitato con Transact-SQL. Connettersi al database in database SQL di Azure ed eseguire la query seguente:

SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases
WHERE name = DB_NAME();
GO

Se è abilitato RCSI, la colonna is_read_committed_snapshot_on restituirà il valore 1. Se è abilitato l'isolamento dello snapshot, la colonna snapshot_isolation_state_desc restituirà il valore ON.

Se RCSI è stato disabilitato per un database in database SQL di Azure, esaminare il motivo per cui è stato disabilitato prima di riabilitarlo. È possibile che il codice applicazione sia stato scritto in attesa che le query che leggono i dati vengano bloccate dalle query che scrivono i dati, causando risultati non corretti da race condition quando RCSI è abilitato.

Interpretazione degli eventi di deadlock

Un evento di deadlock viene generato dopo che il gestore di deadlock in database SQL di Azure rileva un deadlock e seleziona una transazione come vittima. In altre parole, se si configurano avvisi per i deadlock, la notifica viene attivata dopo la risoluzione di un singolo deadlock. Non è necessaria alcuna azione da parte dell'utente per tale deadlock. Le applicazioni devono essere scritte per includere la logica di ripetizione dei tentativi in modo che continuino automaticamente dopo la ricezione dell'errore 1205, "La transazione (ID di processo N) è stata interrotta a causa di un deadlock delle risorse bloccate con un altro processo ed è stata scelta come vittima del deadlock. Rieseguire la transazione".

È utile tuttavia configurare gli avvisi, perché i deadlock possono verificarsi di nuovo. Gli avvisi dei deadlock consentono di analizzare se esiste uno schema di ripetizione dei deadlock nel database, nel qual caso è possibile scegliere di intervenire per impedire la ripetizione dei deadlock. Altre informazioni sull'invio di avvisi nella sezione Monitoraggio e avvisi per i deadlock di questo articolo.

Metodi principali per evitare i deadlock

L'approccio a rischio più basso per impedire la ripetizione dei deadlock consiste in genere nell'ottimizzazione degli indici non cluster per ottimizzare le query coinvolte nel deadlock.

• Il rischio per questo approccio è basso perché l'ottimizzazione degli indici non cluster non richiede modifiche al codice di query stesso, riducendo il rischio di un errore dell'utente durante la riscrittura di Transact-SQL che causa la restituzione di dati non corretti all'utente.
• L'ottimizzazione efficace degli indici non cluster consente alle query di trovare in modo più efficiente i dati da leggere e modificare. Riducendo la quantità di dati a cui una query deve accedere, la probabilità di bloccaggio viene ridotta e spesso è possibile evitare i deadlock.

In alcuni casi, la creazione o l'ottimizzazione di un indice cluster può ridurre il bloccaggio e i deadlock. Poiché l'indice cluster è incluso in tutte le definizioni di indice non cluster, la creazione o la modifica di un indice cluster può essere un'operazione intensa a livello di I/O e dispendiosa in termini di tempo su tabelle di dimensioni maggiori con indici non cluster esistenti. Altre informazioni sulle linee guida per la progettazione di indici cluster.

Quando l'ottimizzazione dell'indice non riesce a prevenire i deadlock, ci sono altri metodi disponibili:

• Se il deadlock si verifica solo quando viene scelto un piano specifico per una delle query coinvolte nel deadlock, forzare un piano di query con Query Store potrebbe impedire la ripetizione dei deadlock.
• Anche la riscrittura di Transact-SQL per una o più transazioni coinvolte nel deadlock può aiutare a evitare i deadlock. Suddividere le transazioni esplicite in transazioni più piccole richiede un'attenta codifica e test per assicurare la validità dei dati quando si verificano modifiche simultanee.

Altre informazioni su ognuno di questi approcci nella sezione Impedire la ripetizione di un deadlock di questo articolo.

Monitoraggio e avvisi per i deadlock

In questo articolo si userà il database di esempio AdventureWorksLT per configurare gli avvisi per i deadlock, provocare un deadlock di esempio, analizzare il grafico dei deadlock per il deadlock di esempio e testare le modifiche per impedire la ripetizione del deadlock.

In questo articolo si userà il client SQL Server Management Studio (SSMS), perché contiene funzionalità per visualizzare i grafici dei deadlock in modalità visiva e interattiva. È possibile usare altri client, come ad esempio Azure Data Studio, per seguire gli esempi, ma è possibile visualizzare i grafici dei deadlock solo come XML.

Creare il database AdventureWorksLT

Per seguire gli esempi, creare un nuovo database in database SQL di Azure e selezionare Dati di esempio come Origine dati.

Per istruzioni dettagliate su come creare AdventureWorksLT con il portale di Azure, l'interfaccia della riga di comando di Azure o PowerShell, selezionare l'approccio preferito in Avvio rapido: creare un database singolo di database SQL di Azure.

Configurare gli avvisi di deadlock nel portale di Azure

Per configurare gli avvisi per gli eventi di deadlock, seguire la procedura illustrata nell'articolo Creare avvisi per il database SQL di Azure e Azure Synapse Analytics con il portale di Azure.

Selezionare Deadlock come nome del segnale per l'avviso. Configurare il Gruppo di azioni in modo che invii una notifica usando il metodo scelto, ad esempio il tipo di azione Email/SMS/Push/Voce.

Raccogliere grafici dei deadlock in database SQL di Azure con Eventi estesi

I grafici dei deadlock sono un'ampia fonte di informazioni relative ai processi e ai blocchi coinvolti in un deadlock. Per raccogliere grafici dei deadlock con Eventi estesi (XEvent) in database SQL di Azure, acquisire l'evento sqlserver.database_xml_deadlock_report.

È possibile raccogliere grafici dei deadlock con XEvent usando la destinazione del buffer circolare o una destinazione file di eventi. Le considerazioni per la selezione del tipo di destinazione appropriato sono riepilogate nella seguente tabella:

Selezionare il tipo di destinazione da usare:

CREATE EVENT SESSION [deadlocks] ON DATABASE 
ADD EVENT sqlserver.database_xml_deadlock_report
ADD TARGET package0.ring_buffer 
WITH (STARTUP_STATE=ON, MAX_MEMORY=4 MB)
GO

ALTER EVENT SESSION [deadlocks] ON DATABASE
    STATE = START;
GO

IF 0 = (SELECT COUNT(*)
    FROM sys.symmetric_keys
    WHERE symmetric_key_id = 101 and name=N'##MS_DatabaseMasterKey##')
BEGIN
    PRINT N'Creating master key';
    CREATE MASTER KEY;
END
ELSE 
BEGIN
    PRINT N'Master key already exists, no action taken';
END
GO

CREATE DATABASE SCOPED CREDENTIAL
    [https://yourstorageaccountname.blob.core.windows.net/yourcontainername]
    WITH
        IDENTITY = 'SHARED ACCESS SIGNATURE', 
        SECRET = 'sp=r&st=2022-04-08T14:34:21Z&se=2032-04-08T22:34:21Z&sv=2020-08-04&sr=c&sig=pUNbbsmDiMzXr1vuNGZh84zyOMBFaBjgWv53IhOzYWQ%3D'
    ;
GO

CREATE EVENT SESSION [deadlocks_eventfile] ON DATABASE 
ADD EVENT sqlserver.database_xml_deadlock_report
ADD TARGET package0.event_file
    (SET filename =
    'https://yourstorageaccountname.blob.core.windows.net/yourcontainername/deadlocks.xel'
    )
WITH (STARTUP_STATE=ON, MAX_MEMORY=4 MB)
GO

ALTER EVENT SESSION [deadlocks_eventfile] ON DATABASE
    STATE = START;
GO

Causare un deadlock in AdventureWorksLT

Per causare un deadlock, è necessario connettere due sessioni al database AdventureWorksLT. Si farà riferimento a tali sessioni come Sessione A e Sessione B.

Nella sessione A eseguire il seguente codice 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. Nella sessione A verrà visualizzato un messaggio di errore con testo simile al seguente:

Msg 1205, Livello 13, Stato 51, Linea 7 La transazione (ID di processo 91) è stata interrotta a causa di un deadlock delle risorse bloccate con un altro processo ed è stata scelta come vittima del deadlock. Ripetere la transazione.

La sessione B verrà completata correttamente.

Se si configurano avvisi di deadlock nel portale di Azure, si dovrebbe ricevere una notifica poco dopo che si verifica il deadlock.

Visualizzare grafici dei deadlock da una sessione di XEvents

Se è stata configurata una sessione di XEvents per raccogliere i deadlock e si è verificato un deadlock dopo l'avvio della sessione, è possibile visualizzare una visualizzazione grafica interattiva del grafico dei deadlock, nonché il codice XML per il grafico dei deadlock.

Sono disponibili vari metodi per ottenere informazioni sui deadlock per la destinazione del buffer circolare e le destinazioni file di eventi. Selezionare la destinazione usata per la sessione di XEvents:

DECLARE @tracename sysname = N'deadlocks';

WITH ring_buffer AS (
    SELECT CAST(target_data AS XML) as rb
    FROM sys.dm_xe_database_sessions AS s 
    JOIN sys.dm_xe_database_session_targets AS t 
        ON CAST(t.event_session_address AS BINARY(8)) = CAST(s.address AS BINARY(8))
    WHERE s.name = @tracename and
    t.target_name = N'ring_buffer'
), dx AS (
    SELECT 
        dxdr.evtdata.query('.') as deadlock_xml_deadlock_report
    FROM ring_buffer
    CROSS APPLY rb.nodes('/RingBufferTarget/event[@name=''database_xml_deadlock_report'']') AS dxdr(evtdata)
) 
SELECT 
    d.query('/event/data[@name=''deadlock_cycle_id'']/value').value('(/value)[1]', 'int') AS [deadlock_cycle_id],
    d.value('(/event/@timestamp)[1]', 'DateTime2') AS [deadlock_timestamp],
    d.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(256)') AS [database_name],
    d.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,
    LTRIM(RTRIM(REPLACE(REPLACE(d.value('.', 'nvarchar(2000)'),CHAR(10),' '),CHAR(13),' '))) as query_text
FROM dx
CROSS APPLY deadlock_xml_deadlock_report.nodes('(/event/data/value/deadlock/process-list/process/inputbuf)') AS ib(d)
ORDER BY [deadlock_timestamp] DESC;
GO

ALTER EVENT SESSION [deadlocks_eventfile] ON DATABASE
    STATE = STOP;
GO
ALTER EVENT SESSION [deadlocks_eventfile] ON DATABASE
    STATE = START;
GO

DECLARE @tracename sysname = N'deadlocks_eventfile',
    @filename nvarchar(2000);

WITH eft as (SELECT CAST(target_data AS XML) as rb
    FROM sys.dm_xe_database_sessions AS s 
    JOIN sys.dm_xe_database_session_targets AS t 
        ON CAST(t.event_session_address AS BINARY(8)) = CAST(s.address AS BINARY(8))
    WHERE s.name = @tracename and
    t.target_name = N'event_file'
)
SELECT @filename = ft.evtdata.value('(@name)[1]','nvarchar(2000)') 
FROM eft
CROSS APPLY rb.nodes('EventFileTarget/File') as ft(evtdata);

WITH xevents AS (
    SELECT cast(event_data as XML) as ed
    FROM sys.fn_xe_file_target_read_file(@filename, null, null, null )
), dx AS (
    SELECT 
        dxdr.evtdata.query('.') as deadlock_xml_deadlock_report
    FROM xevents
    CROSS APPLY ed.nodes('/event[@name=''database_xml_deadlock_report'']') AS dxdr(evtdata)
) 
SELECT 
    d.query('/event/data[@name=''deadlock_cycle_id'']/value').value('(/value)[1]', 'int') AS [deadlock_cycle_id],
    d.value('(/event/@timestamp)[1]', 'DateTime2') AS [deadlock_timestamp],
    d.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(256)') AS [database_name],
    d.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,
    LTRIM(RTRIM(REPLACE(REPLACE(d.value('.', 'nvarchar(2000)'),CHAR(10),' '),CHAR(13),' '))) as query_text
FROM dx
CROSS APPLY deadlock_xml_deadlock_report.nodes('(/event/data/value/deadlock/process-list/process/inputbuf)') AS ib(d)
ORDER BY [deadlock_timestamp] DESC;
GO

declare @filename nvarchar(2000) = N'https://yourstorageaccountname.blob.core.windows.net/yourcontainername/yourfilename.xel';

with xevents AS (
    SELECT cast(event_data as XML) as ed
    FROM sys.fn_xe_file_target_read_file(@filename, null, null, null )
), dx AS (
    SELECT 
        dxdr.evtdata.query('.') as deadlock_xml_deadlock_report
    FROM xevents
    CROSS APPLY ed.nodes('/event[@name=''database_xml_deadlock_report'']') AS dxdr(evtdata)
) 
SELECT 
    d.query('/event/data[@name=''deadlock_cycle_id'']/value').value('(/value)[1]', 'int') AS [deadlock_cycle_id],
    d.value('(/event/@timestamp)[1]', 'DateTime2') AS [deadlock_timestamp],
    d.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(256)') AS [database_name],
    d.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,
    LTRIM(RTRIM(REPLACE(REPLACE(d.value('.', 'nvarchar(2000)'),CHAR(10),' '),CHAR(13),' '))) as query_text
FROM dx
CROSS APPLY deadlock_xml_deadlock_report.nodes('(/event/data/value/deadlock/process-list/process/inputbuf)') AS ib(d)
ORDER BY [deadlock_timestamp] DESC;
GO

Visualizzare e salvare un grafico di deadlock in XML

La visualizzazione di un grafico di deadlock in formato XML consente di copiare inputbuffer delle istruzioni Transact-SQL coinvolte nel deadlock. È anche possibile scegliere di analizzare i deadlock in formato basato su testo.

Se è stata usata una query Transact-SQL per restituire informazioni sul grafico di deadlock, per visualizzare il codice XML del grafico di deadlock, selezionare il valore nella colonna deadlock_xml da qualsiasi riga per aprire il codice XML del grafico di deadlock in una nuova finestra di SSMS.

Il codice XML per questo grafico di deadlock di esempio è:

<deadlock>
  <victim-list>
    <victimProcess id="process24756e75088" />
  </victim-list>
  <process-list>
    <process id="process24756e75088" taskpriority="0" logused="6528" waitresource="KEY: 8:72057594045202432 (98ec012aa510)" waittime="192" ownerId="1011123" transactionname="user_transaction" lasttranstarted="2022-03-08T15:44:43.490" XDES="0x2475c980428" lockMode="U" schedulerid="3" kpid="30192" status="suspended" spid="89" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-03-08T15:44:49.250" lastbatchcompleted="2022-03-08T15:44:49.210" lastattention="1900-01-01T00:00:00.210" clientapp="Microsoft SQL Server Management Studio - Query" hostname="LAPTOP-CHRISQ" hostpid="16716" loginname="chrisqpublic" isolationlevel="read committed (2)" xactid="1011123" currentdb="8" currentdbname="AdventureWorksLT" lockTimeout="4294967295" clientoption1="671096864" clientoption2="128056">
      <executionStack>
        <frame procname="unknown" queryhash="0xef52b103e8b9b8ca" queryplanhash="0x02b0f58d7730f798" line="1" stmtstart="2" stmtend="792" sqlhandle="0x02000000c58b8f1e24e8f104a930776e21254b1771f92a520000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
    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'   </inputbuf>
    </process>
    <process id="process2476d07d088" taskpriority="0" logused="11360" waitresource="KEY: 8:72057594045267968 (39e18040972e)" waittime="2641" ownerId="1013536" transactionname="UPDATE" lasttranstarted="2022-03-08T15:44:46.807" XDES="0x2475ca80428" lockMode="S" schedulerid="2" kpid="94040" status="suspended" spid="95" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-03-08T15:44:46.807" lastbatchcompleted="2022-03-08T15:44:46.760" lastattention="1900-01-01T00:00:00.760" clientapp="Microsoft SQL Server Management Studio - Query" hostname="LAPTOP-CHRISQ" hostpid="16716" loginname="chrisqpublic" isolationlevel="read committed (2)" xactid="1013536" currentdb="8" currentdbname="AdventureWorksLT" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="unknown" queryhash="0xef52b103e8b9b8ca" queryplanhash="0x02b0f58d7730f798" line="1" stmtstart="2" stmtend="798" sqlhandle="0x020000002c85bb06327c0852c0be840fc1e30efce2b7c8090000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
    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';   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057594045202432" dbid="8" objectname="9e011567-2446-4213-9617-bad2624ccc30.SalesLT.ProductDescription" indexname="PK_ProductDescription_ProductDescriptionID" id="lock2474df12080" mode="U" associatedObjectId="72057594045202432">
      <owner-list>
        <owner id="process2476d07d088" mode="U" />
      </owner-list>
      <waiter-list>
        <waiter id="process24756e75088" mode="U" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock hobtid="72057594045267968" dbid="8" objectname="9e011567-2446-4213-9617-bad2624ccc30.SalesLT.Product" indexname="PK_Product_ProductID" id="lock2474b588580" mode="X" associatedObjectId="72057594045267968">
      <owner-list>
        <owner id="process24756e75088" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process2476d07d088" mode="S" requestType="wait" />
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

Per salvare il grafico di deadlock come file XML:

1. Selezionare File, quindi Salva come....
2. Lasciare il valore Tipo file come da impostazione predefinita File XML (*.xml)
3. Impostare il Nome file sul nome desiderato.
4. Seleziona Salva.

Salvare un grafico di deadlock come file XDL che può essere visualizzato in modo interattivo in SSMS

La visualizzazione di una rappresentazione interattiva di un grafico di deadlock può essere utile per ottenere una rapida panoramica dei processi e delle risorse coinvolti in un deadlock e identificare rapidamente la vittima del deadlock.

Per salvare un grafico di deadlock come file che può essere visualizzato graficamente da SSMS:

1. Selezionare il valore nella colonna deadlock_xml da qualsiasi riga per aprire il file XML del grafico di deadlock in una nuova finestra in SSMS.

2. Selezionare File, quindi Salva come....

3. Impostare Tipo file su Tutti i file.

4. Impostare il Nome file sul nome desiderato, con l'estensione impostata su .xdl.

5. Seleziona Salva.

   

6. Chiudere il file selezionando la X nella scheda nella parte superiore della finestra, oppure selezionando File e quindi Chiudi.

7. Riaprire il file in SSMS selezionando File, quindi Apri e poi File. Selezionare il file salvato con l'estensione .xdl.

   Il grafico di deadlock verrà ora visualizzato in SSMS con una rappresentazione visiva dei processi e delle risorse coinvolti nel deadlock.

   

Analizzare un deadlock per il database SQL di Azure

Un grafico di deadlock ha in genere tre nodi:

• Victim-list. Identificatore di processo della vittima del deadlock.
• Process-list. Informazioni su tutti i processi coinvolti nel deadlock. I grafici di deadlock usano il termine "processo" per rappresentare una sessione che esegue una transazione.
• Resource-list. Informazioni sulle risorse coinvolte nel deadlock.

Per analizzare un deadlock, è utile esaminare questi nodi.

Elenco delle vittime del deadlock

L'elenco delle vittime del deadlock mostra il processo che è stato scelto come vittima del deadlock. Nella rappresentazione visiva di un grafico di deadlock, i processi sono rappresentati da ovali. Il processo vittima del deadlock ha una "X" disegnata sull'ovale.

Nella visualizzazione XML di un grafico di deadlock il nodo victim-list fornisce un ID per il processo vittima del deadlock.

Nel deadlock di esempio, l'ID del processo vittima è process24756e75088. È possibile usare questo ID quando si esaminano i nodi process-list e resource-list per altre informazioni sul processo vittima e sulle risorse che stava bloccando o di cui stava richiedendo il blocco.

Elenco dei processi di deadlock

L'elenco dei processi di deadlock è un'ampia fonte di informazioni sulle transazioni coinvolte nel deadlock.

La rappresentazione grafica del grafico di deadlock mostra solo un sottoinsieme di informazioni contenute nel file XML del grafico di deadlock. Gli ovali nel grafico di deadlock rappresentano il processo e mostrano informazioni tra cui:

• ID processo server, noto anche come ID sessione, o SPID.

• Priorità di deadlock della sessione. Se le priorità di deadlock di due sessioni sono diverse, come vittima del deadlock verrà scelta la sessione con la priorità inferiore. Nell'esempio entrambe le sessioni hanno la stessa priorità di deadlock.

• Quantità di log delle transazioni usata dalla sessione in byte. Se entrambe le sessioni hanno la stessa priorità di deadlock, il monitoraggio dei deadlock sceglie come vittima del deadlock la sessione per cui risulta meno oneroso eseguire il rollback. Il costo viene determinato confrontando il numero di byte di log scritti in quel punto in ogni transazione.

  Nel deadlock di esempio session_id 89 aveva usato una quantità inferiore di log delle transazioni ed è stata selezionata come vittima del deadlock.

Inoltre, è possibile visualizzare il buffer di input per l'ultima esecuzione dell'istruzione in ogni sessione prima del deadlock passando il mouse su ogni processo. Il buffer di input verrà visualizzato in un tooltip.

Sono disponibili informazioni aggiuntive per i processi nella visualizzazione XML del grafico di deadlock, tra cui:

• Informazioni di identificazione per la sessione, come ad esempio il nome client, il nome host e il nome di accesso.
• L'hash del piano di query per l'ultima istruzione eseguita da ogni sessione prima del deadlock. L'hash del piano di query è utile per recuperare informazioni aggiuntive sulla query da Query Store.

Nel deadlock di esempio:

• È possibile notare che entrambe le sessioni sono state eseguite usando il client SSMS nell'account di accesso chrisqpublic.
• L'hash del piano di query dell'ultima istruzione eseguita da parte della vittima del deadlock prima del deadlock è 0x02b0f58d7730f798. È possibile visualizzare il testo dell'istruzione nel buffer di input.
• Anche per l'altra sessione nel deadlock, l'hash del piano di query dell'ultima istruzione eseguita è 0x02b0f58d7730f798. È possibile visualizzare il testo dell'istruzione nel buffer di input. In questo caso, entrambe le query hanno lo stesso hash del piano di query perché le query sono identiche ad eccezione di un valore letterale usato come predicato di uguaglianza.

Questi valori verranno usati più avanti nell'articolo per trovare informazioni aggiuntive in Query Store.

Limitazioni del buffer di input nell'elenco dei processi di deadlock

Esistono alcune limitazioni da tenere presenti riguardo alle informazioni sul buffer di input nell'elenco dei processi di deadlock.

Il testo di query può essere troncato nel buffer di input. Il buffer di input è limitato ai primi 4.000 caratteri dell'istruzione in esecuzione.

Inoltre, alcune istruzioni coinvolte nel deadlock potrebbero non essere incluse nel grafico di deadlock. In questo esempio, la sessione A ha eseguito due istruzioni di aggiornamento all'interno di una singola transazione. Solo la seconda istruzione di aggiornamento, l'aggiornamento che ha causato il deadlock, è inclusa nel grafico di deadlock. La prima istruzione di aggiornamento eseguita dalla sessione A ha svolto un ruolo nel deadlock bloccando la sessione B. Il buffer di input, query_hash, e le informazioni correlate per la prima istruzione eseguita dalla sessione A non sono inclusi nel grafico di deadlock.

Per identificare l'esecuzione transact-SQL completa in una transazione con più istruzioni coinvolta in un deadlock, è necessario trovare le informazioni pertinenti nella stored procedure o nel codice applicazione che ha eseguito la query oppure eseguire una traccia usando Eventi estesi per acquisire istruzioni complete eseguite da sessioni coinvolte in un deadlock mentre si verifica. Se un'istruzione coinvolta nel deadlock è stata troncata e nel buffer di input viene visualizzato solo un codice Transact-SQL parziale, è possibile trovare il codice Transact-SQL per l'istruzione in Query Store con il piano di esecuzione.

Elenco delle risorse del deadlock

L'elenco delle risorse del deadlock mostra quali risorse bloccate sono di proprietà e attese dai processi nel deadlock.

Le risorse, nella rappresentazione visiva del deadlock, sono rappresentate da rettangoli:

Nel deadlock di esempio:

• La vittima del deadlock, che è stata chiamata sessione A:

  • Possiede un blocco esclusivo (X) su una chiave dell'indice PK_Product_ProductID nella tabella SalesLT.Product.
  • Richiede un blocco di aggiornamento (U) su una chiave nell'indice PK_ProductDescription_ProductDescriptionID della tabella SalesLT.ProductDescription.

• L'altro processo, che è stato chiamato sessione B:

  • Possiede un blocco di aggiornamento (U) su una chiave nell'indice PK_ProductDescription_ProductDescriptionID della tabella SalesLT.ProductDescription.
  • Richiede un blocco di condivisione (S) su una chiave nell'indice PK_ProductDescription_ProductDescriptionID della tabella SalesLT.ProductDescription.

È possibile visualizzare le stesse informazioni nel codice XML del grafico di deadlock nel nodo resource-list.

Trovare i piani di esecuzione di query in Query Store

Spesso è utile esaminare i piani di esecuzione di query per le istruzioni coinvolte nel deadlock. Questi piani di esecuzione sono spesso disponibili in Query Store usando l'hash del piano di query dalla visualizzazione XML dell'elenco processi del grafico di deadlock.

Questa query Transact-SQL cerca i piani di query corrispondenti all'hash del piano di query trovato per il deadlock di esempio. Connettersi al database utente in database SQL di Azure per eseguire la query.

DECLARE @query_plan_hash binary(8) = 0x02b0f58d7730f798

SELECT 
    qrsi.end_time as interval_end_time,
    qs.query_id,
    qp.plan_id,
    qt.query_sql_text, 
    TRY_CAST(qp.query_plan as XML) as query_plan,
    qrs.count_executions
FROM sys.query_store_query as qs
JOIN sys.query_store_query_text as qt on qs.query_text_id=qt.query_text_id
JOIN sys.query_store_plan as qp on qs.query_id=qp.query_id
JOIN sys.query_store_runtime_stats qrs on qp.plan_id = qrs.plan_id
JOIN sys.query_store_runtime_stats_interval qrsi on qrs.runtime_stats_interval_id=qrsi.runtime_stats_interval_id
WHERE query_plan_hash =  @query_plan_hash
ORDER BY interval_end_time, query_id;
GO

Potrebbe non essere possibile ottenere un piano di esecuzione di query da Query Store, a seconda delle impostazioni CLEANUP_POLICY o QUERY_CAPTURE_MODE di Query Store. In questo caso, è spesso possibile ottenere informazioni necessarie visualizzando il piano di esecuzione stimato per la query.

Cercare schemi che aumentano il bloccaggio

Quando si esaminano i piani di esecuzione delle query coinvolti nei deadlock, esaminare gli schemi che possono contribuire al bloccaggio e ai deadlock.

• Analisi di tabelle o indici. Quando le query che modificano i dati vengono eseguite in RCSI, la selezione delle righe da aggiornare viene eseguita usando un'analisi di bloccaggio in cui viene eseguito un blocco di aggiornamento (U) sulla riga di dati man mano che vengono letti i valori dei dati. Se la riga di dati non rispetta i criteri di aggiornamento, il blocco di aggiornamento viene rilasciato e la riga successiva viene bloccata e analizzata.

  L'ottimizzazione degli indici per consentire alle query di modifica di trovare le righe più efficientemente riduce il numero di blocchi di aggiornamento emessi. Questo riduce le probabilità di bloccaggio e di deadlock.

• Viste indicizzate che fanno riferimento a più tabelle. Quando si modifica una tabella a cui si fa riferimento in una vista indicizzata, il motore di database deve mantenere anche la vista indicizzata. Questo richiede l'uso di più blocchi e può comportare un aumento del bloccaggio e dei deadlock. Le viste indicizzate possono anche causare l'esecuzione interna delle operazioni di aggiornamento nel livello di isolamento Read Committed.

• Modifiche alle colonne a cui si fa riferimento nei vincoli di chiave esterna. Quando si modificano colonne in una tabella a cui si fa riferimento in un vincolo FOREIGN KEY, il motore di database deve cercare le righe correlate nella tabella di riferimento. Non è possibile usare le versioni di riga per tali letture. Nei casi in cui gli aggiornamenti o le eliminazioni a catena sono abilitati, il livello di isolamento può essere riassegnato a serializzabile per la durata dell'istruzione per la protezione da inserimenti fantasma.

• Hint di blocco. Cercare hint di tabella che specificano i livelli di isolamento che richiedono più blocchi. Questi hint includono HOLDLOCK (che è equivalente a serializzabile), SERIALIZABLE, READCOMMITTEDLOCK (che disabilita RCSI) e REPEATABLEREAD. Inoltre, gli hint come PAGLOCK, TABLOCK, UPDLOCK e XLOCK possono aumentare i rischi di bloccaggio e deadlock.

  Se questi hint sono presenti, cercare il motivo per cui sono stati implementati. Questi hint possono impedire le race condition e garantire la validità dei dati. Potrebbe essere possibile lasciare in vigore questi hint e impedire futuri deadlock usando un metodo alternativo illustrato nella sezione Impedire la ripetizione di un deadlock di questo articolo, se necessario.

  Nota

  Altre informazioni sul comportamento durante la modifica dei dati usando il controllo delle versioni delle righe nella Guida per il controllo delle versioni delle righe e il blocco delle transazioni.

Quando si esamina il codice completo di una transazione, in un piano di esecuzione o nel codice di query di applicazione, cercare schemi problematici aggiuntivi:

• Interazione dell'utente nelle transazioni. L'interazione dell'utente in una transazione esplicita con più istruzioni aumenta significativamente la durata delle transazioni. Questo rende più probabile che tali transazioni si sovrappongano e che si verifichino bloccaggi e deadlock.

  Analogamente, mantenere una transazione aperta ed eseguire query su un database o un sistema non correlati durante la transazione aumenta significativamente le probabilità di bloccaggio e deadlock.

• Transazioni che accedono a oggetti in ordini diversi. È meno probabile che si verifichino deadlock quando transazioni simultanee, esplicite e con più istruzioni seguono gli stessi schemi e gli stessi oggetti di accesso nello stesso ordine.

Impedire la ripetizione di un deadlock

Sono disponibili più tecniche per impedire la ripetizione dei deadlock, tra cui l'ottimizzazione dell'indice, la forzatura dei piani con Query Store e la modifica di query Transact-SQL.

• Esaminare l'indice cluster della tabella. La maggior parte delle tabelle trae vantaggio dagli indici cluster, ma le tabelle spesso vengono implementate come heap per errore.

  Un modo per verificare la presenza di un indice cluster consiste nell'usare la stored procedure di sistema sp_helpindex. Ad esempio, è possibile visualizzare un riepilogo degli indici nella tabella SalesLT.Product eseguendo l'istruzione seguente:

  exec sp_helpindex 'SalesLT.Product';
  GO
  

  Esaminare la colonna index_description. Una tabella può avere un solo indice cluster. Se per la tabella è stato implementato un indice cluster, index_description conterrà la parola "clustered".

  Se non è presente un indice cluster, la tabella è un heap. In tal caso, verificare se la tabella è stata creata intenzionalmente come heap per risolvere un problema di prestazioni specifico. Prendere in considerazione l'implementazione di un indice cluster in base alle linee guida per la progettazione di indici cluster.

  In alcuni casi, la creazione o l'ottimizzazione di un indice cluster può ridurre o eliminare il bloccaggio nei deadlock. In altri casi, potrebbe essere necessario usare una tecnica aggiuntiva, come ad esempio le altre in questo elenco.

• Creare o modificare indici non cluster. L'ottimizzazione degli indici non cluster consente alle query di modifica di trovare i dati da aggiornare in modo più rapido, riducendo così il numero di blocchi di aggiornamento necessari.

  Nel deadlock di esempio il piano di esecuzione della query trovato in Query Store contiene un'analisi dell'indice cluster sull'indice PK_Product_ProductID. Il grafico di deadlock indica che l'attesa di un blocco condiviso (S) per questo indice è un componente nel deadlock.

  

  Questa analisi dell'indice viene eseguita perché la query di aggiornamento deve modificare una vista indicizzata con nome vProductAndDescription. Come accennato nella sezione Cercare schemi che aumentano il blocco di questo articolo, le viste indicizzate che fanno riferimento a più tabelle possono aumentare il bloccaggio e la probabilità di deadlock.

  Se si crea l'indice non cluster seguente nel database AdventureWorksLT che "copre" le colonne da SalesLT.Product a cui fa riferimento la vista indicizzata, questo consente alla query di trovare le righe in modo molto più efficiente:

  CREATE INDEX ix_Product_ProductID_Name_ProductModelID on SalesLT.Product (ProductID, Name, ProductModelID);
  GO
  

  Dopo aver creato questo indice, il deadlock non si ripete più.

  Quando i deadlock comportano modifiche alle colonne a cui si fa riferimento nei vincoli di chiave esterna, assicurarsi che gli indici nella tabella di riferimento di FOREIGN KEY supportino in modo efficiente la ricerca di righe correlate.

  Anche se gli indici possono migliorare notevolmente le prestazioni delle query in alcuni casi, gli indici hanno anche costi generali e di gestione. Esaminare lelinee guida generali per la progettazione degli indici per valutare il vantaggio degli indici prima di crearli, in particolare per quanto riguarda indici estesi e indici su grandi tabelle.

• Valutare il valore delle viste indicizzate. Un'altra opzione per impedire la ripetizione del deadlock di esempio consiste nell'escludere la vista indicizzata SalesLT.vProductAndDescription. Se tale vista indicizzata non viene utilizzata, questo riduce il sovraccarico di gestione della vista indicizzata nel tempo.

• Usare l'isolamento dello snapshot. In alcuni casi, l'impostazione del livello di isolamento della transazione su snapshot per una o più delle transazioni coinvolte in un deadlock può impedire la ripetizione di bloccaggi e deadlock.

  È molto probabile che questa tecnica abbia esito positivo quando viene usata nelle istruzioni SELECT quando lo snapshot Read Committed è disabilitato in un database. Quando lo snapshot Read Committed è disabilitato, le query SELECT che usano il livello di isolamento Read Committed richiedono blocchi condivisi (S). L'uso dell'isolamento dello snapshot in queste transazioni elimina la necessità di blocchi condivisi, che possono impedire bloccaggi e deadlock.

  Nei database in cui è stato abilitato l'isolamento dello snapshot Read Committed, le query SELECT non richiedono blocchi condivisi (S), pertanto è più probabile che si verifichino deadlock tra le transazioni che modificano i dati. Nei casi in cui si verificano deadlock tra più transazioni che modificano i dati, l'isolamento dello snapshot può comportare un conflitto di aggiornamento anziché un deadlock. In modo analogo, è necessario che una delle transazioni ritenti l'operazione.

• Forzare un piano tramite Query Store. È possibile che una delle query nel deadlock includa più piani di esecuzione, e che il deadlock si verifichi solo quando viene usato un piano specifico. È possibile impedire la ripetizione del deadlock forzando un piano in Query Store.

• Modificare il codice Transact-SQL. Potrebbe essere necessario modificare il codice Transact-SQL per impedire la ripetizione del deadlock. La modifica del codice Transact-SQL deve essere eseguita con attenzione e le modifiche devono essere testate rigorosamente per assicurarsi che i dati siano corretti quando le modifiche vengono eseguite contemporaneamente. Quando si riscrive il codice Transact-SQL, prendere in considerazione:

  • Istruzioni di ordinamento nelle transazioni in modo che accedano agli oggetti nello stesso ordine.
  • Quando possibile, suddividere le transazioni in transazioni più piccole.
  • Usare hint per la query, se necessario, per ottimizzare le prestazioni. È possibile applicare hint senza modificare il codice applicazione tramite Query Store.

È possibile trovare altri modi per ridurre al minimo i deadlock nella Guida ai deadlock.

Escludere una sessione di XEvents

È possibile lasciare che una sessione di XEvents raccolga informazioni sui deadlock in esecuzione nei database critici per lunghi periodi. Tenere presente che se si usa una destinazione file di eventi, questo può comportare file di grandi dimensioni se si verificano più deadlock. È possibile eliminare i file BLOB da Archiviazione di Azure per una traccia attiva ad eccezione del file in cui è in corso la scrittura.

Quando si desidera rimuovere una sessione di XEvents, il codice Transact-SQL per l'esclusione della sessione è lo stesso, indipendentemente dal tipo di destinazione selezionato.

Per rimuovere una sessione di XEvents, eseguire il codice Transact-SQL seguente. Prima di eseguire il codice, sostituire il nome della sessione con il valore corretto.

ALTER EVENT SESSION [deadlocks] ON DATABASE
    STATE = STOP;
GO

DROP EVENT SESSION [deadlocks] ON DATABASE;
GO

Usare Azure Storage Explorer

Azure Storage Explorer è un'applicazione autonoma che semplifica l'uso delle destinazioni file di eventi archiviate nei BLOB in Archiviazione di Azure. È possibile usare Azure Storage Explorer per:

• Creare un contenitore BLOB per contenere i dati della sessione di XEvent.
• Ottenere una firma di accesso condiviso (SAS) per il contenitore BLOB.
  • Come indicato in Raccogliere grafici di deadlock in database SQL di Azure con Eventi estesi, sono necessarie le autorizzazioni di lettura, scrittura ed elenco.
  • Rimuovere qualsiasi carattere iniziale ? da Query string per usare il valore come segreto durante la creazione di credenziali con ambito database.
• Visualizzare e scaricare i file di eventi estesi da un contenitore BLOB.

Scaricare Azure Storage Explorer.

Passaggi successivi

Informazioni sulle prestazioni in database SQL di Azure:

• Comprendere e risolvere i problemi che causano un blocco per il database SQL di Azure
• Guida per il controllo delle versioni delle righe e il blocco della transazione
• Guida ai deadlock
• SET TRANSACTION ISOLATION LEVEL
• Database SQL di Azure: migliorare l'ottimizzazione delle prestazioni con l'ottimizzazione automatica
• Offrire prestazioni coerenti con Azure SQL
• Logica di ripetizione dei tentativi per errori temporanei.