Изоляция моментального снимка в SQL Server (ADO.NET)
SQL Server 2005 представляет новый уровень изоляции моментального снимка для усовершенствования параллелизма приложений OLTP. В предыдущих версиях SQL Server параллелизм был основан исключительно на блокировках, что вызывало для некоторых приложений проблемы с блокировками и взаимоблокировками. В отличие от этого, изоляция моментального снимка зависит от расширений управления версиями строк и предназначена для улучшения производительности путем исключения сценариев блокировки модулей чтения или записи.
Основные сведения об изоляции моментального снимка и управлении версиями строк
После включения изоляции моментального снимка обновленные версии строк для каждой транзакции содержатся в tempdb. Уникальный порядковый номер транзакции определяет каждую транзакцию, и эти уникальные номера записываются для каждой версии строки. Транзакция работает с последними версиями строк, имеющими порядковый номер, предшествующий порядковому номеру транзакции. Более новые версии строк, созданные после начала транзакции, не учитываются.
Термин «моментальный снимок» отражает тот факт, что все запросы в транзакции обнаруживают одинаковую версию, или моментальный снимок базы данных, который соответствует состоянию базы данных в момент начала транзакции. Транзакция моментального снимка не требует блокировок базовых строк или страниц данных, что позволяет выполнять другую транзакцию без ее блокировки предыдущей незавершенной транзакцией. Транзакции, изменяющие данные, не блокируют транзакции, в которых происходит чтение данных, а транзакции, считывающие данные, не блокируют транзакции, в которых происходит запись данных, что обычно также наблюдается при использовании уровня изоляции READ COMMITTED, заданного по умолчанию в SQL Server. Применение такого подхода, предусматривающего отказ от блокировок, способствует значительному снижению вероятности взаимоблокировок в сложных транзакциях.
В подходе с изоляцией моментального снимка используется модель оптимистического параллелизма. Если транзакция моментального снимка попытается зафиксировать изменения в данных, произошедшие после начала транзакции, то будет произведен откат транзакции и возникнет ошибка. Этого можно избежать, используя подсказки UPDLOCK для инструкций SELECT, которые обеспечивают доступ к измененным данным. Дополнительные сведения см. в разделе «Подсказки блокировок» электронной документации по SQL Server.
Перед использованием в транзакциях изоляция моментального снимка должна быть включена путем установки параметра базы данных 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 Server 2005 предоставляет расширения уровней изоляции стандарта 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, компонент Database Engine использует совмещаемые блокировки для принудительной установки уровня изоляции по умолчанию. Если параметр базы данных READ_COMMITTED_SNAPSHOT установлен в значение ON, компонент Database Engine использует управление версиями строк и изоляцию моментального снимка по умолчанию вместо применения блокировок для защиты данных.
Основные сведения о функционировании уровней изоляции моментального снимка и управлении версиями строк
Если включен уровень изоляции SNAPSHOT, то при обновлении каждой строки компонент SQL Server Database Engine сохраняет копию исходной строки в базе данных tempdb и добавляет в строку порядковый номер транзакции. Далее показана последовательность происходящих событий.
Инициируется новая транзакция, и ей присваивается порядковый номер.
Компонент Database Engine считывает строку внутри транзакции и получает версию строки из базы данных tempdb, чей порядковый номер наиболее близок и ниже порядкового номера транзакции.
При запуске транзакции моментального снимка компонент Database Engine проверяет, не находится ли порядковый номер транзакции в списке номеров активных незафиксированных транзакций.
Транзакция считывает версию строки, которая была текущей во время запуска транзакции, из базы данных 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.
.gif "Примечание") Примечание |
|---|
В следующем примере используется аналогичная строка соединения с отключенным пулом соединений.Если соединение отправляется в пул, сброс его уровня изоляции не приводит к сбросу уровня изоляции на сервере.В результате последующие соединения, в которых используется то же помещенное в пул внутреннее соединение, запускаются с уровнем изоляции, заданным равным уровню изоляции этого соединения.Альтернативным вариантом по отношению к выключению пула соединений является явное задание уровня изоляции для каждого соединения. |
' 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
// Assumes GetConnectionString returns a valid connection string
// where pooling is turned off by setting Pooling=False;.
string connectionString = GetConnectionString();
using (SqlConnection connection1 = new SqlConnection(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 SqlConnection(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).ToString()
+ "," + reader2.GetValue(1).ToString());
}
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 SqlConnection(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 SqlConnection(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).ToString()
+ "," + reader4.GetValue(1).ToString());
}
transaction4.Commit();
}
// Roll back the first transaction
transaction1.Rollback();
}
// CLEANUP
// Delete the TestSnapshot table and set
// ALLOW_SNAPSHOT_ISOLATION OFF
using (SqlConnection connection5 = new SqlConnection(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!");
Пример
Следующий пример демонстрирует поведение изоляции моментального снимка при изменении данных. Код выполняет следующие действия.
Соединяется с образцом базы данных 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;.
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
// Assumes GetConnectionString returns a valid connection string
// where pooling is turned off by setting Pooling=False;.
string connectionString = GetConnectionString();
using (SqlConnection connection1 = new SqlConnection(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 = null;
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 SqlConnection(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 SqlConnection(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);
}
}
Использование подсказок блокировок с изоляцией моментального снимка
В предыдущем примере первая транзакция выбирает данные, а вторая их обновляет до завершения первой, что вызывает конфликт обновления, когда первая транзакция пытается обновить ту же строку. Вероятность возникновения конфликтов обновления в продолжительных транзакциях моментального снимка можно снизить, задавая подсказки блокировок при запуске транзакции. Следующая инструкция SELECT использует подсказку UPDLOCK для блокировки выбранных строк:
SELECT * FROM TestSnapshotUpdate WITH (UPDLOCK)
WHERE PriKey BETWEEN 1 AND 3
Использование подсказки блокировки UPDLOCK приводит к блокировке любых строк при попытке обновить эти строки до завершения первой транзакции. Это гарантирует, что в выбранных строках не будет конфликтов при их будущих обновлениях в транзакции. См. раздел «Подсказки блокировок» электронной документации по SQL Server.
Если приложение содержит множество конфликтов, то для него изоляция моментального снимка не лучший выбор. Подсказки должны использоваться, только если они действительно нужны. Приложение должно быть создано таким образом, чтобы в ходе его работы не нужно было постоянно полагаться на подсказки блокировок.