Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there a way in SQL Server 2008 R2 to cause a timeout for a database modification involving a transaction? We have a scenario where our application code hangs or throws an exception and fails to perform a rollback or commit. This then causes other sessions to hang waiting for the transaction to complete.

share|improve this question
add comment

2 Answers

I'm answering this with hesitation as there isn't enough information in your description of the problem to be 100% sure this is the best advice. "Hangs or throws an exception" suggests the source of the issue isn't properly understood, so proceed with caution.

The simplest solution to this is probably SET XACT_ABORT ON.

XACT_ABORT determines whether SQL Server will rollback a transaction in the event of a run-time error. The default SET XACT_ABORT OFF will rollback only the statement that caused an error, leaving any parent transaction open.

The "gotcha" side-effect of the default setting is that a timeout can cause exactly the same problem, an open transaction that is the clients responsibility to handle and rollback. If the client doesn't try/catch/rollback, the transaction will remain open until attended to with (and I quote @gbn) the ultra-violence of KILL <spid>.

The oft quoted Erland Sommarskog's articles on error handling in SQL Server contain all the background and strategy you need for dealing with these scenarios and more.

Edit (following comment): To identify open transactions, sp_whoisactive is probably the most feature complete.

share|improve this answer
    
I did find, with some Googling, ways to find open transactions when the hang occurs - maybe this is the best solution. That is to find what is cause the unclosed transaction in the code and fix the holes in the code. For reference for others. DBCC OPENTRAN returns the oldest active transaction -> msdn.microsoft.com/en-us/library/ms182792.aspx Or something more like this? -> weblogs.sqlteam.com/mladenp/archive/2008/04/29/… –  David Gray Wright Jan 19 '12 at 5:57
    
I've always thought Erland didn't do justice to the SET XACT_ABORT sommarskog.se/error-handling-I.html#XACT_ABORT –  gbn Jan 19 '12 at 6:14
add comment

Extending Mark's answer...

When a client timeout event occurs (.net CommandTimeout for example), the client sends an "ABORT" to SQL Server. SQL Server then simply abandons the query processing. No transaction is rolled back, no locks are released.

Now, the connection is returned to the connection pool, so it isn't closed on SQL Server. If this ever happens (via KILL or client reboot etc) then the transactions+locks will be cleared. Note that sp_reset_connection won't or doesn't clear them, even though it is advertised to do so

This detritus from the abort will block other processes.

The way to make SQL Server clear transactions+locks on client timeout (strictly, ABORT events) is to use SET XACT_ABORT ON.

You can verify this be opening 2 query windows in SSMS:

Window 1:

In menu Query..Query Options set a timeout of 5 seconds then run this

BEGIN TRAN
UPDATE sometable WITH (TABLOCKX) SET foo = foo WHERE 1 = 0;
WAITFOR DELAY '00:00:10' -- just has to be longer then timeout

Window 2, this will wait forever (or hit your timeout)

SELECT * FROM sometable

SET XACT_ABORT ON has interesting side effects too:

  • @@TRANCOUNT is set to zero on the implicit rollback but error 266 is suppressed (this happens if @@TRANCOUNT is different on entry and exit from a stored proc)
  • XACT_STATE will be -1 (it's "doomed")

The combination of this means that you can't use SAVEPOINTS (although, I can't recall exact behaviour) for partial commits/rollbacks. Which suits me

SO links on SET XACT_ABORT:

On nested stored procs:

On sp_reset_connection:

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.