Foto di James Sutton su Unsplash
Nota: il problema descritto di seguito si applica a tutte le query SELEZIONATE, non solo a quelle adornate con suggerimenti NOLOCK. Il fatto che si applichi alle query NOLOCK è stata un’enorme sorpresa per me, da qui il titolo.
A molte persone non piace NOLOCK (cioè il livello di isolamento read uncommitted) perché può restituire dati imprecisi., Ho visto molti argomenti che mettono in guardia gli sviluppatori dal recupero di letture non committed a causa di come possono restituire dati sporchi, letture fantasma e letture non ripetibili .
Ho conosciuto tutti questi problemi di cui sopra, ma c’è un problema di cui non ho mai sentito parlare fino a poco tempo fa: NOLOCK può bloccare l’esecuzione di altre query.
Guarda il video di questa settimana su YouTube
Facciamo un passo indietro e capiamo perché ho usato così spesso NOLOCK in passato. Un’istanza abbastanza tipica di quando uso NOLOCK è quando voglio lasciare che una query venga eseguita durante la notte per restituire un ampio set di dati., Sto bene con alcune incongruenze nei dati(da letture sporche, ecc…). La mia preoccupazione principale è che non voglio che la query a lunga esecuzione intralci altri processi.
Ho sempre pensato che NOLOCK fosse una soluzione perfetta per questo scenario perché non blocca mai i dati che legge – i risultati potrebbero non essere perfetti, ma almeno la query non avrà un impatto negativo su nessun altro processo sul server.
Questo è dove la mia comprensione di NOLOCK era sbagliata: mentre NOLOCK non bloccherà i dati a livello di riga, eliminerà un blocco di stabilità dello schema.,
Un blocco Sch-S (schema s tability) impedisce la modifica della struttura di una tabella durante l’esecuzione della query. Tutte le istruzioni SELECT, incluse quelle nel livello di isolamento read uncommitted / NOLOCK, eliminano un blocco Sch-S. Questo ha senso perché non vorremmo iniziare a leggere i dati da una tabella e quindi far cambiare la struttura della colonna a metà del recupero dei dati.
Tuttavia, questo significa anche che potrebbero esserci alcune operazioni che vengono bloccate da un blocco Sch-S. Ad esempio, qualsiasi comando che richiede un blocco di modifica dello schema (Sch-M) viene bloccato in questo scenario.,
Quali comandi richiedono i blocchi Sch-M?
Cose come una RICOSTRUZIONE dell’indice o una tabella sp_recompile. Questi sono i tipi di comandi in esecuzione nei miei lavori di manutenzione notturna che stavo cercando di evitare di ferire usando NOLOCK in primo luogo!
Per ribadire, pensavo che l’uso del suggerimento NOLOCK fosse un ottimo modo per prevenire il blocco durante le query di lunga durata., Tuttavia, si scopre che le mie query NOLOCK stavano effettivamente bloccando i miei lavori di indice notturni (tutte le query SELECT bloccano in questo esempio, ma trovo che NOLOCK sia particolarmente fuorviante), il che ha causato anche il blocco di altre istruzioni SELECT!
Diamo un’occhiata a questo in azione. Qui ho una query che crea un database, una tabella e quindi esegue una query a lunga esecuzione con NOLOCK:
Ora, mentre si sta verificando quel miliardo di righe, possiamo verificare che la query abbia eliminato un blocco Sch-S guardando sys.,dm_tran_locks:
SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'OBJECT'
Mentre è in esecuzione, se si tenta di ricostruire un indice, che la ricostruzione è bloccata (indicato come un tempo di ATTESA):
USE GOALTER INDEX IX_Id ON dbo.Test REBUILD
il Nostro indice di ricostruire query rimarrà bloccato fino a quando le nostre miliardi di fila NOLOCK query di selezione al termine dell’esecuzione (o ucciso). Ciò significa che la query che intendevo essere completamente discreta ora sta bloccando l’esecuzione del mio lavoro di manutenzione dell’indice notturno.,
Ancora peggio, tutte le altre query che tentano di eseguire dopo la query di RICOSTRUZIONE (o qualsiasi altro comando che richiede un blocco Sch-M) verranno bloccate! Se provo a eseguire una semplice query COUNT (*):
USE GOSELECT COUNT(*) FROM dbo.Test
Bloccato! Ciò significa che non solo la mia query NOLOCK iniziale causa l’attesa dei lavori di manutenzione della ricostruzione dell’indice, ma il blocco Sch-M posto dal lavoro di manutenzione della RICOSTRUZIONE causa il blocco di tutte le query successive su quella tabella ed è costretto ad attendere., Ho appena deragliato la tempestività del mio lavoro di manutenzione e le successive query con una dichiarazione di blocco NOLOCK!
Soluzioni
Sfortunatamente questo è un problema difficile e non esiste un rimedio adatto a tutti.
Soluzione n.1: non eseguire query lunghe
Potrei evitare di eseguire query lunghe di notte quando potrebbero incorrere nei miei lavori di manutenzione dell’indice., Ciò impedirebbe che i lavori di manutenzione dell’indice e le query successive vengano ritardati, ma significa che la mia query iniziale di selezione di miliardi di righe dovrebbe quindi essere eseguita prima, influenzando negativamente le prestazioni del server durante un’ora del giorno potenzialmente più trafficata.,
Soluzione #2: Usa WAIT_AT_LOW_PRIORITY
A partire dal 2014, potrei eseguire una ricostruzione dell’indice online con il set di opzioni WAIT_AT_LOW_PRIORITY:
ALTER INDEX IX_Id ON dbo.Test REBUILD WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES , ABORT_AFTER_WAIT = BLOCKERS)))
Questa query fornisce fondamentalmente qualsiasi query di selezione di blocco attualmente in esecuzione 1 minuto per completare l’esecuzione altrimenti questa query li ucciderà e quindi eseguirà la ricostruzione dell’indice. In alternativa, avremmo potuto anche impostare ABORT_AFTER_WAIT = SELF e la query di ricostruzione si sarebbe uccisa, consentendo alla selezione di NOLOCK billion row di terminare l’esecuzione e non impedire l’esecuzione di altre query.,
Questa non è una grande soluzione perché significa che la query a lunga esecuzione viene uccisa o la RICOSTRUZIONE dell’indice viene uccisa.
Soluzione #3: RICOSTRUIRE se non Sch-S, RIORGANIZZARE altrimenti
Una soluzione programmatica può essere scritta che tenta di RICOSTRUIRE l’indice, ma ricade per RIORGANIZZARE se sa che dovrà attendere un blocco Sch-M.
Ho creato la piastra della caldaia qui sotto come punto di partenza, ma il cielo è il limite con quello che puoi fare con esso (ad esempio, crea un ciclo WHILE per verificare il blocco ogni x secondi, crea un timeout per quando lo script dovrebbe smettere di provare a RICOSTRUIRE e semplicemente RIORGANIZZARE, ecc…)
Questa soluzione è la mia preferita perché:
- Le query ad hoc a lunga esecuzione non vengono uccise (tutto il tempo trascorso dall’elaborazione non va sprecato)
- Altre query select non sono bloccate dal tentativo di blocco Sch-M di REBUILD
- La manutenzione dell’indice si verifica ancora, anche se finisce per essere una RIORGANIZZAZIONE anziché una RICOSTRUZIONE
