Всем привет! Сегодня мы рассмотрим инструкцию SET XACT_ABORT, которая используется в языке T-SQL для автоматического отката текущей транзакции, а зачем это нужно и как именно это делается, мы как раз и поговорим в этом материале.
Заметка! Что такое T-SQL. Подробное описание для начинающих.
Что такое XACT_ABORT в T-SQL
XACT_ABORT – это параметр, который указывает Microsoft SQL Server, выполнять ли откат всей транзакции и прерывать ли пакет команд в случае возникновения ошибки в инструкциях этой транзакции.
Дело в том, что у Microsoft SQL Server есть очень важная особенность, о которой нужно знать и обязательно помнить при работе с транзакциями.
Суть в следующем: по умолчанию SQL Server не откатывает все изменения текущей транзакции в случае возникновения ошибки с определённым уровнем серьезности, т.е. в некоторых случаях выполняется откат только вызвавшей ошибку инструкции, а обработка транзакции продолжается.
Иными словами, если Вы самостоятельно не перехватываете ошибки внутри транзакции, например, конструкцией TRY…CATCH, то, если возникнет ошибка во время выполнения инструкции, SQL Server может не прервать пакет команд и не откатить уже внесенные изменения в базу данных.
К таким ошибкам в основном относятся ошибки, связанные с нарушением ограничений, например, ограничений внешнего ключа (FOREIGN KEY), ограничений уникальности (UNIQUE), проверочных ограничений (CHECK) и т.д., которые на самом деле возникают чаще других типов ошибок.
Казалось бы, вроде как транзакция должна обладать атомарностью и все команды внутри нее должны либо выполняться вместе, либо отменяться также вместе, а здесь получается, что это не так. И практически у всех возникает один тот же вопрос, а почему SQL Server по умолчанию работает именно так?
Заметка! Транзакции в T-SQL – основы для новичков с примерами.
Но все дело в том, что Microsoft SQL Server дает нам возможность самостоятельно решать, что делать в случае возникновения ошибки в транзакции, так как бывают случаи, что некоторые изменения не надо откатывать даже в случае возникновения ошибки.
Поэтому мы всегда должны либо самостоятельно обрабатывать все ошибки конструкцией TRY…CATCH и решать, что делать в таких случаях, либо использовать инструкцию SET XACT_ABORT ON, которая включает автоматический откат текущей транзакции, в случае возникновения ошибок в инструкциях этой транзакции.
По умолчанию XACT_ABORT установлен в OFF, таким образом, чтобы включить автоматический откат транзакции, мы должны написать перед началом этой транзакции инструкцию
SET XACT_ABORT ON;
А чтобы вернуть все как было
SET XACT_ABORT OFF;
Заметка! Visual Studio Code (VS Code) для разработки на Transact-SQL.
Особенности 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 абсолютно во всех транзакциях, даже если используется обработка ошибок. Это не сказать, что плохо, но на мой взгляд лучше подходить ко всему осознано, особенно если это касается транзакций, т.е. использовать эту инструкцию или нет решать в каждом конкретном случае.
Пример использования 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;
Заметка! Обзор функционала SQL Server Management Studio (SSMS).
И у нас есть некая процедура, которая переводит деньги с одного счета на другой. Принимает она два параметра, первый — сумма снятия, и второй — сумма зачисления на другой счет, эта сумма нужна нам для того, что, допустим, после снятия мы должны как-то обработать эту сумму (применить какой-нибудь коэффициент, снять комиссию и т.д., и это возлагается на какой-то внешний алгоритм).
При этом, как было уже отмечено, никаких проверок и обработок ошибок мы не делаем, а просто полагаемся на тот факт, что транзакция сама по себе атомарна, и изменения, внесенные абсолютно всеми инструкциями, будут откатываться в случае возникновения какой-либо ошибки.
Именно так и происходит во всех штатных ситуациях. Например, следующая инструкция успешно выполняет перевод средств, т.е. снятие с одного счета 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;
А теперь посмотрим, что будет, если нам передадут некорректные данные. Допустим, во второй сумме к нам прилетел 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;
Заметка! Если Вас интересует язык SQL, то рекомендую почитать книгу «SQL код» – это самоучитель по языку SQL для начинающих программистов. В ней язык SQL рассматривается как стандарт, чтобы после прочтения данной книги можно было работать с языком 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;
В данном случае произошел автоматический откат транзакции и всех изменений, более того, инструкция была прервана, и дело до SELECT даже не дошло. Однако, если посмотреть в таблицу, то данные там будут прежние, т.е. не измененные.
Надеюсь, я понятно объяснил, для чего нужна инструкция SET XACT_ABORT ON в языке T-SQL.
Другие интересные статьи по T-SQL:
- Назначение хранимых процедур в языке T-SQL (Microsoft SQL Server)
- Как узнать версию Microsoft SQL Server на T-SQL
- Чем отличается инструкция THROW от RAISERROR в T-SQL (Microsoft SQL Server)
- Чем отличаются функции от хранимых процедур в T-SQL (Microsoft SQL Server)
- Сравнение Azure Data Studio с SQL Server Management Studio (SSMS)
На сегодня это все, удачи Вам, пока!
