SQL Server table hints sono un tipo speciale di comando esplicito che viene utilizzato per sovrascrivere il comportamento predefinito di SQL Server query optimizer durante l’esecuzione della query T-SQL.utilizzato da SQL Server query optimizer per creare il piano di esecuzione della query., I suggerimenti della tabella possono essere aggiunti alla clausola FROM della query T-SQL, influenzando solo la tabella o la vista a cui si fa riferimento nella clausola FROM.
Uno dei suggerimenti di tabella più utilizzati nelle istruzioni SELECT T-SQL è il suggerimento WITH (NOLOCK). Il livello di isolamento delle transazioni predefinito in SQL Server è il livello di isolamento READ COMMITTED, in cui il recupero dei dati modificati verrà bloccato fino al commit di tali modifiche., Il suggerimento tabella WITH (NOLOCK) viene utilizzato per sovrascrivere il livello di isolamento delle transazioni predefinito della tabella o delle tabelle all’interno della vista in una query specifica, consentendo all’utente di recuperare i dati senza essere interessato dai blocchi, sui dati richiesti, a causa di un altro processo che lo sta cambiando. In questo modo, la query consumerà meno memoria nel tenere i blocchi contro quei dati. Inoltre, non si verificherà alcun deadlock rispetto alle query, che richiedono gli stessi dati da quella tabella, consentendo un livello più elevato di concorrenza a causa di un ingombro inferiore., In altre parole, il suggerimento della tabella WITH (NOLOCK) recupera le righe senza attendere che le altre query, che stanno leggendo o modificando gli stessi dati, finiscano la sua elaborazione. Questo è simile al livello di isolamento della transazione NON COMMITTED READ, che consente alla query di vedere le modifiche ai dati prima di eseguire il commit della transazione che la sta modificando. Il livello di isolamento della transazione può essere impostato globalmente a livello di connessione utilizzando il comando IMPOSTA LIVELLO DI ISOLAMENTO DELLA TRANSAZIONE T-SQL, come vedremo più avanti in questo articolo.,
Sebbene il suggerimento di tabella NOLOCK, simile a tutti gli altri suggerimenti di tabella, possa essere utilizzato senza utilizzare la parola chiave WITH, Microsoft ha annunciato che l’omissione della parola chiave WITH è una funzionalità deprecata e verrà rimossa dalle future versioni di Microsoft SQL Server. Detto questo, è meglio includere la parola chiave WITH quando si specificano i suggerimenti della tabella. Un vantaggio dell’utilizzo della parola chiave WITH è che è possibile specificare più suggerimenti di tabella utilizzando la parola chiave WITH rispetto alla stessa tabella.
In generale, l’uso frequente di suggerimenti di tabella espliciti è considerato una cattiva pratica che dovresti generalmente evitare., Per il suggerimento della tabella NOLOCK in particolare, la lettura di dati non committed che potrebbero essere ripristinati dopo averlo letto può portare a una lettura sporca, che può verificarsi quando si leggono i dati che vengono modificati o eliminati durante la lettura dei dati non committed, in modo che i dati letti possano essere diversi o mai esistiti.
Il suggerimento della tabella WITH (NOLOCK) porta anche a letture non ripetibili; questa lettura si verifica quando è necessario leggere gli stessi dati più volte e i dati cambiano durante queste letture. In questo caso, leggerai più versioni della stessa riga.,
Le letture fantasma possono anche essere il risultato dell’utilizzo del suggerimento della tabella WITH(NOLOCK), in cui si otterranno più record quando la transazione che sta inserendo nuovi record viene ripristinata o meno record quando la transazione che sta eliminando i dati esistenti viene ripristinata. Un altro problema che può verificarsi quando altre transazioni spostano i dati che non hai ancora letto in una posizione che hai già scansionato o hanno aggiunto nuove pagine alla posizione che hai già scansionato. In questo caso, ti mancheranno questi record e non li vedrai nel risultato restituito., Se un’altra transazione sposta i dati che hai già scansionato in una nuova posizione che non hai ancora letto, leggerai i dati due volte. Inoltre, poiché i dati richiesti potrebbero essere spostati o eliminati durante il processo di lettura, potrebbe essere riscontrato l’errore seguente:
Msg 601, Livello 12, Stato 1
Impossibile continuare la scansione con NOLOCK a causa del movimento dei dati.
Il suggerimento della tabella WITH (NOLOCK) è una buona idea quando il sistema utilizza pesantemente transazioni esplicite, il che blocca la lettura dei dati molto frequentemente., Il suggerimento della tabella CON (NOLOCK) viene utilizzato quando si lavora con sistemi che accettano dati non sincronizzati, ad esempio i sistemi di reporting.,
comprendere l’utilizzo della CON (NOLOCK) hint di tabella, praticamente, ci consentono di creare una nuova tabella con il CREARE la TABELLA T-SQL istruzione, di seguito:
Dopo la creazione della tabella, si riempirà con 100K righe per scopi di test, utilizzando ApexSQL Generare, SQL test generatore di dati, come mostrato nell’immagine qui sotto:
una Volta che il tavolo è pronto, ci sarà la simulazione di un blocco scenario, in cui una transazione di aggiornamento verrà eseguito all’interno di una transazione che ha inizio e non il commit o il rollback.,
Con i dati della tabella bloccato dalla transazione, noi faremo un’altra istruzione SELECT in SQL sessione numero 54, che recupera i dati dal LockTestDemo tabella, utilizzando l’istruzione SELECT di seguito:
|
1
|
SELECT * FROM LockTestDemo
|
vedrete che la precedente istruzione SELECT avrà un lungo periodo di tempo senza recupero di qualsiasi record.,ocking che SELEZIONARE query utilizzando sp_who2 comando con il numero di sessione per entrambe le SELEZIONA e le istruzioni di AGGIORNAMENTO:
|
1
2
3
|
sp_who2 53
ANDARE
sp_who2 54
|
Il risultato mostra che, aperto prima transazione non è di eseguire qualsiasi azione, come l’istruzione di AGGIORNAMENTO eseguito con successo., Ma a causa del fatto che la transazione non è ancora stata commessa o ripristinata, blocca ancora altre query che stanno cercando di ottenere dati da quella tabella. E l’istruzione SELECT che è in esecuzione sotto la sessione 54 è bloccata da quella transazione che è in esecuzione sotto la sessione 53, come mostrato nel risultato seguente:
L’istruzione SELECT precedente continuerà ad aspettare che la transazione venga interrotta, commessa o ripristinata per ottenere le righe richieste da quella tabella., il comando COMMIT o ROLLBACK nella stessa sessione dell’operazione, se del caso, come mostrato di seguito:
una Volta che il blocco viene rilasciato, vedrete che la richiesta righe verranno recuperati dall’istruzione SELECT direttamente come mostrato nei risultati di seguito:
La soluzione precedente non è sempre preferibile o applicabile, per esempio, quando la transazione che blocca la nostra query è fondamentale e non è facile essere uccisi o il rollback, o quando non si ha il controllo su altre operazioni all’interno del database., In questo caso, il suggerimento della tabella WITH (NOLOCK) è utile qui, se è possibile tollerare il rischio di letture sporche o incoerenza dei dati. Come accennato in precedenza, il suggerimento della tabella WITH (NOLOCK) consente di leggere i dati che sono stati modificati, ma non ancora impegnati nel database., Se si esegue la stessa istruzione SELECT senza uccidere, commit o il rollback della transazione di AGGIORNAMENTO, ma questa volta aggiungendo il CON (NOLOCK) tabella suggerimento per il nome della tabella nell’istruzione SELECT, come illustrato di seguito:
|
1
|
SELECT * FROM LockTestDemo CON (NOLOCK)
|
Quindi controllando l’istruzione SELECT di stato utilizzando il sp_who2 comando., Vedrete che la query è in esecuzione, senza attendere che la transazione di AGGIORNAMENTO per essere completata e rilasciare il fermo sul tavolo, come mostrato nell’immagine qui sotto:
Il CON (NOLOCK) hint di tabella funziona come la READUNCOMMITTED hint di tabella, che ci permette di recuperare i dati che è cambiato, ma non commesso di sicurezza.,iv>
|
1
|
SELECT * FROM LockTestDemo CON (READUNCOMMITTED)
|
Recuperare i dati richiesti direttamente, senza attendere l’istruzione UPDATE per rilasciare il blocco che si è esibito sul tavolo, restituisce lo stesso risultato, come mostrato nel set di risultati di seguito:
Prendere in considerazione che, CON il (NOLOCK) e READUNCOMMITTED hint di tabella può essere utilizzata solo con le istruzioni SELECT.,
Questa query consente di recuperare anche gli stessi dati direttamente, senza l’utilizzo di alcun hint di tabella e, senza attendere che l’istruzione di AGGIORNAMENTO per rilasciare il blocco che si è esibito sul tavolo, come mostrato nel set di risultati di seguito:
Dal risultato precedente, si potrebbe pensare che questa è la soluzione perfetta per tali scenari, in cui si ottiene la richiesta di dati più veloce, senza tempi di attesa per le altre operazioni per essere commesso, prendendo il rischio di non avere dati precisi., Ma la query SELECT che utilizza il suggerimento della tabella WITH (NOLOCK) influisce negativamente su altri processi sul Server SQL? Per ottenere la risposta, controlliamo prima quale tipo di blocchi verrà concesso il suggerimento della tabella WITH (NOLOCK) durante la sua esecuzione.,=”26bd69ce5a”>
|
1
|
sp_lock 54
|
Potete vedere dal risultato della query che utilizza la CON (NOLOCK) hint di tabella sarà concesso S e Sch-S bloccaggio, come mostrato nel risultato:
Dal risultato precedente, si vedrà che il CON (NOLOCK) hint di tabella sarà concesso l’accesso condiviso (S) blocco a livello di database., Il blocco degli accessi condivisi viene utilizzato per l’operazione di lettura, consentendo alle transazioni simultanee di leggere i dati sotto il controllo della concorrenza pessimistica, impedendo ad altre transazioni di modificare la risorsa bloccata mentre i blocchi condivisi esistono su quella risorsa, fino a quando il blocco non viene rilasciato non appena l’operazione di lettura viene completata.
Il secondo tipo di blocco che viene concesso alla query utilizzando il suggerimento della tabella WITH (NOLOCK) è il blocco schema stability (Sch-S)., Questo blocco non impedirà ad altre transazioni di accedere alle risorse ad eccezione delle operazioni DDL simultanee e delle operazioni DML simultanee che acquisiscono blocchi di modifica dello schema (Sch-M) sulla stessa tabella, che verranno bloccate durante l’esecuzione della query. Questo ha davvero senso, poiché non è necessario iniziare a leggere i dati dalla tabella, quindi un’altra transazione modifica la struttura di quella tabella durante il processo di recupero dei dati., SQL Server Database Engine utilizza i blocchi di modifica dello schema (Sch-M) durante l’elaborazione dei comandi DDL (Data Definition Language), ad esempio l’aggiunta di una nuova colonna, l’eliminazione di una colonna esistente, l’eliminazione o la ricostruzione degli indici, per impedire l’accesso simultaneo alla tabella, fino al rilascio del blocco.
La mia query NOLOCK sta bloccando!
Ciò significa che la denominazione NOLOCK non è sempre accurata al 100%. L’uso del suggerimento della tabella WITH (NOLOCK), che contiene il blocco SCH_S (Schema stability), può bloccare altre query che tentano di acquisire un blocco di modifica dello schema (Sch-M) su quella tabella., È un problema critico che dovresti prendere in considerazione se ci sono molti utenti che eseguono le loro query SELECT usando il suggerimento della tabella WITH (NOLOCK), impedendo di apportare modifiche allo schema della tabella o alle manutenzioni sugli indici della tabella, essendo bloccati dal blocco SCH_S (schema stability).,>
Allo stesso tempo, eseguire il seguente query, che è l’eliminazione di un indice nella tabella stessa e creare di nuovo, sotto il numero di sessione 58:
controllare lo stato di entrambe le query utilizzando il sp_who2 comando, verrà visualizzato dal risultato, l’istruzione SELECT CON l’ (NOLOCK) hint di tabella e sessione di corsa numero 53, chiusura GOCCIA/INDICE di CREARE i processi in esecuzione in sessione numero 58, come mostrato chiaramente di seguito:
Se andiamo a vedere i blocchi che vengono eseguite da ogni query, utilizzando il sys.,dm_tran_locks sistema oggetto come nella query riportata di seguito:
|
1
2
3
|
SELECT *
DA sys.dm_tran_locks
DOVE resource_type = ‘OBJECT’)
|
vedrai che, a GOCCIA/INDICE di CREARE i processi in esecuzione in sessione numero 58 è in attesa di acquisire modifica dello schema (Sch-M) tipo di blocco., Ciò si verifica a causa del fatto che, il blocco di modifica dello schema (Sch-M) non può essere acquisito mentre il blocco di stabilità dello schema (Sch_S) già concesso all’istruzione SELECT in esecuzione con il numero di sessione 53, esiste già come mostrato nello snapshot seguente:
La mia query NOLOCK è bloccata!
Al contrario, poiché il suggerimento della tabella CON (NOLOCK) acquisisce il tipo di blocco Sch-S (schema stability), l’istruzione SELECT che utilizza il suggerimento della tabella CON (NOLOCK) verrà bloccata se viene eseguita una modifica dello schema su tale tabella.,
|
1
2
3
|
sp_who2 53
ANDARE
sp_who2 54
|
vedrete che l’istruzione SELECT esecuzione nella sessione 54 è bloccato dall’istruzione ALTER TABLE esecuzione nella sessione 54, come mostrato di seguito:
Poi controllando i blocchi che vengono eseguite da ogni query, utilizzando il sys.,dm_tran_locks system object as in the query below:
|
1
2
3
4
|
SELECT *
FROM sys.,che, l’istruzione SELECT CON l’ (NOLOCK) hint di tabella e di esecuzione nella sessione numero 54, sarà in attesa di acquisire schema di stabilità (Sch_S) blocco, a causa del fatto che lo schema di stabilità (Sch-S) blocco non può essere acquisito durante la modifica dello schema (Sch_M) lock, che è già stato concesso per l’istruzione ALTER esecuzione nella sessione numero 53, esiste già, come mostrato nell’immagine qui sotto:
Si può immaginare la situazione in cui si sta pianificando enorme numero di rapporti, di notte, che sono CON l’ (NOLOCK) tabella suggerimento giusto per essere sicuri., Allo stesso tempo, ci sono lavori di manutenzione che sono anche programmati per ricostruire indici fortemente frammentati sulla stessa tabella! Ci sono un certo numero di best practice e suggerimenti che è possibile seguire, al fine di evitare i problemi che si possono affrontare quando si utilizza CON (NOLOCK) suggerimento tabella. Tali suggerimenti includono:
Ahmad Yaseen è un Microsoft Big Data ingegnere con una profonda conoscenza ed esperienza in BI SQL, Database SQL Server, Amministrazione e Sviluppo dei campi.,
È un esperto di soluzioni certificate Microsoft in gestione e analisi dei dati, Microsoft Certified Solution Associate in amministrazione e sviluppo di database SQL, Azure Developer Associate e Microsoft Certified Trainer. Inoltre, sta contribuendo con i suoi suggerimenti SQL in molti blog., Visualizza tutti i messaggi di Ahmad Yaseen Ultimi messaggi di Ahmad Yaseen (vedi tutti)
|
Lascia un commento