Blocking and Blocked Processes - SQL Server Query Performance Tuning - page 390

SET XACT_ABORT ON
You can modify the atomicity of the INSERT task in the preceding section using the SET XACT_ ABORT ON statement:
SET XACT_ABORT ON;
GO
BEGIN TRAN
--Start: Logical unit of work
--First:
INSERT INTO dbo.ProductTest
SELECT p.ProductID
FROM Production.Product AS p;
--Second:
INSERT INTO dbo.ProductTest
VALUES (1);
COMMIT
--End: Logical unit of work GO
SET XACT_ABORT OFF;
GO
The SET XACT_ABORT statement specifies whether SQL Server should automatically roll back and abort an entire
transaction when a statement within the transaction fails. The failure of the first INSERT statement will automatically
suspend the entire transaction, and thus the second INSERT statement will not be executed. The effect of
SET XACT_ABORT is at the connection level, and it remains applicable until it is reconfigured or the connection is
closed. By default, SET XACT_ABORT is OFF .
Explicit Rollback
You can also manage the atomicity of a user-defined transaction by using the TRY/CATCH error-trapping mechanism
within SQL Server. If a statement within the TRY block of code generates an error, then the CATCH block of code will
handle the error. If an error occurs and the CATCH block is activated, then the entire work of a user-defined transaction
can be rolled back, and further statements can be prevented from execution, as follows:
BEGIN TRY
BEGIN TRAN
--Start: Logical unit of work
--First:
INSERT INTO dbo.ProductTest
SELECT p.ProductID
FROM Production.Product AS p
Second:
INSERT INTO dbo.ProductTest
(ProductID)
VALUES (1)
COMMIT --End: Logical unit of work
END TRY
BEGIN CATCH
ROLLBACK
PRINT 'An error occurred'
RETURN
END CATCH