Limit or abort a Sql Server transaction initiated by Asp.Net

Question

Goal: to limit the duration of a Sql Server transaction that was initiated on an Asp.Net web page.

A transaction is more than a single command, so SqlCommand.CommandTimeout is not strong enough.

Scenario: an Asp.Net page does something like this:

SqlConnection connection = new SqlConnection("yourserver");
connection.Open();
tran1 = connection.BeginTransaction();
command.Transaction = tran1;
command.CommandText = "select lots of stuff";
command.ExecuteScalar()
command.CommandText = "select some more stuff";
command.ExecuteScalar()
command.CommandText = "update some stuff";
command.ExecuteScalar()
tran1.Commit();
connection.Close();

If all the work takes far too long, locking resources along the way, then we want that transaction to be aborted, wherever it is at, either by Sql Server itself, or by Asp.Net. Also if the client machine that initiated it fell out of the window.

If Asp.Net aborts execution, but leaves the transaction open, or the Sql Command running, then that's not good enough. It's all about protecting all other users from too heavy work by one individual. That inividual then needs to reconsider the size of his work, break it up into smaller chunks.

In Asp.Net I've looked at ExecutionTimeout, ScriptTimeout, CommandTimeout. In Sql Server I've looked at QUERY_GOVERNOR_COST_LIMIT and LOCK_TIMEOUT.

None of these seem to achieve the goal.

Any ideas?

Better acept the answers you get, beggars aren't choosers. – vonbrand Mar 16 '13 at 01:33 — vonbrand, Mar 16 '13 at 01:33
Yes, you're quite right about that. Point taken. – Mar 16 '13 at 12:12 — , Mar 16 '13 at 12:12

score 0 · Answer 1 · answered Mar 18 '13 at 12:41

0

TransactionScope manages transactions automatically and it can be given a timeout value.

answered Mar 18 '13 at 12:41

usr

168,620
35
240
369

Ah, I hadn't remembered about that one. Thanks a lot! I'll do some tests with it. Though I'm still skeptical about the behaviour if the client machine (which is timing the transaction) falls out of a window, so to speak. Or the client process simply crashes. I'm afraid to find that the transaction will stay open in Sql Server. But let me do some tests and report back here. – Nick Mar 18 '13 at 16:58
SQL Server will detect a disconnected client (after some time...) and kill the connection and transaction. Need to measure the time it takes. – usr Mar 18 '13 at 17:29

Limit or abort a Sql Server transaction initiated by Asp.Net

1 Answers1