Is it safe to repeat the command / connection timeout?

Question

We used the mechanism for a long time (a class called SqlDeadlockHelper ), and this helped us a lot when trying to fail database calls due to a deadlock. SqlDeadlockHelper will catch a SqlException , find out that it is a dead end, and try again. The second attempt almost always succeeds.

Is it possible to do something similar for command and / or connection timeouts? I mean, it’s not possible to shut down SQL Server, just before the timeout, before the data returns to the caller, right?

Edit:

Transactions are referred to as a way to handle calls as units of work. Thus, it can completely or completely roll back. But what about a single ADO.NET call that does only one. Do I need to wrap this in a transaction?

Answer 1

Depending on your unit of work, SQL may complete some of the work before it closes and throws an error. The way you process units of work is transactions. Most SQL databases support transactions. You need to wrap units of work in a Begin, Commit, and Rollback transaction.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.aspx

Answer 2

From TechNet Library

After a deadlock is detected, the Database Engine completes by selecting one of the threads as a victim of a deadlock. The database engine completes the current batch being run for the thread, rollback the deadlock victim transaction, and returns error 1205 for the application. Rolling back a transaction for a deadlock victim releases all locks held by the transaction. This allows transactions of other threads to unlock and continue. Error 1205 deadlock victim writes thread information and resources involved in a deadlock in the error log.

So, your transaction is safe to rollback, and you can try it again.

EDIT

Being lazy **, I have not fully studied your question. Let's see:

Is it possible to do something similar for command and / or connection timeouts?

I would not do the same generalization because you could try to read data from a database that is not accessible at all. You must have the maximum number of attempts.

I mean, it’s not possible to work on SQL Server, only for a timeout before the data returns to the caller, right?

Never heard of this, and it should not be possible if you use transactions . And you should use them with XACT_ABORT, the flag Specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error. However, for work it is impossible to complete, not incomplete, see the zombie transaction .

I also found this .