УСТАНОВИТЬ УРОВЕНЬ ИЗОЛЯЦИИ ТРАНЗАКЦИЙ (Transact-SQL)

Относится к:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsАналитическая платформа (PDW)SQL база данных в Microsoft Fabric

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

Соглашения о синтаксисе Transact-SQL

Синтаксис

Синтаксис для SQL Server, базы данных SQL Azure и базы данных SQL в Microsoft Fabric.

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

Синтаксис для Azure Synapse Analytics и параллельного хранилища данных.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Примечание.

Azure Synapse Analytics реализует транзакции ACID. Уровень изоляции по умолчанию .READ UNCOMMITTED Его можно изменитьREAD COMMITTED SNAPSHOT ISOLATION, переключив ONREAD_COMMITTED_SNAPSHOT параметр базы данных для пользовательской базы данных при подключении master к базе данных. После включения все транзакции в этой базе данных выполняются READ COMMITTED SNAPSHOT ISOLATION и параметр READ UNCOMMITTED на уровне сеанса не учитывается. Дополнительные сведения см. в разделе "ПАРАМЕТРЫ ALTER DATABASE SET" (Transact-SQL).

Аргументы

READ UNCOMMITTED (чтение без подтверждения)

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

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

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

• Уровень READ COMMITTED изоляции с заданным READ_COMMITTED_SNAPSHOTпараметром ON базы данных.

• Уровень SNAPSHOT изоляции. Дополнительные сведения об изоляции моментальных снимков см. в разделе Изоляция снимков в SQL Server.

ЗАФИКСИРОВАННАЯ ОПЕРАЦИЯ ЧТЕНИЯ

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

Поведение READ COMMITTED зависит от параметра READ_COMMITTED_SNAPSHOT базы данных:

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

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

  • READ_COMMITTED_SNAPSHOT ON является стандартным вариантом Azure SQL Database и SQL Database в Microsoft Fabric.

Внимание

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

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

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

Примечание.

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

ПОВТОРЯЕМОЕ ЧТЕНИЕ

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

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

МОМЕНТАЛЬНЫЙ СНИМОК

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

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

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

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

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

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

Примечание.

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

SERIALIZABLE

Указывает следующие условия:

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

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

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

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

Замечания

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

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

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

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

При возникновении 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	Доступ к файловой системе
Чтение незафиксированного	SQL Server	Не поддерживается
Чтение зафиксировано	SQL Server	SQL Server
Повторяемое чтение	SQL Server	Не поддерживается
Сериализуемый	SQL Server	Не поддерживается
Считывать зафиксированный моментальный снимок	SQL Server	SQL Server
Моментальный снимок	SQL Server	SQL Server

Примеры

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

USE AdventureWorks2022;
GO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO

BEGIN TRANSACTION;
GO

SELECT *
FROM HumanResources.EmployeePayHistory;
GO

SELECT *
FROM HumanResources.Department;
GO

COMMIT TRANSACTION;
GO

Связанный контент

• Изменить базу данных (Transact-SQL)
• DBCC USEROPTIONS (Transact-SQL)
• SELECT (Transact-SQL)
• Инструкции SET (Transact-SQL)
• Табличные подсказки (Transact-SQL)