3

Consider a stored procedure that updates some rows about in 60 seconds without using a transaction. We set ADO.NET's SqlCommand.Timeout to 30 seconds. 

SqlCommand.Timeout = 30;

When that timeout occurs at 30 seconds, will the stored procedure continue to run in database server or not? How does the server communicate this to the client?

Improve this question

3 Answers 3

Reset to default
7

The answer is no, your attempted action on the server will fail after 30s, your SqlCommand object will throw an exception in your code (below) and the implicit stored procedure transaction will rollback.

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

...at least this is the behavior that I can verify using SQL Server...

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Ther special here is "implicit transaction". ALL sql commands, at least each one alone, are AUTOAMTICALLY part of an IMPLICIT transaction, even if no transaction is set explicitly.
The exception would be an explicit transaction, that would not roll back at all and remain open/uncommitted unless SET XACT_ABORT ON is set - until the connection itself is closed. In a connection pool, that may never happen (next command might run in context of the transaction? not sure but is a risk)
2

I was wondering about the exact same. I could not find an answer, but did some experimenting, and it looks like some kind of cancel query is send:

  • If there is a trigger involved or the table is locked, it won't update.
  • if there are multiple statements, not in a transaction, it will cancel the ones after the timeout occured. What was already done stays done.
  • If you add a delay before your insert, nothing will be in your table.
  • if you add a delay after your insert, the insert will be written to your table.

I know this is a five year old post, but if I got here, others will too :-)

By the way, in ado.net, in order to increase your timeout, you must increase in both the sqlcommand and in the sqlconnection, and the defaults for the first is 15 sec, the second 30 sec. So if you just change the sqlcommand to 60 secs, it will still timeout after 30 sec, which can be rather puzzling.

Improve this answer

Comments

0

If you haven't closed your SQL connection yet, it should continue to run if it started proc execution already. (You should be able to test & verify this relatively easily.)

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.