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.
GO--Make a copy of Employee Table
SELECT * INTO HumanResources.EmployeeCopy FROM HumanResources.Employee--starting a transaction
UPDATEHumanResources.EmployeeCopy SETJobTitle='R n D Manager'
WHEREJobTitle='Research and Development Manager'
--generate an error within the transaction
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
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
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.”