Use Caution with Explicit Transactions in Stored Procedures

Use Caution with Explicit Transactions in Stored Procedures

 

 

Explicit transactions are often used within stored procedures to guarantee all-or-nothing data integrity.  However, a little known fact is that a query timeout will leave the transaction open unless non-default session settings and/or special exception handling are used.  I’ll describe how to protect your application from problems following timeouts and other unexpected errors.

Consider the following stored procedure containing an explicit transaction:

 

CREATE PROCEDURE dbo.UpdateWithExplicitTransaction
 @MyKey int,
 @MyColumnValue int
AS

DECLARE @Error int

BEGIN TRAN

UPDATE dbo.Foo
SET MyColumn = @MyColumnValue
WHERE MyKey = @MyKey

SET @Error = @@ERROR
IF @Error <> 0
BEGIN
 GOTO Done
END

UPDATE dbo.Bar
SET MyColumn = @MyColumnValue
WHERE MyKey = @MyKey

SET @Error = @@ERROR
IF @Error <> 0
BEGIN
 GOTO Done
END

COMMIT

Done:

IF @Error <> 0
BEGIN
 ROLLBACK
END

RETURN @Error

 

You execute the script below from SQL Server Management Studio or Query Analyzer with the query timeout option set to 30 seconds and the second UPDATE statement in the proc times out. 

 

 

EXEC dbo.UpdateWithExplicitTransaction
PRINT ‘execution completed’
GO
SELECT
 @@ERROR AS [@@ERROR],
 @@TRANCOUNT AS [@@TRANCOUNT]
GO

 

 

 

Assuming default session settings, check all that apply:

a)      Proc execution continues after the failed UPDATE

b)      @@ERROR is zero

c)       @@TRANCOUNT is zero

d)      The PRINT statement is executed

Let me first mention something important about timeouts before I provide the correct answer(s).  A command timeout occurs in the client application, not the SQL Server backend.  A timeout is basically just a cancel request that is sent by the client API when a command executes longer than the specified interval.  A timeout is very much like pressing the stop button in Query Analyzer or Management Studio because you feel a query has been running too long.  The only difference is that the stop is issued by the client API on behalf of the application.

Both “A” (proc continues) and “D” (PRINT executes) are false because the attention event from the client instructed SQL Server to cancel the currently executing batch in its entirety.  No code after the UPDATE executes, including the PRINT statement following the stored procedure execute.  This is logical since a query cancel or timeout wouldn’t be much use if SQL Server continued executing statements afterward.

“B” (zero @@ERROR) is true.  @@ERROR is zero because no error occurred on the backed; SQL Server successfully canceled the batch per the client cancel request after the timeout.  The timeout error is raised only on the client by the API to notify the application (SSMS/QA in this example) that the command timed out.  SSMS and QA simply catch the error and display the error message from the API.

“C” (zero @@TRANCOINT) is false because XACT_ABORT OFF is the default session setting.  With XACT_ABORT OFF, it is the client application’s responsibility to trap the timeout error and rollback the transaction if necessary.  The transaction is left open and uncommitted following the timeout error.  This can have serious and undesirable consequences if the application performs other work on the connection, unaware of the open transaction.

 

Using SET XACT_ABORT

SET XACT_ABORT specifies what action SQL Server should take following run-time errors.  The default session setting is SET XACT_ABORT OFF, which indicates that only the Transact-SQL statement that raised the error is rolled back and the transaction continues.  Depending on the severity of the error, the entire transaction may be rolled back and batch aborted, even with SET XACT_ABORT is OFF.   

A side effect of SET XACT_ABORT OFF is that a cancel/timeout error can leave an open transaction so it’s the client’s responsibility to cleanup following cancel/timeout.  To safeguard against leaving an open transaction, applications that execute transactions with SET XACT_ABORT OFF need to roll back transactions and perhaps close the connection following SQL exceptions.  

Note that with connection pooling, simply closing the connection without a rollback will only return the connection to the pool and the transaction will remain open until later reused or removed from the pool.  This can result in locks begin held unnecessary and cause other timeouts and rolling blocks.

SET XACT_ABORT ON instructs SQL Server to rollback the entire transaction and abort the batch when a run-time error occurs.  Compile errors (e.g. syntax errors) are not affected by SET XACT_ABORT. 

In my experience, SET XACT_ABORT ON provides the desired behavior in most cases.  I’ve never run into a situation where I wouldn’t want to rollback a transaction following a cancel or timeout.   I nearly always specify SET XACT_ABORT ON in stored procedures that contain explicit transactions to ensure that transactions are rolled back even if the application code doesn’t clean up properly.  The only time I don’t use XACT_ABORT is in rare cases where I need to trap and handle specific errors in Transact-SQL and continue.

I strongly recommend that SET XACT_ABORT ON be included in all stored procedures with explicit transactions unless you have a specific reason to do otherwise.  The consequences of an application unwittingly performing work on a connection with an open transaction are disastrous.

SQL Server error handling in general is a huge topic I focused on only on timeout errors and SET XACT_ABORT here.  For a thorough discussion of SQL Server error handling, I suggest perusing articles Implementing Error Handling with Stored Procedures and Error Handling in SQL Server – a Background by SQL Server MVP Erland Sommarskog.

 

posted @ Saturday, October 20, 2007 4:12 PM

Print

Comments on this entry:

# re: Use Caution with Explicit Transactions in Stored Procedures

Left by Dan Guzman at 10/31/2007 7:26 AM
Gravatar
I always use an explicit transaction when an atomic
unit of work is desired regardless of volume. It's
likely that you will be fine without a transaction but
you can't really guarantee that unless you include
one.

# re: Use Caution with Explicit Transactions in Stored Procedures

Left by Robert Crawford at 2/21/2008 2:16 PM
Gravatar
Thank you!!! I spent about a week and a half investigating a very strange problem we were having, and this article explains exactly what is going on! My boss says you are the SQL King.

# re: Use Caution with Explicit Transactions in Stored Procedures

Left by Dan Guzman at 2/21/2008 2:40 PM
Gravatar
Thanks for the feedback, Robert. This was a hard lesson leaned for me so I'm glad this info helps others too.

# re: Use Caution with Explicit Transactions in Stored Procedures

Left by Chris Leonard at 2/2/2009 11:40 AM
Gravatar
This is probably the best write-up I've ever seen explaining this. It is frustrating to try to explain this to application developers, because they generally don't understand that the transactions they can control are the same ones controlled on the server using stored procedures, and they also have a deeply-rooted intuition that timeouts *should* cause rollbacks. Thanks for writing this up. I know I learned about this the hard way.

The specific things I learned include (a) client code does not usually clean up after itself unless it's written by somebody with a decent understanding of transaction handling; (b) most of that code is not written by people that understand transaction handling very well; and (c) you can tell somebody for years, literally, that timeouts do not cause rollbacks, and they still won't "get it."

Thanks for taking the time to write this up.

Cheers,
Chris Leonard

# re: Use Caution with Explicit Transactions in Stored Procedures

Left by Steve at 4/21/2009 2:14 PM
Gravatar
Would I still need set xact_abort on if I'm using begin try logic in SQL Server 2005

# re: Use Caution with Explicit Transactions in Stored Procedures

Left by Dan Guzman at 4/23/2009 7:14 AM
Gravatar
Yes, Steve, SET XACT_ABORT ON is still needed with structured error handling. A client attention event (timeout or query cancel) will stop the executing code and the CATCH block will not be invoked. This could leave an open transaction unless the application code performs a rollback or closes the connection.

When you use XACT_ABORT with structured error handling, you should check XACT_STATE() in the CATCH block to determine whether or not COMMIT/ROLLBACK is possible. There is more on this in the Books Online along with an example.

# re: Use Caution with Explicit Transactions in Stored Procedures

Left by Sally Hubbard at 11/30/2009 3:00 PM
Gravatar
Thanks for the details, Dan. Is the practice of using "xact_abort on" of any value when the sp contains only SELECT statements? Will setting it on change the type of locks normally applied? If you do not also wrap the statements in a BEGIN/END TRANSACTION block, will a timeout still leave an open transaction for the SELECT statement ?

# re: Use Caution with Explicit Transactions in Stored Procedures

Left by Dan Guzman at 12/1/2009 7:04 AM
Gravatar
Hi, Sally.

XACT_ABORT changes error handling behavior but doesn't change locking when no explicit transaction is specified. Without an explicit transaction, each statement is in an individual transaction that will be committed
or rolled back when the statement completes (successsfully or not) regardless of the XACT_ABORT setting. I think the only advantage of XACT_ABORT without an explicit transaction is that it can prevent the remainder of proc from continuing after an error even when the proc contains no error handling code:

CREATE PROC dbo.foo
AS
SET XACT_ABORT ON;
EXEC sp_executesql N'select * FROM MissingTable';
EXEC sp_executesql N'select * FROM AnotherMissingTable'; --never reached
GO

EXEC dbo.foo
GO

# re: Use Caution with Explicit Transactions in Stored Procedures

Left by Michael Stewart at 3/19/2010 1:48 PM
Gravatar
We have a Win32 client application written in VB6 with an MS SQL 2005 back-end. It's a three-tier application and recently we have been seeing rows simply disappear out of the blue. A clerk will enter a new customer in the app and go back later to see that the account was created; however, later in the day, or the next day there is no trace of the account anywhere in the database. We have noticed time-out errors on the application server, but we're not sure of the exact cause of this problem. After reading your article, it sounds like we need to add the SET XACT_ABORT ON for all of our stored procs that use BEGIN TRANSACTION. Is this correct or do you have any other suggestions. This problem has plagued us now for 2 months and it's keeping me up at night. Any thoughts?

Thanks in advance.

# re: Use Caution with Explicit Transactions in Stored Procedures

Left by Dan Guzman at 3/21/2010 10:21 PM
Gravatar
If you have a persistent database connection and a timeout occurs during an open transaction, it could very well result in these symptoms. I suggest you include SET XACT_ABORT ON in all of your procs to ensure the transaction is rolled back following a time-out.

# re: Use Caution with Explicit Transactions in Stored Procedures

Left by Will at 4/7/2010 2:05 AM
Gravatar
Is there a way to leverage the XACT_ABORT options through APIs like ODBC?

# re: Use Caution with Explicit Transactions in Stored Procedures

Left by Ravi at 2/17/2011 11:29 PM
Gravatar
Thanks for posting this. Although it is an old post, it is still applicable to SQL server 2008, hence my query-
BOL for SQL Server 2008 says
XACT_ABORT must be set ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server. The only case where this option is not required is if the provider supports nested transactions.
Does it mean that all client connections come with default set to ON ?

BOL has an example of how using XACT_ABORT off you can continue a transaction on foreign key errors. If continue on foreign key error or some other error is my requirement how do I handle it in a stored procedure which will be called by client applictaion and I still want to account for timeouts.

# re: Use Caution with Explicit Transactions in Stored Procedures

Left by guzmanda at 2/18/2011 7:21 AM
Gravatar
Hi, Ravi.

I believe the note about OLE DB providers is regarding linked servers rather than general client access via the OLE DB. The XACT_ABORT ON requirement is so that the Distributed Transaction Coordinator can enlist in the distributed transaction controlling both local and remote updates.

I ran a simple test to verify XACT_ABORT OFF behavior using the VBScript below. The batch continued after the error and Profiler showed that XACT_ABORT was not explicitly set.


On Error Resume Next
Set oCn = CreateObject("ADODB.Connection")
oCn.Open "Provider=SQLNCLI;Data Source=MyServer;Initial Catalog=tempdb;Integrated Security=SSPI"

'create test table
oCn.Execute "CREATE TABLE dbo.XACT_ABORT_TEST(" & _
"Col1 INT NOT NULL CONSTRAINT PK_XACT_ABORT_TEST PRIMARY KEY" & _
");"

'run test: first insert succeeds, second fails, batch continues and third succeedes
oCn.Execute("SET NOCOUNT ON;" & _
"INSERT INTO dbo.XACT_ABORT_TEST VALUES(1);" & _
"INSERT INTO dbo.XACT_ABORT_TEST VALUES(1);" & _
"INSERT INTO dbo.XACT_ABORT_TEST VALUES(2);")

If Err <> 0 Then
MsgBox Err.Description
End If

'show table has 2 rows
Set oRs = oCn.Execute("SELECT COUNT(*) FROM dbo.XACT_ABORT_TEST;" & _
"DROP TABLE dbo.XACT_ABORT_TEST;")
MsgBox "Row count is " & oRs.Fields(0)

rRs.Close

oCn.Close

# re: Use Caution with Explicit Transactions in Stored Procedures

Left by Pal at 3/23/2011 7:20 AM
Gravatar
A very good article! The information contained in it should be included in BOL of course.

I just have a problem with the following sentence: "Depending on the severity of the error, the entire transaction and/or batch may be rolled back even with SET XACT_ABORT is OFF". This suggests that a batch can be rolled back (including the already committed transactions in it). Perhaps "the entire transaction may be rolled back and the batch cancelled" would be clearer.

# re: Use Caution with Explicit Transactions in Stored Procedures

Left by guzmanda at 3/30/2011 7:11 AM
Gravatar
Thanks for the feedback, Pal. The wording you suggested is clearer and I will change my post accordingly.

# re: Use Caution with Explicit Transactions in Stored Procedures

Left by Adam M. at 6/2/2011 1:58 PM
Gravatar
It may be worthwhile to note that RAISERROR() does not trigger a rollback even when XACT_ABORT is on, so you must still roll back the transaction manually in that case.

# re: Use Caution with Explicit Transactions in Stored Procedures

Left by Aruna at 9/22/2011 9:53 PM
Gravatar
Hi,
Can we apply this
'SET XACT_ABORT = ON' globally on SQL server so that I need not to change all SPs IF I have to put this statement in all SPS.
Thank you,
Aruna

# re: Use Caution with Explicit Transactions in Stored Procedures

Left by guzmanda at 9/23/2011 7:27 AM
Gravatar
You can use sp_configure to set default connection options at the instance level. XACT_ABORT is bit 16384 so you can turn on the option using the script below. This script uses a bitwise OR (|) to retain other user options that might be set.

I suggest you proceed carefully before doing so since it will alter behavior of existing scripts. For example, a script that checks @@ERROR after an INSERT in order to detect a primary key violation and perform an UPDATE instead will no longer update the row since the batch will be terminated after the constraint violation.

DECLARE @UserOptions int;
SELECT @UserOptions = CAST(value_in_use AS int)
FROM sys.configurations
WHERE name = 'user options';

SET @UserOptions = @UserOptions | 16384;

EXEC sp_configure 'user options', @UserOptions;
RECONFIGURE;

# re: Use Caution with Explicit Transactions in Stored Procedures

Left by Matt at 10/15/2012 9:15 PM
Gravatar
I don't understand why the first script in your article should give a timeout? Can you explain further?

Thanks.
Comments have been closed on this topic.