IMPOSTA IL LIVELLO DI ISOLAMENTO DELLE TRANSAZIONI (Transact-SQL)

Si applica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSistema di Piattaforma Analitica (PDW)Database SQL in Microsoft Fabric

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

Convenzioni relative alla sintassi Transact-SQL

Sintassi

Sintassi per SQL Server, database SQL di Azure e database SQL in Microsoft Fabric.

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

Sintassi per Azure Synapse Analytics e Parallel Data Warehouse.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Nota

Azure Synapse Analytics implementa le transazioni ACID. Il livello di isolamento predefinito è READ UNCOMMITTED. È possibile modificarlo impostando READ COMMITTED SNAPSHOT ISOLATIONON l'opzione READ_COMMITTED_SNAPSHOT di database per un database utente quando si è connessi al master database. Dopo l'abilitazione, tutte le transazioni in questo database vengono eseguite READ COMMITTED SNAPSHOT ISOLATION in e l'impostazione READ UNCOMMITTED a livello di sessione non viene rispettata. Per altre informazioni, vedere Opzioni ALTER DATABASE SET (Transact-SQL).

Argomenti

LETTURA NON CONVALIDATA

Specifica che le istruzioni possono leggere righe modificate da altre transazioni, ma non ancora sottoposte a commit.

Le transazioni in esecuzione a READ UNCOMMITTED livello non rilasciano blocchi condivisi per impedire ad altre transazioni di modificare i dati letti dalla transazione corrente. READ UNCOMMITTED le transazioni non vengono bloccate anche da blocchi esclusivi che impediscono alla transazione corrente di leggere righe modificate ma non sottoposte a commit da altre transazioni. Quando questa opzione è impostata, è possibile leggere le modifiche di cui non è stato eseguito il commit, denominate letture 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 ha lo stesso effetto dell'impostazione NOLOCK su tutte le tabelle in tutte le SELECT istruzioni in una transazione. Tra i livelli di isolamento disponibili, questo è il meno restrittivo.

In SQL Server è inoltre possibile usare 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 READ COMMITTED di isolamento con l'opzione di READ_COMMITTED_SNAPSHOT database impostata su ON.

• Livello SNAPSHOT di isolamento. Per altre informazioni sull'isolamento dello snapshot, vedere Isolamento dello Snapshot in SQL Server.

READ COMMITTED (Lettura Convalidata)

Specifica che le istruzioni non possono leggere i dati modificati ma non sottoposti a commit da altre transazioni. In questo modo vengono evitate 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 opzione è l'impostazione predefinita di SQL Server.

Il comportamento di READ COMMITTED dipende dall'impostazione dell'opzione READ_COMMITTED_SNAPSHOT di database:

• Se READ_COMMITTED_SNAPSHOT è impostato su OFF (impostazione predefinita in SQL Server), il motore di database usa blocchi condivisi per impedire ad altre transazioni di modificare righe mentre la transazione corrente esegue 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 quando viene 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 i blocchi di tabella vengono rilasciati al termine dell'istruzione.

• Se READ_COMMITTED_SNAPSHOT è impostato su ON, il motore di database usa il controllo delle versioni delle righe per presentare a ogni istruzione uno snapshot coerente in modo transazionale dei dati così come esisteva all'inizio dell'istruzione. I blocchi non vengono usati per proteggere i dati dagli aggiornamenti da altre transazioni.

  • READ_COMMITTED_SNAPSHOT ON è la predefinizione su Azure SQL Database e SQL database in Microsoft Fabric.

Importante

La scelta di un livello di isolamento delle transazioni non influisce sui blocchi acquisiti per proteggere le modifiche ai dati. Una transazione ottiene sempre un blocco esclusivo su qualsiasi dato da essa modificato, che mantiene fino al suo completamento, indipendentemente dal livello di isolamento impostato per la transazione. Inoltre, un aggiornamento eseguito a READ COMMITTED livello di isolamento usa blocchi di aggiornamento sulle righe di dati selezionate, mentre un aggiornamento eseguito a SNAPSHOT livello di isolamento usa le versioni di riga per selezionare le righe da aggiornare. Per le operazioni di lettura, i livelli di isolamento delle transazioni definiscono essenzialmente il livello di protezione dagli effetti delle modifiche apportate da altre transazioni. Per altre informazioni, vedere Guida al controllo delle versioni delle transazioni e al controllo delle versioni delle righe.

L'isolamento dello snapshot supporta dati FILESTREAM. In modalità di isolamento dello snapshot, i dati FILESTREAM letti da qualsiasi istruzione in una transazione sono la versione coerente in modo transazionale dei dati esistenti all'inizio della transazione.

Quando l'opzione READ_COMMITTED_SNAPSHOT di database è ON, è possibile usare l'hint di tabella per richiedere il READCOMMITTEDLOCK blocco condiviso anziché il controllo delle versioni delle righe per le singole istruzioni nelle transazioni in esecuzione a READ COMMITTED livello di isolamento.

Nota

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

LETTURA RIPETIBILE

Specifica che le istruzioni non possono leggere i dati modificati ma non ancora sottoposti a commit da altre transazioni 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 le righe lette 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 ritenta l'istruzione , recupera le nuove righe, che genera letture fantasma. Poiché i blocchi condivisi vengono mantenuti alla fine di una transazione anziché essere rilasciati alla fine di ogni istruzione, la concorrenza è inferiore al livello di isolamento predefinito READ COMMITTED . Utilizzare questa opzione solo se necessario.

SNAPSHOT

Specifica che i dati letti da qualsiasi istruzione in una transazione sono la versione coerente a livello di transazione dei dati esistenti all'inizio 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'inizio della transazione corrente non sono visibili alle istruzioni in esecuzione nella transazione corrente. È come se le istruzioni di una transazione ottenessero uno snapshot dei dati di cui è stato eseguito il commit così come si presentavano al momento dell'avvio della transazione.

Tranne quando un database viene recuperato, SNAPSHOT le transazioni non richiedono blocchi durante la lettura dei dati. SNAPSHOT le transazioni che leggono i dati non impediscono ad altre transazioni di scrivere dati. Le transazioni che scrivono dati non impediscono SNAPSHOT alle transazioni di leggere i dati.

Durante la fase di rollback di un ripristino del database, SNAPSHOT le transazioni richiedono un blocco se viene effettuato un tentativo di lettura dei dati bloccati da un'altra transazione di cui viene eseguito il rollback. La SNAPSHOT transazione viene bloccata fino a quando non viene eseguito il rollback della transazione. Il blocco viene rilasciato immediatamente dopo che è stato concesso.

L'opzione ALLOW_SNAPSHOT_ISOLATION di database deve essere impostata su ON prima di avviare una transazione che usa il SNAPSHOT livello di isolamento. Se una transazione che usa il SNAPSHOT livello di isolamento accede ai dati in più database, ALLOW_SNAPSHOT_ISOLATION deve essere impostata su ON in ogni database.

Una transazione non può essere impostata sul SNAPSHOT livello di isolamento che inizia con un altro livello di isolamento. In questo modo la transazione viene interrotta. Se una transazione viene avviata nel SNAPSHOT livello di isolamento, è possibile modificarla in un altro livello di isolamento e quindi tornare a SNAPSHOT. Una transazione viene avviata la prima volta che accede a dati.

Una transazione in esecuzione a SNAPSHOT livello di isolamento può visualizzare le modifiche apportate da tale transazione. Ad esempio, se la transazione esegue un oggetto UPDATE in una tabella e quindi rilascia un'istruzione SELECT sulla stessa tabella, i dati modificati vengono inclusi nel set di risultati.

Nota

In modalità di isolamento dello snapshot, i dati FILESTREAM letti da qualsiasi istruzione in una transazione sono la versione coerente in modo transazionale dei dati esistenti all'inizio della transazione, non all'inizio dell'istruzione.

SERIALIZABLE

Specifica le condizioni seguenti:

• Le istruzioni non possono leggere i dati modificati ma non ancora sottoposti a commit da altre transazioni.

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

• Altre transazioni non possono inserire nuove righe con valori di chiave che rientrano 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 se una delle istruzioni in una transazione viene eseguita una seconda volta, legge 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 ha lo stesso effetto dell'impostazione HOLDLOCK su tutte le tabelle in tutte le SELECT istruzioni in una transazione.

Osservazioni:

Solo una delle opzioni del livello di isolamento può essere impostata alla volta e rimane impostata per tale connessione fino a quando non viene modificata in modo esplicito. Tutte le operazioni di lettura eseguite all'interno della transazione operano in base alle regole per il livello di isolamento specificato, a meno che un hint di tabella nella FROM clausola di un'istruzione specifichi un comportamento di blocco o 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 READ COMMITTED o REPEATABLE READ sono in genere blocchi di riga, anche se i blocchi di riga possono essere inoltrati a blocchi di pagina o di tabella se un numero significativo di righe in una pagina o una tabella fa riferimento alla lettura. Se la transazione modifica una riga dopo la lettura, la transazione acquisisce un blocco esclusivo per proteggere tale riga e il blocco esclusivo viene mantenuto fino al completamento della transazione. Ad esempio, se una REPEATABLE READ transazione ha un blocco condiviso su una riga e la transazione modifica la 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 qualsiasi livello di isolamento all'isolamento SNAPSHOT . Questa operazione causa un errore nella transazione e il relativo rollback. Tuttavia, è possibile modificare una transazione avviata in SNAPSHOT isolamento in 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. Ad esempio, se una transazione è cambiata da READ COMMITTED a SERIALIZABLE, i blocchi condivisi acquisiti dopo la modifica vengono mantenuti fino alla fine della transazione.

Se si esegue un problema SET TRANSACTION ISOLATION LEVEL in una stored procedure o in un trigger, quando l'oggetto restituisce il livello di isolamento viene reimpostato sul livello in vigore quando l'oggetto è stato richiamato. Ad esempio, se si imposta REPEATABLE READ in un batch e il batch chiama una stored procedure che imposta il livello di isolamento su SERIALIZABLE, l'impostazione del livello di isolamento viene ripristinata 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 usa sp_bindsession per associare due sessioni, ogni sessione mantiene l'impostazione del livello di isolamento. L'uso SET TRANSACTION ISOLATION LEVEL di per modificare l'impostazione del livello di isolamento di una sessione non influisce sull'impostazione di altre sessioni associate.

SET TRANSACTION ISOLATION LEVEL ha effetto in fase di esecuzione o di esecuzione e 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 uso 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 altre informazioni sulle operazioni di caricamento bulk, vedere Importazione ed esportazione bulk di dati(SQL Server).

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

Livello di isolamento	Transact-SQL l'accesso	Accesso al file system
Lettura di cui non è stato eseguito il commit	SQL Server	Non supportato
Leggi impegnato	SQL Server	SQL Server
Lettura ripetibile	SQL Server	Non supportato
Serializzabile	SQL Server	Non supportato
Lettura dello snapshot di cui è stato eseguito il commit	SQL Server	SQL Server
Snapshot	SQL Server	SQL Server

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 AdventureWorks2022;
GO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO

BEGIN TRANSACTION;
GO

SELECT *
FROM HumanResources.EmployeePayHistory;
GO

SELECT *
FROM HumanResources.Department;
GO

COMMIT TRANSACTION;
GO

Contenuti correlati

• ALTER DATABASE (Transact-SQL)
• DBCC USEROPTIONS (Transact-SQL)
• SELECT (Transact-SQL)
• Istruzioni SET (Transact-SQL)
• Hint di tabella (Transact-SQL)