Deadlock su SQL Server: cosa sono e come prevenirli

In questo articolo studiamo cosa sono i Deadlock, come si generano su SQL Server e alcune buone pratiche di programmazione per diminuire le probabilità che si ripetano in futuro.

Indice dei contenuti

Generiamo un deadlock su SQL Server
Come evitare i deadlock
Cosa fare quando c’è un errore legato a un Deadlock
Conclusioni

Generiamo un deadlock su SQL Server

Per capire bene cos’è un Deadlock su un database, generiamone insieme uno su SQL Server. Utilizzate ovviamente una vostra installazione personale e di test in quanto eseguiremo delle query di aggiornamento. Nel mio repository di Github trovate gli script per generare dei database d’esempio https://github.com/iantomasinicola/DatabaseYimp/blob/master/Gestionale_SqlServer.txt.

Partiamo aprendo due sessioni della stessa istanza di SQL Server, per far ciò ci basterà cliccare semplicemente due volte sul tasto “Nuova query” di SQL Server Management Studio.

Nella prima sessione apriamo una transazione e aggiorniamo la tabella Clienti del database Gestionale.

--prima sessione
BEGIN TRAN;
UPDATE Gestionale.dbo.Clienti
SET    Nome = 'Nicola'
WHERE  IdCliente = 1;

Eseguiamo un’operazione simile anche nella seconda sessione, aggiornando però una tabella differente, ad esempio Fatture.

--seconda sessione
BEGIN TRAN;
UPDATE Gestionale.dbo.Fatture
SET    Importo = 10 
WHERE  IdFattura = 1;

A questo punto, sempre nella seconda sessione, lanciamo un UPDATE sulla stessa tabella Clienti aggiornata nella prima sessione. Se in precedenza abbiamo inserito una condizione WHERE, ripetiamola identica anche ora:

--seconda sessione
UPDATE Gestionale.dbo.Clienti
SET    Nome = 'Francesco' 
WHERE  IdCliente = 1;

A questo punto l’UPDATE non sarà eseguita, ma resterà in attesa della chiusura della prima transazione. Tecnicamente diciamo che la prima UPDATE ha generato un lock esclusivo sulla riga della tabella. Ritorniamo ora sulla prima sessione e aggiorniamo questa volta la tabella Fatture, utilizzata già all’inizio della seconda sessione.

--prima sessione
UPDATE Gestionale.dbo.Fatture
SET    Importo = 10 
WHERE  IdFattura = 1;

Abbiamo creato una situazione di stallo! La prima sessione è bloccata in attesa della seconda e viceversa, abbiamo quindi un deadlock delle risorse. SQL Server intercetta questo blocco potenzialmente infinito e decide di:

generare un errore in una delle due sessioni;
sbloccare l’aggiornamento nell’altra.

La scelta su quale transazione sbloccare o far andare in errore è demandata a SQL Server. In alcuni casi molto particolari potrei voler pilotare questa scelta tramite l’utilizzo dell’impostazione DEADLOCK_PRIORITY come descritto in questo articolo https://www.mssqltips.com/sqlservertip/7472/sql-deadlock-priority-roll-back-transaction/

T-SQL avanzato e tuning delle query

Come evitare i deadlock

In alcuni casi non è possibile escludere del tutto la possibilità che si verifichi un deadlock. Tramite queste buone pratiche è possibile minimizzare al minimo la probabilità che ciò accada:

raggruppare nella stessa transazione solo gli aggiornamenti veramente essenziali;
rendere il codice nelle transazioni il più performante possibile. Diminuendo la durata delle transazioni, caleranno automaticamente anche le probabilità di deadlock. Può essere utile a tal fine eseguire prima di aprire la transazione tutti i calcoli parziali necessari per l’aggiornamento, ad esempio archiviando in una tabella temporanea i dati da usare per l’UPDATE (se le dimensioni lo permettono);
aver creato un numero eccessivo di indici sulle tabelle si rifletterà su una durata maggiore delle operazioni di aggiornamento, in quanto dovranno essere allineate tutte le copie dei dati presenti negli indici. D’altra parte, alcuni indici possono avere un impatto positivo anche sulle query di aggiornamento. Guardiamo ad esempio il piano d’esecuzione di questa UPDATE su una tabella che contiene un indice nonclustered sulla colonna DataFattura:
```
UPDATE Gestionale.dbo.Fatture
SET    Spedizione = 10
WHERE  DataFattura = '20200101';
```
SQL Server utilizza l’indice per individuare più velocemente le righe da aggiornare. Vi ricordo inoltre che, nonostante il nome, all’interno dell’operatore Clustered Index Update avviene sia l’aggiornamento dell’indice clustered e sia quello degli eventuali indici nonclustered contenenti la colonna spedizione;
evitare sovrascritture inutili negli aggiornamenti. Rendo più chiaro questo punto con un esempio:
```
UPDATE Gestionale.dbo.Nome
SET    Nome = COALESCE(Nome, 'Nicola');
UPDATE Gestionale.dbo.Nome 
SET    Nome = 'Nicola'
WHERE  Nome IS NULL;
```
L’effetto finale delle due UPDATE in alto è sostanzialmente lo stesso, cioè modificare i NULL all’interno della colonna Nome in “Nicola”. Tuttavia con la prima query andrò a bloccare l’intera tabella, mentre con la seconda c’è la possibilità che vengano bloccate solo le righe in cui il Nome è NULL (tenendo presente comunque che SQL Server ha dei meccanismi per cui, quando deve bloccare troppe righe, preferisce bloccare comunque l’intera tabella);
l’ultimo punto riguarda di più la definizione del carico di lavoro sul database che le implementazioni in SQL. Ricordiamoci che possiamo sempre valutare se le procedure che generano i deadlock possano essere schedulate o eseguite in orari sufficientemente distanti.

Il tuo percorso completo di Analisi dei Dati

Cosa fare quando c’è un errore legato a un Deadlock

Nella maggior parte dei casi, per rimediare temporaneamente a un deadlock basterà rieseguire la transazione andata in errore. Ovviamente questa non può essere una regola da applicare sempre a occhi chiusi.

Chiarisco meglio con un esempio: supponiamo che il deadlock si sia generato in una stored procedure. Se essa è stata costruita con criterio avrà il codice incapsulato in un blocco TRY-CATCH per la gestione degli errori, come mostrato in questo articolo https://www.yimp.it/gestione-errori-e-transazioni-su-sql-server/ e conterrà al suo interno una sola transazione. Nessuno però potrebbe impedire a uno sviluppatore poco lungimirante di creare una stored procedure con due transazioni e due commit distinti. In questo caso, rieseguire semplicemente la procedura potrebbe portare a una ripetizioni non voluta anche della prima transazione, di cui era già stato eseguito il COMMIT.

Occorre dunque valutare di volta in volta l’operazione più idonea per rimediare all’errore e valutare se agire pro-attivamente prima che il deadlock si ripeta. Insieme alle attività generali di prevenzione descritte nel paragrafo precedente, il primo passo da fare è individuare la seconda query che ha generato il deadlock, oltre a quella in errore. Per far questo possiamo utilizzare l’extended-event di sistema system_health descritto in questo articolo https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/use-the-system-health-session?view=sql-server-ver16, creare un exdented-event custom o, nelle versioni più vecchie, utilizzare l’SQL Profiler.

Conclusioni

In questo articolo abbiamo approfondito come agire di fronte a una tipologia di errori molto comune su SQL Server legata alla presenza di deadlock. Se vuoi fare un upgrade delle tue competenze su SQL Server e le sue performance, visita la pagina del mio video corso https://www.yimp.it/video-corso-t-sql-avanzato-tuning-query/

Ecco un’altra lista di articoli del mio blog su SQL Server:

vediamo come migliorare le performance di una query con le tabelle temporanee;
analizziamo i piani d’esecuzione delle query;
studiamo il fenomeno del parameter sniffing.