Snapshotisolation in SQL Server (ADO.NET)

Artikel
05/20/2011

SQL Server 2005 stellt einen neuen Snapshot-Isolationsgrad bereit, um die Parallelität für OLTP-Anwendungen zu erhöhen. In früheren Versionen von SQL Server basierte Parallelität nur auf Sperren, wodurch für manche Anwendungen Probleme mit Blockaden und Deadlocks entstanden. Snapshot-Isolation dagegen beruht auf Verbesserungen bei der Zeilenversionserstellung und soll durch das Vermeiden von Lese-Schreib-Blockierungsszenarios die Leistung steigern.

Informationen zur Snapshot-Isolation und Zeilenversionserstellung

Nach dem Aktivieren der Snapshot-Isolation werden aktuelle Zeilenversionen für jede Transaktion in tempdb beibehalten. Jede Transaktion wird durch eine Transaktionsfolgenummer gekennzeichnet, und diese eindeutigen Nummern werden für jede Zeilenversion aufgezeichnet. Für die Transaktion werden die aktuellsten Zeilenversionen verwendet, die über eine Folgenummer verfügen, die niedriger ist als diejenige der Transaktion. Aktuellere, nach dem Beginn der Transaktion erstellte Zeilenversionen werden von der Transaktion ignoriert.

Der Begriff "Snapshot" (Momentaufnahme) gibt die Tatsache wieder, dass alle Abfragen in der Transaktion auf dieselbe Version (Momentaufnahme) der Datenbank zurückgehen, die auf dem Zustand der Datenbank zum Zeitpunkt des Beginns der Transaktion basiert. In einer Snapshot-Transaktion werden für die zugrunde liegenden Datenzeilen oder Datenseiten keine Sperren bezogen, wodurch andere Transaktionen ausgeführt werden können, ohne durch eine vorherige, nicht vollständig ausgeführte Transaktion blockiert zu werden. Transaktionen, die Daten ändern, blockieren keine Transaktionen, die Daten lesen; und Transaktionen, die Daten lesen, blockieren keine Transaktionen, die Daten schreiben. Beim READ COMMITTED-Standardisolationsgrad in SQL Server wäre dies i. d. R. der Fall. Dieses nicht blockierende Verhalten verringert die Wahrscheinlichkeit für Deadlocks bei komplexen Transaktionen beträchtlich.

Bei der Snapshot-Isolation wird ein Modell der vollständigen Parallelität verwendet. Wenn eine Snapshot-Transaktion versucht, Änderungen an Daten zu speichern, die seit dem Beginn der Transaktion geändert wurden, wird die Transaktion zurückgesetzt und ein Fehler ausgelöst. Dies kann durch das Verwenden von UPDLOCK-Hinweisen für SELECT-Anweisungen verhindert werden, die auf zu ändernde Daten zugreifen. Weitere Informationen finden Sie in der Onlinedokumentation zu SQL Server unter "Locking Hints".

Die Snapshot-Isolation muss durch das Festlegen der ALLOW_SNAPSHOT_ISOLATION ON-Datenbankoption aktiviert werden, bevor sie in Transaktionen verwendet wird. Dadurch wird der Mechanismus zum Speichern von Zeilenversionen in der temporären Datenbank (tempdb) aktiviert. Die Snapshot-Isolation muss in jeder Datenbank, die diese verwendet, mit der ALTER DATABASE-Transact-SQL-Anweisung aktiviert werden. In diesem Punkt unterscheidet sich die Snapshot-Isolation von den herkömmlichen Isolationsgraden READ COMMITTED, REPEATABLE READ, SERIALIZABLE und READ UNCOMMITTED, für die keine Konfiguration erforderlich ist. Die folgenden Anweisungen aktivieren die Snapshot-Isolation und ersetzen das READ COMMITTED-Standardverhalten durch SNAPSHOT:

ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON

Das Festlegen der READ_COMMITTED_SNAPSHOT ON-Option ermöglicht Zugriff auf versionierte Zeilen mit dem READ COMMITTED-Isolationsgrad. Wenn die READ_COMMITTED_SNAPSHOT-Option auf OFF festgelegt ist, müssen Sie den Snapshot-Isolationsgrad für jede Sitzung explizit festlegen, um auf versionierte Zeilen zuzugreifen.

Verwalten von Parallelität mit Isolationsgraden

Der Isolationsgrad, mit dem eine Transact-SQL-Anweisung ausgeführt wird, bestimmt das entsprechende Verhalten bei der Sperr- und Zeilenversionserstellung. Ein Isolationsgrad gilt für die gesamte Verbindung, und sobald er mit der SET TRANSACTION ISOLATION LEVEL-Anweisung für eine Verbindung festgelegt wurde, bleibt er aktiv, bis die Verbindung geschlossen oder ein anderer Isolationsgrad festgelegt wird. Wenn eine Verbindung geschlossen und an den Pool zurückgegeben wird, wird die Isolationsstufe aus der letzten SET TRANSACTION ISOLATION LEVEL-Anweisung beibehalten. Nachfolgende Verbindungen, die eine an den Pool zurückgegebene Verbindung erneut verwenden, verwenden die Isolationsstufe, die zu dem Zeitpunkt gültig war, als die Verbindung an den Pool zurückgegeben wurde.

Einzelne innerhalb einer Verbindung durchgeführte Abfragen können Sperrhinweise enthalten, die die Isolation für eine einzelne Anweisung oder Transaktion ändern, aber die Isolationsstufe der Verbindung nicht beeinflussen. Isolationsgrade oder Sperrhinweise, die in gespeicherten Prozeduren oder Funktionen festgelegt sind, ändern den Isolationsgrad der aufrufenden Verbindung nicht. Sie sind nur für die Dauer der gespeicherten Prozedur oder des Funktionsaufrufs aktiv.

Im SQL-92-Standard sind vier Isolationsgrade enthalten, die in vorherigen Versionen von SQL Server unterstützt wurden:

READ UNCOMMITTED ist der am wenigsten restriktive Isolationsgrad, da er von anderen Transaktionen platzierte Sperren ignoriert. Mit READ UNCOMMITTED ausgeführte Transaktionen können geänderte Datenwerte lesen, die noch nicht von anderen Transaktionen gespeichert wurden; diese werden "unsaubere" Lesevorgänge genannt.
READ COMMITTED ist der Standardisolationsgrad für SQL Server. Er verhindert "unsaubere" Lesevorgänge, indem festgelegt wird, dass Anweisungen keine Datenwerte lesen können, die geändert, aber noch nicht von anderen Transaktionen gespeichert wurden. Andere Transaktionen können immer noch Daten zwischen Ausführungen einzelner Anweisungen innerhalb der aktuellen Transaktion ändern, einfügen oder löschen, was zu nicht wiederholbaren Lesevorgängen oder "Phantomdaten" führt.
REPEATABLE READ ist eine restriktiverer Isolationsgrad als READ COMMITTED. Er umfasst READ COMMITTED und gibt zusätzlich an, dass keine andere Transaktion Daten ändern oder löschen kann, die von der aktuellen Transaktion gelesen wurden, bis die aktuelle Transaktion einen Commit durchführt. Die Parallelität ist geringer als bei READ COMMITTED, da gemeinsam verwendete Sperren für gelesene Daten für die Dauer der Transaktion beibehalten und nicht am Ende jeder Anweisung zurückgegeben werden.
SERIALIZABLE ist der restriktivste Isolationsgrad, da er bis zum Abschluss der Transaktion vollständige Schlüsselbereiche sperrt und die Sperren beibehält. Er umfasst REPEATABLE READ und fügt die Einschränkung hinzu, dass andere Transaktionen bis zum Abschluss der Transaktion keine neuen Zeilen in Bereiche einfügen können, die von der Transaktion gelesen wurden.

Weitere Informationen finden Sie in der Onlinedokumentation zu SQL Server unter "Isolation Levels".

Snapshot-Isolationsgraderweiterungen

SQL Server 2005 stellt Erweiterungen für die SQL-92-Isolationsgrade bereit, zu denen der SNAPSHOT-Isolationsgrad und eine zusätzliche Implementierung von READ COMMITTED gehört. Der neue READ_COMMITTED_SNAPSHOT-Isolationsgrad kann auf transparente Weise READ COMMITTED für alle Transaktionen ersetzen.

SNAPSHOT-Isolation gibt an, dass innerhalb einer Transaktion gelesene Daten niemals Änderungen widerspiegeln, die von anderen gleichzeitigen Transaktionen durchgeführt wurden. Die Transaktion verwendet die Datenzeilenversionen, die zu Beginn der Transaktion vorhanden sind. Beim Lesen der Daten werden keine Sperren erstellt, deshalb blockieren SNAPSHOT-Transaktionen das Schreiben von Daten durch andere Transaktionen nicht. Transaktionen, die Daten schreiben, blockieren das Lesen von Daten durch andere Transaktionen nicht. Um die Snapshot-Isolation verwenden zu können, müssen Sie die ALLOW_SNAPSHOT_ISOLATION-Datenbankoption aktivieren.
Die READ_COMMITTED_SNAPSHOT-Datenbankoption bestimmt das Verhalten des READ COMMITTED-Standardisolationsgrads, wenn Snapshot-Isolation in einer Datenbank aktiviert ist. Wenn Sie READ_COMMITTED_SNAPSHOT ON nicht explizit angeben, wird READ COMMITTED für alle impliziten Transaktionen angewendet. Dies führt zum gleichen Verhalten wie beim Festlegen von READ_COMMITTED_SNAPSHOT auf OFF (Standardeinstellung). Wenn READ_COMMITTED_SNAPSHOT auf OFF festgelegt ist, verwendet das Datenbankmodul gemeinsame Sperren, um den Standardisolationsgrad zu erzwingen. Wenn Sie die READ_COMMITTED_SNAPSHOT-Datenbankoption auf ON festlegen, verwendet das Datenbankmodul Zeilenversionserstellung und die Snapshot-Isolation als Standard, anstatt Sperren zum Schutz der Daten zu verwenden.

Funktionsweise der Snapshot-Isolation und der Zeilenversionserstellung

Wenn der SNAPSHOT-Isolationsgrad aktiviert ist, speichert das SQL Server-Datenbankmodul bei jedem Aktualisieren einer Zeile eine Kopie der Ursprungszeile in tempdb und fügt der Zeile eine Transaktionsfolgenummer hinzu. Nachfolgend ist die Reihenfolge der Ereignisse angegeben:

Eine neue Transaktion wird initiiert, und ihr wird eine Transaktionsfolgenummer zugewiesen.
Das Datenbankmodul liest eine Zeile innerhalb der Transaktion und ruft die Zeilenversion von tempdb ab, deren Nummer kleiner ist als die Transaktionsfolgenummer und gleichzeitig am nächsten bei dieser liegt.
Das Datenbankmodul prüft, ob die Transaktionsfolgenummer in der Liste von Transaktionsfolgenummern der nicht übernommenen Transaktionen vorhanden ist, die beim Start der Snapshot-Transaktion aktiv waren.
Die Transaktion liest in tempdb die Version der Zeile, die beim Start der Transaktion aktuell war. Nach dem Start der Transaktion kann diese keine neu eingefügten Zeilen erfassen, da diese Folgenummernwerte höher sind als der Wert der Transaktionsfolgenummer.
Die aktuelle Transaktion erfasst Zeilen, die nach dem Start der Transaktion gelöscht wurden, weil in tempdb eine Zeilenversion mit niedrigerem Folgenummernwert vorhanden ist.

Das Ergebnis der Snapshot-Isolation besteht darin, dass die Transaktion alle Daten so erfasst, wie sie zum Transaktionsstart vorhanden waren, ohne für die zugrunde liegenden Tabellen Sperren umzusetzen oder zu platzieren. Dies kann in Situationen mit Konflikten zu einer Leistungssteigerung führen.

Eine Snapshot-Transaktion verwendet immer vollständige Parallelitätssteuerung, wobei alle Sperren zurückgehalten werden, die das Aktualisieren von Zeilen durch andere Transaktionen verhindern. Wenn eine Snapshot-Transaktion versucht, ein Update für eine Zeile zu übernehmen, die nach dem Beginn der Transaktion geändert wurde, wird die Transaktion zurückgenommen und ein Fehler ausgelöst.

Verwenden der Snapshot-Isolation in ADO.NET

Die Snapshot-Isolation wird in ADO.NET durch die SqlTransaction-Klasse unterstützt. Wenn eine Datenbank für die Snapshot-Isolation aktiviert, aber nicht für READ_COMMITTED_SNAPSHOT ON konfiguriert wurde, müssen Sie eine SqlTransaction mit dem IsolationLevel.Snapshot-Einumerationswert initiieren, wenn Sie die BeginTransaction-Methode aufrufen. Für dieses Codefragment wird angenommen, dass es sich bei der Verbindung um ein offenes SqlConnection-Objekt handelt.

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

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

Beispiel

Im folgenden Beispiel wird gezeigt, wie sich die verschiedenen Isolationsgrade beim Zugriff auf gesperrte Daten verhalten. Dieses Beispiel ist nicht zur Verwendung in Produktionscode bestimmt.

Der Code stellt eine Verbindung mit der AdventureWorks-Beispieldatenbank in SQL Server her, erstellt die Tabelle TestSnapshot und fügt eine Datenzeile ein. Der Code verwendet die ALTER DATABASE Transact-SQL-Anweisung zum Aktivieren der Snapshot-Isolation für die Datenbank, legt aber nicht die READ_COMMITTED_SNAPSHOT-Option fest, wodurch das READ COMMITTED-Isolationsgradverhalten bestehen bleibt. Der Code führt dann die folgenden Aktionen aus:

Er startet sqlTransaction1, die den SERIALIZABLE-Isolationsgrad verwendet, um eine Updatetransaktion zu starten. sqlTransaction1 wird allerdings nicht fertig gestellt. Dadurch wird die Tabelle gesperrt.
Der Code öffnet eine zweite Verbindung und initiiert eine zweite Transaktion mit dem SNAPSHOT-Isolationsgrad zum Lesen der Daten in der TestSnapshot-Tabelle. Da die Snapshot-Isolation aktiviert ist, kann diese Transaktion die Daten lesen, die vor dem Start von sqlTransaction1 vorhanden waren.
Der Code öffnet eine dritte Verbindung und initiiert eine Transaktion mit dem READ COMMITED-Isolationsgrad, um die Daten in der Tabelle zu lesen. In diesem Fall kann der Code die Daten nicht lesen, weil er nicht über die Sperren hinweg lesen kann, die in der ersten Transaktion für die Tabelle platziert wurden, und löst eine Zeitüberschreitung aus. Das gleiche Ergebnis tritt auch bei den Isolationsgraden REPEATABLE READ und SERIALIZABLE auf, weil diese Isolationsebenen ebenfalls nicht über die in der ersten Transaktion platzierten Sperren hinweg lesen können.
Der Code öffnet eine vierte Verbindung und initiiert mit dem READ UNCOMMITTED-Isolationsgrad eine Transaktion, die einen "unsauberen" Lesevorgang für den nicht übernommenen Wert in sqlTransaction1 durchführt. Dieser Wert ist möglicherweise tatsächlich nie in der Datenbank vorhanden, wenn die erste Transaktion nicht übernommen wird.
Der Code nimmt die erste Transaktion zurück und führt eine Bereinigung durch, indem er die TestSnapshot-Tabelle löscht und die Snapshot-Isolation für die AdventureWorks-Datenbank deaktiviert.

Hinweis
In den folgenden Beispielen wird dieselbe Verbindungszeichenfolge verwendet, wobei aber das Verbindungspooling deaktiviert ist.Wenn sich eine Verbindung in einem Pool befindet, führt die Rücksetzung ihrer Isolationsstufe nicht automatisch auch zur Rücksetzung der Isolationsstufe auf dem Server.Nachfolgende Verbindungen, die dieselbe innere Verbindung aus dem Pool verwenden, beginnen daher mit der Isolationsstufe, die für die Verbindung im Pool festgelegt ist.Alternativ zum Deaktivieren des Verbindungspoolings können Sie auch die Isolationsstufe explizit für jede Verbindung festlegen.

In den folgenden Beispielen wird dieselbe Verbindungszeichenfolge verwendet, wobei aber das Verbindungspooling deaktiviert ist.Wenn sich eine Verbindung in einem Pool befindet, führt die Rücksetzung ihrer Isolationsstufe nicht automatisch auch zur Rücksetzung der Isolationsstufe auf dem Server.Nachfolgende Verbindungen, die dieselbe innere Verbindung aus dem Pool verwenden, beginnen daher mit der Isolationsstufe, die für die Verbindung im Pool festgelegt ist.Alternativ zum Deaktivieren des Verbindungspoolings können Sie auch die Isolationsstufe explizit für jede Verbindung festlegen.

' 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"
        Dim reader2 As SqlDataReader = _
            command2.ExecuteReader()
        While reader2.Read()
            Console.WriteLine("Expected 1,1 Actual " _
              & reader2.GetValue(0).ToString() + "," _
              & reader2.GetValue(1).ToString())
        End While
        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
            Dim reader3 As SqlDataReader = command3.ExecuteReader()
            While reader3.Read()
                Console.WriteLine("You should never hit this.")
            End While
            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"
        Dim reader4 As SqlDataReader = _
            command4.ExecuteReader()
        While reader4.Read()
            Console.WriteLine("Expected 1,22 Actual " _
              & reader4.GetValue(0).ToString() _
              & "," + reader4.GetValue(1).ToString())
        End While
        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

// Assumes GetConnectionString returns a valid connection string
// where pooling is turned off by setting Pooling=False;. 
string connectionString = GetConnectionString();
using (SqlConnection connection1 = new SqlConnection(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 SqlConnection(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";
        SqlDataReader reader2 = command2.ExecuteReader();
        while (reader2.Read())
        {
            Console.WriteLine("Expected 1,1 Actual "
                + reader2.GetValue(0).ToString()
                + "," + reader2.GetValue(1).ToString());
        }
        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 SqlConnection(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
        {
            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 SqlConnection(connectionString))
    {
        connection4.Open();
        SqlCommand command4 = connection4.CreateCommand();
        SqlTransaction transaction4 =
            connection4.BeginTransaction(IsolationLevel.ReadUncommitted);
        command4.Transaction = transaction4;
        command4.CommandText =
            "SELECT ID, valueCol FROM TestSnapshot";
        SqlDataReader reader4 = command4.ExecuteReader();
        while (reader4.Read())
        {
            Console.WriteLine("Expected 1,22 Actual "
                + reader4.GetValue(0).ToString()
                + "," + reader4.GetValue(1).ToString());
        }

        transaction4.Commit();
    }

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

// CLEANUP
// Delete the TestSnapshot table and set
// ALLOW_SNAPSHOT_ISOLATION OFF
using (SqlConnection connection5 = new SqlConnection(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!");

Beispiel

Im folgenden Beispiel wird das Verhalten der Snapshot-Isolation beim Ändern von Daten gezeigt. Der Code führt die folgenden Aktionen aus:

Stellt eine Verbindung zur AdventureWorks-Beispieldatenbank her und aktiviert die SNAPSHOT-Isolation.
Erstellt die Tabelle TestSnapshotUpdate und fügt drei Zeilen mit Beispieldaten ein.
Startet sqlTransaction1 mit einer SNAPSHOT-Isolation, stellt diese aber nicht fertig. In der Transaktion werden drei Zeilen mit Daten ausgewählt.
Erstellt eine zweite SqlConnection mit AdventureWorks und erstellt eine zweite Transaktion mit dem READ COMMITTED-Isolationsgrad, die einen Wert in einer der in sqlTransaction1 ausgewählten Zeilen aktualisiert.
Führt einen Commit für sqlTransaction2 durch.
Kehrt zu sqlTransaction1 zurück und versucht, dieselbe Zeile zu aktualisieren, für die sqlTransaction1 bereits einen Commit durchgeführt hat. Der Fehler 3960 wird ausgelöst, und sqlTransaction1 wird automatisch zurückgenommen. Im Konsolenfenster werden die SqlException.Number und die SqlException.Message angezeigt.
Führt Bereinigungscode aus, um die Snapshot-Isolation in AdventureWorks zu deaktivieren und die TestSnapshotUpdate-Tabelle zu löschen.

' 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

// Assumes GetConnectionString returns a valid connection string
// where pooling is turned off by setting Pooling=False;. 
string connectionString = GetConnectionString();
using (SqlConnection connection1 = new SqlConnection(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: {0}", 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: {0}", 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 = null;
    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 SqlConnection(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("  {0}: {1}", ex.Number, ex.Message);
    }
    finally
    {
        transaction1.Dispose();
    }
}

// CLEANUP:
// Turn off Snapshot isolation and delete the table
using (SqlConnection connection3 = new SqlConnection(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: {0}", ex.Message);
    }
    command3.CommandText = "DROP TABLE TestSnapshotUpdate";
    try
    {
        command3.ExecuteNonQuery();
        Console.WriteLine("CLEANUP: TestSnapshotUpdate table deleted.");
    }
    catch (Exception ex)
    {
        Console.WriteLine("CLEANUP FAILED: {0}", ex.Message);
    }
}

Verwenden von Sperrhinweisen mit der Snapshot-Isolation

Im vorigen Beispiel wählt die erste Transaktion Daten aus, und eine zweite Transaktion aktualisiert die Daten, bevor die erste Transaktion beendet werden kann, wodurch ein Updatekonflikt entsteht, wenn die erste Transaktion versucht, dieselbe Zeile zu aktualisieren. Sie können die Gefahr von Updatekonflikten in Snapshot-Transaktionen mit langen Laufzeiten reduzieren, indem Sie zu Beginn der Transaktionen Sperrhinweise bereitstellen. In der folgenden SELECT-Anweisung wird der UPDLOCK-Hinweis zum Sperren der ausgewählten Zeilen verwendet:

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

Mit dem UPDLOCK-Sperrhinweis werden alle Zeilen blockiert, die versuchen, die Zeilen vor dem Fertigstellen der ersten Transaktion zu aktualisieren. Dadurch wird gewährleistet, dass die ausgewählten Zeilen fehlerfrei sind, wenn sie später in der Transaktion aktualisiert werden. Weitere Informationen finden Sie in der Onlinedokumentation zu SQL Server unter "Locking Hints".

Wenn eine Anwendung viele Konflikte aufweist, stellt die Snapshot-Isolation möglicherweise nicht die optimale Vorgehensweise dar. Hinweise sollten nur verwendet werden, wenn sie wirklich erforderlich sind. Eine Anwendung sollte nicht so erstellt werden, dass ihre Ausführung stets von Sperrhinweisen abhängt.

Siehe auch

Weitere Ressourcen

SQL Server und ADO.NET

Freigeben über

Snapshotisolation in SQL Server (ADO.NET)

Informationen zur Snapshot-Isolation und Zeilenversionserstellung

Verwalten von Parallelität mit Isolationsgraden

Snapshot-Isolationsgraderweiterungen

Funktionsweise der Snapshot-Isolation und der Zeilenversionserstellung

Verwenden der Snapshot-Isolation in ADO.NET

Beispiel

Beispiel

Verwenden von Sperrhinweisen mit der Snapshot-Isolation

Siehe auch

Weitere Ressourcen

Zusätzliche Ressourcen