Изоляция снимков в SQL Server
Изоляция моментальных снимков повышает параллелизм для приложений OLTP.
Общие сведения об изоляции моментальных снимков и управлении версиями строк
После включения изоляции моментальных снимков необходимо сохранить обновленные версии строк для каждой транзакции. До SQL Server 2019 эти версии хранятся в tempdb. В SQL Server 2019 представлена новая функция ускоренного восстановления базы данных (ADR), для которой требуется собственный набор версий строк. Таким образом, по состоянию на SQL Server 2019, если ADR не включен, версии строк хранятся в tempdb как всегда. Если ADR включен, все версии строк, связанные с изоляцией моментальных снимков и ADR, хранятся в хранилище постоянных версий ADR (PVS), которое находится в пользовательской базе данных в файловой группе, которую указывает пользователь. Уникальный номер последовательности транзакций идентифицирует каждую транзакцию, и эти уникальные номера записываются для каждой версии строки. Транзакция работает с самыми последними версиями строк, имеющими порядковый номер перед порядковым номером транзакции. Более новые версии строк, созданные после начала транзакции, игнорируются транзакцией.
Термин "моментальный снимок" отражает тот факт, что все запросы в транзакции видят одну и ту же версию или моментальный снимок базы данных в зависимости от состояния базы данных в момент начала транзакции. Блокировки не устанавливаются на базовых строках данных или страницах данных в транзакциях, использующих моментальный снимок, что позволяет другим транзакциям выполняться, не блокируясь предыдущими незавершёнными транзакциями. Транзакции, изменяющие данные, не блокируют транзакции, считывающие данные, и наоборот, операции чтения не блокируют операции записи, как это обычно происходит на уровне изоляции READ COMMITTED по умолчанию в SQL Server. Такое неблокирующее поведение также значительно снижает вероятность возникновения взаимоблокировок при сложных транзакциях.
Изоляция моментальных снимков использует модель оптимистического параллелизма. Если транзакция снимка состояния пытается зафиксировать изменения данных, которые были изменены с момента начала транзакции, транзакция откатится и возникнет ошибка. Это можно избежать с помощью подсказок UPDLOCK для инструкций SELECT, которые обращаются к измененным данным. Дополнительные сведения см. в подсказках (Transact-SQL).
Изоляция моментальных снимков должна быть включена, задав параметр базы данных ALLOW_SNAPSHOT_ISOLATION ON, прежде чем он будет использоваться в транзакциях. Это активирует механизм хранения версий строк во временной базе данных (tempdb). Необходимо включить изоляцию моментальных снимков в каждой базе данных, где она используется, с помощью инструкции ALTER DATABASE Transact-SQL. В этом отношении изоляция моментальных снимков отличается от традиционных уровней изоляции READ COMMITTED, REPEATABLE READ, SERIALIZABLE и READ UNCOMMITTED, которые не требуют настройки. Следующие инструкции активируют изоляцию моментальных снимков и заменяют поведение READ COMMITTED по умолчанию на SNAPSHOT:
ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON
Установка параметра READ_COMMITTED_SNAPSHOT в состояние ON позволяет получить доступ к версиям строк в стандартном уровне изоляции READ COMMITTED. Если параметр READ_COMMITTED_SNAPSHOT имеет значение OFF, необходимо явно задать уровень изоляции моментальных снимков для каждого сеанса, чтобы получить доступ к версиям строк.
Управление параллелизмом с помощью уровней изоляции
Уровень изоляции, в котором выполняется инструкция Transact-SQL, определяет его поведение блокировки и управления версиями строк. Уровень изоляции действует в рамках подключения и после его установки для подключения с помощью оператора SET TRANSACTION ISOLATION LEVEL он остается в силе, пока подключение не будет закрыто или не будет установлен другой уровень изоляции. Когда соединение закрыто и возвращается в пул, сохраняется уровень изоляции, установленный последней инструкцией SET TRANSACTION ISOLATION LEVEL. Последующие подключения, повторно использующиеся из пула, применяют уровень изоляции, который был установлен в момент добавления подключения в пул.
Отдельные запросы, выданные в соединении, могут содержать указания блокировки, которые изменяют изоляцию для одной инструкции или транзакции, но не влияют на уровень изоляции подключения. Уровни изоляции или подсказки блокировки, заданные в хранимых процедурах или функциях, не изменяют уровень изоляции соединения, вызывающего их, и действует только в течение длительности хранимой процедуры или вызова функции.
Четыре уровня изоляции, определенные в стандарте SQL-92, поддерживаются в ранних версиях SQL Server:
READ UNCOMMITTED является наименее строгим уровнем изоляции, поскольку он игнорирует блокировки, размещенные другими транзакциями. Транзакции, выполняемые в разделе READ UNCOMMITTED, могут считывать измененные значения данных, которые еще не зафиксированы другими транзакциями; они называются "грязными" чтениями.
READ COMMITTED — это уровень изоляции по умолчанию для SQL Server. Он предотвращает грязное чтение, указывая, что операции не могут считывать данные, которые были изменены, но еще не зафиксированы другими транзакциями. Другие транзакции по-прежнему могут изменять, вставлять или удалять данные между выполнением отдельных инструкций в текущей транзакции, что приводит к не повторяемым чтениям или фантомным данным.
REPEATABLE READ — это более строгий уровень изоляции, чем READ COMMITTED. Он включает READ COMMITTED и дополнительно указывает, что другие транзакции не могут изменять или удалять данные, считываемые текущей транзакцией до фиксации текущей транзакции. Параллелизм ниже, чем для READ COMMITTED, так как общие блокировки для данных чтения хранятся в течение длительности транзакции, а не отпускаются в конце каждой инструкции.
SERIALIZABLE является самым строгим уровнем изоляции, так как он блокирует целые диапазоны ключей и держит блокировки до завершения транзакции. Она включает REPEATABLE READ и добавляет ограничение: другие транзакции не могут вставлять новые строки в диапазоны, уже считанные текущей транзакцией, до её завершения.
Дополнительные сведения см. в руководстве по блокировке транзакций иверсионированию строк.
Расширения уровня изоляции моментальных снимков
SQL Server представил расширения к уровням изоляции SQL-92 с добавлением уровня изоляции SNAPSHOT и дополнительной реализации READ COMMITTED. Уровень изоляции READ_COMMITTED_SNAPSHOT может прозрачно заменить READ COMMITTED для всех транзакций.
Изоляция SNAPSHOT указывает, что данные, считываемые в транзакции, никогда не будут отражать изменения, внесенные другими одновременными транзакциями. Транзакция использует версии строк данных, которые существуют при запуске транзакции. Никакие блокировки не накладываются на данные при чтении, поэтому транзакции SNAPSHOT не препятствуют другим транзакциям записывать данные. Транзакции, которые записывают данные, не блокируют моментальные транзакции для чтения данных. Чтобы использовать его, необходимо включить изоляцию моментальных снимков, задав параметр ALLOW_SNAPSHOT_ISOLATION базы данных.
Параметр базы данных READ_COMMITTED_SNAPSHOT определяет поведение уровня изоляции READ COMMITTED по умолчанию при включении изоляции моментальных снимков в базе данных. Если вы явно не укажете READ_COMMITTED_SNAPSHOT ON, READ COMMITTED применяется ко всем неявным транзакциям. Это создает то же поведение, что и параметр READ_COMMITTED_SNAPSHOT OFF (по умолчанию). Если READ_COMMITTED_SNAPSHOT OFF действует, ядро СУБД использует общие блокировки для обеспечения уровня изоляции по умолчанию. Если для параметра READ_COMMITTED_SNAPSHOT базы данных установлено значение ON, СУБД использует управление версиями строк и изоляцию моментальных снимков по умолчанию вместо блокировок для защиты данных.
Как работает изоляция моментальных снимков и управление версиями строк
При включении уровня изоляции SNAPSHOT при каждом обновлении строки ядро СУБД SQL Server сохраняет копию исходной строки в tempdbи добавляет в строку порядковый номер транзакции. Ниже приведена последовательность событий, которые происходят:
Инициируется новая транзакция и назначается номер последовательности транзакций.
Ядро СУБД считывает строку в транзакции и извлекает версию строки из tempdb, порядковый номер которого ближе всего к и ниже, чем номер последовательности транзакций.
Ядро СУБД проверяет, что номер последовательности транзакций отсутствует в списке номеров последовательностей незавершенных транзакций, которые были активны при запуске транзакции моментального снимка.
Транзакция считывает версию строки из tempdb, которая была текущей в начале транзакции. Новые строки, вставленные после начала транзакции, не будут видны, так как их порядковые номера будут выше, чем значение порядкового номера транзакции.
Текущая транзакция будет видеть строки, которые были удалены после её начала, поскольку в tempdb имеется версия строки с меньшим порядковым номером.
Чистый эффект изоляции моментального снимка заключается в том, что транзакция видит все данные такими, какими они существовали в начале транзакции, не учитывая и не устанавливая блокировки на исходные таблицы. Это может привести к улучшению производительности в ситуациях, когда существует спор.
Транзакция моментального снимка всегда использует оптимистическое управление параллелизмом, без удержания блокировок, которые предотвращали бы обновление строк другими транзакциями. Если транзакция моментального снимка пытается зафиксировать обновление строки, которая была изменена после её начала, транзакция откатывается, и возникает ошибка.
Работа с изоляцией моментальных снимков в ADO.NET
Класс SqlTransaction в ADO.NET поддерживает изоляцию моментальных снимков. Если база данных включена для изоляции моментальных снимков, но не настроена для READ_COMMITTED_SNAPSHOT ON, необходимо инициировать SqlTransaction с помощью значения перечисления IsolationLevel.Snapshot при вызове метода BeginTransaction. Этот фрагмент кода предполагает, что подключение является открытым объектом SqlConnection.
Dim sqlTran As SqlTransaction = _
connection.BeginTransaction(IsolationLevel.Snapshot)
SqlTransaction sqlTran =
connection.BeginTransaction(IsolationLevel.Snapshot);
Пример
В следующем примере показано, как работают различные уровни изоляции, пытаясь получить доступ к заблокированным данным, и он не предназначен для использования в рабочем коде.
Код подключается к примеру базы данных AdventureWorks AdventureWorks в SQL Server и создает таблицу с именем TestSnapshot и вставляет одну строку данных. Код использует инструкцию ALTER DATABASE Transact-SQL для включения изоляции моментальных снимков для базы данных, но он не задает параметр READ_COMMITTED_SNAPSHOT, оставляя поведение уровня изоляции READ COMMITTED по умолчанию. Затем код выполняет следующие действия:
Начинается, но не завершается sqlTransaction1, который использует уровень изоляции SERIALIZABLE для запуска транзакции обновления. Это приводит к блокировке таблицы.
Он открывает второе подключение и инициирует вторую транзакцию с помощью уровня изоляции SNAPSHOT для чтения данных в таблице TestSnapshot. Так как изоляция моментальных снимков включена, эта транзакция может считывать данные, существующие до начала sqlTransaction1.
Он открывает третье подключение и инициирует транзакцию с помощью уровня изоляции READ COMMITTED, чтобы попытаться считывать данные в таблице. В этом случае код не может считывать данные, так как он не может считывать блокировки, помещенные в таблицу в первой транзакции и время ожидания. Тот же результат будет возникать, если были использованы уровни изоляции REPEATABLE READ и SERIALIZABLE, так как эти уровни изоляции также не могут считывать после блокировок, помещенных в первую транзакцию.
Он открывает четвертое подключение и инициирует транзакцию с помощью уровня изоляции READ UNCOMMITTED, который выполняет грязное чтение незафиксированного значения в sqlTransaction1. Это значение никогда не может существовать в базе данных, если первая транзакция не зафиксирована.
Она откатывает первую транзакцию и выполняет очистку, удалив таблицу TestSnapshot и отключив изоляцию моментальных снимков для базы данных AdventureWorks.
Примечание.
В следующих примерах используется та же строка подключения с отключенным пулом подключений. Если подключение подключено к пулу, сброс уровня изоляции не сбрасывает уровень изоляции на сервере. В результате последующие подключения, использующие одно и то же внутреннее соединение с пулом, начинаются с уровней изоляции, установленных в пуле. Альтернативой отключению пула подключений является явное определение уровня изоляции для каждого подключения.
// 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
Пример
В следующем примере показано поведение изоляции моментальных снимков при изменении данных. Код выполняет следующие действия:
Подключается к образцу базы данных AdventureWorks и включает изоляцию SNAPSHOT.
Создает таблицу с именем TestSnapshotUpdate и вставляет три строки примеров данных.
Начинает, но не завершает sqlTransaction1 в режиме изоляции SNAPSHOT. В транзакции выбраны три строки данных.
Создает второе подключение SqlConnection к AdventureWorks и выполняет вторую транзакцию на уровне изоляции READ COMMITTED, которая обновляет значение в одной из строк, выбранных в sqlTransaction1.
Фиксирует sqlTransaction2.
Возвращается к sqlTransaction1 и пытается обновить ту же строку, которая уже была зафиксирована sqlTransaction1. Возникает ошибка 3960, и sqlTransaction1 автоматически отменяется. В окне консоли отображаются SqlException.Number и SqlException.Message.
Выполняет код очистки, чтобы отключить изоляцию моментальных снимков в AdventureWorks и удалить таблицу 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
Использование подсказок блокировки в режиме изоляции на основе моментальных снимков
В предыдущем примере первая транзакция выбирает данные, а вторая транзакция обновляет данные до завершения первой транзакции, что приводит к конфликту обновления, когда первая транзакция пытается обновить ту же строку. Вы можете снизить вероятность конфликтов обновления в длительных транзакциях со снимками, применяя подсказки блокировки в начале транзакции. Следующая инструкция SELECT использует подсказку UPDLOCK для блокировки выбранных строк:
SELECT * FROM TestSnapshotUpdate WITH (UPDLOCK)
WHERE PriKey BETWEEN 1 AND 3
Использование указания блокировки UPDLOCK блокирует любые строки, пытающиеся обновить их до завершения первой транзакции. Это гарантирует, что выбранные строки не имеют конфликтов, когда они обновляются позже в транзакции. Дополнительные сведения см. в подсказках (Transact-SQL).
Если приложение имеет много конфликтов, изоляция моментальных снимков может быть не лучшим вариантом. Намеки следует использовать только при необходимости. Приложение не должно быть разработано таким образом, чтобы оно постоянно опиралось на подсказки блокировки для его работы.