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 instruction fails? Will the 2nd and 3rd delete operations be performed? There is no error handling in the example, will it leave an open transaction in case of an exception, or will SQL Server refuse the transaction automatically? Open transaction = locked resources, right?

I decide if I should apply TRY ... CATCH to stored procedures that use transactions.

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

Here is what I found in the documents - Transaction Management (Database Engine):

If the error prevents the transaction from completing successfully, SQL Server automatically rolls back the transaction and frees all resources held by the transaction

However, I read in other posts that automatic rollback does not start.

Answer 1

In your example, without using SET XACT_ABORT ON transaction will continue and commit, even if the first statement fails. In the text you quoted, the keywords if an error **prevents** the successful completion of a transaction , and a DELETE failure does not interfere with the completion of the transaction.

An example of an error that will lead to automatic rollback is that the database connection was disconnected in the middle of the transaction. The MSDN article you are referencing further says:

If a run-time error occurs (for example, a constraint violation) in the package, the default behavior in the Database Engine should be executed back only by the operator that generated the error. You can change this using the SET XACT_ABORT statement. After setting SET XACT_ABORT ON, any runtime error during execution causes the current transaction to be automatically rolled back. Compilation errors, such as syntax errors, are independent of SET XACT_ABORT.

It is always useful to use error handling to detect errors and rollback if necessary.

Answer 2

I prefer to control the process manually:

 BEGIN TRY BEGIN TRAN -- do work COMMIT END TRY BEGIN CATCH ROLLBACK RAISERROR (...) END CATCH GO