La domanda è di quelle importanti! Siete pronti per scoprire tutto ma proprio tutto sulla clausola NOLOCK?
Se la riposta è si, allora seguitemi!
Oggi ne spiegheremo il funzionamento in maniera approfondita.
Faremo una introduzione al concetto di tipi di LOCK
Ed infine vedremo come mai una SELECT ne può bloccare un'altra.
Non vi nascondo che il motivo di questo articolo è fare chiarezza.
Su questo argomento si sentono spesso affermazioni “curiose” ma tra le tante questa è la prima: “uso la SELECT con l’opzione WITH(LOCK) così non blocco le altre tabelle”.
Ma, è proprio vero quanto affermato?
Beh oggi facciamo il punto!
Beh oggi facciamo il punto!
Buona lettura!
La clausola NOLOCK
Diciamo subito per iniziare che cos’è la NOLOCK.
NOLOCK è il nome di una clausola che è possibile specificare all’interno di uno statement di tipo SELECT utilizzando la seguente sintassi:
SELECT * FROM TABELLA WITH (NOLOCK)
Tramite la clausola NOLOCK è possibile leggere i dati da una tabella ignorandone tutti i tipi di LOCK.
Per questo motivo la nostra Query, non venendo bloccata da altre stringhe SQL in esecuzione, viene spesso utilizzata come modo per “essere sicuri” che le nostre procedure giungano a termine.
Ma attenzione: il nostro statement SELECT arriverà si a termine ma leggerà dati che non sono ancora stati committati e che potrebbero invece essere soggetti ad un successivo ROLLBACK.
Cercherò di spiegarlo con un paio di esempi.
Esempio 1
Le informazioni sono memorizzate in una semplice tabella STIPENDI che avrà tre colonne: nome del dipendente (NOME), mese di retribuzione (DATA) e importo (IMPORTO).
Cosa farà tipicamente la vostra procedura mentre calcola lo stipendio del sig. Mario?
Aprirà una transazione.
Supponiamo aggiorni il campo IMPORTO nella tabella STIPENDI aggiungendo parte dell’importo in base al numero di ore effettive per ogni giorno lavorato del mese.
Di fatto un ciclo dove per ogni giorno del mese viene scritto nel campo importo un valore crescente.
Se alla fine tutto sarà andato per il verso giusto e non avremo errori faremo il COMMIT
Avete poi sviluppato anche un'altra procedura STAMPA_STIPENDIO che produce un report di stampa leggendo anch’essa i dati calcolati dalla tabella STIPENDI.
Ora, mentre si svolge il calcolo tramite la procedura CALCOLA_STIPENDIO vi viene chiesto di stampare il report dello stipendio del sig. Mario con la procedura STAMPA_STIPENDIO.
Cosa accade?
Se la vostra procedura STAMPA_STIPENDIO esegue una SELECT senza specificare la clausola NOLOCK non sarete in grado di leggere il dato dalla tabella stipendi per cui la stampa per vostro report aspetterà la fine del calcolo.
Potreste a questo punto essere tentati di aggiungere alla vostra SELECT la clausola NOLOCK.
Ma cosa accadrebbe?
Già ma quali dati?
Se il calcolo infatti era ancora in corso il dato che stamperete è sicuramente parziale e sbagliato.
La transazione è nata proprio per questo motivo!
Esempio 2
Supponiamo quanto segue.
L’utente A apre una transazione ed esegue un update sulla tabella TAB mettendo il valore 1 nel campo CMP che prima aveva valore 0.
Arriva l’utente B e legge il valore di TAB.CMP con la NOLOCK. leggerà il valore 1
Ora però l’utente A si accorge di non aver messo il valore corretto e fa il ROLLBACK della transazione.
Il campo TAB.CMP torna a valere 0 ma noi abbiamo letto 1
Bel problema!
Entriamo ora nel dettaglio per capirne il funzionamento.
Creiamoci una semplice tabella ELENCO che avrà due sole colonne: la colonna ID di tipo intero con identità e indice clustered e un campo CODE intero.
Poi popoliamola con almeno una riga di dati.
Confrontiamo ora la SELECT eseguita senza e con il NOLOCK.
SELECT SENZA IL NOLOCK
Facciamo assieme questa prova eseguendo le seguenti istruzioni:
BEGIN TRAN
UPDATE ELENCO SET CODE = 'X' WHERE ID = 1
SELECT CODE FROM ELENCO WHERE ID = 1
La SELECT rimane in attesa che la transazione venga chiusa.
Bene ora utilizziamo un nuovo comando che si chiama sp_lock che restituisce una tabella che contiene informazioni sui LOCK.
(Tenetelo sotto mano nella vostra cassetta degli attrezzi personale)
PS: le righe con spid 57 rappresentano il mio UPDATE, le righe con spid 55 rappresentano invece la mia SELECT
Eseguendo la sp_lock prima di eseguire la SELECT otteniamo
Situazione quando la SELECT è in attesa abbiamo
Vediamo che la nostra SELECT (SPID=55) rimane in attesa (riga con status = WAIT)
SELECT CON IL NOLOCK
Situazione iniziale
La SELECT ha proceduto questa volta con la sua esecuzione.
La nostra SELECT arriva a termine.
Già ma c’è un ma!
Sembra che non ci sia nessun blocco ma in realtà non abbiamo visto cosa accade realmente.
Per capirlo occorre eseguire la sp_lock mentre la SELECT è in esecuzione.
La cosa più pratica è popolarla con molte righe oppure moltiplicare le righe facendo ad esempio dei cross join.
Ed eccola qui quello che cercavamo:
Ed eccola qui quello che cercavamo:
Cosa osservate?
Osservate che una SELECT (anche se specificate la clausola NOLOCK) mette comunque un blocco: mette un blocco di tipo Schema stability (SCH-S) sulla nostra tabella.
Perche?
Per evitare che durante la lettura dei dati dalla nostra tabella qualcuno ne cambi la struttura (ad esempio aggiunga o tolga la colonna che stiamo leggendo, ma anche ne modifiche il tipo di dato)
Ne deriva che ci saranno operazioni che saranno bloccate dal tipo di lock SCH_S.
Le operazioni che sono bloccate dal lock SCH_S sono tutte le operazioni che richiedono un blocco di tipo Schema modification (SCH_M)
E quali sono questi comandi? Sono ad esempio il REBUILD degli indici.
Ma attenzione guardate adesso cosa accade…
Eseguite il REBUILD degli indici e vedrete che si blocca a causa di una SELECT
L’operazione di rebuild degli indici rimarrà in attesa che la SELECT termini e inserirà a sua volta una riga di lock di tipo Schema modification (SCH_M)
Adesso tutti i comandi che sono bloccati da un lock di tipo SCH_M rimarranno in attesa.
E quali sono?
Purtroppo qualsiasi Query!
Se ad esempio eseguissi:
SELECT * FROM ELENCO
Bene, siamo arrivati alla fine di questo articolo dove abbiamo parlato della clausola NOLOCK.
Ne abbiamo analizzato in profondità il funzionamento introducendo anche il concetto dei tipi di LOCK.
Tutto questo per arrivare a capire come mai una SELECT (anche se gli aggiungiamo la clausola NOLOCK) può bloccare delle altre SELECT.
Spero di essere stato chiaro ma se avete dubbi o domande scrivetemi o meglio scrivetele nello spazio dei commenti.
Per oggi è tutto!
Come sempre se trovate utile questo blog iscrivetemi per non perdervi nessun articolo.
La prossima volta parlerò di table variable e di cosa introduce il nuovo SQL Server 2019 a riguardo!
Ciao,
Luca Biondi @ SQLServerPerformance blog!
Previous post: SQL Server 2019 RC1 e la "Resumable Online Index Creation"