SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

Controlla il funzionamento relativo ai blocchi e al controllo delle versioni delle righe per le istruzioni Transact-SQL eseguite da una connessione a SQL Server.

Icona di collegamento a un argomentoConvenzioni della sintassi Transact-SQL

Sintassi

SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }
[ ; ]

Argomenti

  • READ UNCOMMITTED
    Specifica che le istruzioni possono leggere le righe modificate da altre transazioni ma di cui non è ancora stato eseguito il commit.

    Le transazioni eseguite al livello READ UNCOMMITTED non acquisiscono blocchi condivisi per evitare che altre transazioni possano modificare i dati letti dalla transazione corrente. Le transazioni READ UNCOMMITTED, inoltre, non vengono bloccate con blocchi esclusivi che impedirebbero alla transazione corrente di leggere righe modificate da altre transazioni, ma di cui non è stato eseguito il commit. Con l'impostazione di questa opzione è possibile leggere modifiche di cui non è stato eseguito il commit, operazione definita lettura dirty. Prima della fine della transazione è quindi possibile che i dati vengano modificati e che le righe compaiano e scompaiano nel set di dati. Questa opzione equivale all'impostazione NOLOCK per tutte le tabelle in tutte le istruzioni SELECT di una transazione. Tra i livelli di isolamento disponibili, questo è il meno restrittivo.

    In SQL Server è inoltre possibile utilizzare le impostazioni seguenti per ridurre al minimo la contesa dei blocchi pur proteggendo le transazioni da letture dirty di modifiche dei dati di cui non è stato eseguito il commit:

    • Livello di isolamento READ COMMITTED con l'opzione di database READ_COMMITTED_SNAPSHOT impostata su ON.

    • Livello di isolamento SNAPSHOT.

  • READ COMMITTED
    Specifica che le istruzioni non possono leggere le righe modificate da altre transazioni ma di cui non è ancora stato eseguito il commit. In questo modo si evitano letture dirty. Altre transazioni possono modificare i dati nell'intervallo tra le singole istruzioni della transazione corrente, con conseguenze come letture irripetibili e la presenza di dati fantasma. Questa è l'opzione predefinita in SQL Server.

    Il funzionamento di READ COMMITTED varia a seconda dell'impostazione dell'opzione di database READ_COMMITTED_SNAPSHOT:

    • Se l'opzione READ_COMMITTED_SNAPSHOT è impostata su OFF (impostazione predefinita), Motore di database utilizza blocchi condivisi per impedire che altre transazioni possano modificare le righe mentre la transazione corrente sta eseguendo un'operazione di lettura. I blocchi condivisi impediscono inoltre che l'istruzione possa leggere righe modificate da altre transazioni, fino al completamento di tali transazioni. Il tipo di blocco condiviso determina il momento in cui verrà rilasciato. I blocchi a livello di riga vengono rilasciati prima dell'elaborazione della riga successiva. I blocchi a livello di pagina vengono rilasciati nel momento in cui la pagina successiva viene letta, mentre quelli a livello di tabella vengono rilasciati al termine dell'istruzione.

      Nota

      Se READ_COMMITTED_SNAPSHOT è impostata su ON, Motore di database utilizza il controllo delle versioni delle righe per presentare a ogni istruzione uno snapshot dei dati consistente dal punto di vista transazionale, rappresentativo dei dati esistenti al momento dell'avvio dell'istruzione. Non vengono utilizzati blocchi per proteggere i dati da aggiornamenti eseguiti da altre transazioni.

      In SQL Server 2008 R2 il livello di isolamento dello snapshot è stato esteso per supportare i dati FILESTREAM. In modalità isolamento dello snapshot i dati FILESTREAM letti da qualsiasi istruzione in una transazione rappresenteranno la versione consistente dal punto di vista transazionale dei dati presenti all'avvio della transazione.

    Quando l'opzione di database READ_COMMITTED_SNAPSHOT è impostata su ON, è possibile utilizzare l'hint di tabella READCOMMITTEDLOCK per richiedere il blocco condiviso anziché il controllo delle versioni delle righe per singole istruzioni delle transazioni eseguite con il livello di isolamento READ COMMITTED.

    Nota

    Quando si imposta l'opzione READ_COMMITTED_SNAPSHOT, nel database è consentita solo la connessione che esegue il comando ALTER DATABASE. Nel database non devono essere presenti altre connessioni aperte fino al completamento del comando ALTER DATABASE. Non è necessario che il database sia in modalità utente singolo.

  • REPEATABLE READ
    Specifica che le istruzioni non possono leggere dati modificati da altre transazioni di cui non è ancora stato eseguito il commit e che nessun'altra transazione può modificare i dati letti dalla transazione corrente, fino al completamento della transazione corrente.

    Vengono acquisiti blocchi condivisi per tutti i dati letti da ogni istruzione della transazione e tali blocchi vengono mantenuti attivi fino al completamento della transazione. Ciò impedisce ad altre transazioni di modificare qualsiasi riga letta dalla transazione corrente. Altre transazioni possono inserire nuove righe, se tali righe corrispondono alle condizioni di ricerca delle istruzioni eseguite dalla transazione corrente. Se la transazione corrente ripete l'istruzione in seguito, verranno recuperate le nuove righe con conseguenti letture fantasma. Poiché i blocchi condivisi vengono mantenuti attivi fino alla fine di una transazione, anziché essere rilasciati alla fine di ogni istruzione, il livello di concorrenza è inferiore rispetto a quello consentito dal livello di isolamento predefinito READ COMMITTED. Utilizzare questa opzione solo se necessario.

  • SNAPSHOT
    Specifica che i dati letti da qualsiasi istruzione in una transazione rappresenteranno la versione consistente dal punto di vista transazionale dei dati esistenti al momento dell'avvio della transazione. La transazione può quindi accedere solo alle modifiche dei dati di cui è stato eseguito il commit prima dell'avvio della transazione. Le modifiche ai dati apportate da altre transazioni dopo l'avvio della transazione corrente non sono visibili per le istruzioni eseguite nella transazione corrente. Con questo livello di isolamento, è come se le istruzioni di una transazione operassero su uno snapshot dei dati di cui è stato eseguito il commit, corrispondente ai dati esistenti al momento dell'avvio della transazione.

    Con l'eccezione delle operazioni di recupero di un database, le transazioni SNAPSHOT non richiedono blocchi per la lettura dei dati. Le transazioni SNAPSHOT che eseguono letture di dati non impediscono la scrittura di dati da altre transazioni. Viceversa, le transazioni che eseguono scritture di dati non impediscono la lettura di dati da transazioni SNAPSHOT.

    Durante la fase di rollback di un'operazione di recupero di un database, le transazioni SNAPSHOT richiederanno un blocco se viene eseguito un tentativo di lettura di dati bloccati da un'altra transazione per cui è in corso il rollback. In questo caso, la transazione SNAPSHOT viene bloccata fino al completamento del rollback dell'altra transazione. Il blocco viene rilasciato immediatamente dopo essere stato concesso.

    È necessario impostare l'opzione di database ALLOW_SNAPSHOT_ISOLATION su ON prima di avviare una transazione che utilizza il livello di isolamento SNAPSHOT. Se una transazione che utilizza il livello di isolamento SNAPSHOT accede a dati in più database, l'opzione ALLOW_SNAPSHOT_ISOLATION deve essere impostata su ON in ogni database.

    Non è possibile impostare il livello di isolamento SNAPSHOT per una transazione avviata con un altro livello di isolamento. In questo caso la transazione verrà interrotta. Se la transazione viene avviata con il livello di isolamento SNAPSHOT, è invece possibile cambiare il livello di isolamento e quindi reimpostare il livello SNAPSHOT. Una transazione viene avviata la prima volta che accede a dati.

    Una transazione eseguita con il livello di isolamento SNAPSHOT può visualizzare le modifiche apportate dalla transazione stessa. Ad esempio, se la transazione esegue un'operazione UPDATE su una tabella, quindi esegue un'istruzione SELECT sulla stessa tabella, i dati modificati verranno inclusi nel set dei risultati.

    Nota

    In modalità isolamento dello snapshot i dati FILESTREAM letti da qualsiasi istruzione in una transazione rappresenteranno la versione consistente dal punto di vista transazionale dei dati presenti all'avvio della transazione, non all'inizio dell'istruzione.

    Nota importanteImportante

    Quando il livello di isolamento dello snapshot è abilitato, se si elimina una riga in un heap, ovvero una tabella senza un indice cluster, e il log delle transazioni si esaurisce prima che il record di log per la riga fantasma sia persistente, il database viene portato offline. In tal caso, il database verrà riavviato automaticamente, sarà sottoposto a un recupero completo e verrà portato online.

  • SERIALIZABLE
    Specifica quanto segue:

    • Le istruzioni non possono leggere dati modificati da altre transazioni ma di cui non è ancora stato eseguito il commit.

    • Nessun'altra transazione può modificare i dati letti dalla transazione corrente fino al completamento della transazione corrente.

    • Nessun'altra transazione può inserire nuove righe con valori di chiave che rientrerebbero nell'intervallo di chiavi lette da qualsiasi istruzione nella transazione corrente, fino al completamento della transazione corrente.

    Vengono acquisiti blocchi di intervalli di chiavi per l'intervallo dei valori di chiave corrispondenti alle condizioni di ricerca di ogni istruzione eseguita in una transazione. In questo modo si impedisce che altre transazioni possano aggiornare o inserire righe qualificate per l'esecuzione di qualsiasi istruzione della transazione corrente. Ciò significa che in caso di ripetizione di una delle istruzioni di una transazione, la lettura restituirà lo stesso set di righe. I blocchi di intervalli di chiavi vengono mantenuti attivi fino al completamento della transazione. Questo è il livello di isolamento più restrittivo tra quelli disponibili, perché blocca interi intervalli di chiavi e mantiene attivi tali blocchi fino al completamento della transazione. Poiché la concorrenza è inferiore, utilizzare questa opzione solo quando è strettamente necessario. Questa opzione equivale all'impostazione di HOLDLOCK per tutte le tabelle in tutte le istruzioni SELECT di una transazione.

Osservazioni

È possibile impostare una sola opzione di livello di isolamento alla volta. Tale impostazione rimane valida per la connessione fino a quando non viene modificata in modo esplicito. Tutte le operazioni di lettura eseguite nell'ambito della transazione rispettano le regole del livello di isolamento specificato a meno che un hint di tabella nella clausola FROM di un'istruzione non specifichi un funzionamento di blocco o di controllo delle versioni diverso per una tabella.

I livelli di isolamento delle transazioni definiscono i tipi di blocchi acquisiti per le operazioni di lettura. I blocchi condivisi acquisiti per il livello READ COMMITTED o REPEATABLE READ sono in genere blocchi di riga, anche se è possibile che venga eseguita l'escalation a blocchi di pagina o di tabella, se la lettura fa riferimento a un numero significativo di righe in una pagina o una tabella. Se la transazione modifica una riga dopo la lettura, la transazione acquisisce un blocco esclusivo per proteggere tale riga e il blocco viene mantenuto attivo fino al completamento della transazione. Ad esempio, se una transazione REPEATABLE READ acquisisce un blocco condiviso su una riga e quindi modifica tale riga, il blocco di riga condiviso viene convertito in un blocco di riga esclusivo.

Con una sola eccezione, è possibile passare da un livello di isolamento a un altro in qualsiasi momento durante una transazione. L'eccezione si verifica quando si passa da un qualsiasi livello di isolamento all'isolamento SNAPSHOT. Questa operazione causa un errore nella transazione e il relativo rollback. È tuttavia possibile passare da una transazione avviata in isolamento SNAPSHOT a qualsiasi altro livello di isolamento.

Quando si modifica il livello di isolamento di una transazione, le risorse lette dopo la modifica saranno protette in base alle regole del nuovo livello. Le risorse lette prima della modifica continuano a essere protette in base alle regole del livello precedente. Se ad esempio una transazione viene modificata da READ COMMITTED a SERIALIZABLE, i blocchi condivisi acquisiti dopo la modifica vengono mantenuti fino alla fine della transazione.

Se si esegue l'istruzione SET TRANSACTION ISOLATION LEVEL in una stored procedure o un trigger, quando l'oggetto restituisce il controllo il livello di isolamento viene reimpostato sul livello attivo al momento della chiamata dell'oggetto. Ad esempio, se si imposta REPEATABLE READ in un batch e il batch chiama poi una stored procedure che imposta il livello di isolamento su SERIALIZABLE, il livello di isolamento verrà reimpostato su REPEATABLE READ quando la stored procedure restituisce il controllo al batch.

Nota

Le funzioni definite dall'utente e i tipi CLR (Common Language Runtime) definiti dall'utente non possono eseguire SET TRANSACTION ISOLATION LEVEL. È tuttavia possibile sostituire il livello di isolamento utilizzando un hint di tabella. Per ulteriori informazioni, vedere Hint di tabella (Transact-SQL).

Quando si utilizza sp_bindsession per associare due sessioni, ogni sessione mantiene il livello di isolamento impostato. L'utilizzo dell'istruzione SET TRANSACTION ISOLATION LEVEL per modificare il livello di isolamento di una sessione non influisce sull'impostazione di qualsiasi altra sessione associata.

L'istruzione SET TRANSACTION ISOLATION LEVEL diventa effettiva in fase di esecuzione, non in fase di analisi.

Le operazioni ottimizzate di caricamento bulk negli heap bloccano le query eseguite con i livelli di isolamento seguenti:

  • SNAPSHOT

  • READ UNCOMMITTED

  • READ COMMITTED con utilizzo del controllo delle versioni delle righe

Al contrario, le query eseguite con tali livelli di isolamento bloccano le operazioni ottimizzate di caricamento bulk negli heap. Per ulteriori informazioni sulle operazioni di caricamento bulk, vedere Informazioni sulle operazioni di importazione ed esportazione bulk e Ottimizzazione delle prestazioni dell'importazione bulk.

I database abilitati per FILESTREAM supportano i livelli di isolamento della transazione seguenti.

Livello di isolamento

Accesso Transact-SQL

Accesso al file system

Read Uncommitted

SQL Server 2008

Supportato

Read Committed

SQL Server 2008

SQL Server 2008

Repeatable Read

SQL Server 2008

Supportato

Serializable

SQL Server 2008

Supportato

Snapshot Read Committed

SQL Server 2008 R2

SQL Server 2008 R2

Snapshot

SQL Server 2008 R2

SQL Server 2008 R2

Esempi

Nell'esempio seguente viene impostato il livello TRANSACTION ISOLATION LEVEL per la sessione. Per ogni istruzione Transact-SQL successiva, tutti i blocchi condivisi verranno mantenuti attivi da SQL Server fino alla fine della transazione.

USE AdventureWorks2008R2;
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
BEGIN TRANSACTION;
GO
SELECT * 
    FROM HumanResources.EmployeePayHistory;
GO
SELECT * 
    FROM HumanResources.Department;
GO
COMMIT TRANSACTION;
GO