Как правильно работать с транзакциями в MS 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. По идее транзакция должна была завершиться и на откат ничего не должно было пойти...

Ответ

В случае конструкции
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()