SQL Server: ROLLBACK Option

Transactions can be specified explicitly, implicitly, or automatically. In autocommit mode, which is the default, each Transact-SQL statement is treated as a separate transaction, and each statement is automatically committed when it successfully executes. To ensure that both modifications either succeed or fail, DBA/Developer should include them within an explicit transaction. The XACT_ABORT option controls how SQL Server handles transactions when a run-time error occurs. When the SET XACT_ABORT option is set to ON, and a Transact-SQL statement raises an error at run time, the entire transaction is terminated and rolled back. When the XACT_ABORT option is set to OFF, only the Transact-SQL statement that raised the error is rolled back. The remaining statements in the transaction will be executed. The default value of the XACT_ABORT option is OFF. In this scenario, DBA/Developer wanted to ensure that either both statements execute successfully or both are rolled back. Setting XACT_ABORT to ON before the transaction executes will ensure that if either statement generates an error, both statements will be rolled back. For example, in this scenario, DBA/Developer might use the following code:

BEGIN TRANSACTION

UPDATE Inventory
SET UnitsInStock = 0
WHERE InvID = 4;
INSERT INTO InvHistory (InvID, ModifiedDate)
VALUES (4, GETDATE());
COMMIT TRANSACTION

If either DML statement fails, the entire transaction would be rolled back. DBA/Developer can also use the ROLLBACK statement and other procedural constructs within a block of Transact-SQL code to control when transactions are committed or rolled back. DBA/Developer might want to return a custom error message or perform other actions when specific statements are unsuccessful. To accomplish this, DBA/Developer would need to include additional error-handling code. For example, the following code would accomplish the same result, but DBA/Developer could include custom error processing within each BEGIN...END block:

BEGIN TRANSACTION

UPDATE Inventory
SET UnitsInStock = 0
WHERE InvID = 4;
IF@@ERROR <> 0
BEGIN ROLLBACK
RETURN
END INSERT INTO InvHistory(InvID, ModifiedDate)
VALUES (4, GETDATE());
IF @@ERROR <> 0
BEGIN ROLLBACK
RETURN
END ELSE
COMMIT

DBA/Developer should not set the XACT_ABORT option to OFF and enclose the code within a single explicit transaction. With this setting, if one of the statements failed, only the failed statement would be rolled back, rather than the entire transaction. DBA/Developer should not issue the UPDATE and INSERT statements, then check @@ERROR and roll back the transaction if @@ERROR returns a non-zero value. The @@ERROR function returns a non-zero value, but only for the most recently executed statement. Therefore, DBA/Developer would have to check the value of @@ERROR after each DML statement. DBA/Developer should not include the code for both DML operations in a TRY block, and include a CATCH block. Although DBA/Developer can use a TRY...CATCH construct to customize error handling in a transaction, simply including the DML within a TRY block and including a CATCH block is not sufficient. DBA/Developer would need to include the appropriate code to roll back the transaction in the CATCH block if an error occurred.