Condividi tramite

  • Articolo

Informazioni sui livelli di isolamento basati sul controllo delle versioni delle righe

Il controllo delle versioni delle righe è un framework generale di SQL Server utilizzato per eseguire quanto segue:

  • Generare le tabelle inserite ed eliminate nei trigger. Le righe modificate dal trigger vengono sottoposte al controllo delle versioni. Questo include le righe modificate dall'istruzione che ha avviato il trigger, nonché qualsiasi modifica ai dati eseguita dal trigger.

  • Supportare più set di risultati attivi (MARS). Se una sessione MARS genera un'istruzione di modifica dei dati (ad esempio INSERT, UPDATE oppure DELETE) nel momento in cui è attivo un set di risultati, le righe influenzate dall'istruzione di modifica sono sottoposte al controllo delle versioni.

  • Supportare operazioni di indice che specificano l'opzione ONLINE.

  • Supportare i livelli di isolamento delle transazioni basate sul controllo delle versioni delle righe:

    • Una nuova implementazione del livello di isolamento read-committed che utilizza il controllo delle versioni delle righe per la consistenza di lettura a livello di istruzione.

    • Un nuovo livello di isolamento, snapshot, per offrire consistenza di lettura a livello di transazione.

Il database tempdb deve disporre di spazio sufficiente per l'archivio delle versioni. Quando lo spazio su tempdb è esaurito, le operazioni di aggiornamento cessano la generazione di versioni, senza errori, mentre le operazioni di lettura possono avere esito negativo in quanto una particolare versione della riga necessaria non esiste più. Questo ha effetto su operazioni come indicizzazione in linea, MARS e di trigger. Per ulteriori informazioni, vedere Utilizzo delle risorse di controllo delle versioni delle righe.

L'utilizzo del controllo delle versioni delle righe per transazioni snapshot e read-committed è un processo in due passaggi:

  1. Impostazione di una o entrambe le opzioni di database READ_COMMITTED_SNAPSHOT e ALLOW_SNAPSHOT_ISOLATION su ON.

  2. Impostazione del corretto livello di isolamento delle transazioni in un'applicazione:

    • Quando l'opzione di database READ_COMMITTED_SNAPSHOT è ON, le transazioni che impostano il livello di isolamento read-committed utilizzano il controllo delle versioni delle righe.

    • Quando l'opzione di database ALLOW_SNAPSHOT_ISOLATION è ON, le transazioni possono impostare il livello di isolamento snapshot.

Quando l'opzione di database READ_COMMITTED_SNAPSHOT oppure ALLOW_SNAPSHOT_ISOLATION è impostata su ON, Motore di database di SQL Server assegna un numero di sequenza della transazione (XSN) a ogni transazione che modifica i dati utilizzando il controllo delle versioni delle righe. Le transazioni vengono avviate all'esecuzione di un'istruzione BEGIN TRANSACTION. Tuttavia, il numero di sequenza della transazione inizia con la prima operazione di lettura o scrittura dopo l'istruzione BEGIN TRANSACTION. Il numero di sequenza della transazione viene incrementato di uno a ogni assegnazione.

Quando l'opzione di database READ_COMMITTED_SNAPSHOT oppure ALLOW_SNAPSHOT_ISOLATION è impostata su ON, le copie logiche (versioni) vengono mantenute per tutte le modifiche ai dati eseguite nel database. Ogni volta che una riga viene modificata da una transazione specifica, l'istanza di Motore di database archivia una versione della precedente immagine della riga di cui è stato eseguito il commit in tempdb. Ogni versione è contrassegnata con il numero di sequenza della transazione che ha eseguito la modifica. Le versioni delle righe modificate vengono concatenate utilizzando un elenco di collegamenti. Il valore di riga più recente viene sempre archiviato nel database corrente e concatenato alle righe con versioni archiviate in tempdb.

[!NOTA]

Per la modifica di oggetti LOB, solo il frammento modificato viene copiato nell'archivio delle versioni archiviato in tempdb.

Le versioni delle righe vengono mantenute abbastanza a lungo da soddisfare i requisiti delle transazioni in esecuzione con i livelli di isolamento basati sul controllo delle versioni delle righe. Motore di database tiene traccia del primo numero di sequenza della transazione significativo ed elimina periodicamente tutte le versioni di riga che includono numeri di sequenza della transazione inferiori al primo numero di sequenza significativo.

Quando entrambe le opzioni di database sono impostate su OFF, solo le righe modificate da trigger o sessioni MARS, oppure lette da operazioni di indice ONLINE, vengono sottoposte al controllo delle versioni. Queste versioni di riga vengono rilasciate quando non sono più necessarie. Un thread in background viene eseguito periodicamente per rimuovere le versioni di riga non aggiornate.

[!NOTA]

Per le transazioni con esecuzione rapida, è possibile che una versione di una riga modificata venga memorizzata nel pool di buffer senza essere scritta nei file su disco del database tempdb. Se la necessità della riga con controllo delle versioni è di breve durata, essa verrà semplicemente eliminata dal pool di buffer, senza che si verifichi necessariamente un overhead I/O.

Comportamento durante la lettura dei dati

Quando le transazioni in esecuzione con l'isolamento basato sul controllo delle versioni delle righe eseguono la lettura dei dati, le operazioni di lettura non acquisiscono blocchi condivisi (S) sui dati da leggere, e pertanto non bloccano le transazioni che stanno modificando i dati. Inoltre, l'overhead delle risorse di blocco è ridotto al minimo in quanto il numero di blocchi acquisiti si riduce. L'isolamento read-committed tramite controllo delle versioni delle righe e isolamento dello snapshot è pensato per assicurare consistenze a livello di istruzione o di transazione dei dati sottoposti a controllo delle versioni.

Tutte le query, incluse le transazioni in esecuzione con i livelli di isolamento basati sul controllo delle versioni delle righe, acquisiscono blocchi di stabilità dello schema (Sch-S) durante le fasi di compilazione ed esecuzione. Per questo motivo, le query vengono bloccate quando una transazione simultanea mantiene attivo un blocco di modifica dello schema sulla tabella. Ad esempio, un'operazione DDL (Data Definition Language) acquisisce un blocco Sch-M prima di modificare le informazioni dello schema della tabella. Le transazioni delle query, incluse quelle in esecuzione con un livello di isolamento basato sul controllo delle versioni delle righe, vengono quindi bloccate durante il tentativo di acquisizione di un blocco Sch-S. Al contrario, una query che mantiene attivo un blocco Sch-S blocca una transazione simultanea che tenta di acquisire un blocco Sch-M. Per ulteriori informazioni sul comportamento dei blocchi, vedere Compatibilità tra blocchi (Motore di database).

Quando una transazione che utilizza il livello di isolamento dello snapshot ha inizio, l'istanza di Motore di database registra tutte le transazioni attualmente attive. Quando la transazione snapshot legge una riga che include una catena delle versioni, Motore di database segue la catena e recupera la riga per la quale il numero di sequenza della transazione è:

  • Più vicino ma inferiore al numero di sequenza della transazione snapshot che sta leggendo la riga.

  • Non incluso nell'elenco delle transazioni attive quando la transazione snapshot ha avuto inizio.

Le operazioni di lettura eseguite da una transazione snapshot recuperano l'ultima versione di ogni riga di cui è stato eseguito il commit al momento dell'avvio della transazione snapshot. Questo determina uno snapshot dei dati, consistente dal punto di vista transazionale, nello stato in cui si trovavano all'inizio della transazione.

Le transazioni read-committed che utilizzano il controllo delle versioni delle righe funzionano in modo analogo. La differenza è rappresentata dal fatto che la transazione read-committed non utilizza un proprio numero di sequenza della transazione nella scelta delle versioni delle righe. A ogni avvio di un'istruzione, la transazione read-committed legge l'ultimo numero di sequenza della transazione generato per l'istanza di Motore di database. Si tratta del numero di sequenza della transazione utilizzato per selezionare le versioni di riga corrette per l'istruzione. Questo consente alle transazioni read-committed di visualizzare uno snapshot dei dati nello stato in cui si trovavano all'inizio di ogni istruzione.

[!NOTA]

Anche se le transazioni read-committed che utilizzano il controllo delle versioni delle righe offrono una vista consistente dei dati dal punto di vista transazionale a livello di istruzione, le versioni delle righe generate o utilizzate da questo tipo di transazione vengono mantenute fino al completamento della transazione.

Comportamento durante la modifica dei dati

In una transazione read-committed che utilizza il controllo delle versioni delle righe, la selezione delle righe da aggiornare viene eseguita utilizzando un'analisi di blocco in cui un blocco di aggiornamento (U) viene utilizzato sulla riga di dati durante la lettura dei valori dei dati. Questo avviene anche per una transazione read-committed che non utilizza il controllo delle versioni delle righe. Se la riga di dati non rispetta i criteri di aggiornamento, il blocco di aggiornamento viene rilasciato sulla riga e la riga successiva viene bloccata e analizzata.

Le transazioni eseguite con isolamento dello snapshot adottano un approccio ottimistico alla modifica dei dati, in quanto acquisiscono blocchi sui dati prima di eseguire la modifica con l'unico scopo di imporre i vincoli. In caso contrario, non vengono acquisiti blocchi sui dati fino a quando questi non devono essere modificati. Quando una riga di dati rispetta i criteri di aggiornamento, la transazione snapshot verifica che la riga di dati non sia stata modificata da una transazione simultanea che abbia eseguito il commit dopo l'inizio della transazione snapshot. Se la riga di dati è stata modificata all'esterno della transazione snapshot, si verifica un conflitto di aggiornamento e la transazione snapshot viene interrotta. Il conflitto di aggiornamento viene gestito da Motore di database e non è possibile disattivare il rilevamento dei conflitti di aggiornamento.

[!NOTA]

Le operazioni di aggiornamento in esecuzione con il livello di isolamento dello snapshot vengono eseguite internamente con isolamento read-committed quando la transazione snapshot accede a uno degli elementi seguenti:

Una tabella con un vincolo FOREIGN KEY.

Una tabella cui viene fatto riferimento nel vincolo FOREIGN KEY di un'altra tabella.

Una vista indicizzata che fa riferimento a più tabelle.

Tuttavia, anche in queste condizioni l'operazione di aggiornamento continuerà per verificare che i dati non siano stati modificati da un'altra transazione. In questo caso, la transazione snapshot rileverà un conflitto di aggiornamento e verrà interrotta.

Nella tabella seguente vengono illustrate le differenze tra isolamento dello snapshot e isolamento read-committed utilizzando il controllo delle versioni delle righe.

Proprietà

Livello di isolamento read-committed che utilizza il controllo delle versioni delle righe

Livello di isolamento dello snapshot

L'opzione di database che deve essere impostata su ON per attivare il supporto necessario.

READ_COMMITTED_SNAPSHOT

ALLOW_SNAPSHOT_ISOLATION

Modalità con cui una sessione richiede il tipo specifico di controllo delle versioni delle righe.

Utilizzare il livello di isolamento read-committed predefinito o eseguire l'istruzione SET TRANSACTION ISOLATION LEVEL per specificare il livello di isolamento READ COMMITTED. L'operazione può essere eseguita dopo l'inizio della transazione.

Richiede l'esecuzione di SET TRANSACTION ISOLATION LEVEL per specificare il livello di isolamento SNAPSHOT prima dell'inizio della transazione.

La versione dei dati letta dalle istruzioni.

Tutti i dati di cui è stato eseguito il commit prima dell'inizio di ogni istruzione.

Tutti i dati di cui è stato eseguito il commit prima dell'inizio di ogni transazione.

Procedura di gestione degli aggiornamenti.

Esegue il ripristino dalle versioni delle righe ai dati attuali per selezionare le righe per l'aggiornamento e utilizza i blocchi di aggiornamento sulle righe di dati selezionate. Acquisisce blocchi esclusivi sulle righe di dati effettive da modificare. Nessun rilevamento dei conflitti di aggiornamento.

Utilizza le versioni delle righe per selezionare le righe da aggiornare. Tenta di acquisire un blocco esclusivo sulla riga di dati effettiva da modificare. Se i dati sono stati modificati da un'altra transazione, si verifica un conflitto di aggiornamento e la transazione snapshot viene interrotta.

Rilevamento dei conflitti di aggiornamento.

Nessuno

Supporto integrato. Non disattivabile.