Изоляция снимков в SQL ServerSnapshot Isolation in SQL Server

Изоляция моментального снимка приводит к повышению параллелизма для приложений OLTP.Snapshot isolation enhances concurrency for OLTP applications.

Основные сведения об изоляции моментального снимка и управлении версиями строкUnderstanding Snapshot Isolation and Row Versioning

После включения изоляции моментального снимка обновленные версии строк для каждой транзакции содержатся в tempdb.Once snapshot isolation is enabled, updated row versions for each transaction are maintained in tempdb. Уникальный порядковый номер транзакции определяет каждую транзакцию, и эти уникальные номера записываются для каждой версии строки.A unique transaction sequence number identifies each transaction, and these unique numbers are recorded for each row version. Транзакция работает с последними версиями строк, имеющими порядковый номер, предшествующий порядковому номеру транзакции.The transaction works with the most recent row versions having a sequence number before the sequence number of the transaction. Более новые версии строк, созданные после начала транзакции, не учитываются.Newer row versions created after the transaction has begun are ignored by the transaction.

Термин «моментальный снимок» отражает тот факт, что все запросы в транзакции обнаруживают одинаковую версию, или моментальный снимок базы данных, который соответствует состоянию базы данных в момент начала транзакции.The term "snapshot" reflects the fact that all queries in the transaction see the same version, or snapshot, of the database, based on the state of the database at the moment in time when the transaction begins. Транзакция моментального снимка не требует блокировок базовых строк или страниц данных, что позволяет выполнять другую транзакцию без ее блокировки предыдущей незавершенной транзакцией.No locks are acquired on the underlying data rows or data pages in a snapshot transaction, which permits other transactions to execute without being blocked by a prior uncompleted transaction. Транзакции, изменяющие данные, не блокируют транзакции, в которых происходит чтение данных, а транзакции, считывающие данные, не блокируют транзакции, в которых происходит запись данных, что обычно также наблюдается при использовании уровня изоляции READ COMMITTED, заданного по умолчанию в SQL Server.Transactions that modify data do not block transactions that read data, and transactions that read data do not block transactions that write data, as they normally would under the default READ COMMITTED isolation level in SQL Server. Применение такого подхода, предусматривающего отказ от блокировок, способствует значительному снижению вероятности взаимоблокировок в сложных транзакциях.This non-blocking behavior also significantly reduces the likelihood of deadlocks for complex transactions.

В подходе с изоляцией моментального снимка используется модель оптимистического параллелизма.Snapshot isolation uses an optimistic concurrency model. Если транзакция моментального снимка попытается зафиксировать изменения в данных, произошедшие после начала транзакции, то будет произведен откат транзакции и возникнет ошибка.If a snapshot transaction attempts to commit modifications to data that has changed since the transaction began, the transaction will roll back and an error will be raised. Этого можно избежать, используя подсказки UPDLOCK для инструкций SELECT, которые обеспечивают доступ к измененным данным.You can avoid this by using UPDLOCK hints for SELECT statements that access data to be modified. Дополнительные сведения см. в разделе «Подсказки блокировок» электронной документации по SQL Server.See "Locking Hints" in SQL Server Books Online for more information.

Перед использованием в транзакциях изоляция моментального снимка должна быть включена путем установки параметра базы данных ALLOW_SNAPSHOT_ISOLATION в значение ON.Snapshot isolation must be enabled by setting the ALLOW_SNAPSHOT_ISOLATION ON database option before it is used in transactions. Это приводит к активизации механизма сохранения версий строк во временной базе данных (tempdb).This activates the mechanism for storing row versions in the temporary database (tempdb). Необходимо включить изоляцию моментального снимка в каждой использующей ее базе данных с помощью инструкции ALTER DATABASE языка Transact-SQL.You must enable snapshot isolation in each database that uses it with the Transact-SQL ALTER DATABASE statement. В этом отношении изоляция моментального снимка отличается от традиционных уровней изоляции READ COMMITTED, REPEATABLE READ, SERIALIZABLE и READ UNCOMMITTED, которые не требуют настройки конфигурации.In this respect, snapshot isolation differs from the traditional isolation levels of READ COMMITTED, REPEATABLE READ, SERIALIZABLE, and READ UNCOMMITTED, which require no configuration. Следующие инструкции активируют изоляцию моментального снимка и заменяют поведение по умолчанию READ COMMITTED на SNAPSHOT:The following statements activate snapshot isolation and replace the default READ COMMITTED behavior with SNAPSHOT:

ALTER DATABASE MyDatabase  
SET ALLOW_SNAPSHOT_ISOLATION ON  

ALTER DATABASE MyDatabase  
SET READ_COMMITTED_SNAPSHOT ON  

Установка параметра READ_COMMITTED_SNAPSHOT со значением ON обеспечивает доступ к версиям строк уровня изоляции по умолчанию READ COMMITTED.Setting the READ_COMMITTED_SNAPSHOT ON option allows access to versioned rows under the default READ COMMITTED isolation level. Если параметр READ_COMMITTED_SNAPSHOT установлен в значение OFF, то для получения доступа к версиям строк потребуется явно задавать уровень изоляции моментального снимка для каждого сеанса.If the READ_COMMITTED_SNAPSHOT option is set to OFF, you must explicitly set the Snapshot isolation level for each session in order to access versioned rows.

Управление параллелизмом с помощью уровней изоляцииManaging Concurrency with Isolation Levels

Уровень изоляции, при котором выполняется инструкция Transact-SQL, определяет ее блокировку и поведение управления версиями строк.The isolation level under which a Transact-SQL statement executes determines its locking and row versioning behavior. Уровень изоляции действует на уровне соединения и, будучи один раз заданным для соединения с помощью инструкции SET TRANSACTION ISOLATION LEVEL, остается в силе до закрытия соединения или установки другого уровня изоляции.An isolation level has connection-wide scope, and once set for a connection with the SET TRANSACTION ISOLATION LEVEL statement, it remains in effect until the connection is closed or another isolation level is set. После того как соединение закрывается и возвращается в пул, сохраняется уровень изоляции последней инструкции SET TRANSACTION ISOLATION LEVEL.When a connection is closed and returned to the pool, the isolation level from the last SET TRANSACTION ISOLATION LEVEL statement is retained. В последующих соединениях, повторно использующих соединение из пула, применяется уровень изоляции, который был действителен в момент возврата соединения в пул.Subsequent connections reusing a pooled connection use the isolation level that was in effect at the time the connection is pooled.

Отдельные запросы, выполняемые внутри соединения, могут содержать подсказки блокировок, которые изменяют уровень изоляции для одной инструкции или транзакции, но не оказывают влияния на уровень изоляции соединения.Individual queries issued within a connection can contain lock hints that modify the isolation for a single statement or transaction but do not affect the isolation level of the connection. Уровни изоляции или подсказки блокировок, установленные в хранимых процедурах или функциях, не изменяют уровень изоляции вызывающего их соединения и действительны только в течение их вызова.Isolation levels or lock hints set in stored procedures or functions do not change the isolation level of the connection that calls them and are in effect only for the duration of the stored procedure or function call.

В ранних версиях SQL Server поддерживались четыре уровня изоляции, определенные в стандарте SQL-92:Four isolation levels defined in the SQL-92 standard were supported in early versions of SQL Server:

  • READ UNCOMMITTED является наименее строгим уровнем изоляции, поскольку при его использовании не учитываются блокировки, размещенные другими транзакциями.READ UNCOMMITTED is the least restrictive isolation level because it ignores locks placed by other transactions. Транзакции, выполняемые в READ UNCOMMITTED, могут считывать измененные значения данных, которые еще не были зафиксированы другими транзакциями. Это называется чтением «грязных» данных.Transactions executing under READ UNCOMMITTED can read modified data values that have not yet been committed by other transactions; these are called "dirty" reads.

  • READ COMMITTED является уровнем изоляции по умолчанию для SQL Server.READ COMMITTED is the default isolation level for SQL Server. Он запрещает чтение «грязных» данных путем задания условия, что инструкции не могут считывать измененные значения данных, которые еще не зафиксированы другими транзакциями.It prevents dirty reads by specifying that statements cannot read data values that have been modified but not yet committed by other transactions. Другие транзакции все еще могут изменять, вставлять или удалять данные между выполнением отдельных инструкций внутри текущей транзакции, что приводит к выполнению операций чтения без возможности повторения или к получению «фантомных» данных.Other transactions can still modify, insert, or delete data between executions of individual statements within the current transaction, resulting in non-repeatable reads, or "phantom" data.

  • REPEATABLE READ является более ограничительным уровнем изоляции, чем READ COMMITTED.REPEATABLE READ is a more restrictive isolation level than READ COMMITTED. Он включает в себя уровень изоляции READ COMMITTED и дополнительно указывает, что до завершения текущей транзакции ни одна прочая транзакция не может изменять или удалять данные, считанные текущей транзакцией.It encompasses READ COMMITTED and additionally specifies that no other transactions can modify or delete data that has been read by the current transaction until the current transaction commits. Параллелизм данного уровня изоляции ниже по сравнению с READ COMMITTED, поскольку совмещаемые блокировки при чтении данных сохраняются в течение транзакции, а не освобождаются после выполнения каждой инструкции.Concurrency is lower than for READ COMMITTED because shared locks on read data are held for the duration of the transaction instead of being released at the end of each statement.

  • SERIALIZABLE является самым строгим уровнем изоляции, поскольку при его использовании блокируются целые диапазоны ключей и блокировки сохраняются до завершения транзакции.SERIALIZABLE is the most restrictive isolation level, because it locks entire ranges of keys and holds the locks until the transaction is complete. Он включает в себя уровень изоляции REPEATABLE READ и добавляет ограничение, согласно которому до завершения транзакции другие транзакции не могут вставлять новые строки в диапазоны строк, чтение которых осуществляется в данной транзакции.It encompasses REPEATABLE READ and adds the restriction that other transactions cannot insert new rows into ranges that have been read by the transaction until the transaction is complete.

Дополнительные сведения см. в разделе «Уровни изоляции» электронной документации по SQL Server.For more information, see "Isolation Levels" in SQL Server Books Online.

Расширения уровня изоляции моментального снимкаSnapshot Isolation Level Extensions

SQL Server предоставляет расширения уровней изоляции стандарта SQL-92 путем представления уровня изоляции SNAPSHOT и дополнительных изменений в READ COMMITTED.SQL Server introduced extensions to the SQL-92 isolation levels with the introduction of the SNAPSHOT isolation level and an additional implementation of READ COMMITTED. Уровень изоляции READ_COMMITTED_SNAPSHOT может прозрачно заменять READ COMMITTED для всех транзакций.The READ_COMMITTED_SNAPSHOT isolation level can transparently replace READ COMMITTED for all transactions.

  • Изоляция SNAPSHOT указывает, что данные, считанные внутри транзакции, никогда не отразят изменений, сделанных другими одновременными транзакциями.SNAPSHOT isolation specifies that data read within a transaction will never reflect changes made by other simultaneous transactions. Транзакция использует версии строк данных, существующих при начале транзакции.The transaction uses the data row versions that exist when the transaction begins. При чтении данных на них не устанавливаются блокировки, иными словами, транзакции SNAPSHOT не блокируют операции записи данных, выполняемые другими транзакциями.No locks are placed on the data when it is read, so SNAPSHOT transactions do not block other transactions from writing data. Транзакции, осуществляющие запись данных, не блокируют чтение данных транзакциями моментального снимка.Transactions that write data do not block snapshot transactions from reading data. Для использования изоляции моментального снимка необходимо включить ее, установив параметр базы данных ALLOW_SNAPSHOT_ISOLATION.You need to enable snapshot isolation by setting the ALLOW_SNAPSHOT_ISOLATION database option in order to use it.

  • Если изоляция моментального снимка включена в базе данных, то параметр базы данных READ_COMMITTED_SNAPSHOT определяет поведение уровня изоляции по умолчанию READ COMMITTED.The READ_COMMITTED_SNAPSHOT database option determines the behavior of the default READ COMMITTED isolation level when snapshot isolation is enabled in a database. Если параметр READ_COMMITTED_SNAPSHOT со значением ON не задан явно, то ко всем неявным транзакциям применяется уровень изоляции READ COMMITTED.If you do not explicitly specify READ_COMMITTED_SNAPSHOT ON, READ COMMITTED is applied to all implicit transactions. Это аналогично организации работы, которая применяется при установке параметра READ_COMMITTED_SNAPSHOT со значением OFF (по умолчанию).This produces the same behavior as setting READ_COMMITTED_SNAPSHOT OFF (the default). Если действителен параметр READ_COMMITTED_SNAPSHOT со значением OFF, компонент Database Engine использует совмещаемые блокировки для принудительной установки уровня изоляции по умолчанию.When READ_COMMITTED_SNAPSHOT OFF is in effect, the Database Engine uses shared locks to enforce the default isolation level. Если параметр базы данных READ_COMMITTED_SNAPSHOT установлен в значение ON, компонент Database Engine использует управление версиями строк и изоляцию моментального снимка по умолчанию вместо применения блокировок для защиты данных.If you set the READ_COMMITTED_SNAPSHOT database option to ON, the database engine uses row versioning and snapshot isolation as the default, instead of using locks to protect the data.

Основные сведения о функционировании уровней изоляции моментального снимка и управлении версиями строкHow Snapshot Isolation and Row Versioning Work

Если уровень изоляции моментального СНИМКА включена, каждый раз при обновлении строки SQL Server Database Engine сохраняет копию исходной строки в tempdbи добавляет порядковый номер транзакции в строку.When the SNAPSHOT isolation level is enabled, each time a row is updated, the SQL Server Database Engine stores a copy of the original row in tempdb, and adds a transaction sequence number to the row. Далее показана последовательность происходящих событий.The following is the sequence of events that occurs:

  • Инициируется новая транзакция, и ей присваивается порядковый номер.A new transaction is initiated, and it is assigned a transaction sequence number.

  • Компонент Database Engine считывает строку внутри транзакции и получает версию строки из tempdb чей порядковый номер наиболее близкое к и ниже порядкового номера транзакции.The Database Engine reads a row within the transaction and retrieves the row version from tempdb whose sequence number is closest to, and lower than, the transaction sequence number.

  • При запуске транзакции моментального снимка компонент Database Engine проверяет, не находится ли порядковый номер транзакции в списке номеров активных незафиксированных транзакций.The Database Engine checks to see if the transaction sequence number is not in the list of transaction sequence numbers of the uncommitted transactions active when the snapshot transaction started.

  • Транзакция считывает версию строки из tempdb которая была текущей на момент начала транзакции.The transaction reads the version of the row from tempdb that was current as of the start of the transaction. Транзакция не обнаруживает новые строки, вставленные после ее запуска, поскольку эти строки имеют более высокие значения порядковых номеров по сравнению с порядковым номером транзакции.It will not see new rows inserted after the transaction was started because those sequence number values will be higher than the value of the transaction sequence number.

  • Текущая транзакция обнаруживает строки, которые были удалены после начала транзакции, поскольку версия любой строки в tempdb с меньшее значение порядкового номера.The current transaction will see rows that were deleted after the transaction began, because there will be a row version in tempdb with a lower sequence number value.

Суммарным эффектом изоляции моментального снимка является то, что транзакция обнаруживает все данные, существовавшие при ее запуске, без учета или установки каких-либо блокировок на базовых таблицах.The net effect of snapshot isolation is that the transaction sees all of the data as it existed at the start of the transaction, without honoring or placing any locks on the underlying tables. Это может привести к повышению производительности в тех ситуациях, когда возникает конфликт.This can result in performance improvements in situations where there is contention.

В транзакции моментального снимка всегда используется оптимистическое управление параллелизмом, в котором предусматривается отказ от любых блокировок, запрещающих обновление строк другими транзакциями.A snapshot transaction always uses optimistic concurrency control, withholding any locks that would prevent other transactions from updating rows. Если транзакция моментального снимка попытается зафиксировать обновление строки, выполненное после запуска транзакции, будет произведен ее откат и возникнет ошибка.If a snapshot transaction attempts to commit an update to a row that was changed after the transaction began, the transaction is rolled back, and an error is raised.

Работа с изоляцией моментального снимка в ADO.NETWorking with Snapshot Isolation in ADO.NET

Изоляция моментального снимка поддерживается в ADO.NET с помощью класса SqlTransaction.Snapshot isolation is supported in ADO.NET by the SqlTransaction class. Если включена изоляция моментальных снимков базы данных, но не настроен для READ_COMMITTED_SNAPSHOT ON, необходимо инициировать SqlTransaction с помощью IsolationLevel.Snapshot значение перечисления, при вызове BeginTransaction метод.If a database has been enabled for snapshot isolation but is not configured for READ_COMMITTED_SNAPSHOT ON, you must initiate a SqlTransaction using the IsolationLevel.Snapshot enumeration value when calling the BeginTransaction method. В данном фрагменте кода предполагается, что соединение представляет собой открытый объект SqlConnection.This code fragment assumes that connection is an open SqlConnection object.

Dim sqlTran As SqlTransaction = _  
  connection.BeginTransaction(IsolationLevel.Snapshot)  
SqlTransaction sqlTran =   
  connection.BeginTransaction(IsolationLevel.Snapshot);  

ПримерExample

Следующий пример показывает, какие действия осуществляются при использовании различных уровней изоляции, если обнаруживается попытка получения доступа к заблокированным данным, и не предназначен для использования в коде производственного назначения.The following example demonstrates how the different isolation levels behave by attempting to access locked data, and it is not intended to be used in production code.

Код подключается к AdventureWorks образца базы данных в SQL Server и создает таблицу с именем TestSnapshot и вставляет одну строку данных.The code connects to the AdventureWorks sample database in SQL Server and creates a table named TestSnapshot and inserts one row of data. В коде используется инструкция ALTER DATABASE языка Transact-SQL для включения изоляции моментального снимка базы данных, но не устанавливается параметр READ_COMMITTED_SNAPSHOT, поэтому остается в силе поведение уровня изоляции READ COMMITTED, применяемое по умолчанию.The code uses the ALTER DATABASE Transact-SQL statement to turn on snapshot isolation for the database, but it does not set the READ_COMMITTED_SNAPSHOT option, leaving the default READ COMMITTED isolation-level behavior in effect. Затем в коде выполняются следующие действия.The code then performs the following actions:

  • В коде начинается, но не завершается транзакция sqlTransaction1, в которой используется уровень изоляции SERIALIZABLE для запуска транзакции обновления.It begins, but does not complete, sqlTransaction1, which uses the SERIALIZABLE isolation level to start an update transaction. Это приводит к блокировке таблицы.This has the effect of locking the table.

  • Он открывается второе соединение и инициируется вторая транзакция с уровнем изоляции моментального СНИМКА для чтения данных в TestSnapshot таблицы.It opens a second connection and initiates a second transaction using the SNAPSHOT isolation level to read the data in the TestSnapshot table. Поскольку изоляция моментального снимка включена, данная транзакция может считывать данные, существовавшие до запуска sqlTransaction1.Because snapshot isolation is enabled, this transaction can read the data that existed before sqlTransaction1 started.

  • Код открывает третье соединение и инициирует транзакцию, используя уровень изоляции READ COMMITTED для осуществления попытки чтения данных из таблицы.It opens a third connection and initiates a transaction using the READ COMMITTED isolation level to attempt to read the data in the table. В этом случае в коде исключается возможность считывать данные, поскольку чтение не может быть выполнено после установки блокировок на таблице в первой транзакции, поэтому код завершает свою работу в связи с истечением времени ожидания. Аналогичный результат был бы получен при использовании уровней изоляции REPEATABLE READ и SERIALIZABLE, поскольку эти уровни изоляции также не позволяют выполнять чтение после установки блокировок в первой транзакции.In this case, the code cannot read the data because it cannot read past the locks placed on the table in the first transaction and times out. The same result would occur if the REPEATABLE READ and SERIALIZABLE isolation levels were used because these isolation levels also cannot read past the locks placed in the first transaction.

  • Код открывает четвертое соединение и инициирует транзакцию, используя уровень изоляции READ UNCOMMITTED, который выполняет чтение незафиксированного значения в sqlTransaction1 как чтение «грязных» данных.It opens a fourth connection and initiates a transaction using the READ UNCOMMITTED isolation level, which performs a dirty read of the uncommitted value in sqlTransaction1. Это значение может так и не появиться в базе данных, если первая транзакция не будет зафиксирована.This value may never actually exist in the database if the first transaction is not committed.

  • Он выполняет откат первой транзакции и очищает, удаляя TestSnapshot и выключения изоляции моментального снимка в AdventureWorks базы данных.It rolls back the first transaction and cleans up by deleting the TestSnapshot table and turning off snapshot isolation for the AdventureWorks database.

Примечание

В следующем примере используется аналогичная строка соединения с отключенным пулом соединений.The following examples use the same connection string with connection pooling turned off. Если соединение отправляется в пул, сброс его уровня изоляции не приводит к сбросу уровня изоляции на сервере.If a connection is pooled, resetting its isolation level does not reset the isolation level at the server. В результате последующие соединения, в которых используется то же помещенное в пул внутреннее соединение, запускаются с уровнем изоляции, заданным равным уровню изоляции этого соединения.As a result, subsequent connections that use the same pooled inner connection start with their isolation levels set to that of the pooled connection. Альтернативным вариантом по отношению к выключению пула соединений является явное задание уровня изоляции для каждого соединения.An alternative to turning off connection pooling is to set the isolation level explicitly for each connection.

// 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!");
' 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

ПримерExample

Следующий пример демонстрирует поведение изоляции моментального снимка при изменении данных.The following example demonstrates the behavior of snapshot isolation when data is being modified. Код выполняет следующие действия.The code performs the following actions:

  • Подключается к AdventureWorks образца базы данных и позволяет изоляции моментального СНИМКА.Connects to the AdventureWorks sample database and enables SNAPSHOT isolation.

  • Создает таблицу с именем TestSnapshotUpdate и вставляет три строки образца данных.Creates a table named TestSnapshotUpdate and inserts three rows of sample data.

  • Начинает, но не завершает транзакцию sqlTransaction1 с использованием уровня изоляции SNAPSHOT.Begins, but does not complete, sqlTransaction1 using SNAPSHOT isolation. В транзакции выбираются три строки данных.Three rows of data are selected in the transaction.

  • Создает второй SqlConnection для AdventureWorks и создает вторую транзакцию, используя уровень изоляции READ COMMITTED, который обновляет значение в одной из строк, выбранных в sqlTransaction1.Creates a second SqlConnection to AdventureWorks and creates a second transaction using the READ COMMITTED isolation level that updates a value in one of the rows selected in sqlTransaction1.

  • Фиксирует транзакцию sqlTransaction2.Commits sqlTransaction2.

  • Возвращается к транзакции sqlTransaction1 и выполняет попытку обновления той строки, которую sqlTransaction1 уже зафиксировала.Returns to sqlTransaction1 and attempts to update the same row that sqlTransaction1 already committed. Возникает ошибка 3960, и откат транзакции sqlTransaction1 производится автоматически.Error 3960 is raised, and sqlTransaction1 is rolled back automatically. SqlException.Number и SqlException.Message отображаются в окне консоли.The SqlException.Number and SqlException.Message are displayed in the Console window.

  • Выполняет код очистки для выключения изоляции моментального снимка в AdventureWorks и удалить TestSnapshotUpdate таблицы.Executes clean-up code to turn off snapshot isolation in AdventureWorks and delete the TestSnapshotUpdate table.

// 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);
    }
}
' 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

Использование подсказок блокировок с изоляцией моментального снимкаUsing Lock Hints with Snapshot Isolation

В предыдущем примере первая транзакция выбирает данные, а вторая их обновляет до завершения первой, что вызывает конфликт обновления, когда первая транзакция пытается обновить ту же строку.In the previous example, the first transaction selects data, and a second transaction updates the data before the first transaction is able to complete, causing an update conflict when the first transaction tries to update the same row. Вероятность возникновения конфликтов обновления в продолжительных транзакциях моментального снимка можно снизить, задавая подсказки блокировок при запуске транзакции.You can reduce the chance of update conflicts in long-running snapshot transactions by supplying lock hints at the beginning of the transaction. Следующая инструкция SELECT использует подсказку UPDLOCK для блокировки выбранных строк:The following SELECT statement uses the UPDLOCK hint to lock the selected rows:

SELECT * FROM TestSnapshotUpdate WITH (UPDLOCK)   
  WHERE PriKey BETWEEN 1 AND 3  

Использование подсказки блокировки UPDLOCK приводит к блокировке любых строк при попытке обновить эти строки до завершения первой транзакции.Using the UPDLOCK lock hint blocks any rows attempting to update the rows before the first transaction completes. Это гарантирует, что в выбранных строках не будет конфликтов при их будущих обновлениях в транзакции.This guarantees that the selected rows have no conflicts when they are updated later in the transaction. См. раздел «Подсказки блокировок» электронной документации по SQL Server.See "Locking Hints" in SQL Server Books Online.

Если приложение содержит множество конфликтов, то для него изоляция моментального снимка не лучший выбор.If your application has many conflicts, snapshot isolation may not be the best choice. Подсказки должны использоваться, только если они действительно нужны.Hints should only be used when really needed. Приложение должно быть создано таким образом, чтобы в ходе его работы не нужно было постоянно полагаться на подсказки блокировок.Your application should not be designed so that it constantly relies on lock hints for its operation.

См. такжеSee Also

SQL Server и ADO.NETSQL Server and ADO.NET
Центр разработчиков наборов данных и управляемых поставщиков ADO.NETADO.NET Managed Providers and DataSet Developer Center