Uwaga
Dostęp do tej strony wymaga autoryzacji. Może spróbować zalogować się lub zmienić katalogi.
Dostęp do tej strony wymaga autoryzacji. Możesz spróbować zmienić katalogi.
Izolacja migawek zwiększa współbieżność aplikacji OLTP.
Opis izolacji migawek i przechowywania wersji wierszy
Po włączeniu izolacji migawki należy zachować zaktualizowane wersje wierszy dla każdej transakcji. Przed programem SQL Server 2019 te wersje były przechowywane w bazie danych tempdb. Program SQL Server 2019 wprowadza nową funkcję przyspieszonego odzyskiwania bazy danych (ADR), która wymaga własnego zestawu wersji wierszy. Tak więc, od programu SQL Server 2019, jeśli adr nie jest włączona, wersje wierszy są przechowywane w bazie danych tempdb tak jak zawsze. Jeśli funkcja ADR jest włączona, wszystkie wersje wierszy, zarówno związane z izolacją migawki, jak i ADR, są przechowywane w Magazynie Trwałych Wersji ADR (PVS), który znajduje się w grupie plików w bazie danych użytkownika, określonej przez użytkownika. Unikatowy numer sekwencji transakcji identyfikuje każdą transakcję, a te unikatowe liczby są rejestrowane dla każdej wersji wiersza. Transakcja współpracuje z najnowszymi wersjami wierszy, które mają numer sekwencji przed numerem sekwencji transakcji. Nowsze wersje wierszy utworzone po rozpoczęciu transakcji są ignorowane przez transakcję.
Termin "snapshot" odzwierciedla fakt, że wszystkie zapytania w transakcji widzą tę samą wersję lub migawkę bazy danych na podstawie stanu bazy danych w momencie rozpoczęcia transakcji. W transakcji typu migawka nie są pobierane blokady na podstawowych wierszach danych ani stronach danych, co pozwala na wykonywanie innych transakcji bez blokowania ich przez wcześniejszą, nieukończoną transakcję. Transakcje modyfikujące dane nie blokują transakcji odczytujących dane, a transakcje odczytujące dane nie blokują transakcji zapisujących dane, tak jak zwykle w domyślnym poziomie izolacji READ COMMITTED w programie SQL Server. Takie nieblokujące zachowanie znacznie zmniejsza prawdopodobieństwo wystąpienia zakleszczeń w złożonych transakcjach.
Izolacja migawek używa optymistycznego modelu współbieżności. Jeśli transakcja migawki próbuje zatwierdzić modyfikacje danych, które zostały zmienione od momentu rozpoczęcia transakcji, transakcja zostanie cofnięta i zostanie zgłoszony błąd. Można tego uniknąć, używając wskazówek UPDLOCK dla instrukcji SELECT, które uzyskują dostęp do danych, które mają być modyfikowane. Aby uzyskać więcej informacji, zobacz Wskazówki (Transact-SQL).
Izolacja migawki musi być włączona przez ustawienie opcji ALLOW_SNAPSHOT_ISOLATION W bazie danych przed użyciem jej w transakcjach. Spowoduje to aktywowanie mechanizmu przechowywania wersji wierszy w tymczasowej bazie danych (tempdb). Należy włączyć izolację migawek w każdej bazie danych, która z niej korzysta, za pomocą instrukcji Transact-SQL ALTER DATABASE. W tym kontekście izolacja migawkowa różni się od tradycyjnych poziomów izolacji, takich jak READ COMMITTED, REPEATABLE READ, SERIALIZABLE i READ UNCOMMITTED, które nie wymagają konfiguracji. Następujące instrukcje aktywują izolację migawki i zastąp domyślne zachowanie READ COMMITTED migawką migawką:
ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON
Ustawienie opcji READ_COMMITTED_SNAPSHOT na ON umożliwia dostęp do wersjonowanych wierszy w domyślnym poziomie izolacji READ COMMITTED. Jeśli opcja READ_COMMITTED_SNAPSHOT jest ustawiona na WYŁ., należy jawnie ustawić poziom izolacji migawki dla każdej sesji, aby uzyskać dostęp do wierszy w wersji.
Zarządzanie współbieżnością przy użyciu poziomów izolacji
Poziom izolacji, na którym jest wykonywana instrukcja Transact-SQL, określa zachowanie blokowania i wersjonowania wierszy. Poziom izolacji ma zakres całego połączenia, a po ustawieniu połączenia z instrukcją SET TRANSACTION ISOLATION LEVEL pozostaje w mocy, dopóki połączenie nie zostanie zamknięte lub zostanie ustawiony inny poziom izolacji. Po zamknięciu połączenia i powrocie do puli, poziom izolacji z ostatniej instrukcji SET TRANSACTION ISOLATION LEVEL zostaje zachowany. Kolejne połączenia ponownie korzystające z połączenia w puli używają poziomu izolacji, który obowiązywał w momencie połączenia w puli.
Poszczególne zapytania wystawione w ramach połączenia mogą zawierać wskazówki dotyczące blokady, które modyfikują izolację pojedynczej instrukcji lub transakcji, ale nie mają wpływu na poziom izolacji połączenia. Poziomy izolacji lub wskazówki dotyczące blokady ustawione w procedurach składowanych lub funkcjach nie zmieniają poziomu izolacji połączenia, które je wywołuje i obowiązują tylko przez czas trwania procedury składowanej lub wywołania funkcji.
Cztery poziomy izolacji zdefiniowane w standardzie SQL-92 były obsługiwane we wczesnych wersjach programu SQL Server:
ODCZYT UNCOMMITTED jest najmniej restrykcyjnym poziomem izolacji, ponieważ ignoruje blokady umieszczone przez inne transakcje. Transakcje wykonywane w obszarze READ UNCOMMITTED mogą odczytywać zmodyfikowane wartości danych, które nie zostały jeszcze zatwierdzone przez inne transakcje; są one nazywane "brudnymi" odczytami.
READ COMMITTED to domyślny poziom izolacji dla programu SQL Server. Zapobiega to zanieczyszczonym odczytom, określając, że instrukcje nie mogą odczytywać wartości danych, które zostały zmodyfikowane, ale nie zostały jeszcze zatwierdzone przez inne transakcje. Inne transakcje mogą nadal modyfikować, wstawiać lub usuwać dane między wykonaniami poszczególnych instrukcji w ramach bieżącej transakcji, co skutkuje niepowtarzalnymi odczytami lub danymi fantomowymi.
ODCZYT POWTARZALNY jest bardziej restrykcyjnym poziomem izolacji niż ODCZYT ZATWIERDZONY. Obejmuje on wartość READ COMMITTED i dodatkowo określa, że żadne inne transakcje nie mogą modyfikować ani usuwać danych odczytanych przez bieżącą transakcję do momentu zatwierdzenia tej transakcji. Współbieżność jest niższa niż w przypadku READ COMMITTED, ponieważ udostępnione blokady odczytu danych są utrzymywane przez cały okres trwania transakcji, zamiast być zwalniane po zakończeniu każdej instrukcji.
SERIALIZABLE jest najbardziej restrykcyjnym poziomem izolacji, ponieważ blokuje cały zakres kluczy i przechowuje blokady do momentu zakończenia transakcji. Obejmuje on powtarzalny odczyt i dodaje ograniczenie, że inne transakcje nie mogą wstawiać nowych wierszy do zakresów, które zostały odczytane przez transakcję do momentu zakończenia transakcji.
Aby uzyskać więcej informacji, zapoznaj się z Transaction Locking and Row Versioning Guide.
Rozszerzenia poziomu izolacji zrzutów ekranowych
Program SQL Server wprowadził rozszerzenia do poziomów izolacji SQL-92 wraz z wprowadzeniem poziomu izolacji migawki i dodatkową implementacją funkcji READ COMMITTED. Poziom izolacji READ_COMMITTED_SNAPSHOT może w przezroczysty sposób zastąpić poziom READ COMMITTED dla wszystkich transakcji.
Izolacja migawki określa, że dane odczytywane w ramach transakcji nigdy nie będą odzwierciedlać zmian wprowadzonych przez inne jednoczesne transakcje. Transakcja używa wersji wierszy danych, które istnieją po rozpoczęciu transakcji. Podczas odczytu danych nie są stosowane blokady, więc transakcje typu SNAPSHOT nie blokują innych transakcji przed zapisem danych. Transakcje zapisujące dane nie blokują transakcji migawkowych podczas odczytywania danych. Aby użyć izolacji migawki, należy ustawić opcję bazy danych ALLOW_SNAPSHOT_ISOLATION.
Opcja READ_COMMITTED_SNAPSHOT bazy danych określa zachowanie domyślnego poziomu izolacji READ COMMITTED, gdy izolacja migawki jest włączona w bazie danych. Jeśli nie określisz jawnie READ_COMMITTED_SNAPSHOT ON, funkcja READ COMMITTED zostanie zastosowana do wszystkich niejawnych transakcji. Powoduje to takie samo zachowanie, jak ustawienie READ_COMMITTED_SNAPSHOT WYŁĄCZONE (ustawienie domyślne). Gdy READ_COMMITTED_SNAPSHOT jest wyłączone, aparat bazy danych używa blokad współdzielonych w celu wymuszenia domyślnego poziomu izolacji. Jeśli ustawisz opcję bazy danych READ_COMMITTED_SNAPSHOT na WŁĄCZONY, aparat bazy danych używa wersjonowania wierszy i izolacji migawki jako domyślną, zamiast używać blokad do ochrony danych.
Jak działa izolacja migawki i przechowywanie wersji wierszy
Po włączeniu poziomu izolacji SNAPSHOT, za każdym razem, gdy wiersz jest aktualizowany, silnik bazy danych SQL Server przechowuje kopię oryginalnego wiersza w tempdbi dodaje numer sekwencji transakcji do wiersza. Poniżej przedstawiono sekwencję zdarzeń, które występują:
Zainicjowano nową transakcję i przypisano jej numer sekwencji transakcji.
Silnik bazy danych odczytuje wiersz w ramach transakcji i pobiera wersję wiersza z tempdb, którego numer sekwencji jest najbliższy i mniejszy niż numer sekwencji transakcji.
Silnik bazy danych sprawdza, czy numer sekwencji transakcji nie znajduje się na liście numerów sekwencji transakcji niezatwierdzonych transakcji aktywnych, kiedy transakcja migawkowa się rozpoczęła.
Transakcja odczytuje wersję wiersza z tempdb, która była bieżąca od początku transakcji. Po rozpoczęciu transakcji nie będą widoczne nowe wiersze, ponieważ te wartości numeru sekwencji będą wyższe niż wartość numeru sekwencji transakcji.
Bieżąca transakcja zobaczy wiersze, które zostały usunięte po jej rozpoczęciu, ponieważ w tempdb będzie dostępna wersja wiersza o niższej wartości numeru sekwencji.
Efektem izolacji migawki jest to, że transakcja widzi wszystkie dane tak, jak istniały na początku transakcji, bez uznawania lub nakładania blokad na tabele bazowe. Może to spowodować poprawę wydajności w sytuacjach, w których występuje rywalizacja.
Transakcja migawki zawsze używa optymistycznej kontroli współbieżności, wstrzymując wszelkie zablokowania, które uniemożliwiłyby innym transakcjom aktualizowanie wierszy. Jeśli transakcja migawki próbuje zatwierdzić aktualizację wiersza, który został zmieniony po rozpoczęciu transakcji, transakcja zostanie wycofana i zostanie zgłoszony błąd.
Praca z izolacją migawki w ADO.NET
Izolacja migawki jest obsługiwana w ADO.NET przez klasę SqlTransaction. Jeśli baza danych została włączona dla migawkowej izolacji, ale nie jest skonfigurowana dla READ_COMMITTED_SNAPSHOT ON, należy zainicjować SqlTransaction używając wartości wyliczeniowej IsolationLevel.Snapshot podczas wywoływania metody BeginTransaction. Ten fragment kodu zakłada, że połączenie jest otwartym obiektem SqlConnection.
Dim sqlTran As SqlTransaction = _
connection.BeginTransaction(IsolationLevel.Snapshot)
SqlTransaction sqlTran =
connection.BeginTransaction(IsolationLevel.Snapshot);
Przykład
W poniższym przykładzie pokazano, jak zachowują się różne poziomy izolacji, próbując uzyskać dostęp do zablokowanych danych i nie mają być używane w kodzie produkcyjnym.
Kod łączy się z przykładową bazą danych AdventureWorks w programie SQL Server i tworzy tabelę o nazwie TestSnapshot i wstawia jeden wiersz danych. Kod używa instrukcji ALTER DATABASE Transact-SQL, aby włączyć izolację migawki dla bazy danych, ale nie ustawia opcji READ_COMMITTED_SNAPSHOT, pozostawiając domyślne zachowanie poziomu izolacji READ COMMITTED. Następnie kod wykonuje następujące akcje:
Rozpoczyna się, ale nie kończy, sqlTransaction1, który używa poziomu izolacji SERIALIZABLE do rozpoczęcia transakcji aktualizacji. Powoduje to zablokowanie tabeli.
Spowoduje to otwarcie drugiego połączenia i zainicjowanie drugiej transakcji przy użyciu poziomu izolacji migawki w celu odczytania danych w tabeli TestSnapshot. Ponieważ włączono izolację migawek, ta transakcja może odczytywać dane, które istniały przed uruchomieniem polecenia sqlTransaction1.
Otwiera trzecie połączenie i inicjuje transakcję przy użyciu poziomu izolacji READ COMMITTED, aby spróbować odczytać dane w tabeli. W takim przypadku kod nie może odczytać danych, ponieważ nie może czytać poza blokadami założonymi na tabelę w pierwszej transakcji i dochodzi do przekroczenia czasu oczekiwania. Ten sam wynik wystąpiłby, gdyby użyto poziomów izolacji REPEATABLE READ i SERIALIZABLE, ponieważ te poziomy izolacji również nie mogą czytać poza blokadami założonymi w pierwszej transakcji.
Spowoduje to otwarcie czwartego połączenia i zainicjowanie transakcji, wykorzystując poziom izolacji READ UNCOMMITTED, który wykonuje brudny odczyt niezatwierdzonej wartości w sqlTransaction1. Ta wartość może nigdy nie istnieć w bazie danych, jeśli pierwsza transakcja nie zostanie zatwierdzona.
Przywraca pierwszą transakcję i czyści ją, usuwając tabelę TestSnapshot i wyłączając izolację migawki dla bazy danych AdventureWorks.
Uwaga / Notatka
W poniższych przykładach jest używany ten sam łańcuch połączenia z wyłączonym pulowaniem połączeń. Jeśli połączenie jest w puli, zresetowanie poziomu izolacji nie spowoduje zresetowania poziomu izolacji na serwerze. W rezultacie kolejne połączenia korzystające z tego samego połączonego połączenia wewnętrznego rozpoczynają się od poziomów izolacji ustawionych na poziom połączenia w puli. Alternatywą dla wyłączania buforowania połączeń jest jawne ustawienie poziomu izolacji dla każdego połączenia.
// 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
Przykład
Poniższy przykład demonstruje zachowanie izolacji migawki, gdy dane są modyfikowane. Kod wykonuje następujące akcje:
Łączy się z przykładową bazą danych AdventureWorks i umożliwia izolację migawek.
Tworzy tabelę o nazwie TestSnapshotUpdate i wstawia trzy wiersze przykładowych danych.
Rozpoczyna, ale nie kończy sqlTransaction1 przy użyciu izolacji MIGAWKI. W transakcji wybrano trzy wiersze danych.
Tworzy drugą sqlConnection w celu AdventureWorks i tworzy drugą transakcję przy użyciu poziomu izolacji READ COMMITTED, który aktualizuje wartość w jednym z wierszy wybranych w programie sqlTransaction1.
Zatwierdza sqlTransaction2.
Powraca do stanu sqlTransaction1 i próbuje zaktualizować ten sam wiersz, który został już zatwierdzony przez transakcję sqlTransaction1. Zostanie zgłoszony błąd 3960, a polecenie sqlTransaction1 zostanie wycofane automatycznie. SqlException.Number i SqlException.Message są wyświetlane w oknie konsoli.
Wykonuje kod czyszczenia, aby wyłączyć izolację migawek w AdventureWorks i usunąć tabelę 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
Używanie wskazówek blokady z izolacją migawki
W poprzednim przykładzie pierwsza transakcja wybiera dane, a druga transakcja aktualizuje dane przed ukończeniem pierwszej transakcji, powodując konflikt aktualizacji, gdy pierwsza transakcja próbuje zaktualizować ten sam wiersz. Możesz zmniejszyć prawdopodobieństwo konfliktów aktualizacji w długotrwałych transakcjach migawek, stosując hinty blokady na początku transakcji. Poniższa instrukcja SELECT używa wskazówki UPDLOCK, aby zablokować wybrane wiersze:
SELECT * FROM TestSnapshotUpdate WITH (UPDLOCK)
WHERE PriKey BETWEEN 1 AND 3
Użycie wskazówki blokady UPDLOCK blokuje wszystkie wiersze próbujące dokonać aktualizacji, zanim zostanie ukończona pierwsza transakcja. Gwarantuje to, że wybrane wiersze nie mają konfliktów podczas ich aktualizacji w dalszej części transakcji. Aby uzyskać więcej informacji, zobacz Wskazówki (Transact-SQL).
Jeśli aplikacja ma wiele konfliktów, izolacja migawkowa może nie być najlepszym wyborem. Wskazówki powinny być używane tylko wtedy, gdy są naprawdę potrzebne. Aplikacja nie powinna być zaprojektowana tak, aby stale polegała na wskazówkach dotyczących blokady dla jej działania.