SET TRANSACTION ISOLATION LEVEL (Transact-SQL)SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

ОБЛАСТЬ ПРИМЕНЕНИЯ:даSQL Server (начиная с 2008)даБаза данных SQL AzureдаХранилище данных SQL AzureдаParallel Data WarehouseAPPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

Управляет поведением блокировки и версиями строк инструкций Transact-SQLTransact-SQL, выданных при соединении с SQL ServerSQL Server.Controls the locking and row versioning behavior of Transact-SQLTransact-SQL statements issued by a connection to SQL ServerSQL Server.

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

СинтаксисSyntax

-- Syntax for SQL Server and Azure SQL Database

SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

АргументыArguments

READ UNCOMMITTEDREAD UNCOMMITTED
Указывает, что инструкции могут считывать строки, которые были изменены другими транзакциями, но еще не были зафиксированы.Specifies that statements can read rows that have been modified by other transactions but not yet committed.

Транзакции, работающие на уровне READ UNCOMMITTED, не используют совмещаемые блокировки, чтобы предотвратить изменение считываемых текущей транзакцией данных другими транзакциями.Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. Транзакции READ UNCOMMITTED также не блокируются монопольными блокировками, которые не позволили бы текущей транзакции считывать измененные другими транзакциями, но не зафиксированные строки.READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. Установка этого параметра позволяет считывать незафиксированные изменения, которые называются чтением«грязных» данных.When this option is set, it is possible to read uncommitted modifications, which are called dirty reads. Значения в данных могут быть изменены и до окончания транзакции строки могут появляться и исчезать в наборе данных.Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. Этот параметр действует так же, как и настройка NOLOCK всех таблиц во всех инструкциях SELECT в транзакции.This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. Это наименьшее ограничение уровней изоляции.This is the least restrictive of the isolation levels.

В SQL ServerSQL Server конфликты блокировок при защите транзакций от чтения «грязных» данных незафиксированных изменений данных можно сократить с помощью следующего:In SQL ServerSQL Server, you can also minimize locking contention while protecting transactions from dirty reads of uncommitted data modifications using either:

  • уровня изоляции READ COMMITTED с параметром базы данных READ_COMMITTED_SNAPSHOT, находящимся в состоянии ON;The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set to ON.

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

    READ COMMITTEDREAD COMMITTED
    Указывает, что инструкции не могут считывать данные, которые были изменены другими транзакциями, но еще не были зафиксированы.Specifies that statements cannot read data that has been modified but not committed by other transactions. Это предотвращает чтение«грязных» данных.This prevents dirty reads. Данные могут быть изменены другими транзакциями между отдельными инструкциями в текущей транзакции, результатом чего будет неповторяемое чтение или фантомные данные.Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. Этот параметр в SQL ServerSQL Server установлен по умолчанию.This option is the SQL ServerSQL Server default.

    Поведение READ COMMITTED зависит от настройки аргумента базы данных READ_COMMITTED_SNAPSHOT.The behavior of READ COMMITTED depends on the setting of the READ_COMMITTED_SNAPSHOT database option:

  • Если параметр READ_COMMITTED_SNAPSHOT находится в состоянии OFF (по умолчанию), компонент Компонент Database EngineDatabase Engine при выполнении операций считывания текущей транзакцией использует совмещаемые блокировки для предотвращения изменения строк другими транзакциями.If READ_COMMITTED_SNAPSHOT is set to OFF (the default), the Компонент Database EngineDatabase Engine uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation. Совмещаемые блокировки также блокируют инструкции от считывания строк, измененных другими транзакциями, пока не завершится другая транзакция.The shared locks also block the statement from reading rows modified by other transactions until the other transaction is completed. От типа совмещаемой блокировки зависит время ее освобождения.The shared lock type determines when it will be released. Блокировка строки освобождается перед обработкой следующей строки.Row locks are released before the next row is processed. Блокировка страницы освобождается при чтении следующей страницы, а блокировка таблицы освобождается при завершении выполнения инструкции.Page locks are released when the next page is read, and table locks are released when the statement finishes.

    Примечание

    Если параметр READ_COMMITTED_SNAPSHOT находится в состоянии ON, компонент Компонент Database EngineDatabase Engine использует управление версиями строк для представления каждой инструкции согласованного на уровне транзакций моментального снимка данных в том виде, который они имели на момент начала выполнения инструкции.If READ_COMMITTED_SNAPSHOT is set to ON, the Компонент Database EngineDatabase Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Для защиты данных от обновления другими транзакциями блокировки не используются.Locks are not used to protect the data from updates by other transactions.

    Изоляция моментальных снимков поддерживает данные FILESTREAM.Snapshot isolation supports FILESTREAM data. В режиме изоляции моментальных снимков данные FILESTREAM, считанные любой инструкцией транзакции, будут согласованы на уровне транзакции с версией данных, существовавших на момент начала транзакции.Under snapshot isolation mode, FILESTREAM data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction.

    Если параметр базы данных READ_COMMITTED_SNAPSHOT имеет значение ON, для запроса совмещаемой блокировки можно использовать табличное указание READCOMMITTEDLOCK вместо управления версиями строк для отдельных инструкций в транзакциях, работающих на уровне изоляции READ COMMITTED.When the READ_COMMITTED_SNAPSHOT database option is ON, you can use the READCOMMITTEDLOCK table hint to request shared locking instead of row versioning for individual statements in transactions running at the READ COMMITTED isolation level.

Примечание

При установке параметра READ_COMMITTED_SNAPSHOT разрешается только то соединение с базой данных, которое выполняет команду ALTER DATABASE.When you set the READ_COMMITTED_SNAPSHOT option, only the connection executing the ALTER DATABASE command is allowed in the database. До завершения инструкции ALTER DATABASE в базе данных не должно быть других открытых соединений.There must be no other open connection in the database until ALTER DATABASE is complete. База данных не обязательно должна находиться в однопользовательском режиме.The database does not have to be in single-user mode.

REPEATABLE READREPEATABLE READ
Указывает на то, что инструкции не могут считывать данные, которые были изменены, но еще не зафиксированы другими транзакциями, а также на то, что другие транзакции не могут изменять данные, читаемые текущей транзакцией, до ее завершения.Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.

Совмещаемые блокировки применяются ко всем данным, считываемым любой инструкцией транзакции, и сохраняются до ее завершения.Shared locks are placed on all data read by each statement in the transaction and are held until the transaction completes. Это запрещает другим транзакциям изменять строки, считываемые текущей транзакцией.This prevents other transactions from modifying any rows that have been read by the current transaction. Другие транзакции могут вставлять новые строки, соответствующие условиям поиска инструкций, содержащихся в текущей транзакции.Other transactions can insert new rows that match the search conditions of statements issued by the current transaction. При повторном запуске инструкции текущей транзакцией будут извлечены новые строки, что приведет к фантомному чтению.If the current transaction then retries the statement it will retrieve the new rows, which results in phantom reads. Учитывая то, что совмещаемые блокировки сохраняются до завершения транзакции и не снимаются в конце каждой инструкции, степень совпадений ниже, чем при уровне изоляции по умолчанию READ COMMITTED.Because shared locks are held to the end of a transaction instead of being released at the end of each statement, concurrency is lower than the default READ COMMITTED isolation level. Используйте этот параметр только в случае необходимости.Use this option only when necessary.

SNAPSHOTSNAPSHOT
Указывает на то, что данные, считанные любой инструкцией транзакции, будут согласованы на уровне транзакции с версией данных, существовавших в ее начале.Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction. Транзакция распознает только те изменения, которые были зафиксированы до ее начала.The transaction can only recognize data modifications that were committed before the start of the transaction. Инструкции, выполняемые текущей транзакцией, не видят изменений данных, произведенных другими транзакциями после запуска текущей транзакции.Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. Таким образом достигается эффект получения инструкциями в транзакции моментального снимка зафиксированных данных на момент запуска транзакции.The effect is as if the statements in a transaction get a snapshot of the committed data as it existed at the start of the transaction.

Транзакции моментальных снимков не требуют блокировки при считывании данных, за исключением случаев восстановления базы данных.Except when a database is being recovered, SNAPSHOT transactions do not request locks when reading data. Считывание данных транзакциями моментальных снимков не блокирует запись данных другими транзакциями.SNAPSHOT transactions reading data do not block other transactions from writing data. Транзакции, осуществляющие запись данных, не блокируют считывание данных транзакциями моментальных снимков.Transactions writing data do not block SNAPSHOT transactions from reading data.

На этапе отката восстановления базы данных транзакция моментальных снимков запросит блокировку, если будет предпринята попытка считывания данных, заблокированных другой откатываемой транзакцией.During the roll-back phase of a database recovery, SNAPSHOT transactions will request a lock if an attempt is made to read data that is locked by another transaction that is being rolled back. Блокировка транзакции моментальных снимков сохраняется до завершения отката.The SNAPSHOT transaction is blocked until that transaction has been rolled back. Блокировка снимается сразу после предоставления.The lock is released immediately after it has been granted.

Перед запуском транзакции, использующей уровень изоляции моментальных снимков, необходимо установить параметр базы данных ALLOW_SNAPSHOT_ISOLATION в ON.The ALLOW_SNAPSHOT_ISOLATION database option must be set to ON before you can start a transaction that uses the SNAPSHOT isolation level. Если транзакция с уровнем изоляции моментального снимка обращается к данным из нескольких баз данных, аргумент ALLOW_SNAPSHOT_ISOLATION должен быть включен в каждой базе данных.If a transaction using the SNAPSHOT isolation level accesses data in multiple databases, ALLOW_SNAPSHOT_ISOLATION must be set to ON in each database.

Невозможно изменить на уровень изоляции моментального снимка уровень изоляции транзакции, запущенной с другим уровнем изоляции; в этом случае транзакция будет прервана.A transaction cannot be set to SNAPSHOT isolation level that started with another isolation level; doing so will cause the transaction to abort. Если транзакция запущена с уровнем изоляции моментальных снимков, ее уровень изоляции можно изменять.If a transaction starts in the SNAPSHOT isolation level, you can change it to another isolation level and then back to SNAPSHOT. Транзакция начинается в момент первого доступа к данным.A transaction starts the first time it accesses data.

Транзакция, работающая с уровнем изоляции моментального снимка, может просматривать внесенные ею изменения.A transaction running under SNAPSHOT isolation level can view changes made by that transaction. Например, если транзакция выполняет инструкцию UPDATE, а затем инструкцию SELECT для одной и той же таблицы, измененные данные будут включены в результирующий набор.For example, if the transaction performs an UPDATE on a table and then issues a SELECT statement against the same table, the modified data will be included in the result set.

Примечание

В режиме изоляции моментальных снимков данные FILESTREAM, считанные любой инструкцией транзакции, будут согласованы на уровне транзакции с версией данных, существовавших на момент начала транзакции, а не на момент начала выполнения инструкции.Under snapshot isolation mode, FILESTREAM data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction, not at the start of the statement.

SERIALIZABLESERIALIZABLE
Указывает следующее.Specifies the following:

  • Инструкции не могут считывать данные, которые были изменены другими транзакциями, но еще не были зафиксированы.Statements cannot read data that has been modified but not yet committed by other transactions.

  • Другие транзакции не могут изменять данные, считываемые текущей транзакцией, до ее завершения.No other transactions can modify data that has been read by the current transaction until the current transaction completes.

  • Другие транзакции не могут вставлять новые строки со значениями ключа, которые входят в диапазон ключей, считываемых инструкциями текущей транзакции, до ее завершения.Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

    Блокировка диапазона устанавливается в диапазоне значений ключа, соответствующих условиям поиска любой инструкции, выполненной во время транзакции.Range locks are placed in the range of key values that match the search conditions of each statement executed in a transaction. Обновление и вставка строк, удовлетворяющих инструкциям текущей транзакции, блокируется для других транзакций.This blocks other transactions from updating or inserting any rows that would qualify for any of the statements executed by the current transaction. Это гарантирует, что если какая-либо инструкция транзакции выполняется повторно, она будет считывать тот же самый набор строк.This means that if any of the statements in a transaction are executed a second time, they will read the same set of rows. Блокировки диапазона сохраняются до завершения транзакции.The range locks are held until the transaction completes. Это самый строгий уровень изоляции, поскольку он блокирует целые диапазоны ключей и сохраняет блокировку до завершения транзакции.This is the most restrictive of the isolation levels because it locks entire ranges of keys and holds the locks until the transaction completes. Из-за низкого параллелизма этот параметр рекомендуется использовать только при необходимости.Because concurrency is lower, use this option only when necessary. Этот параметр действует так же, как и настройка HOLDLOCK всех таблиц во всех инструкциях SELECT в транзакции.This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.

RemarksRemarks

Одновременно может быть установлен только один параметр уровня изоляции, который продолжает действовать для текущего соединения до тех пор, пока не будет явно изменен.Only one of the isolation level options can be set at a time, and it remains set for that connection until it is explicitly changed. Все операции считывания, выполняемые в рамках транзакции, функционируют в соответствии с правилами уровня изоляции, если только табличное указание в предложении FROM инструкции не задает другое поведение блокировки или управления версиями строк для таблицы.All read operations performed within the transaction operate under the rules for the specified isolation level unless a table hint in the FROM clause of a statement specifies different locking or versioning behavior for a table.

Уровни изоляции транзакции определяют тип блокировки, применяемый к операциям считывания.The transaction isolation levels define the type of locks acquired on read operations. Совмещаемые блокировки, применяемые для READ COMMITTED или REPEATABLE READ, как правило, являются блокировками строк, но при этом, если в процессе считывания идет обращение к большому числу строк, блокировка строк может быть расширена до блокировки страниц или таблиц.Shared locks acquired for READ COMMITTED or REPEATABLE READ are generally row locks, although the row locks can be escalated to page or table locks if a significant number of the rows in a page or table are referenced by the read. Если строка была изменена транзакцией после считывания, для защиты такой строки транзакция применяет монопольную блокировку, которая сохраняется до завершения транзакции.If a row is modified by the transaction after it has been read, the transaction acquires an exclusive lock to protect that row, and the exclusive lock is retained until the transaction completes. Например, если транзакция REPEATABLE READ имеет разделяемую блокировку строки и при этом изменяет ее, совмещаемая блокировка преобразуется в монопольную.For example, if a REPEATABLE READ transaction has a shared lock on a row, and the transaction then modifies the row, the shared row lock is converted to an exclusive row lock.

В любой момент транзакции можно переключиться с одного уровня изоляции на другой, однако есть одно исключение.With one exception, you can switch from one isolation level to another at any time during a transaction. Это смена уровня изоляции на уровень изоляции SNAPSHOT.The exception occurs when changing from any isolation level to SNAPSHOT isolation. Такая смена приводит к ошибке и откату транзакции.Doing this causes the transaction to fail and roll back. Однако для транзакции, которая была начата с уровнем изоляции SNAPSHOT, можно установить любой другой уровень изоляции.However, you can change a transaction started in SNAPSHOT isolation to any other isolation level.

Когда для транзакции изменяется уровень изоляции, ресурсы, которые считываются после изменения, защищаются в соответствии с правилами нового уровня.When you change a transaction from one isolation level to another, resources that are read after the change are protected according to the rules of the new level. Ресурсы, которые считываются до изменения, остаются защищенными в соответствии с правилами предыдущего уровня.Resources that are read before the change continue to be protected according to the rules of the previous level. Например, если для транзакции уровень изоляции изменяется с READ COMMITTED на SERIALIZABLE, то совмещаемые блокировки, полученные после изменения, будут удерживаться до завершения транзакции.For example, if a transaction changed from READ COMMITTED to SERIALIZABLE, the shared locks acquired after the change are now held until the end of the transaction.

Если инструкция SET TRANSACTION ISOLATION LEVEL использовалась в хранимой процедуре или триггере, то при возврате управления из них уровень изоляции будет изменен на тот, который действовал на момент их вызова.If you issue SET TRANSACTION ISOLATION LEVEL in a stored procedure or trigger, when the object returns control the isolation level is reset to the level in effect when the object was invoked. Например, если уровень изоляции REPEATABLE READ устанавливается в пакете, а пакет затем вызывает хранимую процедуру, которая меняет уровень изоляции на SERIALIZABLE, при возвращении хранимой процедурой управления пакету, настройки уровня изоляции меняются назад на REPEATABLE READ.For example, if you set REPEATABLE READ in a batch, and the batch then calls a stored procedure that sets the isolation level to SERIALIZABLE, the isolation level setting reverts to REPEATABLE READ when the stored procedure returns control to the batch.

Примечание

Определяемые пользователем функции и типы данных среды CLR не могут выполнять инструкцию SET TRANSACTION ISOLATION LEVEL.User-defined functions and common language runtime (CLR) user-defined types cannot execute SET TRANSACTION ISOLATION LEVEL. Однако уровень изоляции можно переопределить с помощью табличного указания.However, you can override the isolation level by using a table hint. Дополнительные сведения см. в разделе Табличные указания (Transact-SQL).For more information, see Table Hints (Transact-SQL).

Если для привязки двух сеансов используется процедура sp_bindsession, каждый сеанс сохраняет свои настройки уровня изоляции.When you use sp_bindsession to bind two sessions, each session retains its isolation level setting. Применение инструкции SET TRANSACTION ISOLATION LEVEL для изменения настройки уровня изоляции одного сеанса не повлияет на настройки других сеансов, привязанных к нему.Using SET TRANSACTION ISOLATION LEVEL to change the isolation level setting of one session does not affect the setting of any other sessions bound to it.

Инструкция SET TRANSACTION ISOLATION LEVEL работает во время выполнения, но не во время синтаксического анализа.SET TRANSACTION ISOLATION LEVEL takes effect at execute or run time, and not at parse time.

Оптимизированные операции массовой загрузки, работающие с кучами, блокируют запросы, которые выполняются со следующими уровнями изоляции:Optimized bulk load operations on heaps block queries that are running under the following isolation levels:

  • SNAPSHOTSNAPSHOT

  • READ UNCOMMITTEDREAD UNCOMMITTED

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

    Обратное также верно — запросы, которые выполняются с этими уровнями изоляции, блокируют оптимизированные операции массовой загрузки, работающие с кучами.Conversely, queries that run under these isolation levels block optimized bulk load operations on heaps. Дополнительные сведения об операциях массового импорта см. в разделе Массовый импорт и экспорт данных (SQL Server).For more information about bulk load operations, see Bulk Import and Export of Data (SQL Server).

    Базы данных с поддержкой FILESTREAM поддерживают следующие уровни изоляции транзакций.FILESTREAM-enabled databases support the following transaction isolation levels.

Уровень изоляцииIsolation level Доступ с помощью Transact-SQLTransact SQL access Доступ к файловой системеFile system access
Уровень изоляции read uncommittedRead uncommitted SQL Server 2017SQL Server 2017 Не поддерживаетсяUnsupported
Уровень изоляции read committedRead committed SQL Server 2017SQL Server 2017 SQL Server 2017SQL Server 2017
Уровень изоляции repeatable readRepeatable read SQL Server 2017SQL Server 2017 Не поддерживаетсяUnsupported
Упорядочиваемый уровень изоляцииSerializable SQL Server 2017SQL Server 2017 Не поддерживаетсяUnsupported
Моментальный снимок с уровнем изоляции read commitedRead committed snapshot SQL Server 2017SQL Server 2017 SQL Server 2017SQL Server 2017
Моментальный снимокSnapshot SQL Server 2017SQL Server 2017 SQL Server 2017SQL Server 2017

ПримерыExamples

В следующем примере устанавливается уровень изоляции TRANSACTION ISOLATION LEVEL для сеанса.The following example sets the TRANSACTION ISOLATION LEVEL for the session. Для каждой последующей инструкции Transact-SQLTransact-SQL SQL ServerSQL Server сохраняет все совмещаемые блокировки до конца транзакции.For each Transact-SQLTransact-SQL statement that follows, SQL ServerSQL Server holds all of the shared locks until the end of the transaction.

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  

См. также:See Also

ALTER DATABASE (Transact-SQL) ALTER DATABASE (Transact-SQL)
DBCC USEROPTIONS (Transact-SQL) DBCC USEROPTIONS (Transact-SQL)
SELECT (Transact-SQL) SELECT (Transact-SQL)
Инструкции SET (Transact-SQL) SET Statements (Transact-SQL)
Табличные указания (Transact-SQL)Table Hints (Transact-SQL)