Automatic Transaction Management

SQL Server provides a number of facilities for automating transaction management. The most prominent example of these is the automatic transaction (auto-commit) facility. As mentioned earlier, an automatic transaction is begun and committed or rolled back implicitly by the server. There's no need for explicit BEGIN TRAN or COMMIT/ROLLBACK TRAN statements. The server initiates a transaction when a modification command begins and, depending on the command's success, commits or rolls it back afterward. Automatic transaction mode is SQL Server's default mode but is disabled when implicit or user-defined transactions are enabled.

Implicit transactions offer another type of automated transaction management. Whenever certain commands (ALTER TABLE, FETCH, REVOKE, CREATE, GRANT, SELECT, DELETE, INSERT, TRUNCATE TABLE, DROP, OPEN, UPDATE) are executed, a transaction is automatically started. In a sense, implicit transactions offer an automated alternative to explicit transactions—a facility falling somewhere between auto-commit transactions and user-defined transactions in terms of functionality. These transactions are only semi-automated, though, since an explicit ROLLBACK TRAN or COMMIT TRAN is required to close them. Only the first part of the process is automated—the initiation of the transaction. Its termination must still be performed explicitly. Transact-SQL's SET IMPLICIT_TRANSACTIONS command is used to toggle implicit transaction mode.

SET XACT_ABORT toggles whether a transaction is aborted when a command raises a runtime error. The error can be a system-generated error condition or a user-generated one. It's essentially equivalent to checking @@ERROR after every statement and rolling back the transaction if an error is detected. Note that the command is a bit of misnomer. When XACT_ABORT is enabled and a runtime error occurs, not only is the current transaction aborted but the entire batch is as well. For example, consider the code in Listing 13.2.

Listing 13.2. Using XACT_ABORT When a Command Raises an Error

SET XACT_ABORT ON
SELECT TOP 5 au_lname, au_fname FROM authors ORDER BY
    au_lname, au_fname
BEGIN TRAN
DELETE authors
DELETE sales
SELECT TOP 5 au_lname, au_fname FROM authors ORDER BY
    au_lname, au_fname
ROLLBACK TRAN
PRINT 'End of batch -- never makes it here'
GO
SELECT TOP 5 au_lname, au_fname FROM authors ORDER BY
    au_lname, au_fname
SET XACT_ABORT ON
au_lname                                 au_fname
---------------------------------------- --------------------
Bennet                                   Abraham
Blotchet-Halls                           Reginald
Carson                                   Cheryl
DeFrance                                 Michel
del Castillo                             Innes

(5 row(s) affected)

Server: Msg 547, Level 16, State 1, Line 1
DELETE statement conflicted with COLUMN REFERENCE constraint
    'FK__titleauth__au_id__164452B1'. The conflict occurred in
    database 'pubs', table 'titleauthor', column 'au_id'.
au_lname                                 au_fname
---------------------------------------- --------------------
Bennet                                   Abraham
Blotchet-Halls                           Reginald
Carson                                   Cheryl
DeFrance                                 Michel
del Castillo                             Innes

(5 row(s) affected)

Execution never reaches the PRINT statement because the constraint violation generated by attempting to empty the authors table aborts the entire command batch (the statements before the GO). This is in spite of the fact that a ROLLBACK TRAN immediately precedes the PRINT.

The fact that the entire command batch is aborted is what makes checking @@ERROR after each data modification preferable to enabling SET XACT_ABORT. This is particularly true when calling a stored procedure within a transaction. If the procedure causes a runtime error, the statements following it in the command batch are aborted, affording no opportunity to handle the error condition.