SqlException: Timeout Expired

Question

I have a process where two threads are continuously executing sql queries on the same tables. Is there any chance that one of these two threads gets Timeout error? If so, what is the best practice to avoid this?

I am getting following error and resulting is process crash.

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

Answer 1

There are many reasons you may receive a timeout. If you are getting a connection exception then what is the timeout in the SqlConnection? If not then what is the command timeout in the SqlCommand?

Is your query properly structured? How many rows do you expect your query to return? How many rows are there in the two tables?

Answer 2

Sounds like you've got a lock on a table. Look into locking mechanisms. If you lock entire table, it's inefficient, instead, you can lock a subset of a table, i.e. - row.

Also in your connection string, you can decrease the timeout. Change it to 2 seconds (time to establish connection, not query data). If you do get that error, than you can work from there.

Answer 3

If your queries are only reading, not updating or inserting, you can use NOLOCKS.

SELECT * FROM MyTable With NOLOCK

This should stop any locking.

Answer 4

sound like Table locking... you should use a transactionscope with ReadUncommited option something like:

var islolation=new TransactionOptions();
isolation=IsolationLevel.ReadUncommitted;
using(var scope=new TransactionScope(TransactionScopeOption.requiresnew,isolation))
{
//code here
scope.complete();
}

also it would be nice to check if you are closing connection right after query execution.