Momentaufnahmenisolation in SQL Server
Die Momentaufnahmenisolation verbessert die Parallelität für OLTP-Anwendungen.
Informationen zur Snapshot-Isolation und Zeilenversionserstellung
Sobald die Snapshot-Isolierung aktiviert ist, müssen aktualisierte Zeilenversionen für jede Transaktion beibehalten werden. Vor SQL Server 2019 wurden diese Versionen in tempdb gespeichert. SQL Server 2019 wird das neue Feature Accelerated Database Recovery (ADR) eingeführt, das einen eigenen Satz von Zeilenversionen erfordert. Ab SQL Server 2019 werden Zeilenversionen also wie immer in tempdb beibehalten, wenn ADR nicht aktiviert ist. Wenn ADR aktiviert ist, werden alle Zeilenversionen, die sich auf Momentaufnahme Isolation und ADR beziehen, im Persistent Version Store (PVS) von ADR aufbewahrt, der sich in der Benutzerdatenbank in einer Vom Benutzer angegebenen Dateigruppe befindet. Eine eindeutige Transaktionssequenznummer identifiziert jede Transaktion, wobei diese eindeutigen Nummern für jede Zeilenversion aufgezeichnet werden. Die Transaktion arbeitet mit den neuesten Zeilenversionen, die eine Sequenznummer vor der Sequenznummer der Transaktion haben. Neuere Zeilenversionen, die nach Beginn der Transaktion erstellt wurden, werden von der Transaktion ignoriert.
Der Begriff „Momentaufnahme“ spiegelt die Tatsache wider, dass alle Abfragen in der Transaktion die gleiche Version bzw. Momentaufnahme der Datenbank sehen, und zwar basierend auf dem Zustand der Datenbank zum Zeitpunkt des Transaktionsbeginns. 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. Transaktionen, die Daten lesen, blockieren keine Transaktionen, die Daten schreiben. Dies ist bei der Standardisolationsstufe READ COMMITTED in SQL Server normalerweise der Fall. Dieses nicht blockierende Verhalten verringert auch beträchtlich die Wahrscheinlichkeit für Deadlocks bei komplexen Transaktionen.
Für die Momentaufnahmenisolation wird ein optimistisches Parallelitätsmodell verwendet. Wenn eine Momentaufnahmentransaktion versucht, Änderungen an Daten zu committen, die sich seit Beginn der Transaktion geändert haben, wird die Transaktion rückgängig gemacht und ein Fehler ausgelöst. Sie können dies vermeiden, indem Sie UPDLOCK-Hinweise für SELECT-Anweisungen verwenden, die auf zu ändernde Daten zugreifen. Weitere Informationen finden Sie unter Hints (Transact-SQL).
Die Momentaufnahmenisolation muss durch Festlegen der Datenbankoption ALLOW_SNAPSHOT_ISOLATION ON aktiviert werden, bevor sie in Transaktionen verwendet wird. Dadurch wird der Mechanismus zum Speichern von Zeilenversionen in der temporären Datenbank (tempdb) aktiviert. Sie müssen die Momentaufnahmenisolation in jeder Datenbank aktivieren, die sie mit der Transact-SQL-Anweisung ALTER DATABASE verwendet. In dieser Hinsicht unterscheidet sich die Momentaufnahmenisolation von den herkömmlichen Isolationsstufen READ COMMITTED, REPEATABLE READ, SERIALIZABLE und READ UNCOMMITTED, die keine Konfiguration erfordern. Die folgenden Anweisungen aktivieren die Momentaufnahmenisolation und ersetzen das Standardverhalten von READ COMMITTED durch SNAPSHOT:
ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON
Das Festlegen der Option READ_COMMITTED_SNAPSHOT auf ON ermöglicht den Zugriff auf Zeilen mit Versionsangabe unter der Standardisolationsstufe READ_COMMITTED. Wenn die Option READ_COMMITTED_SNAPSHOT auf OFF festgelegt ist, müssen Sie die Isolationsstufe für Momentaufnahmen für jede Sitzung explizit festlegen, um auf Zeilen mit Versionsangabe zugreifen zu können.
Verwalten von Parallelität mit Isolationsstufen
Die Isolationsstufe, auf der eine Transact-SQL-Anweisung ausgeführt wird, bestimmt ihr Sperr- und Zeilenversionsverwaltungs-Verhalten. Eine Isolationsstufe gilt verbindungsweit. Sobald sie für eine Verbindung mit der Anweisung SET TRANSACTION ISOLATION LEVEL festgelegt wurde, bleibt sie in Kraft, bis die Verbindung geschlossen oder eine andere Isolationsstufe festgelegt wird. Wenn eine Verbindung geschlossen und an den Pool zurückgegeben wird, wird die Isolationsstufe der letzten SET TRANSACTION ISOLATION LEVEL-Anweisung beibehalten. Nachfolgende Verbindungen, die eine Verbindung im Pool wiederverwenden, arbeiten mit der Isolationsstufe, die zum Zeitpunkt des Hinzufügens zum Pool in Kraft war.
Einzelne innerhalb einer Verbindung gestellte Abfragen können Sperrhinweise enthalten, die die Isolation für eine einzelne Anweisung oder Transaktion ändern, aber die Isolationsstufe der Verbindung nicht beeinflussen. In gespeicherten Prozeduren oder Funktionen festgelegte Isolationsstufen oder Sperrhinweise ändern die Isolationsstufe der Verbindung, die sie aufruft, nicht und sind nur für die Dauer des Aufrufs der gespeicherten Prozedur oder Funktion wirksam.
In frühen Versionen von SQL Server wurden vier im Standard SQL-92 definierte Isolationsstufen unterstützt:
READ UNCOMMITTED ist die am wenigsten restriktive Isolationsstufe, da sie Sperren ignoriert, die von anderen Transaktionen aktiviert wurden. Transaktionen, die unter READ UNCOMMITTED ausgeführt werden, können geänderte Datenwerte lesen, die noch nicht von anderen Transaktionen committet wurden. Diese werden als „Dirty Reads“ bezeichnet.
Die Standardisolationsstufe für SQL Server ist READ COMMITTED. Sie verhindert Dirty Reads, indem sie festlegt, dass Anweisungen keine Datenwerte lesen können, die zwar geändert, aber noch nicht von anderen Transaktionen committet wurden. Andere Transaktionen können nach wie vor Daten zwischen der Ausführung 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 restriktivere Isolationsstufe als READ COMMITTED. Sie umfasst READ COMMITTED und legt zusätzlich fest, dass keine anderen Transaktionen Daten, die von der aktuellen Transaktion gelesen wurden, ändern oder löschen können, bis die aktuelle Transaktion committet wurde. Die Parallelität ist geringer als bei READ COMMITTED, da gemeinsame Sperren für gelesene Daten für die Dauer der Transaktion bestehen bleiben, anstatt am Ende jeder Anweisung freigegeben zu werden.
SERIALIZABLE ist die restriktivste Isolationsstufe, da gesamte Schlüsselbereiche gesperrt werden, und die Sperren bis zum Abschluss der Transaktion aufrechterhalten werden. Sie umfasst REPEATABLE READ und fügt die Einschränkung hinzu, dass andere Transaktionen keine neuen Zeilen in Bereiche einfügen können, die von der Transaktion gelesen wurden, bis die Transaktion abgeschlossen ist.
Weitere Informationen finden Sie im Handbuch zu Transaktionssperren und Zeilenversionsverwaltung.
Snapshot-Isolationsstufenerweiterungen
SQL Server hat Erweiterungen für die SQL-92-Isolationsgrade eingeführt, zu denen der SNAPSHOT-Isolationsgrad und eine zusätzliche Implementierung von READ COMMITTED gehört. Der READ_COMMITTED_SNAPSHOT-Isolationsgrad kann auf transparente Weise READ COMMITTED für alle Transaktionen ersetzen.
Die SNAPSHOT-Isolation legt fest, dass Daten, die innerhalb einer Transaktion gelesen werden, niemals Änderungen widerspiegeln, die durch andere gleichzeitige Transaktionen vorgenommen wurden. Die Transaktion verwendet die Datenzeilenversionen, die zu Beginn der Transaktion vorhanden sind. Beim Lesen werden keine Sperren für die Daten aktiviert, sodass SNAPSHOT-Transaktionen andere Transaktionen nicht am Schreiben von Daten hindern. Transaktionen, die Daten schreiben, halten SNAPSHOT-Transaktionen nicht vom Lesen von Daten ab. Sie müssen die Momentaufnahmenisolation durch Festlegen der Datenbankoption ALLOW_SNAPSHOT_ISOLATION aktivieren, um sie verwenden zu können.
Die Datenbankoption READ_COMMITTED_SNAPSHOT bestimmt das Verhalten der Standardisolationsstufe READ COMMITTED, wenn die Momentaufnahmenisolation in einer Datenbank aktiviert ist. Wenn Sie ON für READ_COMMITTED_SNAPSHOT nicht explizit angeben, gilt READ COMMITTED für alle impliziten Transaktionen. Dies führt zum selben Verhalten wie das Festlegen von READ_COMMITTED_SNAPSHOT auf OFF (Standard). Wenn OFF für READ_COMMITTED_SNAPSHOT aktiviert ist, verwendet die Datenbank-Engine gemeinsame Sperren, um die Standardisolationsstufe zu erzwingen. Wenn Sie die Datenbankoption READ_COMMITTED_SNAPSHOT auf ON festlegen, verwendet die Datenbank-Engine standardmäßig die Zeilenversionsverwaltung und Momentaufnahmenisolation, anstatt Sperren zum Schutz der Daten einzusetzen.
Funktionsweise der Snapshot-Isolation und der Zeilenversionserstellung
Wenn die SNAPSHOT-Isolationsstufe aktiviert ist, speichert die SQL Server-Datenbank-Engine bei jedem Aktualisieren einer Zeile eine Kopie der Ursprungszeile in tempdb und fügt der Zeile eine Transaktionsfolgenummer hinzu. Es folgt die Abfolge der eintretenden Ereignisse:
Eine neue Transaktion wird eingeleitet, der eine Transaktionssequenznummer zugewiesen wird.
Die Datenbank-Engine 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.
Die Datenbank-Engine prüft, ob die Transaktionssequenznummer nicht in der Liste der Transaktionssequenznummern der nicht committeten Transaktionen enthalten ist, die beim Start der Momentaufnahmentransaktion aktiv waren.
Die Transaktion liest in tempdb die Version der Zeile, die beim Start der Transaktion aktuell war. Es werden keine neuen Zeilen eingefügt, nachdem die Transaktion gestartet wurde, da diese Sequenznummernwerte höher als der Wert der Transaktionssequenznummer sind.
Die aktuelle Transaktion erfasst Zeilen, die nach dem Start der Transaktion gelöscht wurden, weil in tempdb eine Zeilenversion mit niedrigerem Folgenummernwert vorhanden ist.
Der Nutzeffekt der Momentaufnahmenisolation besteht darin, dass die Transaktion alle Daten so sieht, wie sie zu Beginn der Transaktion vorhanden waren, ohne die zugrunde liegenden Tabellen zu berücksichtigen oder zu sperren. Dies kann in Konfliktsituationen zu Leistungsverbesserungen führen.
Eine Momentaufnahmentransaktion verwendet immer eine optimistische Parallelitätssteuerung, wobei alle Sperren zurückgehalten werden, die andere Transaktionen an der Aktualisierung von Zeilen hindern würden. Wenn eine Momentaufnahmentransaktion versucht, eine Aktualisierung einer Zeile zu committen, die nach Beginn der Transaktion geändert wurde, wird die Transaktion rückgängig gemacht und ein Fehler ausgelöst.
Verwenden der Snapshot-Isolation in ADO.NET
Die Momentaufnahmenisolation wird in ADO.NET von der 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-Enumerationswert initiieren, wenn Sie die BeginTransaction-Methode aufrufen. Dieses Codefragment geht davon aus, dass die Verbindung ein geöffnetes SqlConnection-Objekt ist.
Dim sqlTran As SqlTransaction = _
connection.BeginTransaction(IsolationLevel.Snapshot)
SqlTransaction sqlTran =
connection.BeginTransaction(IsolationLevel.Snapshot);
Beispiel
Das folgende Beispiel veranschaulicht das Verhalten der verschiedenen Isolationsstufen beim Versuch, auf gesperrte Daten zuzugreifen. Es ist nicht für die Verwendung im Produktionscode vorgesehen.
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 Transact-SQL-Anweisung ALTER DATABASE, um die Momentaufnahmenisolation für die Datenbank zu aktivieren. Die Option READ_COMMITTED_SNAPSHOT wird jedoch nicht festgelegt, sodass das Standardverhalten der Isolationsstufe READ_COMMITTED weiterhin gilt. Der Code führt dann die folgenden Aktionen aus:
Er beginnt sqlTransaction1, ohne sie jedoch abzuschließen, die die Isolationsstufe SERIALIZABLE verwendet, um eine Aktualisierungstransaktion zu starten. Dies hat Auswirkungen auf das Sperren der Tabelle.
Der Code öffnet eine zweite Verbindung und initiiert eine zweite Transaktion mit der SNAPSHOT-Isolationsstufe zum Lesen der Daten in der TestSnapshot-Tabelle. Da die Momentaufnahmenisolation aktiviert ist, kann diese Transaktion die Daten lesen, die vor dem Start von sqlTransaction1 vorhanden waren.
Der Code öffnet eine dritte Verbindung und leitet eine Transaktion mit der Isolationsstufe READ COMMITTED ein, um zu versuchen, die Daten in der Tabelle zu lesen. In diesem Fall kann der Code die Daten nicht lesen, weil er nicht über die Sperren hinaus lesen kann, die in der ersten Transaktion für die Tabelle festgelegt wurden, und es tritt ein Timeout auf. Dasselbe Ergebnis würde erzielt, wenn die Isolationsstufen REPEATABLE READ und SERIALIZABLE verwendet würden, weil diese Isolationsstufen ebenfalls nicht über die in der ersten Transaktion festgelegten Sperren hinaus lesen können.
Der Code öffnet eine vierte Verbindung und leitet eine Transaktion mit der Isolationsstufe READ UNCOMMITTED ein, die einen Dirty Read des nicht committeten Werts in sqlTransaction1 durchführt. Dieser Wert ist möglicherweise nie wirklich in der Datenbank vorhanden, wenn die erste Transaktion nicht committet 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
Die folgenden Beispiele verwenden dieselbe Verbindungszeichenfolge bei deaktiviertem Verbindungspooling. Wenn eine Verbindung in einem Pool vorhanden ist, wird durch das Zurücksetzen der Isolationsstufe die Isolationsstufe auf dem Server nicht zurückgesetzt. Infolgedessen beginnen nachfolgende Verbindungen, die dieselbe innere Poolverbindung verwenden, mit der Isolationsstufe, die auf die der Poolverbindung festgelegt ist. Eine Alternative zum Deaktivieren des Verbindungspoolings besteht darin, die Isolationsstufe für jede Verbindung explizit festzulegen.
// 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";
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
{
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";
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"
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
Beispiel
Das folgende Beispiel veranschaulicht das Verhalten der Momentaufnahmenisolation, wenn Daten geändert werden. Der Code führt die folgenden Aktionen aus:
Herstellen einer Verbindung mit der AdventureWorks-Beispieldatenbank und Aktivieren der SNAPSHOT-Isolation.
Erstellen der Tabelle TestSnapshotUpdate und Einfügen von drei Zeilen mit Beispieldaten.
sqlTransaction1 wird mit SNAPSHOT-Isolation gestartet, aber nicht abgeschlossen. In der Transaktion sind drei Datenzeilen ausgewählt.
Erstellt eine zweite SqlConnection mit AdventureWorks und erstellt eine zweite Transaktion mit der READ COMMITTED-Isolationsstufe, die einen Wert in einer der in sqlTransaction1 ausgewählten Zeilen aktualisiert.
Committet sqlTransaction2.
Kehrt zu sqlTransaction1 zurück und versucht, dieselbe Zeile zu aktualisieren, für die sqlTransaction1 bereits committet wurde. Fehler 3960 wird ausgelöst, und für sqlTransaction1 wird automatisch ein Rollback ausgeführt. 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;.
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: {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 = 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(" {0}: {1}", 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: {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);
}
}
' 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
Verwenden von Sperrhinweisen mit der Snapshot-Isolation
Im vorherigen Beispiel wählt die erste Transaktion Daten aus. Eine zweite Transaktion aktualisiert die Daten, bevor die erste Transaktion abgeschlossen werden kann. Dadurch entsteht ein Aktualisierungskonflikt, wenn die erste Transaktion versucht, dieselbe Zeile zu aktualisieren. Sie können die Wahrscheinlichkeit von Aktualisierungskonflikten bei lang laufenden Momentaufnahmentransaktionen verringern, indem Sie zu Beginn der Transaktion Sperrhinweise angeben. Die folgende SELECT-Anweisung verwendet den UPDLOCK-Hinweis, um die ausgewählten Zeilen zu sperren:
SELECT * FROM TestSnapshotUpdate WITH (UPDLOCK)
WHERE PriKey BETWEEN 1 AND 3
Die Verwendung des UPDLOCK-Sperrhinweises blockiert alle Zeilen, die versuchen, die Zeilen zu aktualisieren, bevor die erste Transaktion abgeschlossen ist. Dies garantiert, dass die ausgewählten Zeilen keine Konflikte aufweisen, wenn sie später in der Transaktion aktualisiert werden. Weitere Informationen finden Sie unter Hints (Transact-SQL).
Wenn Ihre Anwendung viele Konflikte aufweist, ist die Momentaufnahmenisolation möglicherweise nicht die beste Wahl. Hinweise sollten nur verwendet werden, wenn unbedingt nötig. Ihre Anwendung sollte nicht so konzipiert sein, dass ihr Betrieb ständig auf Sperrhinweise angewiesen ist.