Статья
04/01/2012

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 использует управление версиями строк для представления каждой инструкции согласованного на уровне транзакций моментального снимка данных в том виде, который они имели на момент начала выполнения инструкции. Для защиты данных от обновления другими транзакциями блокировки не используются. В SQL Server 2008 R2 изоляция моментальных снимков расширена, теперь она поддерживает данные FILESTREAM. В режиме изоляции моментальных снимков данные FILESTREAM, прочитанные любой инструкцией транзакции, будут согласованы на уровне транзакции с версией данных, существовавших в ее начале.

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

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

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

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

При установке параметра 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).

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

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

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

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

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

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

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

Уровень изоляции	Доступ с помощью Transact-SQL	Доступ к файловой системе
уровень изоляции read uncommited	SQL Server 2008	Не поддерживается
уровень изоляции read committed	SQL Server 2008	SQL Server 2008
Повторяющиеся операции чтения	SQL Server 2008	Не поддерживается
Упорядочиваемые	SQL Server 2008	Не поддерживается
Моментальный снимок с уровнем изоляции read commited	SQL Server 2008 R2	SQL Server 2008 R2
Моментальный снимок	SQL Server 2008 R2	SQL Server 2008 R2

Примеры

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

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