```
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.