No discussion of exceptions in SQL Server can be
complete without mentioning the interplay between transactions and
exceptions. This is a fairly simple area, but one that often confuses
developers who don't quite understand the role that transactions play.
SQL
Server is a database management system, and as such one of the main
goals is management and manipulation of data. Therefore, at the heart
of every exception-handling scheme within SQL Server must live the idea
that these are not mere exceptions—they're also data issues.
The Myths of Transaction Abortion
The
biggest mistake that some developers make is the assumption that if an
exception occurs during a transaction, that transaction will be
aborted. By default, that is almost never the case. Most transactions will live on even in the face of exceptions, as running the following T-SQL will show:
BEGIN TRANSACTION
GO
SELECT 1/0 AS DivideByZero
GO
SELECT @@TRANCOUNT AS ActiveTransactionCount
GO
The output from this T-SQL is as follows:
DivideByZero
@Results:------------
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
ActiveTransactionCount
----------------------
1
(1 row(s) affected).
Another mistake is the
belief that stored procedures represent some sort of atomic unit of
work, complete with their own implicit transaction that will get rolled
back in case of an exception. Alas, this is also not the case, as the
following T-SQL proves:
--Create a table for some data
CREATE TABLE SomeData
(
SomeColumn INT
)
GO
--This procedure will insert one row, then throw a divide by zero exception
CREATE PROCEDURE NoRollback
AS
BEGIN
INSERT SomeData VALUES (1)
INSERT SomeData VALUES (1/0)
END
GO
--Execute the procedure
EXEC NoRollback
GO
--Select the rows from the table
SELECT *
FROM SomeData
GO
The
result is that even though there is an error, the row that didn't throw
an exception is still in the table; there is no implicit transaction
thanks to the stored procedure:
(1 row(s) affected)
Msg 8134, Level 16, State 1, Procedure NoRollback, Line 7
Divide by zero error encountered.
The statement has been terminated.
SomeColumn
-----------
1
(1 row(s) affected).
Even if an explicit
transaction is begun in the stored procedure before the inserts and
committed after the exception occurs, this example will still return
the same output. By default, unless a rollback is explicitly issued, in
most cases an exception will not roll anything back. It will simply
serve as a message that something went wrong.
XACT_ABORT: Turning Myth into (Semi-)Reality
As mentioned in the section on XACT_ABORT
and its effect on exceptions, the setting also has an impact on
transactions, as its name might indicate (it is pronounced "transact
abort"). In addition to making exceptions act like batch-level
exceptions, the setting also causes any active transactions to
immediately roll back in the event of an exception. This means that the
following T-SQL results in an active transaction count of 0:
SET XACT_ABORT ON
BEGIN TRANSACTION
GO
SELECT 1/0 AS DivideByZero
GO
SELECT @@TRANCOUNT AS ActiveTransactionCount
GO
The output is now
DivideByZero
@Results:------------
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
ActiveTransactionCount
----------------------
0
(1 row(s) affected).
XACT_ABORT does not create an implicit transaction within a stored procedure, but it does
cause any exceptions that occur within an explicit transaction within a
stored procedure to cause a rollback. The following T-SQL shows a much
more atomic stored procedure behavior than the previous example:
--Create a table for some data
CREATE TABLE SomeData
(
SomeColumn INT
)
GO
--This procedure will insert one row, then throw a divide-by-zero exception
CREATE PROCEDURE NoRollback
AS
BEGIN
SET XACT_ABORT ON
BEGIN TRANSACTION
INSERT SomeData VALUES (1)
INSERT SomeData VALUES (1/0)
COMMIT TRANSACTION
END
GO
--Execute the procedure
EXEC NoRollback
GO
--Select the rows from the table
SELECT *
FROM SomeData
GO
This T-SQL results in the following output, which shows that no rows were inserted:
(1 row(s) affected)
Msg 8134, Level 16, State 1, Procedure NoRollback, Line 11
Divide by zero error encountered.
SomeColumn
-----------
(0 row(s) affected).
XACT_ABORT
is a very simple, yet extremely effective means of ensuring that an
exception does not result in a transaction committing with only part of
its work done. I recommend turning this setting on in any stored
procedure that uses an explicit transaction, in order to guarantee that
it will get rolled back in case of an exception.
TRY/CATCH and Doomed Transactions
The introduction of TRY/CATCH
syntax to SQL Server brings with it a strange new concept: transactions
can now enter a state in which they can only be rolled back. In this
case the transaction is not automatically rolled back, as it is with XACT_ABORT;
instead, SQL Server throws an exception letting the caller know that
the transaction cannot be committed, and must be rolled back. This
condition is known as a doomed transaction, and the following T-SQL shows one way of producing it:
--Create a table for some data
CREATE TABLE SomeData
(
SomeColumn INT
)
GO
BEGIN TRANSACTION
BEGIN TRY
--Throw an exception on insert
INSERT SomeData VALUES (CONVERT(INT, 'abc'))
END TRY
BEGIN CATCH
--Try to commit...
COMMIT TRANSACTION
END CATCH
GO
This results in the following output:
Msg 3930, Level 16, State 1, Line 10
The current transaction cannot be committed and cannot support
operations that write to the log file. Roll back the transaction.
Should
a transaction enter this state, any attempt to either commit the
transaction or roll forward (do more work) will result in the same
exception. This exception will keep getting thrown until the
transaction is rolled back.
In order to determine whether an active transaction can be committed or rolled forward, check the value of the XACT_STATE
function. This function returns 0 if there are no active transactions,
1 if the transaction is in a state in which more work can be done, and
−1 if the transaction is doomed. It is a good idea to always check XACT_STATE in any CATCH block that involves an explicit transaction.
