17. Управление транзакциями - KattyOG/Database GitHub Wiki

Общие сведения о транзакциях

Транзакция — это последовательность операций, выполняемая как единое целое. В составе транзакций можно исполнять почти все операторы языка Transact-SQL. Если при выполнении транзакции не возникает никаких ошибок, то все модификации базы данных, сделанные во время выполнения транзакции, становятся постоянными. Транзакция выполняется по принципу «все или ничего». Транзакция не оставляет данные в промежуточном состоянии, в котором база данных не согласована. Транзакция переводит базу данных из одного целостного состояния в другое.

В качестве примера транзакции рассмотрим последовательность операций по приему заказа в коммерческой компании. Для приема заказа от клиента приложение ввода заказов должно:

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

Для поддержания целостности транзакция должна обладать четырьмя свойствами АСИД: атомарность, согласованность, изоляция и долговечность. Эти свойства называются также ACID-свойствами (от англ., atomicity, consistency, isolation, durability).

Атомарность (Atomicity). Транзакция должна представлять собой атомарную (неделимую) единицу работы (исполняются либо все модификации, из которых состоит транзакция, либо ни одна).
Согласованность (или Непротиворечивость) (Consistency). По завершении транзакции все данные должны остаться в согласованном состоянии. Чтобы сохранить целостность всех данных, необходимо выполнение модификации транзакций по всем правилам, определенным в реляционных СУБД.
Изоляция (Isolation). Модификации, выполняемые одними транзакциями, следует изолировать от модификаций, выполняемых другими транзакциями параллельно. Уровни изоляции транзакции могут изменяться в широких пределах. На каждом уровне изоляции достигается определенный компромисс между степенью распараллеливания и степенью непротиворечивости. Чем выше уровень изоляции, тем выше степень непротиворечивости данных. Но чем выше степень непротиворечивости, тем ниже степень распараллеливания и тем ниже степень доступности данных.
Долговечность (или Устойчивость) (Durability). По завершении транзакции ее результат должен сохраниться в системе, несмотря на сбой системы, либо (что касается незафиксированных транзакций) может быть полностью отменен вслед за сбоем системы.

Управление транзакциями в приложениях реализуется, главным образом, путем указания того, когда транзакция начинается и заканчивается. Это можно указать либо с помощью инструкций языка Transact-SQL, либо используя функции интерфейса прикладного программирования (API) для баз данных. В системе также должна быть возможность правильной обработки ошибок, прерывающих транзакцию до ее окончания.

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

Автоматические транзакции
Режим автоматической фиксации транзакций является режимом управления транзакциями SQL Server по умолчанию. В этом режиме каждая инструкция T-SQL выполняется как отдельная транзакция. Если выполнение инструкции завершается успешно, происходит фиксация; в противном случае происходит откат. Если возникает ошибка компиляции, то план выполнения пакета не строится и пакет не выполняется

Явные транзакции
Явный запуск транзакции через функцию API или посредством инструкции языка Transact-SQL BEGIN TRANSACTION. Автоматическая фиксация транзакций- Режим по умолчанию для компонента Database Engine. Каждая отдельная инструкция языка Transact-SQL фиксируется после завершения. Нет необходимости указывать какие-либо инструкции для управления транзакциями.

Для определения явных транзакций используются следующие инструкции:

BEGIN TRANSACTION – задает начальную точку явной транзакции для соединения;
COMMIT TRANSACTION или COMMIT WORK – используется для успешного завершения транзакции, если не возникла ошибка;
ROLLBACK TRANSACTION или ROLLBACK WORK – используется для отмены транзакции, во время которой возникла ошибка.
SAVE TRANSACTION – используется для установки точки сохранения или маркера внутри транзакции. Точка сохранения определяет место, к которому может возвратиться транзакция, если часть транзакции условно отменена. Если транзакция откатывается к точке сохранения, то ее выполнение должно быть продолжено до завершения с обработкой дополнительных инструкций языка T-SQL, если необходимо, и инструкции COMMIT TRANSACTION, либо транзакция должна быть полностью отменена откатом к началу. Для отмены всей транзакции следу

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

Неявные транзакции
Установка неявного режима транзакции либо через функцию API, либо через инструкцию языка Transact-SQL SET IMPLICIT_TRANSACTIONS ON. Следующая инструкция автоматически запускает новую транзакцию. После завершения этой транзакции следующая инструкция языка Transact-SQL запускает новую транзакцию

Если соединение работает в режиме неявных транзакций, то после фиксации или отката текущей транзакции SQL Server автоматически начинает новую транзакцию. В этом режиме явно указывается только граница окончания транзакции с помощью инструкций COMMIT TRANSACTION и ROLLBACK TRANSACTION. Для ввода в действие поддержки неявных транзакций применяется инструкция SET IMPLICIT_TRANSACTION ON. В конце каждого пакета необходимо отключать этот режим. По умолчанию режим неявных транзакций в SQL Server отключен

Транзакции можно завершить инструкцией COMMIT или ROLLBACK

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

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

Функции для обработки транзакций

@@TRANCOUNT возвращает число активных транзакций для текущего соединения. Инструкция BEGIN TRANSACTION увеличивает значение @@TRANCOUNT на 1, а инструкция ROLLBACK TRANSACTION уменьшает его до 0 (исключение — инструкция ROLLBACK TRANSACTION имя_точки_сохранения, которая не влияет на значение @@TRANCOUNT). Инструкции COMMIT TRANSACTION уменьшают значение @@TRANCOUNT на 1.
XACT_STATE () сообщает о состоянии пользовательской транзакции текущего выполняемого запроса в соответствии с данными, представленными в таблице.

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

Ограничения.

Функция @@TRANCOUNT не может использоваться для определения фиксируемости транзакции.
Функция XACT_STATE не может использоваться для определения наличия вложенных транзакций.

Управлением параллельным выполнением транзакций

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

Пессимистическое управление параллельным выполнением.
Оптимистическое управление параллельным выполнением.

Пессимистическое управление реализуется с помощью технологии блокировок, оптимистическое управления реализуется с помощью технологии версии строк.

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

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