Как правильно работать с транзакциями в MS SQL Server?

#sql #sql_server

Запустил 3 запрос на обновление 3-ех разных таблиц и обернул в Begin Tran и Commit Tran.

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

Разве не должен был быть откат?

UPD

Как я понимаю, я должен был использовать вместо

BEGIN TRAN
UPDATE 1
UPDATE 2
UPDARE 3
COMMIT TRAN


Вот это

    BEGIN TRAN
    UPDATE 1
    UPDATE 2
    UPDARE 3
if @error<>0
    rollback tran
    COMMIT TRAN


?

Если это так, то статься на MSDN https://msdn.microsoft.com/ru-ru/library/ms190295.aspx

Вводит в заблуждение, так не показан пример с проверкой на ошибки...

Еще не понял вот такого поведения:

Делал такой эксперимент:
Сначала делаю N раз вот этот запрос:

BEGIN TRAN
INSERT INTO ttt VALUES  (GETDATE())


--Транзакцию специально не закомитил

Затем отдельно делаю:

COMMIT TRAN


Затем 

ROLLBACK tran


У меня откатились все мои N инсертов, хотя я сделал COMMIT TRAN, а лишь потом ROLLBACK
tran.  По идее транзакция должна была завершиться и на откат ничего не должно было пойти...
    

Ответы

Ответ 1

В случае конструкции

BEGIN TRAN
UPDATE 1
UPDATE 2 --error
UPDATE 3
COMMIT TRAN


(при выполнении команд не в блоке TRY ... CATCH ...) если на втором UPDATE возникнет
ошибка, исполнение команд может продолжиться и дойти до COMMIT.

Использование варианта

BEGIN TRAN
UPDATE 1
UPDATE 2
UPDATE 3
IF @@ERROR <> 0
    ROLLBACK TRAN
ELSE
    COMMIT TRAN


не будет правильным, т.к. глобальная переменная @@ERROR содержит номер ошибки для
последней исполненной команды. Это означает следующее: если UPDATE 1 или UPDATE 2 завершится
с обшибкой, а UPDATE 3 - без ошибки, то после UPDATE 3 значение переменной @@ERROR
станет равно 0, что сделает ложным вывод об успешности всей транзакции.

Если нужно при ошибке делать откат, то тут могут быть два варианта.

Первый - это исполнение команд в блоке TRY ... CATCH ...

BEGIN TRY
    BEGIN TRAN
    UPDATE 1
    UPDATE 2 --error
    UPDATE 3
    COMMIT TRAN
END TRY
BEGIN CATCH
    ROLLBACK TRAN
END CATCH


В этом случае, при возникновении ошибки на 2м шаге, исполнение не продолжится, а
по-возможности перейдёт в блок CATCH, где можно принудительно вызвать ROLLBACK.

Второй вариант - включение опции XACT_ABORT перед входом в транзакцию.

SET XACT_ABORT ON
BEGIN TRAN
UPDATE 1
UPDATE 2 --error
UPDATE 3
COMMIT TRAN


В этом случае при возникновении ошибок (определённого рода, не любых) на 2-м UPDATE
выполнение команд будет прервано и произойдёт автоматический откат изменений. (Может
быть даже не стоит считать этот вариант самостоятельным, на мой взгляд включение XACT_ABORT
- это некое дополнительное средство; не припомню случая, когда бы я для отката транзакции
пользовался этой опцией обособленно).

В некоторых случаях используется и то и другое.

Ниже чуть подробнее об автоматическом и управляемом откате.



Автоматический ROLLBACK

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

create table test (id int primary key);


Открываем соединение и в нём выполняем

begin tran;
insert into test (id) values (1);
select * from test;


закрываем соединение, не завершив транзакцию (ни COMMIT, ни ROLLBACK не сделали).
SqlServer сделает откат такой транзакции, при разрыве соединения. Запросив затем данные
из таблицы в новом соединении, мы увидим, что она пустая.

Также автоматический ROLLBACK может происходить при возникновении ошибок (таких как,
например, нарушение PK, FK ограничений при вставке или удалении данных), если включена
опция XACT_ABORT (по умолчанию OFF). Например:

set xact_abort on;
begin tran;
insert into test (id) values (2);
select * from test;
insert into test (id) values (2); --error: Violation of PK ...
select * from test;
commit tran;


в этом случае до второго select и до commit дело не дойдёт, и откат произойдёт автоматически.
Теперь при выключенном xact_abort (то что по умолчанию):

set xact_abort off;
begin tran;
insert into test (id) values (3);
insert into test (id) values (3); -- error
insert into test (id) values (4);
commit tran;
select * from test;


Несмотря на ошибку дело дойдёт и до commit (соответственно отката не будет) и до
select после него.

К сожалению опция set xact_abort on полезна далеко не всегда. В частности она не
откатывает транзакцию при генерации пользовательских исключений (в том числе сгенерированных
в DML-триггерах). Например:

set xact_abort on;
begin tran;
insert into test (id) values (5);
if not exists (select 1 from test where id = 0)
    raiserror('Bad data', 16, 1);
commit tran;
select * from test;


Несмотря на set xact_abort on и сгенерированное исключение дело дойдёт и до commit
и до select после него. Поэтому полезнее может быть целенаправленный вызов rollback.

-- вернули опцию в состояние по-умолчанию, если она была оставлена в состоянии ON
set xact_abort off; 


Управляемый ROLLBACK

Часто применяется в catch блоке, при оборачивании транзакции в try ... catch ...
конструкцию:

begin try
    begin tran;
    -- тут делаем что-то
    commit tran;
end try
begin catch
    rollback tran;
end catch


При xact_abort off (т.е. по умолчанию) ROLLBACK не происходит автоматически, если
транзакция была открыта, но из-за ошибки не достигла COMMIT. В этом случае SqlServer
позволяет программисту самому решить будет ли откат полезен при той или иной ошибке,
или нет. Далее пара примеров, когда откат может быть полезен в catch и когда вреден.

Пример 1: Изменение данных в транзакции.

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

create procedure dbo.SetUserInfo
(
    @uid uniqueidentifier = NULL,
    @info xml
)
as
begin try
    set nocount, xact_abort on;

    if @info is NULL or @info.exist('/User') = 0
    begin
        raiserror('No or bad data provided.', 16, 1);
        return;
    end;

    begin transaction;

    declare @inserted table (ID int not NULL);
    declare @id int;

    merge into dbo.Users t
    using(
        select
            @uid,
            @info.value('(/User/@FirstName)[1]', 'nvarchar(50)'),
            @info.value('(/User/@LastName)[1]', 'nvarchar(50)')
        ) s(UID, FirstName, LastName)
    on t.UID = s.UID
    when matched then
        update
        set t.FirstName = s.FirstName, t.LastName = s.LastName
    when not matched then
        insert (UID, FirstName, LastName)
        values (s.UID, s.FirstName, s.LastName)
    output inserted.ID into @inserted (ID)
        ;

    select @id = ID from @inserted;

    merge into dbo.UserContacts t
    using (
        select @id, ct.ID, x.c.value('@Value', 'nvarchar(400)')
        from @info.nodes('/User[1]/Contacts[1]/Contact') x(c)
            join dbo.UserContactTypes ct on ct.Type = x.c.value('@Type', 'nvarchar(400)')
    ) s (UserID, ContactTypeID, ContactInfo)
    on t.UserID = s.UserID and t.ContactTypeID = s.ContactTypeID
    when not matched by source then
        delete
    when matched then
        update
        set t.ContactInfo = s.ContactInfo
    when not matched then
        insert (UserID, ContactTypeID, ContactInfo)
        values (s.UserID, s.ContactTypeID, s.ContactInfo)
        ;

    commit transaction;

end try
begin catch
    declare
        @errMsg nvarchar(4000) = error_message(),
        @errLine int = error_line(),
        @procName sysname = quotename(object_schema_name(@@procid)) + '.' + quotename(object_name(@@procid))
        ;

    if @@trancount > 0
        rollback transaction;

    raiserror('%s in %s at %d', 16, 1, @errMsg, @procName, @errLine);
end catch
GO


Допустим теперь, что произошёл вызов процедуры и началась вставка данных. Предположим,
что вставка в Users прошла успешно, а при вставке в UserContacts произошел конфликт
с уникальным индексом (UserID, ContactTypeID) (из-за того, например, что в @info один
и тот же  затесался дважды).

Если логикой приложения продиктовано, что либо сущность вставляется целиком, либо
вообще не вставляется - тогда в catch делается rollback (как в данном примере).

Но возможны ситуации, когда ошибки, возникшие в результате выполнения каких-то отдельных
запросов, не являются серьёзным основанием для отката всех совершенных действий. Например,
если у нас не две связанных таблицы, а импорт данных в несколько независимых таблиц,
и мы не хотим откатывать ту часть данных, что была уже успешно внесена. Тогда в catch
можно попытаться сделать commit (не любая ошибка сделает это возможным, о том как это
сделать корректно - в следующем примере).

Т.е. rollback не обязан происходить при возникновении любой ошибки. Делать откат,
или нет - зависит от семантики данных и логики приложения.

Пример 2: Чтение данных в транзакции.

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

Пусть есть процедура, которая в repeatable read или snapshot транзакции читает данные:

create procedure dbo.GetSalesData
(
    @dateFrom datetime,
    @dateTo datetime
)
as
begin try
    set nocount on;

    declare @userID int;
    select @userID = UserID from #Session;

    if @userID is NULL
    begin
        raiserror('Access denied.', 16, 1);
        return;
    end;

    create table #Orders (OrderID int not NULL);
    alter table #Orders add primary key (OrderID);

    set transaction isolation level snapshot;
    begin transaction;

    insert into #Orders (OrderID)
    select op.OrderID
    from dbo.OrderPermissions(@userID) op
        join dbo.Orders ord on ord.ID = op.OrderID
    where op.[Permissions] > 0
        and ord.[Date] >= @dateFrom and ord.[Date] < @dateTo

    -- some check based on #Order and other data
    if exists (select 1 from #Orders o join ... where ...)
    begin
        raiserror('Check fail.', 16, 1);
        return;
    end;

    select ...
    from dbo.Orders ord
        join #Orders o on o.OrderID = ord.ID

    select ...
    from dbo.Invoices inv
        join #Orders o on o.OrderID = inv.OrderID

    select ...
    from dbo.Shipment sh
        join #Orders o on o.OrderID = sh.OrderID

    commit transaction;

end try
begin catch
    declare
        @errMsg nvarchar(4000) = error_message(),
        @errLine int = error_line(),
        @procName sysname = quotename(object_schema_name(@@procid)) + '.' + quotename(object_name(@@procid))
        ;

    if xact_state() = 1
        commit transaction;
    else if xact_state() = -1
        rollback transaction;

    raiserror('%s in %s at %d', 16, 1, @errMsg, @procName, @errLine);
end catch
GO


В процедуре происходит следующее. Открывается транзакция. В ней заполняется фильтрующая
таблица #Orders (чтобы потом дать пользователю только то, что ему разрешено видеть).
Затем некоторая проверка на основании #Orders и других данных. Если проверка проходит,
то данные отдаются, если нет - генерируется ошибка.

Предположим, что эта проверка не была успешной. Открыта транзакция и произошла ошибка
raiserror('Check fail.', 16, 1), вследствие чего управление передаётся в catch. Должен
ли в catch произойти rollback? Нет. Ведь мы только читаем данные и ничего не изменяем
(кроме временной таблицы #Orders). Более того, таблица #Orders создана до входа в транзакцию,
а заполнялась в транзакции. Вследствие чего, если бы мы стали делать rollback начал
бы происходить откат вставленных в неё данных, а это дольше, чем commit и простое уничтожение
#Orders при выходе из процедуры. Т.е. в данном случае в catch лучше попытаться сделать
commit, возможность или невозможность которого определяется функцией XACT_STATE().