Set isolation level transaction: SET TRANSACTION ISOLATION LEVEL (Transact-SQL) — SQL Server

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

• Статья
• 09/26/2022
• Чтение занимает 9 мин

Область применения: SQL Server (все поддерживаемые версии) База данных SQL Azure Управляемый экземпляр SQL Azure Azure Synapse Analytics Analytics Platform System (PDW)

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

Синтаксические обозначения в Transact-SQL

Синтаксис

-- Syntax for SQL Server and Azure SQL Database
  
SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }

-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse
  
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Примечание

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

Примечание

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

Аргументы

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

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

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

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

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

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

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

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

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

Важно!

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

Примечание

Изоляция моментальных снимков поддерживает данные 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 работает во время выполнения, но не во время синтаксического анализа.

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

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

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

Примеры

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

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)

На пути к правильным SQL транзакциям (Часть 1) / Хабр

Мне часто приходилось сталкиваться с тем, что люди прекрасно понимают, что такое транзакции в базе данных и для чего они нужны, но при этом не всегда умеют ими правильно пользоваться. Безусловно, для достижения 80-го уровня сакрального знания нужно иметь не один год опыта и прочесть множество толстенных книг по SQL. Поэтому в этой статье я даже не буду пытаться описать всё, что может быть связано с транзакциями в MS SQL. Я хочу затронуть один простой, но очень важный вопрос, который разработчики часто упускают из вида – уровни изоляции транзакций.
Несмотря на то, что тема очень проста, во многих источниках она освящается плохо – информации либо очень мало, либо очень много. Т.е. прочитав 5-6 кратких теоретических определений невозможно их применить на практике. Для уверенного понимания предмета статьи нужно обращаться к специализированной литературе, но там информации на столько много, что далеко не каждый может уделить необходимое время для её усваивания.
Сегодня я хочу поделиться своим простым рецептом, который помог мне раз и на всегда запомнить особенности уровней изоляции транзакций и по сей день помогает без проблем принимать взвешенные решения о выборе необходимого уровня.

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

Побочные эффекты параллелизма

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

• Потерянное обновление (lost update)
• «Грязное» чтение (dirty read)
• Неповторяющееся чтение (non-repeatable read)
• Фантомное чтение (phantom reads)

Далее, эти эффекты рассматриваются подробно и приводятся SQL скрипты, показывающие проблему на практике. Я настоятельно рекомендую попробовать выполнить их и увидеть проблему «в живую», но для этого нужно сначала подготовить ваш сервер. Шаги по подготовки и особенности запуска скриптов описаны ниже.

Требования для запуска скриптов

1. Первым нужно запускать скрипт для транзакции №1, а затем сразу же скрипт для транзакции №2 (не позднее чем через 10 секунд после начала выполнения первого скрипта).
2. В базе должна существовать таблица с именем Table1 и колонками Id и Value. В ней ожидается наличие одной строки:
   
   Для создания таблицы и наполнения её данными можно запустить следующий скрипт.

   IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = 'Table1'))
   	DROP TABLE Table1
   CREATE TABLE Table1  (Id INT IDENTITY, Value INT)
   INSERT INTO Table1 (Value) VALUES(1)
   

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

Потерянное обновление (lost update)

Эффект проявляется при одновременном изменении одного блока данных разными транзакциями. Причём одно из изменений может теряться.
Данная формулировка может по-разному интерпретироваться.

Потерянное обновление – Интерпретация №1

Две транзакции выполняют одновременно UPDATE для одной и той же строки, и изменения, сделанные одной транзакцией, затираются другой.

UPDATE Table1 
SET Value = Value + 5
WHERE Id = 1;
SELECT Value 
FROM Table1
WHERE Id = 1;

UPDATE Table1 
SET Value = Value + 7
WHERE Id = 1;
SELECT Value 
FROM Table1
WHERE Id = 1;

Почему так происходит?

Прежде чем выполнить обновление, обе транзакции читают значение в колонке Value – оно равно 1. Предположим, что транзакция 2 успевает записать значение первой, тогда новое значение в колонке Value будет 8 (1+7). Затем транзакция 1 так же вычисляет новое значение, но для расчёта использует ранее вычитанное значение (1). В итоге после завершения транзакции 1 в колонке Value окажется 6 (1+5), а не 13 (1+7+5).
К счастью в MS SQL данный сценарий невозможен, потому что даже самый низкий уровень изоляции предотвращает такую ситуацию и результатом всегда будет 13, а не 8.

Потерянное обновление – Интерпретация №2

Сценарий аналогичен первому, но значение Value вычитывается во временную переменную.

BEGIN TRAN;
DECLARE @Value INT;
SELECT @Value = Value
FROM Table1
WHERE Id = 1;
WAITFOR DELAY '00:00:10';
UPDATE Table1 
SET Value = @Value + 5
WHERE Id = 1;
COMMIT TRAN;
SELECT Value 
FROM Table1
WHERE Id = 1;

BEGIN TRAN;
DECLARE @Value INT;
SELECT @Value = Value
FROM Table1
WHERE Id = 1;
UPDATE Table1 
SET Value = @Value + 7
WHERE Id = 1;
COMMIT TRAN;
SELECT Value 
FROM Table1
WHERE Id = 1;

«Грязное» чтение (dirty read)

Это такое чтение, при котором могут быть считаны добавленные или изменённые данные из другой транзакции, которая впоследствии не подтвердится (откатится).
Так как данный эффект возможен только при минимальном уровне изоляции, а по умолчанию используется более высокий уровень изоляции (READ COMMITTED), то в скрипте чтения данных уровень изоляции будет явно установлен как READ UNCOMMITTED. Если вернуть уровень изоляции по умолчанию (READ COMMITTED) для транзакции 2, то поведение поменяется.

BEGIN TRAN;
UPDATE Table1
SET Value = Value * 10
WHERE Id = 1;
WAITFOR DELAY '00:00:10';
ROLLBACK;
SELECT Value 
FROM Table1
WHERE Id = 1;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
--SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRAN;
SELECT Value 
FROM Table1
WHERE Id = 1;
COMMIT TRAN;

Мы видим, что внутри второй транзакции было вычитано значение 10, которое никогда не было успешно сохранено в базу (оно было отклонено командой ROLLBACK).

Неповторяющееся чтение (non-repeatable read)

Проявляется, когда при повторном чтении в рамках одной транзакции, ранее прочитанные данные, оказываются изменёнными. Данный эффект может наблюдаться при уровне изоляции ниже, чем REPEATABLE READ.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
--SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN;
SELECT Value 
FROM Table1
WHERE Id = 1;
WAITFOR DELAY '00:00:10';
SELECT Value 
FROM Table1
WHERE Id = 1;
COMMIT;

BEGIN TRAN;
UPDATE Table1 
SET Value = 42
WHERE Id = 1;
COMMIT TRAN;

Фантомное чтение (phantom reads)

Можно наблюдать, когда одна транзакция в ходе своего выполнения несколько раз выбирает множество строк по одним и тем же критериям. При этом другая транзакция в интервалах между этими выборками добавляет или удаляет строки, или изменяет столбцы некоторых строк, используемых в критериях выборки первой транзакции, и успешно заканчивается. В результате получится, что одни и те же выборки в первой транзакции дают разные множества строк. Данный эффект можно наблюдать, когда уровень изоляции ниже чем SERIALIZABLE.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE  
BEGIN TRAN;
SELECT * FROM Table1  
WAITFOR DELAY '00:00:10'  
SELECT * FROM Table1
COMMIT;

BEGIN TRAN;
INSERT INTO Table1 (Value)
VALUES(100)
COMMIT TRAN;

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

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

(*) – эффект присутствует только в случае, если он трактуется согласно описанию в разделе «Потерянное обновление – Интерпретация №2».
(**) – для данных уровней изоляция достигается не при помощи блокировок, а при помощи создания копии изменяемых данных, которые на время транзакции помещаются в tempdb; подробней тут.

Заключение

Теперь, разобравшись в назначении каждого уровня, вы уже готовы к более осмысленному использованию транзакций. Но я бы не останавливался на достигнутом. Во второй части статьи, материал будет представлять чуть меньшую практическую ценность, но при этом он не будет менее полезный. Когда-то Ли Кэмпбел однажды отлично сказал: «Вы должны понимать как минимум на один уровень абстракции ниже того уровня, на котором программируете». Именно поэтому, понимание реализации позволит максимально глубоко разобраться в теме и вы сможете правильно и эффективно пользоваться предлагаемым инструментом.

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

• Статья
• 24. 08.2022
• 10 минут на чтение

Применимо к: SQL Server (все поддерживаемые версии) База данных SQL Azure Управляемый экземпляр Azure SQL Аналитика синапсов Azure Система аналитической платформы (PDW)

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

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

Синтаксис

 -- Синтаксис для SQL Server и базы данных SQL Azure
  
УСТАНОВИТЬ УРОВЕНЬ ИЗОЛЯЦИИ ТРАНЗАКЦИИ
    { ЧИТАТЬ БЕЗ СООТВЕТСТВИЯ
    | ПРОЧИТАТЬ СОВЕРШЕНО
    | ПОВТОРЯЕМОЕ ЧТЕНИЕ
    | СНИМОК
    | СЕРИАЛИЗУЕМЫЙ
    }
 

 -- Синтаксис для Azure Synapse Analytics и хранилища параллельных данных
  
УСТАНОВИТЬ УРОВЕНЬ ИЗОЛЯЦИИ ТРАНЗАКЦИИ READ UNCOMMITTED
 

Примечание

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

Примечание

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

Аргументы

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

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

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

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

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

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

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

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

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

Важно

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

Примечание

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

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

Примечание

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

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

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

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

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

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

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

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

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

Примечание

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

SERIALIZABLE
Задает следующее:

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

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

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

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

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

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

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

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

Если вы выдаете SET TRANSACTION ISOLATION LEVEL в хранимой процедуре или триггере, когда объект возвращает управление, уровень изоляции сбрасывается до уровня, действовавшего при вызове объекта. Например, если вы установили ПОВТОРЯЕМОЕ ЧТЕНИЕ в пакете, а пакет затем вызывает хранимую процедуру, которая устанавливает уровень изоляции в ПОСЛЕДОВАТЕЛЬНЫЙ, параметр уровня изоляции возвращается к ПОВТОРЯЕМОЕ ЧТЕНИЕ, когда хранимая процедура возвращает управление пакету.

Примечание

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

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

УСТАНОВИТЬ УРОВЕНЬ ИЗОЛЯЦИИ ТРАНЗАКЦИИ вступает в силу во время выполнения или выполнения, а не во время синтаксического анализа.

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

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

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

Примеры

В следующем примере задается УРОВЕНЬ ИЗОЛЯЦИИ ТРАНЗАКЦИИ для сеанса. Для каждой следующей инструкции Transact-SQL SQL Server удерживает все общие блокировки до конца транзакции.

 ИСПОЛЬЗОВАТЬ AdventureWorks2012;
ИДТИ
УСТАНОВИТЬ УРОВЕНЬ ИЗОЛЯЦИИ ТРАНЗАКЦИИ ПОВТОРЯЕМОЕ ЧТЕНИЕ;
ИДТИ
НАЧАТЬ СДЕЛКУ;
ИДТИ
ВЫБРАТЬ *
    ИЗ HumanResources.EmployeePayHistory;
ИДТИ
ВЫБРАТЬ *
    ОТ HumanResources.Department;
ИДТИ
СОВЕРШИТЬ СДЕЛКУ;
ИДТИ
 

См. также

ALTER DATABASE (Transact-SQL)
DBCC USEROPTIONS (Transact-SQL)
SELECT (Transact-SQL)
Операторы SET (Transact-SQL)
Table Hints (Transact-SQL)

MySQL :: MySQL 8.0 Справочное руководство :: 13.3.7 Оператор SET TRANSACTION

версия 8.0

5.7
5.6

8,0 Японский
5. 6 Японский

13.3.7 Заявление SET TRANSACTION

 SET [GLOBAL | СЕССИЯ] ТРАНЗАКЦИЯ
      характеристика_транзакции  [  характеристика_транзакции  ] ...
  transaction_characteristic  : {
    УРОВЕНЬ ИЗОЛЯЦИИ  уровень 
  |  режим_доступа 
}
  уровень  : {
     ПОВТОРЯЕМОЕ ЧТЕНИЕ
   | ПРОЧИТАТЬ СОВЕРШЕНО
   | ЧИТАТЬ БЕЗ ЗАЯВЛЕНИЙ
   | СЕРИАЛИЗУЕМЫЙ
}
  режим_доступа  : {
     ЧИТАЙ ПИШИ
   | ТОЛЬКО ЧТЕНИЕ
} 

Это заявление указывает сделка характеристики. Он принимает список из одной или нескольких характеристик значения, разделенные запятыми. Каждое значение характеристики задает изоляция транзакций уровень или режим доступа. Уровень изоляции используется для операций над таблицами InnoDB . режим доступа указывает, работают ли транзакции в режиме чтения/записи. или только для чтения.

Кроме того, SET TRANSACTION может включите дополнительный GLOBAL или Ключевое слово SESSION для указания области действия утверждение.

• Уровни изоляции транзакций

• Режим доступа к транзакциям

• Область действия характеристик

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

Уровень изоляции по умолчанию ПОВТОРЯЕМОЕ ЧТЕНИЕ . Другой допустимые значения: ЧТЕНИЕ СОВЕРШЕНО , ЧТЕНИЕ БЕЗОПАСНЫЙ и СЕРИАЛИЗУЕМЫЙ . Для информации об этих уровнях изоляции см. Раздел 15.7.2.1, «Уровни изоляции транзакций».

Режим доступа к транзакции

Чтобы установить режим доступа к транзакции, используйте команду READ. WRITE или READ ONLY . это не разрешено указывать несколько пунктов режима доступа в тот же оператор SET TRANSACTION .

По умолчанию транзакция выполняется в режиме чтения/записи с как чтение, так и запись разрешены для таблиц, используемых в сделка. Этот режим может быть указан явно с помощью УСТАНОВИТЬ ТРАНЗАКЦИЮ с доступом режим ЧТЕНИЕ ЗАПИСЬ .

Если установлен режим доступа к транзакциям READ ТОЛЬКО , изменения в таблицах запрещены. Это может позволяют механизмам хранения повышать производительность, возможно, когда запись не разрешена.

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

ЧТЕНИЕ ЗАПИСЬ и ЧТЕНИЕ ТОЛЬКО режимов доступа также могут быть указаны для индивидуальная сделка с использованием СТАРТ Выписка по ТРАНЗАКЦИИ .

Область действия характеристики

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

• С ключевым словом GLOBAL :

• С ключевым словом SESSION :

  • Заявление распространяется на все последующие транзакции выполняется в рамках текущего сеанса.

  • Заявление разрешено внутри транзакций, но не не влияет на текущую текущую транзакцию.

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

• Без каких-либо SESSION или ГЛОБАЛЬНОЕ ключевое слово:

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

  • Последующие транзакции возвращаются к использованию сеанса значение названных характеристик.

  • Оператор не разрешен в транзакциях:

     mysql> НАЧАТЬ ТРАНЗАКЦИЮ;
    Запрос выполнен успешно, затронуто 0 строк (0,02 сек.)
    mysql> УСТАНОВИТЬ УРОВЕНЬ ИЗОЛЯЦИИ ТРАНЗАКЦИИ SERIALIZABLE;
    ОШИБКА 1568 (25001): характеристики транзакции не могут быть изменены
    пока выполняется транзакция 

Изменение глобальных характеристик транзакции требует CONNECTION_ADMIN привилегия (или устаревшая привилегия SUPER ). Любой сеанс может свободно изменять свои характеристики сеанса (даже в середине транзакции) или характеристики для ее следующей транзакции (до начала этой транзакции).

Чтобы установить глобальный уровень изоляции при запуске сервера, используйте --transaction-isolation= уровень параметр в командной строке или в файле параметров. значения уровень для этой опции используйте тире а не пробелы, поэтому допустимые значения ЧТЕНИЕ-НЕЗАПИСАННЫЙ , ЧТЕНИЕ СОВЕРШЕНО , ПОВТОРЯЕМОЕ ЧТЕНИЕ , или СЕРИАЛИЗУЕМЫЙ .

Аналогично, чтобы установить режим доступа к глобальной транзакции на сервере запуск, используйте --транзакция только для чтения опция. По умолчанию OFF (режим чтения/записи), но значение может быть установлено на для режима чтения Только.

Например, чтобы установить уровень изоляции на ПОВТОРЯЕМОЕ ЧТЕНИЕ и режим доступа к READ WRITE , используйте эти строки в [mysqld] раздел файла опций:

 [mysqld]
изоляция транзакций = ПОВТОРЯЕМОЕ-ЧТЕНИЕ
транзакция только для чтения = ВЫКЛ 

Во время выполнения характеристики в глобальном, сеансовом и уровни области действия следующей транзакции могут быть установлены косвенно с помощью Оператор SET TRANSACTION , как описано ранее. Их также можно установить непосредственно с помощью НАБОР оператор для присвоения значений transaction_isolation и transaction_read_only система переменные:

• SET TRANSACTION разрешает опционально GLOBAL и SESSION ключевые слова для установки транзакции характеристики на разных уровнях охвата.

• НАБОР оператор для присвоения значений transaction_isolation и транзакция_только для чтения системные переменные имеют синтаксис для установки этих переменных в разные уровни охвата.

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

Таблица 13.9 Синтаксис SET TRANSACTION для характеристик транзакции

Таблица 13. 10 Синтаксис SET для характеристик транзакции

Синтаксис	Затронутая область характеристик
НАБОР ГЛОБАЛЬНЫХ имя_переменной = значение	Глобальный
НАБОР @@GLOBAL. имя_переменной = значение	Глобальный
НАБОР СОХРАНЯЕТСЯ имя_переменной = значение	Глобальный
НАБОР @@ПЕРСИСТ. имя_переменной = значение	Глобальный
SET PERSIST_ONLY var_name = значение	Нет эффекта времени выполнения
НАБОР @@PERSIST_ONLY. имя_переменной = значение	Нет эффекта времени выполнения
НАСТРОЙКА СЕССИИ имя_переменной = значение	Сессия
НАБОР @@СЕССИЯ. имя_переменной = значение	Сессия
НАБОР имя_переменной = значение	Сессия
НАБОР @@ имя_переменной = значение	Только следующая транзакция

Можно проверить глобальные и сеансовые значения характеристики транзакции во время выполнения:

 ВЫБЕРИТЕ @@GLOBAL.