How to work with transactions in MS SQL Server?

In the case of

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

(when executing commands not in the TRY ... CATCH ... block) if an error occurs on the second UPDATE , execution of the commands can continue and reach the COMMIT .

Use option

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

will not be right The global variable @@ERROR contains the error number for the last command executed. This means the following: if UPDATE 1 or UPDATE 2 ends with an error, and UPDATE 3 fails, then after UPDATE 3 value of the @@ERROR variable will become 0 , which will make a false conclusion about the success of the entire transaction.

If you need to rollback on an error, then there may be two options.

The first is the execution of commands in the TRY ... CATCH ... block TRY ... CATCH ...

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

In this case, if an error occurs at the 2nd step, the execution will not continue, but, if possible, will go to the CATCH block, where you can force the ROLLBACK .

The second option is to enable the XACT_ABORT option before entering the transaction.

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

In this case, if errors occur (of a certain kind, not any) on the 2nd UPDATE execution of commands will be interrupted and automatic changes will be rolled back. (It may not even be necessary to consider this option independent, in my opinion the inclusion of XACT_ABORT is some kind of additional tool; I don’t recall the case when I used this option separately for rolling back a transaction).

In some cases, both are used.

Below is a little more about automatic and managed rollback.

Automatic rollback

ROLLBACK can occur automatically when the connection is closed, if there are incomplete transactions for this connection. Those. created, for example, a table

 create table test (id int primary key); 

We open connection and in it we execute

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

close the connection without completing the transaction (neither COMMIT nor ROLLBACK did). SqlServer will roll back such a transaction when the connection is broken. After requesting the data from the table in the new connection, we will see that it is empty.

Also, an automatic ROLLBACK can occur when errors occur (such as, for example, PK violation, FK constraints when inserting or deleting data), if the XACT_ABORT option is enabled (default OFF ). For example:

 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; 

in this case, the second select and commit will not get to the commit , and the rollback will occur automatically. Now with xact_abort turned off (by default):

 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; 

Despite the error, it will come to commit (there will be no rollback, respectively) and to select after it.

Unfortunately, the set xact_abort on option is not always useful. In particular, it does not roll back a transaction when generating user exceptions (including those generated in DML triggers). For example:

 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; 

Despite set xact_abort on and the generated exception, it comes to both commit and select after it. Therefore, a targeted rollback call can be more useful.

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

Managed ROLLBACK

Often used in a catch block, when wrapping a transaction in a try ... catch ... construct:

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

With xact_abort off (i.e. by default), ROLLBACK does not occur automatically if the transaction was opened, but did not reach a COMMIT due to an error. In this case, SqlServer allows the programmer to decide for himself whether the rollback will be useful for a particular error or not. Next, a couple of examples where a rollback can be useful in catch and when it is harmful.

Example 1: Changing data in a transaction.

Let there be a procedure that, in a transaction, inserts data into two related tables:

 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, xcvalue('@Value', 'nvarchar(400)') from @info.nodes('/User[1]/Contacts[1]/Contact') x(c) join dbo.UserContactTypes ct on ct.Type = xcvalue('@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 

Suppose now that a procedure call has occurred and data insertion has begun. Suppose that the insertion into Users was successful, and when inserted into UserContacts there was a conflict with a unique index (UserID, ContactTypeID) (due to, for example, the same <Contact Type="Phone" Value="0(000)000-00-00" /> in @info <Contact Type="Phone" Value="0(000)000-00-00" /> hesitated twice).

If the application logic dictates that either the entity is inserted entirely or not inserted at all, then rollback is made in catch (as in this example).

But there may be situations where errors resulting from the performance of some separate requests are not a serious reason to roll back all the actions performed. For example, if we have not two related tables, but data import into several independent tables, and we do not want to roll back that part of the data that has already been successfully entered. Then in catch you can try to commit (not every mistake makes it possible, about how to do it correctly - in the following example).

Those. rollback not required to occur if any error occurs. Do rollback, or not - depends on the semantics of the data and application logic.

Example 2 : Reading data in a transaction.

Transactions to change data are fairly familiar, but sometimes a transaction needs reading. For such transactions, a thoughtlessly induced rollback can do a disservice.

Let there be a procedure that reads data in a repeatable read or snapshot transaction:

 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 

In the procedure, the following occurs. The transaction opens. It fills the #Orders filter table (to later give the user only what he is allowed to see). Then some checking based on #Orders and other data. If the check passes, then the data is returned; if not, an error is generated.

Suppose this check was not successful. A transaction is opened and a raiserror('Check fail.', 16, 1) error raiserror('Check fail.', 16, 1) , and control is passed to catch . Should a catch occur in rollback ? Not. After all, we only read the data and do not change anything (except for the #Orders temporary table). Moreover, the #Orders table #Orders created before entering the transaction, and filled in the transaction. As a result, if we began to do rollback , the data inserted into it would begin to roll back, which is longer than the commit and simple destruction of #Orders when exiting the procedure. Those. in this case in catch it is better to try to make commit , the possibility or impossibility of which is determined by the XACT_STATE () function.