Transaction and Connection Timeout

[Guest]

We are running a lot of unit tests for the product which is built on .NET 1.1
with ADO.NET, and SQL server 2000. Recently some unit test encount this
error:
"System.Data.SqlClient.SqlException : Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not responding."

Here are some settings we have
connection pool = True
CommandTimeout = 300 (for command object, not the one in Connection object)

The one or several sql commands are wrapped in a transaction. It happened
randomly in different unit test. One of long process stored procedure hit
this problem quite often, but some very short db execution command happened
as well. But all failure happened way before 300s. Through research I notice
there is a transaction timeout which inherits value from connection timeout.
By default it is 15 second. So my questions are:
1) How does transaction timeout relate to CommandTimeout?
2) Which other areas I should look into to prevent Timeout? Any SQL server
setting can help reduce the chance of Timeout?
3) Does it related to connection pool?
4) Does it related to deadlock?

Thanks in advance,
Michael

 

[Marina Levit [MVP]]

The timeout could be due to a database deadlock due to transactions. If
there is a long running transaction holding on to a resource, and another
connection is trying to get at the same data locked by the transaction, then
this could happen.

 

[Guest]

Thanks for the reply. Deadlock is one of the areas our db team is looking
into. Are there any other area I should pay attention to since it timeout on
some short transaction as well even before reach the long transcation ones.

And I'm still bit confuse with CommandTimeout, and transcation timeout.
Anywhere I can find information about how these two setting affect each other.

Michael