Set transaction isolation level (transact-sql)

Транзакция между контейнерами

Транзакция называется транзакцией между контейнерами, если она:

обращается к таблице, оптимизированной для памяти, из интерпретированного Transact-SQL; или
выполняет хранимую процедуру, скомпилированную в собственном коде, если транзакция уже открыта (XACT_STATE() = 1).

Использование термина «между контейнерами» обусловлено тем, что транзакция выполняется между двумя контейнерами по управлению транзакциями (один — для дисковых таблиц, а другой — для таблиц, оптимизированных для памяти).

В рамках одной транзакции между контейнерами можно использовать разные уровни изоляции для доступа к дисковым таблицам и таблицам, оптимизированным для памяти. Это различие выражено через явные табличные указания, такие как WITH (SERIALIZABLE) или через параметр базы данных MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT, который неявно повышает уровень изоляции для оптимизированной для памяти таблицы до снимка, если TRANSACTION ISOLATION LEVEL имеет значение READ COMMITTED или READ UNCOMMITTED.

В следующем примере кода Transact-SQL:

Доступ к дисковой таблице Table_D1 осуществляется с использованием уровня изоляции READ COMMITTED.
Доступ к оптимизированной для памяти таблице Table_MO7 осуществляется с использованием уровня изоляции SERIALIZABLE. Table_MO6 не имеет определенного уровня изоляции, поскольку вставляемые элементы отличаются единообразием и выполняются в основном с изоляцией serializable.

Неповторяющееся чтение / Non-Repeatable Read

Когда одинаковый запрос в одной транзакции может вернуть разные данные; NON-REPEATABLE READ касается лишь только набора данных, который однажды уже был прочитан в транзакции — это значит, если вы прочитали первые 10 строк таблицы А, то NON-REPEATABLE READ случится, если вы заново прочитаете первые 10 строк таблицы А и получите другой результат;

Это уже менее опасный эффект, но более сложный в понимании. Допустим, одна транзакция считает полную стоимость услуги на основе тарифов, а другая меняет параллельно эти тарифы. Non-repeatable read случится, если последовательность действий будет такова:

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

Основные сведения о функционировании уровней изоляции моментального снимка и управлении версиями строк

Если включен уровень изоляции SNAPSHOT, то при обновлении каждой строки компонент SQL Server Database Engine сохраняет копию исходной строки в базе данных tempdb и добавляет в строку порядковый номер транзакции. Ниже приведены происходящие события в последовательном порядке.

Инициируется новая транзакция, и ей назначается порядковый номер.
Компонент Database Engine считывает строку внутри транзакции и получает версию строки из базы данных tempdb, чей порядковый номер наиболее близок и ниже порядкового номера транзакции.
Ядро СУБД проверяет, не находится ли порядковый номер транзакции в списке порядковых номеров незафиксированных транзакций, активных при запуске транзакции моментальных снимков.
Транзакция считывает версию строки, которая была текущей во время запуска транзакции, из базы данных tempdb. Новые строки, вставленные после запуска транзакции, не будут видны, так как эти порядковые номера будут выше, чем значение порядкового номера транзакции.
Текущая транзакция обнаруживает строки, которые были удалены после ее запуска, поскольку версия любой строки в базе данных tempdb имеет меньшее значение порядкового номера, чем транзакция.

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

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

Включение уровня изоляции транзакций моментальных снимков

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

В SQL Server Management Studio выполните следующие инструкции.

Примечание.

В этих инструкциях является заполнителем для базы данных в источнике данных, который вы хотите использовать в службах Analysis Services.
В Студии разработки бизнес-аналитики создайте проект служб Analysis Services.

Кроме того, можно открыть существующий проект служб Analysis Services.
Если вы создали новый проект служб Analysis Services на шаге 2, выполните следующие действия.
1. В Обозреватель решений щелкните правой кнопкой мыши источники данных и выберите команду «Создать источник данных».
2. В диалоговом окне «Выбор способа определения подключения» нажмите кнопку » Создать». Появится диспетчер подключений диалоговое окно.
Если вы открыли существующий проект служб Analysis Services на шаге 2, выполните следующие действия.
1. В папке «Источники данных» дважды щелкните существующий источник данных.
2. В диалоговом окне конструктора источников данных нажмите кнопку «Изменить «. Появится диспетчер подключений диалоговое окно.
В диалоговом диспетчер подключений выберите native OLE DB\SQL Native Client в списке поставщиков.
Укажите имя сервера и проверку подлинности.
Чтобы проверить подключение, щелкните «Проверить подключение».
В левой области щелкните » Все».
В области справа щелкните True в списке подключений MARS и нажмите кнопку » ОК».
В диалоговом окне конструктора источников данных щелкните «Моментальный снимок» в списке изоляции и нажмите кнопку «ОК».

Побочные эффекты параллельного выполнения транзакций

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

Потерянное обновление

При одновременном изменении одного блока данных разными транзакциями одно из изменений может теряться.

Пусть у нас в одной транзакции выполняется команда:

Oracle PL/SQL

UPDATE tbl1 SET field1 = field1 + 10;

1	UPDATEtbl1SETfield1=field1+10;

Во второй транзакции выполняется команда:

Oracle PL/SQL

UPDATE tbl1 SET field1 = field1 + 3;

1	UPDATEtbl1SETfield1=field1+3;

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

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

«Грязное» чтение

Грязное чтение возникает, когда считываются результаты другой транзакции, которая в последующем откатывается (rollback).

Транзакция 1

Транзакция 2

Oracle PL/SQL

UPDATE tbl1 SET field2=field2+1 WHERE field1=1;

1	UPDATEtbl1SETfield2=field2+1WHEREfield1=1;

Oracle PL/SQL

SELECT field2 FROM tbl1 WHERE field1=1;

1	SELECTfield2FROMtbl1WHEREfield1=1;

Oracle PL/SQL

ROLLBACK WORK;

1	ROLLBACKWORK;

Неповторяющееся чтение

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

Фантомное чтение

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

Примеры транзакций в T-SQL

Давайте рассмотрим примеры транзакций, реализованные на языке T-SQL.

Исходные данные для примеров

Но сначала нам необходимо создать тестовые данные для нашего примера.

Для этого выполните следующую инструкцию.

   
   --Создание таблицы
   CREATE TABLE Goods (
      ProductId INT IDENTITY(1,1) NOT NULL,
      ProductName VARCHAR(100) NOT NULL,
      Price MONEY NULL,
   );

   --Добавление данных в таблицу
   INSERT INTO Goods(ProductName, Price)
      VALUES ('Системный блок', 50),
             ('Клавиатура', 30),
             ('Монитор', 100);

   SELECT ProductId, ProductName, Price
   FROM Goods;

Простой пример транзакции в T-SQL

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

Поэтому мы решили эти инструкции объединить в одну транзакцию.

Сначала мы открываем транзакцию командой BEGIN TRANSACTION, далее пишем все необходимые инструкции, которые мы хотим объединить в транзакцию.

После этого командой COMMIT TRANSACTION мы сохраняем все внесенные изменения.

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

   
   BEGIN TRANSACTION

   --Инструкция 1
   UPDATE Goods SET Price = 70
   WHERE ProductId = 1;

   --Инструкция 2
   UPDATE Goods SET Price = 40
   WHERE ProductId = 2;

   COMMIT TRANSACTION

   SELECT ProductId, ProductName, Price
   FROM Goods;

Однако, если в любой из инструкций возникнет ошибка, транзакция не завершится, и все изменения не сохранятся.

Например, если во второй инструкции мы попытаемся записать в столбец Price какое-нибудь текстовое значение, то у нас возникнет ошибка, и изменения, внесённые первой инструкцией, не зафиксируются на постоянной основе.

Пример транзакции в T-SQL с обработкой ошибок

В языке T-SQL существует механизм перехвата и обработки ошибок – конструкция TRY… CATCH.

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

Сначала мы открываем блок для обработки ошибок, затем открываем транзакцию командой BEGIN TRANSACTION, далее пишем наши инструкции, например, те же самые две инструкции UPDATE.

После этого закрываем блок TRY, открываем блок CATCH, в котором в случае возникновения ошибки мы откатываем все изменения командой ROLLBACK TRANSACTION. Также мы принудительно завершаем нашу инструкцию командой RETURN.

Если ошибок нет, то в блок CATCH мы, соответственно, не попадаем и у нас выполнится команда COMMIT TRANSACTION, которая сохранит все изменения.

В этом примере нет ошибок, поэтому транзакция завершена успешно.

   
   BEGIN TRY
   --Начало транзакции
   BEGIN TRANSACTION

   --Инструкция 1
   UPDATE Goods SET Price = 70
   WHERE ProductId = 1;

   --Инструкция 2
   UPDATE Goods SET Price = 40
   WHERE ProductId = 2;

   END TRY
   BEGIN CATCH
      --В случае непредвиденной ошибки
      --Откат транзакции
      ROLLBACK TRANSACTION

      --Выводим сообщение об ошибке
      SELECT ERROR_NUMBER() AS ,
             ERROR_MESSAGE() AS 

   --Прекращаем выполнение инструкции
   RETURN

   END CATCH

   --Если все хорошо. Сохраняем все изменения
   COMMIT TRANSACTION

   GO

   SELECT ProductId, ProductName, Price
   FROM Goods;

А в этом примере мы намерено допускаем ошибку во второй инструкции. Поэтому управление передается в блок CATCH, где мы откатываем все изменения, возвращаем номер и описание ошибки и принудительно завершаем всю инструкцию командой RETURN.

Первая инструкция отработала нормально, но ее изменения не были сохранены, так как вторая инструкция выполнена с ошибкой.

Синтаксис

Примечание

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 и более ранних версий, см. в статье .

Язык YQL

Реализованные конструкции YQL можно разделить на два класса: data definition language (DDL) и data manipulation language (DML).

Подробнее о поддерживаемых конструкциях YQL можно почитать в документации YQL.

Ниже перечислены возможности и ограничения поддержки YQL в YDB, которые могут быть неочевидны на первый взгляд и на которые стоит обратить внимание:

Допускаются multistatement transactions, то есть транзакции, состоящие из последовательности выражений YQL. При выполнении транзакции допускается взаимодействие с клиентской программой, иначе говоря, взаимодействие клиента с базой может выглядеть следующим образом: . Стоит отметить, что если тело транзакции полностью сформировано до обращения к базе данных, то транзакция может обрабатываться эффективнее.
В YDB не поддерживается возможность смешивать DDL и DML запросы в одной транзакции. Традиционное понятие ACID транзакции применимо именно к DML запросам, то есть к запросам, которые меняют данные. DDL запросы должны быть идемпотентными, то есть повторяемы в случае ошибки. Если необходимо выполнить действие со схемой, то каждое из действий будет транзакционно, а набор действий — нет.
Реализация YQL в YDB использует механизм Optimistic Concurrency Control. На затронутые в ходе транзакции сущности ставятся оптимистичные блокировки, при завершении транзакции проверяется, что блокировки не были инвалидированы

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

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

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

Подробнее о поддержке YQL в YDB можно прочитать в документации YQL.

Мертвые блокировки DEADLOCK

Вернем назад уровень изоляции repeatable read:

SET session transaction isolation level repeatable read;

Теперь в одном окне создаем транзакцию и выполняем обновление таблицы Team:

START TRANSACTION;
    update team 
    set name = 'dfgerdg' 
    where teamid = 1;

В результате обновляемая запись в таблице team будет заблокирована.

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

START TRANSACTION;
    update person 
    set firstname = 'sdfsdfds' 
    where personid = 1;

В результате обновляемая запись в таблице person будет заблокирована.

Каждая транзакция блокирует по одной записи. А что если теперь в первой транзакции попытаться обновить заблокированную запись в таблице Person:

update person 
set firstname = 'sdfsdfds' 
where personid = 1;

Эта операция подвиснет и не сможет завершиться, потому что запись заблокирована в другой транзакции и пока та транзакция не завершиться COMMIT или ROLLBACK, первое окно будет в состоянии ожидании.

А что если теперь второе окно попытается обновить team:

update team set name = ‘dfgerdg’ where teamid = 1;

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

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

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

Проверка того, включен ли уровень изоляции транзакций моментальных снимков

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

Запустите SQL Server Profiler.
Создайте новую трассировку для подключения к источнику данных, указанному в проекте служб Analysis Services.
В диалоговом окне «Свойства трассировки» откройте вкладку «Выбор событий «.
В столбце TransactionID установите флажки в строке события и в строке события .

Примечание.

Чтобы отобразить столбец TransactionID , установите флажок «Показать все столбцы «.
Нажмите кнопку » Выполнить», чтобы запустить трассировку.
В Студии разработки бизнес-аналитики обработать проект служб Analysis Services.
В SQL Server Profiler найдите события и события, которые имеют одинаковое значение в столбце TransactionID. Как правило, эти события содержат инструкцию в столбце TextData . Для этих событий получите идентификатор сеанса в столбце SPID .
Чтобы подключиться к источнику данных, запустите SQL Server Management Studio.
Создайте новый запрос и выполните следующую инструкцию Transact-SQL.

Примечание.

В этой инструкции <SPID> является заполнителем идентификатора сеанса, полученного на шаге 7.

На вкладке » Результаты» запишите значение в Transaction_Isolation_Level столбце . Это значение указывает уровень изоляции транзакций, используемый в проекте служб Analysis Services. Если включен уровень изоляции транзакции моментального снимка, значение в столбце Transaction_Isolation_Level равно 5. В следующей таблице показаны значения в столбце Transaction_Isolation_Level и соответствующие уровни изоляции транзакций.

Значение	Уровень изоляции транзакций
Не определено.
1	ReadUncommitted
2	ReadCommitted
3	Повторяемые
4	Сериализуемый
5	Снимок

Различия между пессимистичным и оптимистичным подходами

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

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

В таблицах, оптимизированных для памяти, может возникать ошибка 1205, взаимоблокировка.

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

Ректальное администрирование: Основы для практикующих системных АДминистраторов

Одной из самых популярных и зарекомендовавших себя методологий системного администрирования является так называемое ректальное. Редкий случай сопровождения и обслуживания информационных систем, инфраструктуры организации обходится без его использования. Зачастую без знания данной методологии сисадминам даже бывает сложно найти работу в сфере ИТ, потому что работодатели, особенно всякие аутсорсинговые ИТ фирмы, в основном отдают предпочтение классическим, зарекомендовавшим себя методикам, а не новомодным заграничным веяниям: практикам ITIL, нормальным ITSM и прочей ерунде.

Remarks

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

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

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

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

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

Примечание

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

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

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

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

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

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

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

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