Поделиться через

SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

  • Статья

Управляет поведением блокировки и версиями строк инструкций Transact-SQL, выданных при соединении с SQL Server.

Значок ссылки на раздел Синтаксические обозначения в Transact-SQL

Синтаксис

SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }
[ ; ]

Аргументы

  • READ UNCOMMITTED
    Указывает, что инструкции могут считывать строки, которые были изменены другими транзакциями, но еще не были зафиксированы.

    Транзакции, работающие на уровне READ UNCOMMITTED, не используют совмещаемые блокировки, чтобы предотвратить изменение считываемых текущей транзакцией данных другими транзакциями. Транзакции READ UNCOMMITTED также не блокируются монопольными блокировками, которые не позволили бы текущей транзакции считывать измененные другими транзакциями, но не зафиксированные строки. Установка этого параметра позволяет считывать незафиксированные изменения, что называется чтением «грязных» данных. Значения в данных могут быть изменены и до окончания транзакции строки могут появляться и исчезать в наборе данных. Этот параметр действует так же, как и настройка NOLOCK всех таблиц во всех инструкциях SELECT в транзакции. Это наименьшее ограничение уровней изоляции.

    В SQL Server конфликты блокировок при защите транзакций от чтения «грязных» данных незафиксированных изменений данных можно сократить с помощью следующего:

    • уровня изоляции READ COMMITTED с параметром базы данных READ_COMMITTED_SNAPSHOT, находящимся в состоянии ON;

    • уровня изоляции моментального снимка (SNAPSHOT).

  • READ COMMITTED
    Указывает, что инструкции не могут считывать данные, которые были изменены другими транзакциями, но еще не были зафиксированы. Это предотвращает чтение«грязных» данных. Данные могут быть изменены другими транзакциями между отдельными инструкциями в текущей транзакции, результатом чего будет неповторяемое чтение или фантомные данные. Этот параметр в SQL Server установлен по умолчанию.

    Поведение READ COMMITTED зависит от настройки аргумента базы данных READ_COMMITTED_SNAPSHOT.

    • Если параметр READ_COMMITTED_SNAPSHOT находится в состоянии OFF (по умолчанию), компонент Компонент Database Engine при выполнении операций считывания текущей транзакцией использует совмещаемые блокировки для предотвращения изменения строк другими транзакциями. Совмещаемые блокировки также блокируют инструкции от считывания строк, измененных другими транзакциями, пока не завершится другая транзакция. От типа совмещаемой блокировки зависит время ее освобождения. Блокировка строки освобождается перед обработкой следующей строки. Блокировки страницы завершаются при чтении следующей страницы, а блокировки таблицы завершаются по окончании выполнения инструкции.

      ПримечаниеПримечание

      Если параметр READ_COMMITTED_SNAPSHOT находится в состоянии ON, компонент Компонент Database Engine использует управление версиями строк для представления каждой инструкции согласованного на уровне транзакций моментального снимка данных в том виде, который они имели на момент начала выполнения инструкции. Для защиты данных от обновления другими транзакциями блокировки не используются.

      Изоляция моментальных снимков поддерживает данные FILESTREAM. В режиме изоляции моментальных снимков данные FILESTREAM, считанные любой инструкцией транзакции, будут согласованы на уровне транзакции с версией данных, существовавших на момент начала транзакции.

    Если параметр базы данных READ_COMMITTED_SNAPSHOT имеет значение ON, для запроса совмещаемой блокировки можно использовать табличное указание READCOMMITTEDLOCK вместо управления версиями строк для отдельных инструкций в транзакциях, работающих на уровне изоляции READ COMMITTED.

    ПримечаниеПримечание

    При установке параметра READ_COMMITTED_SNAPSHOT разрешается только то соединение с базой данных, которое выполняет команду ALTER DATABASE. До завершения инструкции ALTER DATABASE в базе данных не должно быть других открытых соединений. База данных не обязательно должна находиться в однопользовательском режиме.

  • REPEATABLE READ
    Указывает на то, что инструкции не могут считывать данные, которые были изменены, но еще не зафиксированы другими транзакциями, а также на то, что другие транзакции не могут изменять данные, читаемые текущей транзакцией, до ее завершения.

    Совмещаемые блокировки применяются ко всем данным, считываемым любой инструкцией транзакции, и сохраняются до ее завершения. Это запрещает другим транзакциям изменять строки, считываемые текущей транзакцией. Другие транзакции могут вставлять новые строки, соответствующие условиям поиска инструкций, содержащихся в текущей транзакции. При повторном запуске инструкции текущей транзакцией будут извлечены новые строки, что приведет к фантомному чтению. Учитывая то, что совмещаемые блокировки сохраняются до завершения транзакции и не снимаются в конце каждой инструкции, степень совпадений ниже, чем при уровне изоляции по умолчанию READ COMMITTED. Используйте этот параметр только в случае необходимости.

  • SNAPSHOT
    Указывает на то, что данные, считанные любой инструкцией транзакции, будут согласованы на уровне транзакции с версией данных, существовавших в ее начале. Транзакция распознает только те изменения, которые были зафиксированы до ее начала. Инструкции, выполняемые текущей транзакцией, не видят изменений данных, произведенных другими транзакциями после запуска текущей транзакции. Таким образом достигается эффект получения инструкциями в транзакции моментального снимка зафиксированных данных на момент запуска транзакции.

    Транзакции моментальных снимков не требуют блокировки при считывании данных, за исключением случаев восстановления базы данных. Считывание данных транзакциями моментальных снимков не блокирует запись данных другими транзакциями. Транзакции, осуществляющие запись данных, не блокируют считывание данных транзакциями моментальных снимков.

    На этапе отката восстановления базы данных транзакция моментальных снимков запросит блокировку, если будет предпринята попытка считывания данных, заблокированных другой откатываемой транзакцией. Блокировка транзакции моментальных снимков сохраняется до завершения отката. Блокировка снимается сразу после предоставления.

    Перед запуском транзакции, использующей уровень изоляции моментальных снимков, необходимо установить параметр базы данных ALLOW_SNAPSHOT_ISOLATION в ON. Если транзакция с уровнем изоляции моментального снимка обращается к данным из нескольких баз данных, аргумент ALLOW_SNAPSHOT_ISOLATION должен быть включен в каждой базе данных.

    Невозможно изменить на уровень изоляции моментального снимка уровень изоляции транзакции, запущенной с другим уровнем изоляции; в этом случае транзакция будет прервана. Если транзакция запущена с уровнем изоляции моментальных снимков, ее уровень изоляции можно изменять. Транзакция начинается в момент первого доступа к данным.

    Транзакция, работающая с уровнем изоляции моментального снимка, может просматривать внесенные ею изменения. Например, если транзакция выполняет инструкцию UPDATE, а затем инструкцию SELECT для одной и той же таблицы, измененные данные будут включены в результирующий набор.

    ПримечаниеПримечание

    В режиме изоляции моментальных снимков данные FILESTREAM, считанные любой инструкцией транзакции, будут согласованы на уровне транзакции с версией данных, существовавших на момент начала транзакции, а не на момент начала выполнения инструкции.

  • SERIALIZABLE
    Указывает следующее.

    • Инструкции не могут считывать данные, которые были изменены другими транзакциями, но еще не были зафиксированы.

    • Другие транзакции не могут изменять данные, считываемые текущей транзакцией, до ее завершения.

    • Другие транзакции не могут вставлять новые строки со значениями ключа, которые входят в диапазон ключей, считываемых инструкциями текущей транзакции, до ее завершения.

    Блокировка диапазона устанавливается в диапазоне значений ключа, соответствующих условиям поиска любой инструкции, выполненной во время транзакции. Обновление и вставка строк, удовлетворяющих инструкциям текущей транзакции, блокируется для других транзакций. Это гарантирует, что если какая-либо инструкция транзакции выполняется повторно, она будет считывать тот же самый набор строк. Блокировки диапазона сохраняются до завершения транзакции. Это самый строгий уровень изоляции, поскольку он блокирует целые диапазоны ключей и сохраняет блокировку до завершения транзакции. Из-за низкого параллелизма этот параметр рекомендуется использовать только при необходимости. Этот параметр действует так же, как и настройка HOLDLOCK всех таблиц во всех инструкциях SELECT в транзакции.

Замечания

Одновременно может быть установлен только один параметр уровня изоляции, который продолжает действовать для текущего соединения до тех пор, пока не будет явно изменен. Все операции считывания, выполняемые в рамках транзакции, функционируют в соответствии с правилами уровня изоляции, если только табличное указание в предложении FROM инструкции не задает другое поведение блокировки или управления версиями строк для таблицы.

Уровни изоляции транзакции определяют тип блокировки, применяемый к операциям считывания. Совмещаемые блокировки, применяемые для READ COMMITTED или REPEATABLE READ, как правило, являются блокировками строк, но при этом, если в процессе считывания идет обращение к большому числу строк, блокировка строк может быть расширена до блокировки страниц или таблиц. Если строка была изменена транзакцией после считывания, для защиты такой строки транзакция применяет монопольную блокировку, которая сохраняется до завершения транзакции. Например, если транзакция REPEATABLE READ имеет разделяемую блокировку строки и при этом изменяет ее, совмещаемая блокировка преобразуется в монопольную.

В любой момент транзакции можно переключиться с одного уровня изоляции на другой, однако есть одно исключение. Это смена уровня изоляции на уровень изоляции SNAPSHOT. Такая смена приводит к ошибке и откату транзакции. Однако для транзакции, которая была начата с уровнем изоляции SNAPSHOT, можно установить любой другой уровень изоляции.

Когда для транзакции изменяется уровень изоляции, ресурсы, которые считываются после изменения, защищаются в соответствии с правилами нового уровня. Ресурсы, которые считываются до изменения, остаются защищенными в соответствии с правилами предыдущего уровня. Например, если для транзакции уровень изоляции изменяется с READ COMMITTED на SERIALIZABLE, то совмещаемые блокировки, полученные после изменения, будут удерживаться до завершения транзакции.

Если инструкция SET TRANSACTION ISOLATION LEVEL использовалась в хранимой процедуре или триггере, то при возврате управления из них уровень изоляции будет изменен на тот, который действовал на момент их вызова. Например, если уровень изоляции REPEATABLE READ устанавливается в пакете, а пакет затем вызывает хранимую процедуру, которая меняет уровень изоляции на SERIALIZABLE, при возвращении хранимой процедурой управления пакету, настройки уровня изоляции меняются назад на REPEATABLE READ.

ПримечаниеПримечание

Определяемые пользователем функции и типы данных среды CLR не могут выполнять инструкцию SET TRANSACTION ISOLATION LEVEL. Однако уровень изоляции можно переопределить с помощью табличного указания. Дополнительные сведения см. в разделе Табличные указания (Transact-SQL).

Если для привязки двух сеансов используется процедура sp_bindsession, каждый сеанс сохраняет свои настройки уровня изоляции. Применение инструкции SET TRANSACTION ISOLATION LEVEL для изменения настройки уровня изоляции одного сеанса не повлияет на настройки других сеансов, привязанных к нему.

Инструкция SET TRANSACTION ISOLATION LEVEL работает во время выполнения, но не во время синтаксического анализа.

Оптимизированные операции массовой загрузки, работающие с кучами, блокируют запросы, которые выполняются со следующими уровнями изоляции:

  • SNAPSHOT

  • READ UNCOMMITTED

  • READ COMMITTED с использованием управления версиями строк

Обратное также верно — запросы, которые выполняются с этими уровнями изоляции, блокируют оптимизированные операции массовой загрузки, работающие с кучами. Дополнительные сведения об операциях массовой загрузки см. в разделе Массовый импорт и экспорт данных (SQL Server).

Базы данных с поддержкой FILESTREAM поддерживают следующие уровни изоляции транзакций.

Уровень изоляции

Доступ с помощью Transact-SQL

Доступ к файловой системе

уровень изоляции read uncommited

SQL Server 2012

Не поддерживается

Уровень изоляции read committed

SQL Server 2012

SQL Server 2012

Уровень изоляции repeatable read

SQL Server 2012

Не поддерживается

Уровень изоляции сериализуемый

SQL Server 2012

Не поддерживается

Моментальный снимок с уровнем изоляции read commited

SQL Server 2012

SQL Server 2012

Уровень изоляции моментальных снимков

SQL Server 2012

SQL Server 2012

Примеры

В следующем примере устанавливается уровень изоляции TRANSACTION ISOLATION LEVEL для сеанса. Для каждой последующей инструкции Transact-SQLSQL Server сохраняет все совмещаемые блокировки до конца транзакции.

USE AdventureWorks2012;
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
BEGIN TRANSACTION;
GO
SELECT * 
    FROM HumanResources.EmployeePayHistory;
GO
SELECT * 
    FROM HumanResources.Department;
GO
COMMIT TRANSACTION;
GO

См. также

Справочник

ALTER DATABASE (Transact-SQL)

DBCC USEROPTIONS (Transact-SQL)

SELECT (Transact-SQL)

Инструкции SET (Transact-SQL)

Табличные указания (Transact-SQL)