Notatka
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 wersją SQL Server 2019 te wersje były przechowywane w tempdb. Program SQL Server 2019 wprowadza nową funkcję przyspieszonego odzyskiwania bazy danych (ADR), która wymaga własnego zestawu wersji wierszy. Tak więc, w SQL Server 2019, jeśli ADR nie jest włączona, wersje wierszy są przechowywane 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 READ COMMITTED domyślnym poziomie izolacji w 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ąć, korzystając z UPDLOCK wskazówek dotyczących 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 migawkowa musi zostać włączona przez ustawienie opcji bazy danych ALLOW_SNAPSHOT_ISOLATION ON, zanim będzie używana w transakcjach. To aktywuje mechanizm przechowywania wersji wierszy w tymczasowej bazie danych (tempdb). Należy włączyć izolację migawki w każdej bazie danych, w której jest używana, za pomocą instrukcji Transact-SQL ALTER DATABASE. Pod tym względem izolacja migawki różni się od tradycyjnych poziomów izolacji READ COMMITTED, REPEATABLE READ, SERIALIZABLE i READ UNCOMMITTED, które nie wymagają żadnej konfiguracji. Następujące instrukcje umożliwiają SNAPSHOT transakcje i odczyty wersjonowane na poziomie wierszy w READ COMMITTED:
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 wersjonowanych wierszy przy domyślnym poziomie izolacji READ COMMITTED. Jeśli opcja READ_COMMITTED_SNAPSHOT jest ustawiona na OFF, należy jawnie ustawić poziom izolacji migawki dla każdej sesji, aby uzyskać dostęp do wersjonowanych wierszy.
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 obowiązuje dla całego połączenia, a po ustawieniu go dla połączenia za pomocą instrukcji SET TRANSACTION ISOLATION LEVEL pozostaje ustawiony do momentu zamknięcia połączenia lub ustawienia innego poziomu izolacji. Po zamknięciu połączenia i zwróceniu go do puli poziom izolacji ustawiony przez ostatnią instrukcję 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:
READ UNCOMMITTEDjest najmniej restrykcyjnym poziomem izolacji, ponieważ ignoruje blokady założone przez inne transakcje. Transakcje wykonywane przyREAD UNCOMMITTEDmogą odczytywać zmodyfikowane wartości danych, które nie zostały jeszcze zatwierdzone przez inne transakcje; są one nazywane „brudnymi” odczytami.READ COMMITTEDjest domyślnym poziomem izolacji dla 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.REPEATABLE READjest bardziej restrykcyjnym poziomem izolacji niżREAD COMMITTED. ObejmujeREAD COMMITTEDi dodatkowo określa, że żadne inne transakcje nie mogą modyfikować ani usuwać danych odczytanych przez bieżącą transakcję do czasu zatwierdzenia bieżącej transakcji. Współbieżność jest niższa niż w przypadkuREAD COMMITTED, ponieważ blokady współdzielone na odczytywanych danych są utrzymywane przez cały czas trwania transakcji, zamiast być zwalniane na końcu każdej instrukcji.Poziom izolacji
SERIALIZABLEjest najbardziej restrykcyjny, ponieważ blokuje całe zakresy kluczy i utrzymuje blokady do momentu zakończenia transakcji. ObejmujeREPEATABLE READi 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
W programie SQL Server wprowadzono rozszerzenia poziomów izolacji SQL-92, wprowadzając poziom izolacji SNAPSHOT oraz dodatkową implementację READ COMMITTED.
SNAPSHOTpoziom izolacji 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ęcSNAPSHOTtransakcje nie blokują zapisywania danych przez inne transakcje. Transakcje zapisujące dane nie blokują transakcji migawkowych podczas odczytywania danych. Należy włączyć izolację migawki, ustawiając opcję bazy danychALLOW_SNAPSHOT_ISOLATION, aby można było z niej korzystać.Opcja bazy danych
READ_COMMITTED_SNAPSHOTokreśla, w jaki sposóbREAD COMMITTEDodczytuje dane. W przypadku tej opcjiOFF, domyślnej w SQL Server, Silnik bazy danych używa udostępnionych blokad w celu ochrony odczytów. W przypadku tej opcjiONREAD COMMITTEDprogram używa wersji wierszy na poziomie instrukcji zamiast udostępnionych blokad, aby chronić operacje odczytu.
Jak działa izolacja migawki i przechowywanie wersji wierszy
Po włączeniu SNAPSHOT poziomu izolacji za każdym razem, gdy wiersz jest aktualizowany, SQL Server Database Engine przechowuje kopię oryginalnego wiersza w pliku 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ę tego wiersza z
tempdb, której numer sekwencyjny jest najbliższy i niższy niż numer sekwencyjny 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 aktualna na 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 rozpoczęciu transakcji, ponieważ w
tempdbbędzie dostępna wersja wiersza z niższą 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
Aby rozpocząć transakcję z izolacją migawki, włącz ALLOW_SNAPSHOT_ISOLATION dla bazy danych i przekaż Snapshot do BeginTransaction. Opcja READ_COMMITTED_SNAPSHOT bazy danych steruje odczytami opartymi na wersjach wierszy na poziomie izolacji Read Committed; nie wybiera izolacji migawki dla transakcji. Poniższy fragment zakłada, że connection jest otwartą instancją klasy 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ą AdventureWorks danych w programie SQL Server i tworzy tabelę o nazwie TestSnapshot i wstawia jeden wiersz danych. Kod używa instrukcji Transact-SQL ALTER DATABASE, aby włączyć izolację migawki dla bazy danych, ale nie ustawia opcji READ_COMMITTED_SNAPSHOT, pozostawiając domyślne zachowanie READ COMMITTED na poziomie izolacji. Następnie kod wykonuje następujące akcje:
Rozpoczyna, ale nie kończy,
sqlTransaction1, który używa poziomu izolacjiSERIALIZABLEdo rozpoczęcia transakcji aktualizacji. Powoduje to zablokowanie tabeli.Spowoduje to otwarcie drugiego połączenia i zainicjowanie drugiej transakcji przy użyciu
SNAPSHOTpoziomu izolacji w celu odczytania danych wTestSnapshottabeli. Ponieważ jest włączona izolacja migawki, ta transakcja może odczytywać dane, które istniały sprzed rozpoczęciasqlTransaction1tej transakcji.Spowoduje to otwarcie trzeciego połączenia i zainicjowanie transakcji przy użyciu
READ COMMITTEDpoziomu izolacji w celu próby odczytania danych w tabeli. W takim przypadku kod nie może odczytać danych, ponieważ nie może odczytać danych z powodu blokad założonych na tabelę w pierwszej transakcji i operacja przekracza limit czasu. Ten sam wynik występuje, jeśli użyto poziomów izolacjiREPEATABLE READiSERIALIZABLE, ponieważ te poziomy izolacji również nie mogą odczytać danych z powodu blokad założonych w pierwszej transakcji.Spowoduje to otwarcie czwartego połączenia i zainicjowanie transakcji przy użyciu poziomu izolacji
READ UNCOMMITTED, który wykonuje brudny odczyt niezatwierdzonej wartości wsqlTransaction1. Ta wartość może nigdy nie istnieć w bazie danych, jeśli pierwsza transakcja nie zostanie zatwierdzona.Powoduje wycofanie pierwszej transakcji i wyczyszczenie jej przez usunięcie
TestSnapshottabeli i wyłączenie izolacji migawki dlaAdventureWorksbazy danych.
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";
using (SqlDataReader reader2 = command2.ExecuteReader())
{
while (reader2.Read())
{
Console.WriteLine("Expected 1,1 Actual "
+ reader2.GetValue(0)
+ "," + reader2.GetValue(1));
}
}
transaction2.Commit();
}
// Open a third connection to AdventureWorks and
// initiate a third transaction to read from TestSnapshot
// using ReadCommitted isolation level. This transaction
// will not be able to view the data because of
// the locks placed on the table in transaction1
// and will time out after 4 seconds.
// You would see the same behavior with the
// RepeatableRead or Serializable isolation levels.
using (SqlConnection connection3 = new(connectionString))
{
connection3.Open();
SqlCommand command3 = connection3.CreateCommand();
SqlTransaction transaction3 =
connection3.BeginTransaction(IsolationLevel.ReadCommitted);
command3.Transaction = transaction3;
command3.CommandText =
"SELECT ID, valueCol FROM TestSnapshot";
command3.CommandTimeout = 4;
try
{
using (SqlDataReader sqldatareader3 = command3.ExecuteReader())
{
while (sqldatareader3.Read())
{
Console.WriteLine("You should never hit this.");
}
}
transaction3.Commit();
}
catch (Exception ex)
{
Console.WriteLine("Expected timeout expired exception: "
+ ex.Message);
transaction3.Rollback();
}
}
// Open a fourth connection to AdventureWorks and
// initiate a fourth transaction to read from TestSnapshot
// using the ReadUncommitted isolation level. ReadUncommitted
// will not hit the table lock, and will allow a dirty read
// of the proposed new value 22 for valueCol. If the first
// transaction rolls back, this value will never actually have
// existed in the database.
using (SqlConnection connection4 = new(connectionString))
{
connection4.Open();
SqlCommand command4 = connection4.CreateCommand();
SqlTransaction transaction4 =
connection4.BeginTransaction(IsolationLevel.ReadUncommitted);
command4.Transaction = transaction4;
command4.CommandText =
"SELECT ID, valueCol FROM TestSnapshot";
using (SqlDataReader reader4 = command4.ExecuteReader())
{
while (reader4.Read())
{
Console.WriteLine("Expected 1,22 Actual "
+ reader4.GetValue(0)
+ "," + reader4.GetValue(1));
}
}
transaction4.Commit();
}
// Roll back the first transaction
transaction1.Rollback();
}
// CLEANUP
// Delete the TestSnapshot table and set
// ALLOW_SNAPSHOT_ISOLATION OFF
using (SqlConnection connection5 = new(connectionString))
{
connection5.Open();
SqlCommand command5 = connection5.CreateCommand();
command5.CommandText = "DROP TABLE TestSnapshot";
SqlCommand command6 = connection5.CreateCommand();
command6.CommandText =
"ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION OFF";
try
{
command5.ExecuteNonQuery();
command6.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
Console.WriteLine("Done!");
' Assumes GetConnectionString returns a valid connection string
' where pooling is turned off by setting Pooling=False;.
Dim connectionString As String = GetConnectionString()
Using connection1 As New SqlConnection(connectionString)
' Drop the TestSnapshot table if it exists
connection1.Open()
Dim command1 As SqlCommand = connection1.CreateCommand
command1.CommandText = "IF EXISTS " & _
"(SELECT * FROM sys.tables WHERE name=N'TestSnapshot') " _
& "DROP TABLE TestSnapshot"
Try
command1.ExecuteNonQuery()
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
' Enable SNAPSHOT isolation
command1.CommandText = _
"ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON"
command1.ExecuteNonQuery()
' Create a table named TestSnapshot and insert one row of data
command1.CommandText = _
"CREATE TABLE TestSnapshot (ID int primary key, valueCol int)"
command1.ExecuteNonQuery()
command1.CommandText = _
"INSERT INTO TestSnapshot VALUES (1,1)"
command1.ExecuteNonQuery()
' Begin, but do not complete, a transaction to update the data
' with the Serializable isolation level, which locks the table
' pending the commit or rollback of the update. The original
' value in valueCol was 1, the proposed new value is 22.
Dim transaction1 As SqlTransaction = _
connection1.BeginTransaction(IsolationLevel.Serializable)
command1.Transaction = transaction1
command1.CommandText = _
"UPDATE TestSnapshot SET valueCol=22 WHERE ID=1"
command1.ExecuteNonQuery()
' Open a second connection to AdventureWorks
Dim connection2 As SqlConnection = New SqlConnection(connectionString)
Using connection2
connection2.Open()
' Initiate a second transaction to read from TestSnapshot
' using Snapshot isolation. This will read the original
' value of 1 since transaction1 has not yet committed.
Dim command2 As SqlCommand = connection2.CreateCommand()
Dim transaction2 As SqlTransaction = _
connection2.BeginTransaction(IsolationLevel.Snapshot)
command2.Transaction = transaction2
command2.CommandText = _
"SELECT ID, valueCol FROM TestSnapshot"
Using reader2 As SqlDataReader = command2.ExecuteReader()
While reader2.Read()
Console.WriteLine("Expected 1,1 Actual " _
& reader2.GetValue(0).ToString() + "," _
& reader2.GetValue(1).ToString())
End While
End Using
transaction2.Commit()
End Using
' Open a third connection to AdventureWorks and
' initiate a third transaction to read from TestSnapshot
' using the ReadCommitted isolation level. This transaction
' will not be able to view the data because of
' the locks placed on the table in transaction1
' and will time out after 4 seconds.
' You would see the same behavior with the
' RepeatableRead or Serializable isolation levels.
Dim connection3 As SqlConnection = New SqlConnection(connectionString)
Using connection3
connection3.Open()
Dim command3 As SqlCommand = connection3.CreateCommand()
Dim transaction3 As SqlTransaction = _
connection3.BeginTransaction(IsolationLevel.ReadCommitted)
command3.Transaction = transaction3
command3.CommandText = _
"SELECT ID, valueCol FROM TestSnapshot"
command3.CommandTimeout = 4
Try
Using reader3 As SqlDataReader = command3.ExecuteReader()
While reader3.Read()
Console.WriteLine("You should never hit this.")
End While
End Using
transaction3.Commit()
Catch ex As Exception
Console.WriteLine("Expected timeout expired exception: " _
& ex.Message)
transaction3.Rollback()
End Try
End Using
' Open a fourth connection to AdventureWorks and
' initiate a fourth transaction to read from TestSnapshot
' using the ReadUncommitted isolation level. ReadUncommitted
' will not hit the table lock, and will allow a dirty read
' of the proposed new value 22. If the first transaction
' transaction rolls back, this value will never actually have
' existed in the database.
Dim connection4 As SqlConnection = New SqlConnection(connectionString)
Using connection4
connection4.Open()
Dim command4 As SqlCommand = connection4.CreateCommand()
Dim transaction4 As SqlTransaction = _
connection4.BeginTransaction(IsolationLevel.ReadUncommitted)
command4.Transaction = transaction4
command4.CommandText = _
"SELECT ID, valueCol FROM TestSnapshot"
Using reader4 As SqlDataReader = command4.ExecuteReader()
While reader4.Read()
Console.WriteLine("Expected 1,22 Actual " _
& reader4.GetValue(0).ToString() _
& "," + reader4.GetValue(1).ToString())
End While
End Using
transaction4.Commit()
' Rollback transaction1
transaction1.Rollback()
End Using
End Using
' CLEANUP
' Drop TestSnapshot table and set
' ALLOW_SNAPSHOT_ISOLATION OFF for AdventureWorks
Dim connection5 As New SqlConnection(connectionString)
Using connection5
connection5.Open()
Dim command5 As SqlCommand = connection5.CreateCommand()
command5.CommandText = "DROP TABLE TestSnapshot"
Dim command6 As SqlCommand = connection5.CreateCommand()
command6.CommandText = _
"ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION OFF"
Try
command5.ExecuteNonQuery()
command6.ExecuteNonQuery()
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
End Using
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ą
AdventureWorksdanych i umożliwiaSNAPSHOTizolację.Tworzy tabelę o nazwie
TestSnapshotUpdatei wstawia trzy wiersze przykładowych danych.Rozpoczyna się, ale nie kończy,
sqlTransaction1przy użyciu izolacjiSNAPSHOT. W transakcji wybrano trzy wiersze danych.Tworzy drugie
AdventureWorksdoSqlConnectioni tworzy drugą transakcję przy użyciu poziomu izolacjiREAD COMMITTED, która aktualizuje wartość w jednym z wierszy wybranych wsqlTransaction1.Zatwierdza
sqlTransaction2.Wraca do
sqlTransaction1i próbuje zaktualizować ten sam wiersz, którysqlTransaction1już zatwierdził. Zostanie zgłoszony błąd 3960, a transakcjasqlTransaction1zostanie automatycznie wycofana.SqlException.MessageiSqlException.Numbersą wyświetlane w oknie Konsola.Wykonuje kod czyszczenia, aby wyłączyć izolację migawki w
AdventureWorksi 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.
SELECT Poniższa instrukcja używa UPDLOCK wskazówki do blokowania wybranych wierszy:
SELECT * FROM TestSnapshotUpdate WITH (UPDLOCK)
WHERE PriKey BETWEEN 1 AND 3
Użycie wskazówki blokady UPDLOCK blokuje wszelkie próby zaktualizowania tych wierszy 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 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.