I have the ASP.Net application, that uses SqlTransaction.BeginTransaction before the call to Update SP and commit after the call.If any exception would happened, transaction should be Rollback.
The code snippet is the following:
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
try
{
ds = SqlHelper.ExecuteDataset(trans, CommandType.StoredProcedure, SPName);
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
//Log the error
}
}
We noticed that during update of web.config the ASP.NET appDomain was recycled (or restarted) and it seems that at the time transaction was left open(neither Commit nor Rollback). And it caused timeout for all subsequent updates to the locked table.
I beleive that the best approach is to remove transactions from .Net code and implement them inside each SP.
It is a fair amount of t-SQL coding, but thanks to SQL Server 2005 TRY/CATCH feature it can be done easier.
In MSDN the article "Improving .NET Application Performance and Scalability" suggests
Use SQL transactions for server-controlled transactions on a single data store.
Use ADO.NET transactions for client-controlled transactions on a single data store.
Use DTC for transactions that span multiple data stores.
Other related links:
I found the article Tips On How to Minimize SQL Server Blocking very useful. In particular it pointed to the commands
DBCC OPENTRAN and DBCC INPUTBUFFER ( session_id [ , request_id ] )