Deadlock in SQL Server: Come funzionano e come evitarli!

I deadlock in SQL Server e Azure SQL possono rappresentare un problema significativo per le prestazioni e quando si presentano diventano presto motivo di discordia tra chi fornisce la soluzione database e chi la personalizza.

Prima di dare la definizione di deadlock esaminiamo le differenze tra lock e deadlock; quest’ultimo viene spesso confuso con il normale lock, ma sono due concetti distinti.

Quando una transazione T2 richiede un lock su una risorsa già bloccata da un’altra transazione T1, la transazione T2 entra in attesa e vi rimane finché il lock acquisito dalla transazione T1 non viene rilasciato. In questo caso, la transazione T2 è bloccata, ma non si tratta di un deadlock. Un deadlock si verifica solo quando due o più transazioni si bloccano reciprocamente, impedendo l’avanzamento di ciascuna di esse. I deadlock vengono risolti quasi immediatamente, l’Engine li rileva automaticamente e termina una delle transazioni coinvolte per sbloccare la situazione. Il lock, invece, può teoricamente durare all’infinito. By default, le transazioni in SQL Server e Azure SQL non hanno un timeout e rimangono in attesa potenzialmente all’infinito, a meno che non venga impostato un timeout con l’opzione LOCK_TIMEOUT o non intervenga manualmente un DBA.

La figura seguente esprime il concetto lock, una automobile ha acquisito un lock esclusivo sulla risorsa “uscita del traghetto” e le altre auto attendono che il lock venga rilasciato

Falsi miti sui deadlock

• I deadlock in SQL Server sono bug: Falso! I deadlock non sono bug, ma situazioni normali che possono verificarsi quando più transazioni competono per le stesse risorse

• I deadlock non possono essere prevenuti: Falso! Sebbene i deadlock non possano essere eliminati completamente in tutti i sistemi concorrenti, è possibile ridurne drasticamente l’incidenza attraverso una gestione corretta delle transazioni, un accesso coerente alle risorse e strategie di indicizzazione appropriate

• Questo è il falso mito su cui ho combattuto di più: Usare NOLOCK su tutte le SELECT previene i deadlock. È falso! L’hint di query NOLOCK non considera i lock in essere, come se il livello di isolamento fosse READ UNCOMMITTED; consente la lettura di dati non confermati, potenzialmente inconsistenti. Non è una soluzione ai deadlock, ma un compromesso con effetti collaterali

• Aggiungere indici di copertura per ogni query previene i deadlock: Falso! Gli indici possono ridurre il locking e migliorare le performance, ma non sono una soluzione universale ai deadlock. Il loro utilizzo va valutato caso per caso

Cosa sono i deadlock?

Un deadlock è un tipo specifico di lock che si verifica quando due o più task si bloccano reciprocamente in modo permanente, creando una situazione di dipendenza ciclica. In altre parole, ciascun task aspetta che un altro task rilasci una risorsa di cui ha bisogno, ma nessuno dei task coinvolti può procedere. Questo ciclo di attesa infinita impedisce l’avanzamento di tutte le transazioni coinvolte, a meno che una di esse non venga terminata forzatamente per interrompere la dipendenza ciclica.
Un esempio di dipendenza ciclica, T1 e T2 sono due transazioni e nel loro contesto vengono eseguiti comandi di modifica dei dati.

• T1 acquisisce un lock su una riga row 1
• T2 acquisisce un lock su un’altra riga row 2
• T1 tenta di acquisire un lock su row 2, ma non può farlo e rimane bloccata perché T2 detiene già un lock su quella riga
• T2, a sua volta, tenta di acquisire un lock su row 1, ma non può farlo e rimane bloccata perché T1 ha già un lock su quella riga
  

Nessuna delle due transazioni può proseguire perché ognuna sta aspettando che l’altra rilasci il lock di cui ha bisogno per completarsi. Si crea così una dipendenza ciclica, dove T1 dipende da T2 e viceversa. Senza un intervento esterno, entrambe le transazioni rimarrebbero bloccate all’infinito.

SQL Server lock monitor

Lock monitor è il processo di sistema a cui è affidata l’individuazione e risoluzione dei deadlock. Funziona eseguendo scansioni periodiche per identificare le transazioni bloccate, analizza le transazioni e le risorse coinvolte, segue la catena di blocchi per verificare se si è formato un deadlock. By default esegue una scansione ogni 5 secondi ma se vengono rilevati deadlock, l’intervallo di scansione si riduce fino a 100 millisecondi al fine di identificare più rapidamente nuovi deadlock. Quando viene rilevato un deadlock, una delle transazioni viene scelta come “vittima” e terminata con l’errore 1205, in modo che l’altra possa completarsi. La transazione da terminare viene scelta con questi criteri:

• La transazione con la priorità più bassa DEADLOCK_PRIORITY viene terminata per prima
• Se le transazioni hanno la stessa priorità, viene terminata quella con il minor costo di rollback

DEV e DBA possono influenzare la scelta della transazione da terminare forzatamente in caso di deadlock utilizzando l’opzione DEADLOCK_PRIORITY a livello di sessione, l’opzione permette di assegnare una priorità bassa, normale o alta alle transazioni nella sessione, un esempio di impostazione dell’opzione nel seguente frammento di codice T-SQL:

-- Aumenta la probabilità che questa sessione venga terminata in caso di deadlock
SET DEADLOCK_PRIORITY LOW;

-- Diminuisce la probabilità che questa sessione venga terminata
SET DEADLOCK_PRIORITY HIGH;

Strumenti per l’identificazione dei Deadlock

Per individuare i deadlock in SQL Server, è possibile utilizzare i seguenti strumenti di monitoraggio:

• Extended Events (raccomandato): fornisce un metodo efficiente per tracciare e analizzare i deadlock in SQL Server
• SQL Profiler: È lo strumento più datato ma ancora utile per analizzare il comportamento delle transazioni e individuare i deadlock
• Trace Flags 1204 e 1222: forniscono informazioni dettagliate sui deadlock nel log errori di SQL Server, ma non sono consigliati su sistemi con carichi di lavoro elevati
• Event Notifications: permettono di ricevere notifiche sugli eventi di deadlock

Una volta individuato un deadlock, è importante analizzare il grafico del deadlock per capire le risorse coinvolte e le transazioni in conflitto. Questo aiuterà a identificare le cause principali e a prendere misure correttive.

Best practices per la prevenzione dei deadlock

Sebbene i deadlock non possano essere completamente evitati in un sistema multiutente, esistono diverse strategie per ridurne significativamente la frequenza. Seguendo le seguenti best practice di programmazione e di gestione delle transazioni, è possibile minimizzare il rischio di deadlock:

• Accesso ordinato alle risorse: Quando più transazioni devono accedere agli stessi oggetti, assicurarsi che seguano sempre lo stesso ordine; questo riduce il rischio di dipendenze cicliche
• Evitare l’interazione utente all’interno delle transazioni: Inserire input da parte dell’utente all’interno di una transazione aumenta il rischio di deadlock
• Ridurre la durata delle transazioni: Più a lungo una transazione mantiene un lock su una risorsa, maggiore è la possibilità di generare blocchi e deadlock
• Adottare i livelli di isolamento basi sul versioning delle righe (quando possibile): Livelli di isolamento come READ_COMMITTED_SNAPSHOT e SNAPSHOT limitano i lock in lettura con conseguente riduzione dei deadlock

Conclusioni

I deadlock in SQL Server non sono bug, ma il risultato naturale della contesa delle risorse in ambienti multiutente. Per prevenire i deadlock, è fondamentale progettare attentamente la strategia di accesso alle risorse garantendo un ordine coerente e adottando un livello isolamento basato sul versioning delle righe, quando possibile. Strumenti come Extended Events, SQL Profiler, Trace Flags e First Responder Kit sono essenziali per identificare e monitorare i deadlock, fornendo informazioni dettagliate che aiutano DEV e DBA a ottimizzare la gestione della concorrenza e migliorare le prestazioni complessive del database.

Segnalo la registrazione della sessione SQL Server deadlocks: Tecniche per individuarli e risolverli tenuta al Data Saturday Parma 2024.