Isolamento dello snapshot in SQL Server

L'isolamento dello snapshot migliora la concorrenza per le applicazioni OLTP.

Informazioni sull'isolamento dello snapshot e sul versionamento delle righe

Dopo aver abilitato l'isolamento dello snapshot, è necessario mantenere le versioni aggiornate delle righe per ogni transazione. Prima di SQL Server 2019, queste versioni venivano archiviate in tempdb. SQL Server 2019 introduce una nuova funzionalità, Ripristino accelerato del database (ADR) che richiede il proprio set di versioni di riga. Pertanto, a partire da SQL Server 2019, se ADR non è abilitato, le versioni di riga vengono mantenute come tempdb sempre. Se ADR è abilitato, tutte le versioni di riga, sia quelle relative all'isolamento dello snapshot che all'ADR, vengono mantenute nel Persistent Version Store (PVS) di ADR, che si trova nel database utente in un filegroup specificato dall'utente. Un numero di sequenza di transazioni univoco identifica ogni transazione. Questi numeri univoci vengono registrati per ogni versione di riga. La transazione funziona con le versioni di riga più recenti che hanno un numero di sequenza precedente al numero di sequenza della transazione. Le versioni di riga più recenti create dopo l'inizio della transazione vengono ignorate dalla transazione.

Il termine "snapshot" riflette il fatto che tutte le query nella transazione visualizzano la stessa versione, o snapshot, del database, in base allo stato del database nel momento in cui viene avviata la transazione. Nessun blocco viene acquisito nelle righe di dati o nelle pagine di dati sottostanti in una transazione snapshot, che consente l'esecuzione di altre transazioni senza essere bloccate da una transazione precedente non completata. Le transazioni che modificano i dati non bloccano le transazioni che leggono i dati e le transazioni che leggono i dati non bloccano le transazioni che scrivono dati, come avverrebbe normalmente con il livello di isolamento predefinito READ COMMITTED in SQL Server. Questo comportamento, oltre a non causare blocchi, riduce anche in modo significativo la probabilità di deadlock per le transazioni complesse.

L'isolamento dello snapshot usa un modello di concorrenza ottimistica. Se una transazione snapshot tenta di eseguire il commit delle modifiche apportate ai dati modificati dall'inizio della transazione, verrà eseguito il rollback della transazione e verrà generato un errore. È possibile evitare questo problema usando UPDLOCK hint per SELECT le istruzioni che accedono ai dati da modificare. Per ulteriori informazioni, vedere Suggerimenti (Transact-SQL).

L'isolamento dello snapshot deve essere abilitato impostando l'opzione ALLOW_SNAPSHOT_ISOLATION ON di database prima che venga usata nelle transazioni. In questo modo viene attivato il meccanismo per l'archiviazione delle versioni di riga nel database temporaneo (tempdb). È necessario abilitare l'isolamento dello snapshot in ogni database che lo usa con l'istruzione Transact-SQL ALTER DATABASE . In questo senso, l'isolamento dello snapshot differisce dai livelli di isolamento tradizionali di READ COMMITTED, REPEATABLE READ, SERIALIZABLEe READ UNCOMMITTED, che non richiedono alcuna configurazione. Le istruzioni seguenti consentono transazioni SNAPSHOT e letture READ COMMITTED con controllo delle versioni delle righe:

ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON

L'impostazione dell'opzione READ_COMMITTED_SNAPSHOT ON consente l'accesso alle righe con controllo delle versioni nel livello di isolamento predefinito READ COMMITTED. Se l'opzione READ_COMMITTED_SNAPSHOT è impostata su OFF, è necessario impostare in modo esplicito il livello di isolamento snapshot per ogni sessione per accedere alle righe con controllo delle versioni.

Gestione della concorrenza con i livelli di isolamento

Il livello di isolamento in cui viene eseguita un'istruzione Transact-SQL determina il comportamento di blocco e di controllo delle versioni delle righe. Un livello di isolamento ha un ambito a livello di connessione e, una volta impostato per una connessione con l'istruzione SET TRANSACTION ISOLATION LEVEL , rimane attivo fino a quando la connessione non viene chiusa o viene impostato un altro livello di isolamento. Quando una connessione viene chiusa e restituita al pool, viene mantenuto il livello di isolamento dell'ultima SET TRANSACTION ISOLATION LEVEL istruzione. Le connessioni successive che riutilizzano una connessione in pool utilizzano il livello di isolamento che era attivo quando la connessione è stata aggiunta al pool.

Le singole query eseguite in una connessione possono contenere hint di blocco che modificano l'isolamento per una singola istruzione o transazione, ma non influiscono sul livello di isolamento della connessione. I livelli di isolamento o gli hint di blocco impostati in stored procedure o funzioni non modificano il livello di isolamento della connessione che li chiama e sono attivi solo per la durata della chiamata della stored procedure o della funzione.

Nelle versioni precedenti di SQL Server sono supportati quattro livelli di isolamento definiti nello standard SQL-92:

• READ UNCOMMITTED è il livello di isolamento meno restrittivo perché ignora i blocchi applicati da altre transazioni. Le transazioni eseguite in READ UNCOMMITTED possono leggere i valori di dati modificati di cui non è ancora stato eseguito il commit da altre transazioni. Queste sono denominate letture "dirty".

• READ COMMITTEDè il livello di isolamento predefinito per SQL Server. Evita le letture dirty specificando che le istruzioni non possono leggere i valori dei dati modificati da altre transazioni, ma di cui non è ancora stato eseguito il commit. Altre transazioni possono comunque modificare, inserire o eliminare i dati nell'intervallo tra l'esecuzione delle singole istruzioni nella transazione corrente, con conseguenze come letture irripetibili e la presenza di dati fantasma.

• REPEATABLE READ è un livello di isolamento più restrittivo di READ COMMITTED. Comprende READ COMMITTED e specifica inoltre che nessun'altra transazione può modificare o eliminare dati letti dalla transazione corrente fino al commit della transazione corrente. La concorrenza è inferiore rispetto a READ COMMITTED perché i blocchi condivisi sui dati di lettura vengono mantenuti per la durata della transazione anziché essere rilasciati alla fine di ogni istruzione.

• SERIALIZABLE è il livello di isolamento più restrittivo, perché blocca interi intervalli di chiavi e mantiene i blocchi fino al completamento della transazione. Comprende REPEATABLE READ e aggiunge la restrizione che altre transazioni non possono inserire nuove righe in intervalli letti dalla transazione fino al completamento della transazione.

Per ulteriori informazioni, fare riferimento alla Guida al blocco delle transazioni e al controllo delle versioni delle righe.

Estensioni del livello di isolamento dello snapshot

SQL Server ha introdotto estensioni ai livelli di isolamento di SQL-92 con l'introduzione del livello di isolamento SNAPSHOT e un'implementazione aggiuntiva di READ COMMITTED.

• SNAPSHOT l'isolamento specifica che i dati letti all'interno di una transazione non rifletteranno mai le modifiche apportate da altre transazioni simultanee. La transazione usa le versioni delle righe di dati esistenti all'inizio della transazione. Non vengono applicati lock ai dati quando vengono letti, quindi SNAPSHOT le transazioni non impediscono ad altre transazioni di scrivere dati. Le transazioni che eseguono scritture di dati non impediscono alle transazioni snapshot di leggere i dati. Per usarlo, è necessario abilitare l'isolamento dello snapshot impostando l'opzione ALLOW_SNAPSHOT_ISOLATION del database.

• L'opzione READ_COMMITTED_SNAPSHOT di database controlla il modo in cui READ COMMITTED legge i dati. Con questa opzione OFF, il valore predefinito in SQL Server, il motore di database usa blocchi condivisi per proteggere le letture. Con questa opzione ON, READ COMMITTED usa versioni di riga a livello di istruzione anziché blocchi condivisi per proteggere le letture.

Funzionamento dell'isolamento dello snapshot e del controllo delle versioni delle righe

Quando il SNAPSHOT livello di isolamento è abilitato, ogni volta che viene aggiornata una riga, il Motore di database di SQL Server archivia una copia della riga originale in tempdbe aggiunge un numero di sequenza di transazione alla riga. Di seguito è riportata la sequenza di eventi che si verifica:

• Viene avviata una nuova transazione a cui viene assegnato un numero di sequenza delle transazioni.

• Il motore del database legge una riga all'interno della transazione e recupera la versione della riga dal tempdb il cui numero di sequenza è più vicino e minore del numero di sequenza della transazione.

• Il motore del database verifica che il numero di sequenza della transazione non sia presente nell'elenco dei numeri di sequenza delle transazioni attive che non sono state sottoposte a commit al momento dell'avvio della transazione snapshot.

• La transazione legge la versione della riga da tempdb aggiornata all'inizio della transazione. Non visualizza le nuove righe inserite dopo l'avvio della transazione, perché queste avranno valori dei numeri di sequenza superiori al valore del numero di sequenza della transazione.

• La transazione corrente visualizzerà le righe eliminate dopo l'avvio della transazione, perché sarà presente una versione di riga in tempdb con un valore di numero di sequenza inferiore.

L'effetto netto dell'isolamento dello snapshot è che la transazione visualizza tutti i dati come esistevano all'inizio della transazione, senza applicare o inserire blocchi nelle tabelle sottostanti. Ciò può comportare un miglioramento delle prestazioni in situazioni in cui si verificano contese.

Una transazione snapshot usa sempre il controllo della concorrenza ottimistica, evitando i blocchi che impediscono alle altre transazioni di aggiornare le righe. Se una transazione snapshot tenta di eseguire il commit di un aggiornamento a una riga che è stata modificata dopo l'inizio della transazione, la transazione viene annullata e viene generato un errore.

Uso dell'isolamento dello snapshot in ADO.NET

Per avviare una transazione con isolamento dello snapshot, abilitare ALLOW_SNAPSHOT_ISOLATION per il database e passare Snapshot a BeginTransaction. L'opzione READ_COMMITTED_SNAPSHOT di database controlla le letture con controllo delle versioni delle righe a livello di isolamento read-committed e non seleziona l'isolamento dello snapshot per una transazione. Il frammento seguente presuppone che connection sia un'istanza aperta della SqlConnection classe .

Dim sqlTran As SqlTransaction = _
  connection.BeginTransaction(IsolationLevel.Snapshot)

SqlTransaction sqlTran =
  connection.BeginTransaction(IsolationLevel.Snapshot);

Esempio

L'esempio seguente illustra come i diversi livelli di isolamento si comportano tentando di accedere ai dati bloccati e non è inteso come da usare nel codice di produzione.

Il codice si connette al AdventureWorks database di esempio in SQL Server e crea una tabella denominata TestSnapshot e inserisce una riga di dati. Il codice usa l'istruzione ALTER DATABASE Transact-SQL per attivare l'isolamento dello snapshot per il database, ma non imposta l'opzione READ_COMMITTED_SNAPSHOT , lasciando in vigore il comportamento predefinito READ COMMITTED a livello di isolamento. Il codice esegue quindi le azioni seguenti:

• Inizia, ma non si completa, sqlTransaction1, che usa il livello di isolamento SERIALIZABLE per avviare una transazione di aggiornamento. Questo ha l'effetto di bloccare la tabella.

• Apre una seconda connessione e avvia una seconda transazione utilizzando il livello di isolamento SNAPSHOT per leggere i dati nella tabella TestSnapshot. Poiché l'isolamento snapshot è abilitato, questa transazione può leggere i dati esistenti prima che sqlTransaction1 iniziasse.

• Apre una terza connessione e avvia una transazione usando il READ COMMITTED livello di isolamento per tentare di leggere i dati nella tabella. In questo caso, il codice non può leggere i dati perché non è in grado di leggere oltre i blocchi posizionati nella tabella nella prima transazione e si verifica il timeout. Lo stesso risultato si verifica se sono stati usati i livelli di isolamento REPEATABLE READ e SERIALIZABLE perché questi livelli di isolamento non possono anche leggere oltre i blocchi inseriti nella prima transazione.

• Apre una quarta connessione e avvia una transazione usando il READ UNCOMMITTED livello di isolamento, che esegue una lettura dirty del valore di cui non è stato eseguito il commit in sqlTransaction1. Questo valore potrebbe non esistere mai nel database se non viene eseguito il commit della prima transazione.

• Esegue il rollback della prima transazione e pulisce eliminando la tabella TestSnapshot e disattivando l'isolamento snapshot per il database AdventureWorks.

// Assumes GetConnectionString returns a valid connection string
// where pooling is turned off by setting Pooling=False;.
var connectionString = GetConnectionString();
using (SqlConnection connection1 = new(connectionString))
{
    // Drop the TestSnapshot table if it exists
    connection1.Open();
    SqlCommand command1 = connection1.CreateCommand();
    command1.CommandText = "IF EXISTS "
        + "(SELECT * FROM sys.tables WHERE name=N'TestSnapshot') "
        + "DROP TABLE TestSnapshot";
    try
    {
        command1.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
    // Enable Snapshot isolation
    command1.CommandText =
        "ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON";
    command1.ExecuteNonQuery();

    // Create a table named TestSnapshot and insert one row of data
    command1.CommandText =
        "CREATE TABLE TestSnapshot (ID int primary key, valueCol int)";
    command1.ExecuteNonQuery();
    command1.CommandText =
        "INSERT INTO TestSnapshot VALUES (1,1)";
    command1.ExecuteNonQuery();

    // Begin, but do not complete, a transaction to update the data
    // with the Serializable isolation level, which locks the table
    // pending the commit or rollback of the update. The original
    // value in valueCol was 1, the proposed new value is 22.
    SqlTransaction transaction1 =
        connection1.BeginTransaction(IsolationLevel.Serializable);
    command1.Transaction = transaction1;
    command1.CommandText =
        "UPDATE TestSnapshot SET valueCol=22 WHERE ID=1";
    command1.ExecuteNonQuery();

    // Open a second connection to AdventureWorks
    using (SqlConnection connection2 = new(connectionString))
    {
        connection2.Open();
        // Initiate a second transaction to read from TestSnapshot
        // using Snapshot isolation. This will read the original
        // value of 1 since transaction1 has not yet committed.
        SqlCommand command2 = connection2.CreateCommand();
        SqlTransaction transaction2 =
            connection2.BeginTransaction(IsolationLevel.Snapshot);
        command2.Transaction = transaction2;
        command2.CommandText =
            "SELECT ID, valueCol FROM TestSnapshot";
        using (SqlDataReader reader2 = command2.ExecuteReader())
        {
            while (reader2.Read())
            {
                Console.WriteLine("Expected 1,1 Actual "
                    + reader2.GetValue(0)
                    + "," + reader2.GetValue(1));
            }
        }
        transaction2.Commit();
    }

    // Open a third connection to AdventureWorks and
    // initiate a third transaction to read from TestSnapshot
    // using ReadCommitted isolation level. This transaction
    // will not be able to view the data because of
    // the locks placed on the table in transaction1
    // and will time out after 4 seconds.
    // You would see the same behavior with the
    // RepeatableRead or Serializable isolation levels.
    using (SqlConnection connection3 = new(connectionString))
    {
        connection3.Open();
        SqlCommand command3 = connection3.CreateCommand();
        SqlTransaction transaction3 =
            connection3.BeginTransaction(IsolationLevel.ReadCommitted);
        command3.Transaction = transaction3;
        command3.CommandText =
            "SELECT ID, valueCol FROM TestSnapshot";
        command3.CommandTimeout = 4;
        try
        {
            using (SqlDataReader sqldatareader3 = command3.ExecuteReader())
            {
                while (sqldatareader3.Read())
                {
                    Console.WriteLine("You should never hit this.");
                }
            }
            transaction3.Commit();
        }
        catch (Exception ex)
        {
            Console.WriteLine("Expected timeout expired exception: "
                + ex.Message);
            transaction3.Rollback();
        }
    }

    // Open a fourth connection to AdventureWorks and
    // initiate a fourth transaction to read from TestSnapshot
    // using the ReadUncommitted isolation level. ReadUncommitted
    // will not hit the table lock, and will allow a dirty read
    // of the proposed new value 22 for valueCol. If the first
    // transaction rolls back, this value will never actually have
    // existed in the database.
    using (SqlConnection connection4 = new(connectionString))
    {
        connection4.Open();
        SqlCommand command4 = connection4.CreateCommand();
        SqlTransaction transaction4 =
            connection4.BeginTransaction(IsolationLevel.ReadUncommitted);
        command4.Transaction = transaction4;
        command4.CommandText =
            "SELECT ID, valueCol FROM TestSnapshot";
        using (SqlDataReader reader4 = command4.ExecuteReader())
        {
            while (reader4.Read())
            {
                Console.WriteLine("Expected 1,22 Actual "
                    + reader4.GetValue(0)
                    + "," + reader4.GetValue(1));
            }
        }

        transaction4.Commit();
    }

    // Roll back the first transaction
    transaction1.Rollback();
}

// CLEANUP
// Delete the TestSnapshot table and set
// ALLOW_SNAPSHOT_ISOLATION OFF
using (SqlConnection connection5 = new(connectionString))
{
    connection5.Open();
    SqlCommand command5 = connection5.CreateCommand();
    command5.CommandText = "DROP TABLE TestSnapshot";
    SqlCommand command6 = connection5.CreateCommand();
    command6.CommandText =
        "ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION OFF";
    try
    {
        command5.ExecuteNonQuery();
        command6.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}
Console.WriteLine("Done!");

' Assumes GetConnectionString returns a valid connection string
' where pooling is turned off by setting Pooling=False;.
Dim connectionString As String = GetConnectionString()

Using connection1 As New SqlConnection(connectionString)
    ' Drop the TestSnapshot table if it exists
    connection1.Open()
    Dim command1 As SqlCommand = connection1.CreateCommand
    command1.CommandText = "IF EXISTS " & _
    "(SELECT * FROM sys.tables WHERE name=N'TestSnapshot') " _
      & "DROP TABLE TestSnapshot"
    Try
        command1.ExecuteNonQuery()
    Catch ex As Exception
        Console.WriteLine(ex.Message)
    End Try

    ' Enable SNAPSHOT isolation
    command1.CommandText = _
    "ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON"
    command1.ExecuteNonQuery()

    ' Create a table named TestSnapshot and insert one row of data
    command1.CommandText = _
    "CREATE TABLE TestSnapshot (ID int primary key, valueCol int)"
    command1.ExecuteNonQuery()
    command1.CommandText = _
      "INSERT INTO TestSnapshot VALUES (1,1)"
    command1.ExecuteNonQuery()

    ' Begin, but do not complete, a transaction to update the data
    ' with the Serializable isolation level, which locks the table
    ' pending the commit or rollback of the update. The original
    ' value in valueCol was 1, the proposed new value is 22.
    Dim transaction1 As SqlTransaction = _
      connection1.BeginTransaction(IsolationLevel.Serializable)
    command1.Transaction = transaction1
    command1.CommandText = _
     "UPDATE TestSnapshot SET valueCol=22 WHERE ID=1"
    command1.ExecuteNonQuery()

    ' Open a second connection to AdventureWorks
    Dim connection2 As SqlConnection = New SqlConnection(connectionString)
    Using connection2
        connection2.Open()

        ' Initiate a second transaction to read from TestSnapshot
        ' using Snapshot isolation. This will read the original
        ' value of 1 since transaction1 has not yet committed.
        Dim command2 As SqlCommand = connection2.CreateCommand()
        Dim transaction2 As SqlTransaction = _
          connection2.BeginTransaction(IsolationLevel.Snapshot)
        command2.Transaction = transaction2
        command2.CommandText = _
            "SELECT ID, valueCol FROM TestSnapshot"
        Using reader2 As SqlDataReader = command2.ExecuteReader()
            While reader2.Read()
                Console.WriteLine("Expected 1,1 Actual " _
                  & reader2.GetValue(0).ToString() + "," _
                  & reader2.GetValue(1).ToString())
            End While
        End Using
        transaction2.Commit()
    End Using

    ' Open a third connection to AdventureWorks and
    ' initiate a third transaction to read from TestSnapshot
    ' using the ReadCommitted isolation level. This transaction
    ' will not be able to view the data because of
    ' the locks placed on the table in transaction1
    ' and will time out after 4 seconds.
    ' You would see the same behavior with the
    ' RepeatableRead or Serializable isolation levels.
    Dim connection3 As SqlConnection = New SqlConnection(connectionString)
    Using connection3
        connection3.Open()
        Dim command3 As SqlCommand = connection3.CreateCommand()
        Dim transaction3 As SqlTransaction = _
            connection3.BeginTransaction(IsolationLevel.ReadCommitted)
        command3.Transaction = transaction3
        command3.CommandText = _
            "SELECT ID, valueCol FROM TestSnapshot"
        command3.CommandTimeout = 4

        Try
            Using reader3 As SqlDataReader = command3.ExecuteReader()
                While reader3.Read()
                    Console.WriteLine("You should never hit this.")
                End While
            End Using
            transaction3.Commit()
        Catch ex As Exception
            Console.WriteLine("Expected timeout expired exception: " _
              & ex.Message)
            transaction3.Rollback()
        End Try
    End Using

    ' Open a fourth connection to AdventureWorks and
    ' initiate a fourth transaction to read from TestSnapshot
    ' using the ReadUncommitted isolation level. ReadUncommitted
    ' will not hit the table lock, and will allow a dirty read
    ' of the proposed new value 22. If the first transaction
    ' transaction rolls back, this value will never actually have
    ' existed in the database.
    Dim connection4 As SqlConnection = New SqlConnection(connectionString)
    Using connection4
        connection4.Open()
        Dim command4 As SqlCommand = connection4.CreateCommand()
        Dim transaction4 As SqlTransaction = _
          connection4.BeginTransaction(IsolationLevel.ReadUncommitted)
        command4.Transaction = transaction4
        command4.CommandText = _
            "SELECT ID, valueCol FROM TestSnapshot"
        Using reader4 As SqlDataReader = command4.ExecuteReader()
            While reader4.Read()
                Console.WriteLine("Expected 1,22 Actual " _
                  & reader4.GetValue(0).ToString() _
                  & "," + reader4.GetValue(1).ToString())
            End While
        End Using
        transaction4.Commit()

        ' Rollback transaction1
        transaction1.Rollback()
    End Using
End Using

' CLEANUP
' Drop TestSnapshot table and set
' ALLOW_SNAPSHOT_ISOLATION OFF for AdventureWorks
Dim connection5 As New SqlConnection(connectionString)
Using connection5
    connection5.Open()
    Dim command5 As SqlCommand = connection5.CreateCommand()
    command5.CommandText = "DROP TABLE TestSnapshot"
    Dim command6 As SqlCommand = connection5.CreateCommand()
    command6.CommandText = _
   "ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION OFF"
    Try
        command5.ExecuteNonQuery()
        command6.ExecuteNonQuery()
    Catch ex As Exception
        Console.WriteLine(ex.Message)
    End Try
End Using

Esempio

Nell'esempio seguente viene illustrato il comportamento dell'isolamento dello snapshot quando i dati vengono modificati. Il codice esegue le azioni seguenti:

• Si connette al database di esempio AdventureWorks e abilita l'isolamento SNAPSHOT.

• Crea una tabella denominata TestSnapshotUpdate e inserisce tre righe di dati di esempio.

• Inizia, ma non completa, sqlTransaction1 usando SNAPSHOT l'isolamento. Nella transazione vengono selezionate tre righe di dati.

• Crea un secondo SqlConnection per AdventureWorks e crea una seconda transazione usando il livello di isolamento READ COMMITTED che aggiorna un valore in una delle righe selezionate in sqlTransaction1.

• Esegue il commit sqlTransaction2.

• Torna a sqlTransaction1 e tenta di aggiornare la stessa riga su cui sqlTransaction1 ha già eseguito il commit. Viene generato l'errore 3960 e sqlTransaction1 viene automaticamente sottoposto a rollback. Il SqlException.Number e il SqlException.Message vengono visualizzati nella finestra della console.

• Esegue il codice di pulizia per disattivare l'isolamento dello snapshot in AdventureWorks ed eliminare la tabella TestSnapshotUpdate.

// Assumes GetConnectionString returns a valid connection string
// where pooling is turned off by setting Pooling=False;.
var connectionString = GetConnectionString();
using (SqlConnection connection1 = new(connectionString))
{
    connection1.Open();
    SqlCommand command1 = connection1.CreateCommand();

    // Enable Snapshot isolation in AdventureWorks
    command1.CommandText =
        "ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON";
    try
    {
        command1.ExecuteNonQuery();
        Console.WriteLine(
            "Snapshot Isolation turned on in AdventureWorks.");
    }
    catch (Exception ex)
    {
        Console.WriteLine($"ALLOW_SNAPSHOT_ISOLATION ON failed: {ex.Message}");
    }
    // Create a table
    command1.CommandText =
        "IF EXISTS "
        + "(SELECT * FROM sys.tables "
        + "WHERE name=N'TestSnapshotUpdate')"
        + " DROP TABLE TestSnapshotUpdate";
    command1.ExecuteNonQuery();
    command1.CommandText =
        "CREATE TABLE TestSnapshotUpdate "
        + "(ID int primary key, CharCol nvarchar(100));";
    try
    {
        command1.ExecuteNonQuery();
        Console.WriteLine("TestSnapshotUpdate table created.");
    }
    catch (Exception ex)
    {
        Console.WriteLine($"CREATE TABLE failed: {ex.Message}");
    }
    // Insert some data
    command1.CommandText =
        "INSERT INTO TestSnapshotUpdate VALUES (1,N'abcdefg');"
        + "INSERT INTO TestSnapshotUpdate VALUES (2,N'hijklmn');"
        + "INSERT INTO TestSnapshotUpdate VALUES (3,N'opqrstuv');";
    try
    {
        command1.ExecuteNonQuery();
        Console.WriteLine("Data inserted TestSnapshotUpdate table.");
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }

    // Begin, but do not complete, a transaction
    // using the Snapshot isolation level.
    SqlTransaction transaction1 = default!;
    try
    {
        transaction1 = connection1.BeginTransaction(IsolationLevel.Snapshot);
        command1.CommandText =
            "SELECT * FROM TestSnapshotUpdate WHERE ID BETWEEN 1 AND 3";
        command1.Transaction = transaction1;
        command1.ExecuteNonQuery();
        Console.WriteLine("Snapshot transaction1 started.");

        // Open a second Connection/Transaction to update data
        // using ReadCommitted. This transaction should succeed.
        using (SqlConnection connection2 = new(connectionString))
        {
            connection2.Open();
            SqlCommand command2 = connection2.CreateCommand();
            command2.CommandText = "UPDATE TestSnapshotUpdate SET CharCol="
                + "N'New value from Connection2' WHERE ID=1";
            SqlTransaction transaction2 =
                connection2.BeginTransaction(IsolationLevel.ReadCommitted);
            command2.Transaction = transaction2;
            try
            {
                command2.ExecuteNonQuery();
                transaction2.Commit();
                Console.WriteLine(
                    "transaction2 has modified data and committed.");
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
                transaction2.Rollback();
            }
            finally
            {
                transaction2.Dispose();
            }
        }

        // Now try to update a row in Connection1/Transaction1.
        // This transaction should fail because Transaction2
        // succeeded in modifying the data.
        command1.CommandText =
            "UPDATE TestSnapshotUpdate SET CharCol="
            + "N'New value from Connection1' WHERE ID=1";
        command1.Transaction = transaction1;
        command1.ExecuteNonQuery();
        transaction1.Commit();
        Console.WriteLine("You should never see this.");
    }
    catch (SqlException ex)
    {
        Console.WriteLine("Expected failure for transaction1:");
        Console.WriteLine($"  {ex.Number}: {ex.Message}");
    }
    finally
    {
        transaction1.Dispose();
    }
}

// CLEANUP:
// Turn off Snapshot isolation and delete the table
using (SqlConnection connection3 = new(connectionString))
{
    connection3.Open();
    SqlCommand command3 = connection3.CreateCommand();
    command3.CommandText =
        "ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION OFF";
    try
    {
        command3.ExecuteNonQuery();
        Console.WriteLine(
            "CLEANUP: Snapshot isolation turned off in AdventureWorks.");
    }
    catch (Exception ex)
    {
        Console.WriteLine($"CLEANUP FAILED: {ex.Message}");
    }
    command3.CommandText = "DROP TABLE TestSnapshotUpdate";
    try
    {
        command3.ExecuteNonQuery();
        Console.WriteLine("CLEANUP: TestSnapshotUpdate table deleted.");
    }
    catch (Exception ex)
    {
        Console.WriteLine($"CLEANUP FAILED: {ex.Message}");
    }
}

' Assumes GetConnectionString returns a valid connection string
' where pooling is turned off by setting Pooling=False;.
Dim connectionString As String = GetConnectionString()

Using connection1 As New SqlConnection(connectionString)
    ' Enable Snapshot isolation in AdventureWorks
    connection1.Open()
    Dim command1 As SqlCommand = connection1.CreateCommand
    command1.CommandText = _
   "ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON;"
    Try
        command1.ExecuteNonQuery()
        Console.WriteLine( _
         "Snapshot Isolation turned on in AdventureWorks.")
    Catch ex As Exception
        Console.WriteLine("ALLOW_SNAPSHOT_ISOLATION failed: {0}", ex.Message)
    End Try

    ' Create a table
    command1.CommandText = _
      "IF EXISTS (SELECT * FROM sys.databases " _
      & "WHERE name=N'TestSnapshotUpdate') " _
      & "DROP TABLE TestSnapshotUpdate"
    command1.ExecuteNonQuery()
    command1.CommandText = _
      "CREATE TABLE TestSnapshotUpdate (ID int primary key, " _
      & "CharCol nvarchar(100));"
    Try
        command1.ExecuteNonQuery()
        Console.WriteLine("TestSnapshotUpdate table created.")
    Catch ex As Exception
        Console.WriteLine("CREATE TABLE failed: {0}", ex.Message)
    End Try

    ' Insert some data
    command1.CommandText = _
      "INSERT INTO TestSnapshotUpdate VALUES (1,N'abcdefg');" _
      & "INSERT INTO TestSnapshotUpdate VALUES (2,N'hijklmn');" _
      & "INSERT INTO TestSnapshotUpdate VALUES (3,N'opqrstuv');"
    Try
        command1.ExecuteNonQuery()
        Console.WriteLine("Data inserted TestSnapshotUpdate table.")
    Catch ex As Exception
        Console.WriteLine(ex.Message)
    End Try

    ' Begin, but do not complete, a transaction
    ' using the Snapshot isolation level
    Dim transaction1 As SqlTransaction = Nothing
    Try
        transaction1 = connection1.BeginTransaction(IsolationLevel.Snapshot)
        command1.CommandText = _
          "SELECT * FROM TestSnapshotUpdate WHERE ID " _
          & "BETWEEN 1 AND 3"
        command1.Transaction = transaction1
        command1.ExecuteNonQuery()
        Console.WriteLine("Snapshot transaction1 started.")

        ' Open a second Connection/Transaction to update data
        ' using ReadCommitted. This transaction should succeed.
        Dim connection2 As SqlConnection = New SqlConnection(connectionString)
        Using connection2
            connection2.Open()
            Dim command2 As SqlCommand = connection2.CreateCommand()
            command2.CommandText = "UPDATE TestSnapshotUpdate SET " _
              & "CharCol=N'New value from Connection2' WHERE ID=1"
            Dim transaction2 As SqlTransaction = _
              connection2.BeginTransaction(IsolationLevel.ReadCommitted)
            command2.Transaction = transaction2
            Try
                command2.ExecuteNonQuery()
                transaction2.Commit()
                Console.WriteLine( _
                  "transaction2 has modified data and committed.")
            Catch ex As SqlException
                Console.WriteLine(ex.Message)
                transaction2.Rollback()
            Finally
                transaction2.Dispose()
            End Try
        End Using

        ' Now try to update a row in Connection1/Transaction1.
        ' This transaction should fail because Transaction2
        ' succeeded in modifying the data.
        command1.CommandText = _
          "UPDATE TestSnapshotUpdate SET CharCol=" _
            & "N'New value from Connection1' WHERE ID=1"
        command1.Transaction = transaction1
        command1.ExecuteNonQuery()
        transaction1.Commit()
        Console.WriteLine("You should never see this.")

    Catch ex As SqlException
        Console.WriteLine("Expected failure for transaction1:")
        Console.WriteLine("  {0}: {1}", ex.Number, ex.Message)
    Finally
        transaction1.Dispose()
    End Try
End Using

' CLEANUP:
' Turn off Snapshot isolation and delete the table
Dim connection3 As New SqlConnection(connectionString)
Using connection3
    connection3.Open()
    Dim command3 As SqlCommand = connection3.CreateCommand()
    command3.CommandText = _
  "ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION OFF"
    Try
        command3.ExecuteNonQuery()
        Console.WriteLine( _
         "Snapshot isolation turned off in AdventureWorks.")
    Catch ex As Exception
        Console.WriteLine("CLEANUP FAILED: {0}", ex.Message)
    End Try

    command3.CommandText = "DROP TABLE TestSnapshotUpdate"
    Try
        command3.ExecuteNonQuery()
        Console.WriteLine("TestSnapshotUpdate table deleted.")
    Catch ex As Exception
        Console.WriteLine("CLEANUP FAILED: {0}", ex.Message)
    End Try
End Using

Uso dei suggerimenti di blocco con isolamento dello snapshot

Nell'esempio precedente la prima transazione seleziona i dati e una seconda transazione aggiorna i dati prima che la prima transazione venga completata, causando un conflitto di aggiornamento quando la prima transazione tenta di aggiornare la stessa riga. Puoi ridurre la probabilità di conflitti di aggiornamento nelle transazioni snapshot che durano a lungo specificando suggerimenti di blocco all'inizio della transazione. L'istruzione seguente SELECT usa l'hint UPDLOCK per bloccare le righe selezionate:

SELECT * FROM TestSnapshotUpdate WITH (UPDLOCK)
  WHERE PriKey BETWEEN 1 AND 3

L'uso dell'hint di blocco UPDLOCK blocca tutte le righe che tentano di aggiornare tali righe prima del completamento della prima transazione. Ciò garantisce che le righe selezionate non siano oggetto di conflitti quando vengono aggiornate nella transazione in un secondo momento. Per ulteriori informazioni, vedere Suggerimenti (Transact-SQL).

Se l'applicazione presenta molti conflitti, l'isolamento dello snapshot potrebbe non essere la scelta migliore. Gli hint devono essere usati solo quando strettamente necessario. L'applicazione non deve essere progettata in modo che il suo funzionamento si basi costantemente sugli hint di blocco.

Vedere anche

• SQL Server e ADO.NET
• Panoramica di ADO.NET
• Guida sul blocco delle transazioni e sul versionamento delle righe