Sql-server – SQL Server Transaction Timeout

lockingsql-server-2008-r2transaction

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.

Best Answer

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:

https://stackoverflow.com/a/1539686/27535 (me)
https://stackoverflow.com/a/919279/27535 (me)
https://stackoverflow.com/a/1150350/27535 (Remus Rusanu)

On nested stored procs:

https://stackoverflow.com/a/2074139/27535 (me)

On sp_reset_connection:

https://stackoverflow.com/a/2924456/27535

Related Solutions

Sql-server – connection pooling, transactions, nested transaction and rollback

There is no such thing as nested transactions in any released version of SQL Server. You can say BEGIN TRANSACTION as many times as you want, but a ROLLBACK affects all of them (never mind what @@TRANCOUNT says - it only reflects nesting level).

From the documentation:

It is not legal for the transaction_name parameter of a ROLLBACK TRANSACTION statement to refer to the inner transactions of a set of named nested transactions. transaction_name can refer only to the transaction name of the outermost transaction. If a ROLLBACK TRANSACTION transaction_name statement using the name of the outer transaction is executed at any level of a set of nested transactions, all of the nested transactions are rolled back. If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all of the nested transactions, including the outermost transaction.

A demonstration:

CREATE TABLE dbo.foo(a INT);

BEGIN TRANSACTION;
  INSERT dbo.foo(a) SELECT 1;
  BEGIN TRANSACTION;
    INSERT dbo.foo(a) SELECT 2;
    BEGIN TRANSACTION;
    ROLLBACK TRANSACTION;

SELECT tc = @@TRANCOUNT;

SELECT a FROM dbo.foo;

DROP TABLE dbo.foo;

Results:

tc    
----
0

1 row(s) affected.

a
----

0 row(s) affected.

So, there is no way to only roll back part of a "nested" transaction - it's all or nothing. If you want previous parts of this transaction to commit even if later parts fail, then stop nesting, and commit the first transaction before starting the next one.

Sql-server – How are these two SQL Server rollbacks different

As indicated above, after running more tests, I arrived at an calculated conclusions. I summarized all of them into a blog post here, but I'll copy some content over to this post for posterity.

Conjecture (based on some tests)

As of now, I do not have a clear explanation as to why this is. But following are my estimations based on the artifacts gathered during the tests.

Rollback happens in both scenarios. One is explicit rollback (user hitting Cancel button), the other is implicit (Sql Server making that decision internally).

In both scenarios, the traffic going to the log file is consistent. See the images below:

Scenario 1:

Scenario 2:

One artifact that reinforced this line of thinking is capturing Sql Trace during both the scenarios.
- Scenario 1 is self evident a.k.a. when we hit ‘Cancel’, it rolls back.
- In Scenario 2, the error message is displayed after performing ‘rollback’ implicitly. In Sql Trace, we see the error message “The transaction log for database ‘SampleDB’ is full” a long time before the message is displayed on the screen. So, my guess is that rollbacks happens in both scenarios, but the error message is Scenario 2 is displayed after successfully and completely performing the rollback.
Scenario 2 seems to take longer as as it progresses much further along, so the rollback takes longer.

Unexplained behavior:

Why does the log file usage vary so much?
- It increases to 90%, then down to 85%, then up to 99% and hovers there for a long time. I see it going up and down like this several times: 99.2 %, 99.8 %, 99.1 %, 99.7 %. Why does this happen?
- One possible explanation is that, there might be a background process (something like Log Flush) that cleans up log file every few minutes. And everytime it kicks in, some entries are cleared up, resulting in more free space available.

Any ideas to help explain this behavior in a better way are welcome.