SQL Server deadlock - cause, diagnosi e soluzioni pratiche

Una transazione resta bloccata, un’altra attende una risorsa e in pochi secondi l’applicazione inizia a restituire errori. Il termine tecnico sql server deadlock descrive proprio questo circolo vizioso tra sessioni concorrenti. In questa guida mostro come riconoscerlo, leggere il deadlock graph, intervenire sul codice e ridurre il rischio che il problema ritorni.

Che cosa succede davvero durante un deadlock

Un blocco normale nasce quando una sessione deve attendere che un’altra liberi una riga, una pagina o una tabella. Il deadlock è diverso perché l’attesa è circolare: la transazione A possiede una risorsa che serve a B, mentre B possiede una risorsa che serve ad A. Nessuna delle due può andare avanti da sola.

SQL Server rileva il ciclo e interrompe una delle transazioni. La sessione scelta diventa la vittima, il lavoro già svolto nella sua transazione viene annullato e il client riceve l’errore 1205. L’altra transazione può quindi proseguire, ma l’applicazione deve essere preparata a ripetere l’operazione interrotta.

Questo dettaglio cambia il modo in cui affronto il problema. Un deadlock non si risolve semplicemente aumentando il timeout o riavviando il servizio SQL Server, perché il riavvio cancella il sintomo e non elimina il conflitto nel codice o nel modello di accesso ai dati.

Deadlock e blocking non sono la stessa cosa

Le cause più comuni nelle applicazioni reali

La causa che incontro più spesso è l’ordine inverso di accesso alle tabelle. Una transazione aggiorna prima Orders e poi Customers, mentre un’altra aggiorna prima Customers e poi Orders. Anche se ogni singola query sembra corretta, l’insieme crea facilmente un ciclo.

La regola pratica è semplice: tutte le operazioni che modificano più risorse dovrebbero seguire lo stesso ordine. Non basta applicarla a una stored procedure isolata, perché il deadlock può nascere dall’interazione tra codice applicativo, trigger, job pianificati e procedure eseguite da sistemi diversi.

Transazioni troppo lunghe

Più una transazione resta aperta, più a lungo conserva i lock. Una chiamata HTTP, una richiesta a un servizio esterno o una fase di elaborazione eseguita dentro BEGIN TRAN può trasformare un’operazione breve in una finestra di conflitto molto ampia.

Io tengo dentro la transazione solo le istruzioni che devono essere atomiche. La validazione complessa, la preparazione dei dati e le chiamate esterne dovrebbero avvenire prima o dopo, quando il caso d’uso lo permette. La durata reale della transazione conta spesso più del numero di query contenute al suo interno.

Indici mancanti e piani di esecuzione inefficienti

Una query che cerca poche righe ma non dispone dell’indice adatto può leggere migliaia o milioni di record. Questo aumenta il numero di lock, allunga la transazione e può favorire anche l’escalation dei lock, cioè il passaggio da lock più granulari a lock su pagine o tabelle.

Non aggiungerei indici alla cieca. Prima controllerei il piano di esecuzione, i predicati usati nei filtri e l’ordine delle colonne negli indici esistenti. Un indice ben scelto riduce il lavoro, ma uno superfluo aumenta il costo delle scritture e può introdurre nuovi compromessi.

Il classico schema lettura e aggiornamento

Un pattern delicato è leggere una riga per verificare una condizione e aggiornarla solo in un secondo momento. Con transazioni concorrenti e livelli di isolamento più restrittivi, due sessioni possono ottenere lock condivisi e poi tentare entrambe di convertirli in lock esclusivi.

Quando la logica lo richiede, l’hint UPDLOCK può rendere esplicita l’intenzione di aggiornare una riga. Non lo considero una cura universale: mantiene un lock più impegnativo e, se usato senza criterio, può aumentare l’attesa anziché ridurla.

Come diagnosticare il problema senza andare a tentativi

La prima cosa da cercare è il deadlock graph, non il solo messaggio mostrato dall’applicazione. Il grafo contiene la vittima, i processi coinvolti, le risorse contese, le istruzioni SQL e i lock posseduti o richiesti.

Nelle versioni moderne di SQL Server, la sessione Extended Events system_health raccoglie già gli eventi xml_deadlock_report. In SQL Server Management Studio si può aprire il percorso Management, Extended Events, Sessions, system_health e visualizzare il target dell’evento. Non disattiverei questa sessione: è attiva di default e ha un impatto normalmente molto contenuto.

Che cosa leggere nel grafo

• victim-list identifica la transazione terminata da SQL Server.
• process-list mostra session ID, query, database, isolamento e informazioni sulla connessione.
• resource-list indica righe, chiavi, pagine, tabelle o metadati coinvolti nel conflitto.
• I nodi con lock posseduti e richiesti permettono di ricostruire la direzione del ciclo.

Osservo soprattutto tre elementi. Il primo è l’ordine delle risorse, il secondo è la query che ha acquisito troppi lock e il terzo è il valore Log Used, utile per capire perché SQL Server abbia scelto proprio quella sessione come vittima quando le priorità erano uguali.

Una verifica rapida con Transact-SQL

SELECT
    CAST(xet.target_data AS xml) AS target_data
FROM sys.dm_xe_session_targets AS xet
JOIN sys.dm_xe_sessions AS xe
    ON xe.address = xet.event_session_address
WHERE xe.name = N'system_health';

La query consente di controllare i dati disponibili nel target della sessione. Per un’analisi continuativa preferisco salvare gli eventi in un file Extended Events e conservarli abbastanza a lungo da confrontare orario, query e frequenza degli incidenti. In Azure SQL Database, invece, la raccolta può richiedere una sessione Extended Events dedicata con l’evento appropriato.

Come interrompere il ciclo e correggere la causa

La correzione migliore dipende dal grafo, ma nella pratica le soluzioni ricorrenti sono abbastanza riconoscibili. Uniformare l’ordine di accesso alle tabelle, restringere i filtri, aggiungere l’indice corretto e ridurre la durata della transazione risolvono molti casi senza modificare il livello di isolamento.

Quando il codice esegue una sequenza di aggiornamenti, cerco di renderla prevedibile. Se due transazioni devono modificare più entità, entrambe dovrebbero acquisire i lock nello stesso ordine e lavorare su un insieme di righe il più piccolo possibile. La prevedibilità della concorrenza è più utile di una serie di hint aggiunti per tentativi.

Gestire l’errore 1205 nell’applicazione

Il retry deve ripetere l’intera transazione, non soltanto l’istruzione che ha fallito. Deve inoltre essere limitato, perché una transazione non idempotente ripetuta senza controllo potrebbe creare duplicati o applicare due volte lo stesso effetto.

DECLARE @attempt tinyint = 1;
DECLARE @max_attempts tinyint = 3;

WHILE @attempt <= @max_attempts
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;

        -- Operazioni che devono essere atomiche

        COMMIT TRANSACTION;
        BREAK;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;

        IF ERROR_NUMBER() <> 1205
            THROW;

        IF @attempt = @max_attempts
            THROW;

        WAITFOR DELAY '00:00:00.200';
        SET @attempt += 1;
    END CATCH;
END;

In produzione userei un backoff leggermente variabile, ad esempio tra 100 e 500 millisecondi, per evitare che più client ritentino nello stesso istante. Tre tentativi sono un punto di partenza ragionevole, non una legge: il valore deve dipendere dalla latenza accettabile e dall’importanza dell’operazione.

Quando usare DEADLOCK_PRIORITY

SET DEADLOCK_PRIORITY LOW è utile quando una sessione secondaria, come un report o un job di manutenzione, deve cedere il passo a una transazione operativa. Serve a decidere chi sarà più probabilmente la vittima, non a eliminare il conflitto.

Non lo userei per nascondere deadlock frequenti. Se l’errore compare ogni giorno, impostare una priorità bassa e ignorare il problema significa soltanto spostare il costo sull’utente o sul processo batch.

Come ridurre il rischio nel design del database

Il livello di isolamento READ_COMMITTED_SNAPSHOT può ridurre i conflitti tra letture e scritture perché le letture usano versioni delle righe invece di mantenere gli stessi lock condivisi. È una scelta interessante per sistemi con molte letture concorrenti, ma va valutata osservando il consumo di tempdb e il comportamento delle transazioni lunghe.

Il row versioning non elimina i deadlock tra due scritture che modificano le stesse righe. Inoltre, può portare a conflitti diversi, soprattutto con snapshot isolation e aggiornamenti concorrenti. Lo considero uno strumento di progettazione, non un interruttore da attivare per risolvere ogni problema di blocco.

Leggi anche: Python @staticmethod - quando usarlo e quali errori evitare

Pratiche che fanno davvero la differenza

• Tenere le transazioni brevi e delimitate alle sole operazioni necessarie.
• Usare lo stesso ordine di accesso per tabelle e risorse condivise.
• Verificare gli indici con piani di esecuzione e statistiche aggiornate.
• Evitare di usare NOLOCK come rimedio automatico, perché può restituire dati sporchi o incoerenti.
• Separare i job pesanti dalle finestre di massimo traffico quando è possibile.
• Rendere le operazioni ritentabili e idempotenti prima di aggiungere il retry.

Un errore comune è concentrarsi solo sulla query più lenta. Un deadlock dipende dalla combinazione tra query, ordine, isolamento, durata e dati coinvolti. Per questo confronto sempre almeno due esecuzioni concorrenti, anche quando una sola sembra essere la responsabile.

La checklist che uso prima di chiudere un incidente di deadlock

Conservo il grafo XML e annoto l’orario, il database, l’applicazione e le sessioni coinvolte. Poi verifico se il caso è ricorrente o isolato, se le query seguono ordini diversi e se un piano di esecuzione sta leggendo molte più righe del necessario.

Infine applico una correzione misurabile, come un indice, una transazione più breve o un ordine uniforme degli aggiornamenti, e controllo il comportamento nei giorni successivi. Un deadlock gestito correttamente non è solo un errore che l’applicazione ritenta: è un segnale utile per rendere il database più prevedibile sotto carico.