Запустил 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().
