Izolacja migawki w programie SQL Server
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 bazie danych użytkownika w grupie plików, którą określa użytkownik. Unikatowy numer sekwencji transakcji identyfikuje każdą transakcję, a te unikatowe liczby są rejestrowane dla każdej wersji wiersza. Transakcja działa z najnowszymi wersjami wierszy o numerze 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. Żadne blokady nie są pobierane na źródłowych wierszach danych lub na stronach danych w transakcji migawki, co umożliwia wykonywanie innych transakcji bez blokowania wcześniejszej transakcji bez uzupełniania. 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 zachowanie nieblokacyjne znacznie zmniejsza prawdopodobieństwo zakleszczenia złożonych transakcji.
Izolacja migawek używa optymistycznego modelu współbieżności. Jeśli transakcja migawki próbuje zatwierdzić modyfikacje danych, które uległy zmianie od momentu rozpoczęcia transakcji, transakcja zostanie wycofana 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 używa jej z instrukcją Transact-SQL ALTER DATABASE. W związku z tym izolacja migawki różni się od tradycyjnych poziomów izolacji 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 ON umożliwia dostęp do wierszy w wersji na 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 Języka Transact-SQL, określa zachowanie blokowania i przechowywania wersji 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 zostaje zachowany poziom izolacji z ostatniej instrukcji SET TRANSACTION ISOLATION LEVEL. 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 nie powtarzalnymi odczytami lub danymi "phantom".
POWTARZALNY ODCZYT jest bardziej restrykcyjnym poziomem izolacji niż ODCZYT ZATWIERDZONY. Obejmuje on wartość READ COMMIT i dodatkowo określa, że żadne inne transakcje nie mogą modyfikować ani usuwać danych odczytanych przez bieżącą transakcję do momentu zatwierdzenia bieżącej transakcji. Współbieżność jest niższa niż w przypadku zatwierdzenia odczytu, ponieważ udostępnione blokady danych odczytu są przechowywane przez czas trwania transakcji zamiast zwalniania na końcu 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 przewodnikiem dotyczącym blokowania transakcji i przechowywania wersji wierszy.
Rozszerzenia poziomu izolacji migawek
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ć wartość 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. Żadne blokady nie są umieszczane na danych podczas ich odczytu, więc transakcje MIGAWKi nie blokują zapisywania danych przez inne transakcje. Transakcje zapisujące dane nie blokują transakcji migawek podczas odczytywania danych. Należy włączyć izolację migawki, ustawiając opcję ALLOW_SNAPSHOT_ISOLATION bazy danych w celu jej użycia.
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 WŁĄCZONE, 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 wyłączone, aparat bazy danych używa udostępnionych blokad w celu wymuszenia domyślnego poziomu izolacji. Jeśli ustawisz opcję READ_COMMITTED_SNAPSHOT bazy danych na WŁ., aparat bazy danych używa przechowywania wersji wierszy i izolacji migawki jako domyślnej, zamiast używać blokad do ochrony danych.
Jak działa izolacja migawki i przechowywanie wersji wierszy
Po włączeniu poziomu izolacji MIGAWKi za każdym razem, gdy wiersz jest aktualizowany, aparat bazy danych programu SQL Server przechowuje kopię oryginalnego wiersza w bazie danych tempdb i dodaje numer sekwencji transakcji do wiersza. Poniżej przedstawiono sekwencję zdarzeń, które występują:
Zainicjowano nową transakcję i przypisano jej numer sekwencji transakcji.
Aparat bazy danych odczytuje wiersz w ramach transakcji i pobiera wersję wiersza z bazy danych tempdb , której numer sekwencji jest najbliżej i niższy niż numer sekwencji transakcji.
Aparat bazy danych sprawdza, czy numer sekwencji transakcji nie znajduje się na liście numerów sekwencji transakcji niezatwierdzonych transakcji aktywnych po rozpoczęciu transakcji migawki.
Transakcja odczytuje wersję wiersza z bazy danych 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 będzie widzieć wiersze, które zostały usunięte po rozpoczęciu transakcji, ponieważ w bazie danych tempdb będzie dostępna wersja wiersza z niższą wartością numeru sekwencji.
Efektem izolacji migawki jest to, że transakcja widzi wszystkie dane, ponieważ istniały na początku transakcji, bez honorowania ani umieszczania żadnych blokad w tabelach bazowych. Może to spowodować poprawę wydajności w sytuacjach, w których występuje rywalizacja.
Transakcja migawki zawsze używa optymistycznej kontrolki współbieżności, wstrzymując wszelkie blokady, 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 na potrzeby izolacji migawki, ale nie jest skonfigurowana dla READ_COMMITTED_SNAPSHOT WŁĄCZONE, należy zainicjować SqlTransaction przy użyciu wartości wyliczenia IsolationLevel.Snapshot podczas wywoływania BeginTransaction metody. Ten fragment kodu zakłada, że połączenie jest otwartym SqlConnection obiektem.
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 Języka Transact-SQL, aby włączyć izolację migawek dla bazy danych, ale nie ustawia READ_COMMITTED_SNAPSHOT opcji, pozostawiając domyślne zachowanie na poziomie 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. Ma to wpływ na blokowanie tabeli.
Spowoduje to otwarcie drugiego połączenia i zainicjowanie drugiej transakcji przy użyciu poziomu izolacji SNAPSHOT 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 odczytać blokad umieszczonych w tabeli w pierwszej transakcji i przekroczeniu limitu czasu. Ten sam wynik występuje, jeśli użyto poziomów izolacji POWTARZALNY ODCZYT i SERIALIZABLE, ponieważ te poziomy izolacji nie mogą również odczytać przekleńsów umieszczonych w pierwszej transakcji.
Spowoduje to otwarcie czwartego połączenia i zainicjowanie transakcji przy użyciu poziomu izolacji READ UNCOMMITTED, który wykonuje zanieczyszczony odczyt niezatwierdzonej wartości w programie sqlTransaction1. Ta wartość może nigdy nie istnieć w bazie danych, jeśli pierwsza transakcja nie zostanie zatwierdzona.
Spowoduje to wycofanie pierwszej transakcji i wyczyszczenie jej przez usunięcie tabeli TestSnapshot i wyłączenie izolacji migawek dla bazy danych AdventureWorks .
Uwaga
W poniższych przykładach użyto tego samego parametry połączenia z wyłączonym buforowaniem połączeń. Jeśli połączenie jest w puli, zresetowanie poziomu izolacji nie spowoduje zresetowania poziomu izolacji na serwerze. W związku z tym kolejne połączenia korzystające z tego samego połączenia wewnętrznego w puli zaczynają się od ich poziomów izolacji ustawionych na połączenie 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
W poniższym przykładzie pokazano zachowanie izolacji migawki podczas modyfikowania danych. Kod wykonuje następujące akcje:
Połączenie do Przykładowa baza danych AdventureWorks i umożliwia izolację migawek.
Tworzy tabelę o nazwie TestSnapshotUpdate i wstawia trzy wiersze przykładowych danych.
Rozpoczyna się, ale nie kończy, sqlTransaction1 przy użyciu izolacji MIGAWKI. W transakcji wybrano trzy wiersze danych.
Tworzy drugą wartość sql Połączenie ion do bazy danych AdventureWorks i tworzy drugą transakcję przy użyciu poziomu izolacji READ COMMITTED, który aktualizuje wartość w jednym z wierszy wybranych w poleceniu sqlTransaction1.
Zatwierdza sqlTransaction2.
Zwraca wartość sqlTransaction1 i próbuje zaktualizować ten sam wiersz, który został już zatwierdzony przez polecenie sqlTransaction1. Zostanie zgłoszony błąd 3960, a polecenie sqlTransaction1 zostanie wycofane automatycznie. W oknie Konsoli są wyświetlane pozycje SqlException.Number i SqlException.Message .
Wykonuje kod oczyszczania, aby wyłączyć izolację migawek w firmie 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: {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
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, podając wskazówki dotyczące 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 zaktualizować wiersze przed ukończeniem pierwszej transakcji. 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 migawki 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.