SET XACT_ABORT Option and Transaction Behavior

One of my colleagues asked what if a transaction has only BEGIN TRAN and COMMIT TRAN without a ROLLBACK TRAN. I did not have a proper answer on top of my head however I could remember the behavior is depending on the SET OPTION known as XACT_ABORT.
I initially tried the below query to test the above mentioned situation. Note that I did not change the default setting of XACT_ABORT option. The default is set to OFF.


USE Adventureworks2012
GO--Make a copy of Employee Table
SELECT * INTO HumanResources.EmployeeCopy FROM HumanResources.Employee--starting a transaction
BEGIN TRANSACTION

   --change JobTitle
   UPDATEHumanResources.EmployeeCopy SETJobTitle='R n D Manager'
   WHEREJobTitle='Research and Development Manager'

   --generate an error within the transaction
   SELECT1/0

--commit transaction
COMMIT TRANSACTION
GO

SELECT @@TRANCOUNT

Output;

First affected two is for the update statement and the last row affected is for the @@TRANCOUNT. Since the error occurred within a transaction our expectation is the entire transaction should be rolled back. SELECT @@TRANCOUNT output as 0 which means there is no open transaction in the current session. Then I checked whether the update statement is succeeded by using the following query.

SELECT JobTitle FROM HumanResources.EmployeeCopyWHERE JobTitle='R n D Manager' -- New value of the JobTitle

Output;



Which means even though the error occurred in the transaction the update is successful. This contradicts the statement that “Transaction is atomic”.

Then I repeat the same test with changing the XACT_ABORT option to ON.

USE Adventureworks2012GO

SET XACT_ABORT ON
GO
--starting a transactionBEGIN TRANSACTION 

    --change JobTitle
    UPDATE HumanResources.EmployeeCopy SET JobTitle='Research and Development Manager'
    WHERE JobTitle='R n D Manager'

    --generate an error within the transaction
    SELECT 1/0

--commit transactionCOMMIT TRANSACTION
GO

SELECT @@TRANCOUNT

This time I changed the JobTitle back to the previous value which is “Research and Development Manager” After completing the query I checked whether the update is successful and it was not which is the expected behavior of the transaction.

I did a Google search for SET XACT_ABORT to get the more information. Below is the excerpt of the site.

“When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF is the default setting.
Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.
XACT_ABORT must be set ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server. The only case where this option is not required is if the provider supports nested transactions.
The setting of SET XACT_ABORT is set at execute or run time and not at parse time.”