Is rollback required in SQL Server when transaction failed in commit tran statement

Question

I am working in some existing application using SQL Server 2014 in the backend. I find that the pattern to commit the transaction is like

USE AdventureWorks;
GO
BEGIN TRANSACTION;
GO
DELETE FROM HumanResources.JobCandidate WHERE JobCandidateID = 10;
DELETE FROM HumanResources.JobCandidate WHERE JobCandidateID = 11;
DELETE FROM HumanResources.JobCandidate WHERE JobCandidateID = 12;
GO
COMMIT TRANSACTION;
GO

I am wondering if the query failed in commit transaction statement, do i need to have the rollback statement there?

according to this question Can a COMMIT statement (in SQL) ever fail? How?, the commit tran can fail, but do I have to roll that back since the transaction hasn't been commit successfully. Would SQL server roll that back automatically when the connection is closed?

Please point me to the documentation in MSDN or wherever you got the information.

Answer 1

I believe it will, after the connection is closed. You should not count on this, there are many factors to consider including connection pooling. I suggest you look into SET XACT_ABORT ON and / or using a try catch block.

Answer 2

The way that I use transactions:

Begin Try
    Begin Tran
    -- do some work here...
    Commit Tran
End Try

Begin Catch
    If ( @@TranCount > 0 )
        Rollback Tran
End Catch

Answer 3

doing your transaction commit/rollbacks in a try/catch is probably a best practice.

if, however you want your code to automatically rollback all of the statements in the transaction you need to add the "set xact_abort on" statement somewhere before the begin trans statement. xact_abort automatically rolls back all of the statements in a transaction if any of them fail. to understand the effect of xact_abort, execute the following code. set xact_abort on and off and observe the contents of the table. the first statement of the batch in the sample will always fail because of a primary key violation.

use tempdb 
go 
if exists (select * from sys.tables where name='t') drop table t 
go 
create table t (id int not null primary key) 
go 
insert t values(1) 
go
set xact_abort on 
begin transaction 
 insert t values(1) 
 insert t values(2) 
commit transaction 
go 
select * from t

Answer 4

You can use try...catch like the below.

BEGIN TRY
    DELETE
    FROM HumanResources.JobCandidate
    WHERE JobCandidateID = 10;

    DELETE
    FROM HumanResources.JobCandidate
    WHERE JobCandidateID = 11;

    DELETE
    FROM HumanResources.JobCandidate
    WHERE JobCandidateID = 12;

    COMMIT;
END TRY

BEGIN CATCH
    ROLLBACK

    SELECT Db_name()
        ,CONVERT(NVARCHAR(15), Error_number())
        ,CONVERT(NVARCHAR(10), Error_line())
        ,Error_message()
END CATCH