What if my process is too long for transaction timeout duration?

Question

Hi,

I would like to know what are the best practices, if you have a quite long lasting process that ends up with a transaction timeout and which should definitely be possible to rollback if any exception is thrown within?

Imagine that all along the application set timeout duration is quite enough but for such a specific one it is not long enough.

How do you overcome scenarios similar to this? Do the people kind of simulate transactions to solve this or re-set timeout duration only for this process... Any other ways that are safe?

Thanks, burak ozdogan

Answer 1

I would simply remember the current timeout value, set a new longer timeout, perform the transaction and reset the timeout to the old value.

Timeout values can usually be set at the connection level, so other connections shouldn't be impacted.

Answer 2

Personally, if I have a process that is timing out and that is unusual, then something is wrong or has changed. Find out what. I usually start with what has changed recently.

If it times out repeatedly, I revisit the process and find a way to optimize it. The one thing I never do is increase the timeout.

Answer 3

You want to use SqlCommand.CommandTimeout.

Just set it to an arbitrarily high number if needed, such as 300 seconds.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout%28VS.71%29.aspx

Answer 4

I would say, either try to split up the long lasting task in a few smaller tasks (in case you are block updating a DataTable for example), either increase the transaction timeout for the given task.

Answer 5

Something is usually wrong:

• Your statements are too slow .. use indexes, rewrites etc. to tune the statements.
• Try to do things in a different way (e.g. better algorithm).
• You're trying to do "too much" for a single statement, try to split them into statements that work on a smaller subset or use smaller sub-steps to accomplish the "big thing".
• Something has changed so that things take longer than they used to. Find out what is and do what you can.

Only if all fails (or is unreasonable to do), fiddle around with the timeout parameter.