Изоляция моментального снимка в SQL Server
Изоляция моментальных снимков улучшает параллелизм для приложений OLTP.
Основные сведения об изоляции моментального снимка и управлении версиями строк
После включения изоляции моментальных снимков необходимо сохранить обновленные версии строк для каждой транзакции. До SQL Server 2019 эти версии хранятся в tempdb. В SQL Server 2019 представлена новая функция ускоренного восстановления базы данных (ADR), для которой требуется собственный набор версий строк. Таким образом, по состоянию на SQL Server 2019, если ADR не включен, версии строк хранятся в tempdb как всегда. Если ADR включен, все версии строк, связанные с изоляцией моментальных снимков и ADR, хранятся в хранилище постоянных версий ADR (PVS), которое находится в пользовательской базе данных в файловой группе, которую указывает пользователь. Уникальный порядковый номер транзакции идентифицирует каждую транзакцию, и эти уникальные числа записываются для каждой версии строки. Транзакция работает с последними версиями строк, порядковый номер которых предшествует порядковому номеру транзакции. Транзакция игнорирует более новые версии строк, созданные после начала транзакции.
Термин "моментальный снимок" означает, что все запросы в транзакции имеют одну и ту же версию или моментальный снимок базы данных, исходя из состояния базы данных в момент начала транзакции. Транзакция моментального снимка не требует блокировок базовых строк или страниц данных, что позволяет выполнять другую транзакцию без ее блокировки предыдущей незавершенной транзакцией. Транзакции, изменяющие данные, не блокируют транзакции, считывающие данные, а транзакции, считывающие данные, не блокируют транзакции, которые записывают данные, так как в SQL Server они обычно находятся на стандартном уровне изоляции READ COMMITTED. Такое неблокирующее поведение также значительно снижает вероятность взаимоблокировок в сложных транзакциях.
Изоляция моментальных снимков использует модель оптимистической блокировки. Если транзакция моментального снимка пытается зафиксировать изменения в данных, которые имели место с момента начала транзакции, произойдет откат транзакции и возникнет ошибка. Этого можно избежать, используя указания UPDLOCK для инструкций SELECT, обращающихся к изменяемым данным. Дополнительные сведения см. в разделе "Подсказки" (Transact-SQL).
Для использования изоляции моментальных снимков в транзакции необходимо включить эту функцию с помощью параметра ALLOW_SNAPSHOT_ISOLATION ON базы данных. Это приводит к активизации механизма сохранения версий строк во временной базе данных (tempdb). Необходимо включить изоляцию моментальных снимков в каждой использующей ее базе данных с помощью инструкции Transact-SQL ALTER DATABASE. В этом отношении изоляция моментальных снимков отличается от традиционных уровней изоляции 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 Server поддерживались четыре уровня изоляции, определенные в стандарте SQL-92:
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 указывает, что данные, считываемые в рамках транзакции, никогда не будут отражать изменения, внесенные другими одновременными транзакциями. Транзакция использует версии строк данных, которые существуют в начале транзакции. При чтении данные не блокируются, поэтому транзакции моментальных снимков не блокируют запись данных другими транзакциями. Транзакции, осуществляющие запись данных, не блокируют считывание данных транзакциями моментальных снимков. Для использования изоляции моментальных снимков необходимо включить ее, настроив параметр базы данных ALLOW_SNAPSHOT_ISOLATION.
Параметр базы данных READ_COMMITTED_SNAPSHOT определяет поведение стандартного уровня изоляции READ COMMITTED, если изоляция моментальных снимков включена в базе данных. Если не указать параметр READ_COMMITTED_SNAPSHOT явным образом, то для всех неявных транзакций применяется уровень READ COMMITTED. Это приводит к тому же поведению, что и при использовании параметра READ_COMMITTED_SNAPSHOT (по умолчанию). Если действует параметр READ_COMMITTED_SNAPSHOT, ядро СУБД использует совмещаемые блокировки для применения уровня изоляции по умолчанию. Если параметру базы данных READ_COMMITTED_SNAPSHOT присвоить значение ON, то ядро СУБД использует управление версиями строк и режим изоляции моментальных снимков по умолчанию вместо блокировок для защиты данных.
Основные сведения о функционировании уровней изоляции моментального снимка и управлении версиями строк
Если включен уровень изоляции SNAPSHOT, то при обновлении каждой строки компонент SQL Server Database Engine сохраняет копию исходной строки в базе данных tempdb и добавляет в строку порядковый номер транзакции. Ниже приведены происходящие события в последовательном порядке.
Инициируется новая транзакция, и ей назначается порядковый номер.
Компонент Database Engine считывает строку внутри транзакции и получает версию строки из базы данных tempdb, чей порядковый номер наиболее близок и ниже порядкового номера транзакции.
Ядро СУБД проверяет, не находится ли порядковый номер транзакции в списке порядковых номеров незафиксированных транзакций, активных при запуске транзакции моментальных снимков.
Транзакция считывает версию строки, которая была текущей во время запуска транзакции, из базы данных tempdb. Новые строки, вставленные после запуска транзакции, не будут видны, так как эти порядковые номера будут выше, чем значение порядкового номера транзакции.
Текущая транзакция обнаруживает строки, которые были удалены после ее запуска, поскольку версия любой строки в базе данных tempdb имеет меньшее значение порядкового номера, чем транзакция.
Конечный результат изоляции моментального снимка заключается в том, что транзакция видит все данные в том виде, в котором они существовали в начале транзакции, без учета или размещения каких-либо блокировок в базовых таблицах. Это может привести к улучшению производительности в ситуациях, когда возникает состязание.
Транзакция моментального снимка всегда использует управление оптимистической блокировкой, удерживая все блокировки, которые препятствуют обновлению строк другими транзакциями. Если транзакция моментального снимка пытается зафиксировать обновление строки, которая была изменена после начала транзакции, то выполняется откат транзакции и возникает ошибка.
Работа с изоляцией моментального снимка в ADO.NET
Изоляция моментальных снимков поддерживается в ADO.NET классом SqlTransaction. Если в базе данных включена изоляция моментального снимка, но настройка конфигурации не выполнена с учетом параметра READ_COMMITTED_SNAPSHOT со значением ON, то необходимо инициировать транзакцию SqlTransaction с помощью значения перечисления IsolationLevel.Snapshot при вызове метода BeginTransaction. В этом фрагменте кода предполагается, что подключение является открытым объектом SqlConnection.
Dim sqlTran As SqlTransaction = _
connection.BeginTransaction(IsolationLevel.Snapshot)
SqlTransaction sqlTran =
connection.BeginTransaction(IsolationLevel.Snapshot);
Пример
В следующем примере демонстрируется поведение различных уровней изоляции при попытке доступа к заблокированным данным. Этот пример не предназначен для использования в рабочем коде.
В коде устанавливается соединение с образцом базы данных 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: {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
Использование подсказок блокировок с изоляцией моментального снимка
В предыдущем примере первая транзакция выбирает данные, а вторая обновляет данные до того, как первая транзакция сможет завершиться, что приведет к конфликту обновления, когда первая транзакция попытается обновить одну и ту же строку. Можно уменьшить вероятность конфликтов обновления в длительных транзакциях моментальных снимков, предоставив указания блокировки в начале транзакции. Следующая инструкция SELECT использует указание UPDLOCK для блокировки выбранных строк:
SELECT * FROM TestSnapshotUpdate WITH (UPDLOCK)
WHERE PriKey BETWEEN 1 AND 3
Использование указания блокировки UPDLOCK блокирует любые строки, пытающиеся обновить строки до завершения первой транзакции. Это гарантирует, что выбранные строки не будут иметь конфликтов, когда они будут обновлены позже в транзакции. Дополнительные сведения см. в разделе "Подсказки" (Transact-SQL).
Если в приложении много конфликтов, изоляцию моментальных снимков лучше не использовать. Указания следует использовать, только если это действительно требуется. Приложение не должно быть спроектировано таким образом, чтобы оно постоянно полагалось на указания блокировки для своей работы.