DATABASE

SQL Server 2005 : Transactions and Exceptions

10/12/2010 9:42:58 AM
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.

Other  
 
Video
Top 10
SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
The latest Audi TT : New angles for TT
Era of million-dollar luxury cars
Game Review : Hearthstone - Blackrock Mountain
Game Review : Battlefield Hardline
Google Chromecast
Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
Michael Kors Designs Stylish Tech Products for Women
REVIEW
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
Popular Tags
Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone