Self-Learning – видеокурсы и книги по IT

Всем привет! Сегодня мы рассмотрим инструкцию SET XACT_ABORT, которая используется в языке T-SQL для автоматического отката текущей транзакции, а зачем это нужно и как именно это делается, мы как раз и поговорим в этом материале.

XACT_ABORT в T-SQL – автоматический откат текущей транзакции

Что такое XACT_ABORT в T-SQL

XACT_ABORT – это параметр, который указывает Microsoft SQL Server, выполнять ли откат всей транзакции и прерывать ли пакет команд в случае возникновения ошибки в инструкциях этой транзакции.

Дело в том, что у Microsoft SQL Server есть очень важная особенность, о которой нужно знать и обязательно помнить при работе с транзакциями.

Заметка! Что такое транзакции в T-SQL – подробное описание для начинающих.

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

Иными словами, если Вы самостоятельно не перехватываете ошибки внутри транзакции, например, конструкцией TRY…CATCH, то, если возникнет ошибка во время выполнения инструкции, SQL Server может не прервать пакет команд и не откатить уже внесенные изменения в базу данных.

К таким ошибкам в основном относятся ошибки, связанные с нарушением ограничений, например, ограничений внешнего ключа (FOREIGN KEY), ограничений уникальности (UNIQUE), проверочных ограничений (CHECK) и т.д., которые на самом деле возникают чаще других типов ошибок.

Казалось бы, вроде как транзакция должна обладать атомарностью и все команды внутри нее должны либо выполняться вместе, либо отменяться также вместе, а здесь получается, что это не так. И практически у всех возникает один тот же вопрос, а почему SQL Server по умолчанию работает именно так?

Но все дело в том, что Microsoft SQL Server дает нам возможность самостоятельно решать, что делать в случае возникновения ошибки в транзакции, так как бывают случаи, что некоторые изменения не надо откатывать даже в случае возникновения ошибки.

Поэтому мы всегда должны либо самостоятельно обрабатывать все ошибки конструкцией TRY…CATCH и решать, что делать в таких случаях, либо использовать инструкцию SET XACT_ABORT ON, которая включает автоматический откат текущей транзакции, в случае возникновения ошибок в инструкциях этой транзакции.

По умолчанию XACT_ABORT установлен в OFF, таким образом, чтобы включить автоматический откат транзакции, мы должны написать перед началом этой транзакции инструкцию

SET XACT_ABORT ON;

А чтобы вернуть все как было

SET XACT_ABORT OFF;

Особенности XACT_ABORT и рекомендации

  • Параметр XACT_ABORT применяется для текущего сеанса подключения;
  • Значение параметра XACT_ABORT устанавливается во время выполнения, а не во время синтаксического анализа;
  • RAISERROR не учитывает инструкцию SET XACT_ABORT, а THROW учитывает, поэтому рекомендовано использовать инструкцию THROW вместо RAISERROR для генерации исключений в транзакциях;
  • Инструкция SET XACT_ABORT не влияет на компиляцию ошибок (например, синтаксических);
  • XACT_ABORT OFF — это значение по умолчанию в инструкциях T-SQL, а ON — значение по умолчанию в триггере.

Кроме этого обязательно стоит отметить, что на многих форумах, и во многих статьях рекомендуют использовать SET XACT_ABORT ON абсолютно во всех транзакциях, даже если используется обработка ошибок. Это не сказать, что плохо, но на мой взгляд лучше подходить ко всему осознано, особенно если это касается транзакций, т.е. использовать эту инструкцию или нет решать в каждом конкретном случае.

Интересное.  Что нужно знать и уметь разработчику T-SQL. Технологии, языки, навыки.

Пример использования XACT_ABORT

Чтобы посмотреть на работу инструкции SET XACT_ABORT ON, давайте смоделируем ситуацию, когда в транзакции выполняется несколько инструкций, и в одной из этих инструкций возникает ошибка, а SQL Server не откатывает внесенные изменения другими инструкциями, при этом мы не используем обработку ошибок.

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

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


--Создание таблицы
CREATE TABLE Accounts (
   ls INT IDENTITY(1,1) NOT NULL,
   balance MONEY NOT NULL
);

GO

--Добавление данных в таблицу
INSERT INTO Accounts (balance)
  VALUES (100),
         (100);

GO

SELECT ls, balance
FROM Accounts;

Скриншот 1

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

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

Именно так и происходит во всех штатных ситуациях. Например, следующая инструкция успешно выполняет перевод средств, т.е. снятие с одного счета 10, и пополнение другого на 9.

DECLARE @Summa1 MONEY = 10,
        @Summa2 MONEY = 9;

DECLARE @ls1 INT = 1,
        @ls2 INT = 2;

BEGIN TRANSACTION

--Снятие со счета
UPDATE Accounts SET balance = balance - @Summa1
WHERE ls = @ls1;

--Зачисление на счет
UPDATE Accounts SET balance = balance + @Summa2
WHERE ls = @ls2;

COMMIT TRANSACTION

SELECT ls, balance
FROM Accounts;

Скриншот 2

А теперь посмотрим, что будет, если нам передадут некорректные данные. Допустим, во второй сумме к нам прилетел NULL.


DECLARE @Summa1 MONEY = 10,
        @Summa2 MONEY = NULL;

DECLARE @ls1 INT = 1,
        @ls2 INT = 2;

BEGIN TRANSACTION

--Снятие со счета
UPDATE Accounts SET balance = balance - @Summa1
WHERE ls = @ls1;

--Зачисление на счет
UPDATE Accounts SET balance = balance + @Summa2
WHERE ls = @ls2;

COMMIT TRANSACTION

SELECT ls, balance
FROM Accounts;

Скриншот 3

Заметка! Курсы по T-SQL с участием ментора.

Мы видим, что инструкция снятия средств успешно выполнилась, а инструкция зачисления нет, и транзакция не откатила внесенные изменения. Тем самым, деньги у нас просто пропали.

Чтобы этого не допустить, мы можем использовать инструкцию SET XACT_ABORT ON перед началом транзакции.

DECLARE @Summa1 MONEY = 10,
        @Summa2 MONEY = NULL;

DECLARE @ls1 INT = 1,
        @ls2 INT = 2;

SET XACT_ABORT ON;

BEGIN TRANSACTION

--Снятие со счета
UPDATE Accounts SET balance = balance - @Summa1
WHERE ls = @ls1;

--Зачисление на счет
UPDATE Accounts SET balance = balance + @Summa2
WHERE ls = @ls2;

COMMIT TRANSACTION

SELECT ls, balance
FROM Accounts;

Скриншот 4

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

Скриншот 5

Надеюсь, я понятно объяснил, для чего нужна инструкция SET XACT_ABORT ON в языке T-SQL.

Видео материал

Другие интересные статьи по T-SQL:

На сегодня это все, удачи Вам, пока!

Мы в соцсетях
Следите за выходом новых статей в наших группах в социальных сетях: Подписывайтесь, и Вы не пропустите выход нового материала!
Теги: # #
Понравилась статья? Поделитесь с друзьями:
Share on VK
VK
Share on Facebook
Facebook
Tweet about this on Twitter
Twitter
Share on Tumblr
Tumblr
Share on Reddit
Reddit
Email this to someone
email

Авторские онлайн-курсы по T-SQL

Оставьте комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Пролистать наверх