Is ROLLBACK TRANSACTION required?

Question

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

What happens if the first delete statement fails? Will the 2nd and 3rd delete statements be executed? The example doesn't have any error handling, will it leave an open transaction in the case of an exception, or will SQL Server rollback the transaction automatically? Open transaction = locked resources, right?

I am deciding whether I must apply TRY...CATCH to stored procedures that use transactions.

I am aware about set xact_abort on, but want to know what happens without it.

Here is what I found in docs - Controlling Transactions (Database Engine):

If an error prevents the successful completion of a transaction, SQL Server automatically rolls back the transaction and frees all resources held by the transaction

However I read in other posts that automatic rollback is not fired.

Answer 1

In your example, without the use of SET XACT_ABORT ON, the transaction will continue and commit even if the first statement fails. In the text you quoted, the key words are if an error **prevents** the successful completion of a transaction, and a DELETE statement failing does not prevent the transaction from completing.

An example of an error that would cause an automatic rollback is if the connection to the database was severed in the middle of a transaction. Further down the MSDN article you referenced says:

If a run-time statement error (such as a constraint violation) occurs in a batch, the default behavior in the Database Engine is to roll back only the statement that generated the error. You can change this behavior using the SET XACT_ABORT statement. After SET XACT_ABORT ON is executed, any run-time statement error causes an automatic rollback of the current transaction. Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.

It's always a good idea to use error handling to catch errors and rollback if needed.