🟡 "with (nolock)” e quello che (non tutti) sappiamo

🔵 Introduzione

Cos’è “with (nolock)” e quando si usa? L’obiettivo del mio lavoro era scandagliare il software di un prodotto intercettando gli utilizzi di “with (nolock)”.

E’ noto che la clausola è utilizzabile solo in ambito SQL Server quindi, per rendere compatibile il prodotto con altri dbms, era necessario individuare i punti di utilizzo e considerare la piattaforma in cui il prodotto veniva distribuito.

Ho voluto comprendere meglio “with (nolock)” e ho scoperto alcuni aspetti importanti che non avevo ancora considerato.

L’attivazione della clausola è spesso impropriamente utilizzata allo scopo di migliorare le prestazioni di una query. Questo si avvera, in realtà, perché in questo modo il motore di SQL Server evita i blocchi (nolock) che si possono generare quando i dati, oggetto di estrazione, sono contemporaneamente in fase di aggiornamento.

Quindi, la query “sembra” migliorare le prestazioni solo perché non rimane in attesa. In realtà, il piano di esecuzione della query, con o senza “with (nolock)”, resta il medesimo.

E’ in questo scenario che entra in gioco l’attuazione di “with (nolock)”: l’accesso al database non rimane in attesa, però i dati estratti potrebbero non essere confermati (rollback). Si verifica in questo modo l’effetto comunemente noto come “dati sporchi” (dirty read).

Attenzione però che non è solamente questo il punto. In fin dei conti, potrebbe non essere una divergenza significativa in quanto tutto dipenderebbe dal contesto di utilizzo e di precisione dei dati.

L’effetto potrebbe essere ancora più inatteso e, di conseguenza, incomprensibile se non si osserva in dettaglio come ragiona il motore di SQL Server.

🟣 Un’occhiata oltre

“With (nolock)” impone a SQL Server di elaborare l’estrazione senza attendere che i dati siano confermati. Più precisamente, l’estrazione legge dati anche se questi non sono confermati, quindi oggetto di aggiornamento da connessioni concorrenti.

Aggiungiamo ancora un po’ di logica e capiamo perché possono generarsi effetti ancora più indesiderati. L’esito di un aggiornamento può determinare lo spostamento di una riga in una pagina già scansionata. Tale riga non verrà letta.

Risulta essere ancora più critico se una riga già letta, sempre per effetto di un aggiornamento, viene spostata in una pagina non ancora elaborata. Tale riga viene letta una seconda volta.

Lo spostamento fisico dei dati è un meccanismo noto come “divisione delle pagine” (page split).

“Ma a me non è mai successo e continuo a fare come ho sempre fatto”, mi potrei attendere come riscontro. Dico solamente che magari non ci siamo mai accorti di nulla semplicemente perché i volumi di dati, in certe occasioni, sono talmente elevati da fare assorbire l’effetto indesiderato.

Per quanto i casi possano essere limitati, andrebbero comunque valutati attentamente. L’attenzione ai dettagli e la volontà di comprensione sono da sempre determinanti per migliorare la qualità del software che scriviamo.

🔴 Trovare una soluzione (o almeno provarci)

Di seguito riporterò una soluzione che potrà essere presa in considerazione cercando di valutare i pro e i contro rispetto alle proprie esigenze.

La documentazione ufficiale di SQL Server non consiglia l’utilizzo di “with (nolock)” pur chiaramente mantenendone possibile l’utilizzo. E’ piuttosto probabile che la clausola non sarà mai resa deprecata perché avrebbe un impatto troppo elevato sui sistemi esistenti.

Un’alternativa è quindi la modifica della configurazione da attuare a livello di database. Ciò non richiederebbe alcuna modifica al software. Si agirebbe solamente a livello di impostazioni del database.

ALTER DATABASE NomeDatabase

SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

(attenzione a NON attivare questa clausola senza averne bene compreso gli effetti; soprattutto in ambienti di PRODUZIONE)

In sintesi, READ_COMMITTED_SNAPSHOT (RCSI) cambia il modo in cui SQL Server gestisce la concorrenza tra chi legge e chi scrive. Si passa da un modello basato sui blocchi (locking) a un modello basato sulle versioni dei dati (row versioning).

In modalità standard, un’istruzione di SELECT richiede un blocco in lettura (shared lock). Se un'altra transazione sta modificando quei dati, l’estrazione si ferma e aspetta.

Attivando invece READ_COMMITTED_SNAPSHOT (RCSI), le estrazioni non bloccano gli aggiornamenti. Una SELECT non chiede più blocchi in lettura.

Gli aggiornamenti non bloccano le estrazioni. Quando una transazione modifica una riga, SQL Server non nasconde il dato, ma copia la versione precedente (l'ultima confermata) in un'area di memoria in tempdb.

Quindi, l’istruzione di SELECT, mentre è in corso un aggiornamento, legge semplicemente la versione precedente memorizzata in tempdb.

Il beneficio principale di RCSI è equiparabile a “with (nolock)”: letture istantanee senza attese o blocchi. Elimina però il suo difetto peggiore, in quanto con RCSI l’estrazione utilizza solo dati confermati azzerando il rischio di dirty read, righe mancanti o duplicate (causate da page split).

Di contro, RCSI incide su tempdb in quanto SQL Server deve salvare le vecchie versioni dei dati. Devono essere presenti dischi veloci e configurazioni ad hoc per evitare colli di bottiglia. Inoltre, ogni INSERT o UPDATE richiede un piccolo sforzo extra di CPU e memoria (+14 byte per riga per gestire i puntatori di versione).

✨ Conclusione

Capire se un’impostazione è meglio dell’altra fa parte del nostro lavoro. Va sempre adattata la soluzione giusta in base all’ambiente di riferimento in quanto gli scenari possono essere davvero un’infinità.

Ho sentito la necessità di condividere queste nozioni. Quando possibile, chiedo sempre a me stesso di rimanere costantemente vigile. E’ un approccio che negli anni ha dato i suoi frutti.

So di non aver dato la soluzione perfetta. Spero di aver piuttosto fornito, per alcuni, dei nuovi strumenti su cui ragionare.

Ragionare, appunto: il lavoro dell’informatico è anche questo. Darsi il tempo per riflettere, staccare gli occhi dal monitor, scostare le mani dalla tastiera, alzarsi e guardare fuori dalla finestra cercando la giusta ispirazione.