Nel post parliamo su come gestire i deadlock in SQL Server.
Un deadlock si verifica quando 2 processi competono per l’accesso esclusivo a una risorsa, ma non è in grado di ottenere l’accesso esclusivo ad essa perché l’altro processo la impedisce. Ciò si traduce in una situazione di stallo in cui nessuno dei due processi può procedere. L’unica via d’uscita da un deadlock è che uno dei processi venga terminato. SQL Server rileva automaticamente quando si sono verificati deadlock e si attiva uccidendo uno dei processi noti come vittima.
I deadlock non si verificano solo nei blocchi, da SQL Server 2012 in poi, i deadlock possono verificarsi anche con memoria, risorse MARS (Multiple Active Result Sets), thread di lavoro e risorse relative all’esecuzione parallela delle query.
Facciamo una panoramica sul modo in cui SQL Server gestisce i deadlock e vediamo alcuni tipi di deadlock in SQL Server e come è possibile evitarli e risolverli.
Sommario
Come faccio a sapere se ho un deadlock?
Il primo segno che avrai di un deadlock è il seguente messaggio di errore che verrà visualizzato all’utente che possiede il processo selezionato come vittima del deadlock.
Msg 1205, Level 13, State 51, Line 6
Transaction (Process ID 62) è stato bloccato sulle risorse di blocco con un altro processo ed è stato scelto come vittima del deadlock. Rieseguire la transazione.L’altro utente il cui processo non è stato selezionato come vittima sarà molto probabilmente completamente all’oscuro che il loro processo ha partecipato a un deadlock.
Gestione deadlock
La cosa bella dei deadlock è che SQL Server li rileva e li risolve automaticamente. Per risolvere un deadlock, SQL Server deve eseguire il rollback delle transazioni più economiche.
Nel contesto di SQL Server, la transazione più economica è la transazione che ha scritto il minor numero di byte nel log delle transazioni.
SQL Server implementa il rilevamento dei deadlock in un processo in background denominato Deadlock Monitor (LOCK MONITOR).
Questo processo in background viene eseguito ogni 5 secondi e controlla la situazione di blocco corrente alla ricerca di deadlock. Nel peggiore dei casi, quindi, un blocco non dovrebbe durare più di 5 secondi.
Quando viene rilevato un deadlock in SQL Server viene scelta una delle transazioni come vittima e inviato un errore 1205 al client che possiede la connessione. Questa transazione viene quindi terminata e ripristinata, rilasciando tutte le risorse su cui ha tenuto un blocco, consentendo all’altra transazione coinvolta nel deadlock di continuare.
In che modo SQL Server sceglie la vittima?
Ci sono un paio di fattori che entrano in gioco qui. Il primo è la priorità. La priorità di deadlock di una transazione può essere impostata utilizzando il comando seguente:
SET DEADLOCK_PRIORITY LOW; I valori tipici per la priorità del deadlock sono:
| Priorità | Valore | Risultato |
| Basso | -5 | Se altre transazioni hanno una priorità NORMAL o HIGH o numericamente superiore a -5 , questa transazione verrà scelta come vittima del deadlock |
| Normale | 0 | Questa è la priorità predefinita. La transazione potrebbe essere scelta come vittima se altre transazioni hanno una priorità superiore a 0. |
| alto | 5 | Questo processo non verrà selezionato come vittima a meno che non vi sia un processo con una priorità numerica superiore a 5. |
| <numerico> | Da -10 a 10 anni | Questo può essere usato per gestire la priorità del deadlock a un livello più granulare. |
Se le transazioni coinvolte in un deadlock hanno la stessa priorità di deadlock, viene ripristinato quello con il costo più basso. In un esempio quello in cui è stata utilizzata la quantità minima di log delle transazioni, che indica che sono disponibili meno dati da eseguire il rollback.
Tipi di deadlock in SQL Server
In SQL Server possono verificarsi più tipi di deadlock. In questa sezione vediamo i più comuni.
Un tipico deadlock che vedo in quasi tutte le installazione di SQL Server è il famoso Bookmark Lookup Deadlock, che si verifica quando si dispone di attività di lettura e scrittura simultanee negli indici cluster e non cluster.
È principalmente un deadlock che si verifica a causa di una cattiva strategia di indicizzazione.
Un Bookmark Lookup Deadlock può essere eliminato molto facilmente fornendo un indice di copertura non cluster.
Un altro deadlock comune è il cosiddetto Cycle Deadlock , in cui le singole query hanno eseguito l’accesso a tabelle in ordini diversi. Per evitare questo deadlock specifico, è sempre necessario assicurarsi che le query accedono alle tabelle nello stesso ordine.
E il deadlock più “bello” che può verificarsi in SQL Server è il cosiddetto deadlock intra-parallelismo, in cui un operatore di parallelismo (Distribute Streams, Gather Streams, Repartition Streams) si è bloccato internamente tra i singoli thread. L’immagine seguente mostra un tipico grafico a deadlock.
Deadlock con SQL Server Profiler
È possibile risolvere un deadlock in più modi. SQL Server Profiler fornisce l’evento Deadlock Graph, che si verifica non appena viene rilevato un deadlock. A partire da SQL Server 2008 e versione successiva, è anche possibile utilizzare eventi estesi per risolvere i problemi relativi ai deadlock. Extended Events fornisce la sessione system_health event, che tiene traccia dei deadlock cronologici dall’ultimo riavvio di SQL Server. Inoltre, con il flag di traccia 1222 abilitato, SQL Server registrerà le informazioni sui deadlock nel registro degli errori.
Risoluzione dei deadlock con il monitoraggio delle prestazioni di SQL Server
Se si sospetta che si verifichino deadlock nelle istanze di SQL Server, cosa è possibile fare? Vedremo tre approcci al loro rilevamento e risoluzione, ognuno dei quali comporta il recupero da SQL Server delle informazioni che descrivono il deadlock, ovvero il Deadlock Graph. In questo modo vengono eseguite le sessioni con deadlock, istruzioni in esecuzione, oggetti e le pagine in cui le sessioni sono state bloccate e altro ancora.
Traceflag 1222 – Metodo 1
L’unico modo per ottenere il grafico del deadlock era quello di abilitarlo per acquisirlo nel registro degli errori. La figura 1 mostra il contenuto del registro errori, evidenziando il processo selezionato come vittima del deadlock e quindi sopra di esso l’output del grafico di deadlock.
Tuttavia, a meno che il traceflag non sia abilitato in modo permanente, è necessario abilitarlo e attendere il ripetersi del deadlock.
Come puoi vedere, SQL Server riempie il registro degli errori con molte informazioni e c’è anche molto lavoro manuale da fare per l’amministratore di database nell’individuare l’errore.
Eventi estesi – Metodo 2
In SQL Server 2008 e versioni successive, la sessione di eventi estesa è abilitata per impostazione predefinita e acquisisce automaticamente il grafico dei deadlock. È possibile recuperarlo in risposta ad un avviso di errore 1205, eseguendo una query T-SQL/XPath o utilizzando il visualizzatore dati di destinazione dell’interfaccia utente eventi estesi in SSMS (SOLO SQL Server 2012 e versioni successive), come illustrato nella figura.
Potrebbero essere visualizzati migliaia di eventi, ma fare clic con il pulsante destro del mouse sul visualizzatore dati e scegliere Filtra in base a questo valore (oppure utilizzare il | Filter) per impostare un filtro sulla colonna name in modo che il visualizzatore visualizza un evento solo se “Contiene” il valore “deadlock“.
Facendo clic su un evento nel visualizzatore dati, è possibile visualizzare il grafico del deadlock, sia come XML(scheda Dettagli) che in forma grafica(scheda Deadlock).
Questo approccio è migliore rispetto all’utilizzo di traceflags, ma richiede comunque che l’amministratore di database sia abile nella lettura dei grafici di deadlock XML (l’output grafico ‘nasconde’ troppe informazioni) e manca ancora l’immagine completa di ciò che stava accadendo sul server al momento in cui si è verificato il deadlock. Il problema è stato esacerbato perché il server sotto CPU o IO o pressione della memoria in quel momento? Quale altra attività simultanea si stava verificando sul server?
SQL Monitor – Metodo 3
Uno strumento di monitoraggio delle prestazioni di SQL Server, ad esempio SQL Monitor, mira a fornire all’amministratore di database informazioni sufficienti per risolvere un deadlock, senza entrare troppo in profondità in un grafico di deadlock XML, e inserisce queste informazioni nel contesto del modello generale di attività sul server in quel momento.
Ciò non solo semplifica il processo di risoluzione dei problemi relativi al deadlock, ma rende anche la risoluzione dei problemi di prestazioni nel complesso molto più efficace.
Conclusione
I deadlock in Sql Server vengono gestiti automaticamente da motore del database che esegue il rollback della transazione più economica. Tuttavia, è oppurtuno ridurre al minimo i deadlock, perché ogni transazione di roll back influenza gli utenti finali in modo negativo.
I deadlock in Sql Server possono essere evitatii con una buona strategia di indicizzazione e l’utilizzo ottimale della gestione della concorrenza.
- Come scegliere un tema WordPress per il tuo blog. [4° Parte]
- Crittografare DNS su Ubuntu. Utilizziamo DNSCrypt
- Come disattivare Gutenberg in WordPress e usare l’editor classico.
- Malware Detection per sistemi mobili. [version 1.0]
- Aggiornamento Google Chrome 61. Stop ai certificati SSL da Wosign e StartCom.
