Start Transaction from ASP.Net application can cause unexpected locks on the database.

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.
 See TRY...CATCH in SQL Server 2005: An Easier Approach to Rolling Back Transactions in the Face of an Error
The article Managing Transactions in SQL Server Stored Procedures suggests for simple batch statements that are known at compile time I tend to use stored procedures. However when I need to issue a series of atomic statements but I don't know what statements will need to be executed, exactly, until runtime ADO.NET transactions should be used.
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 ] ) 

    posted @ Wednesday, May 16, 2007 2:25 AM
    Print

    Comments on this entry:

    # re: Start Transaction from ASP.Net application can cause unexpected locks on the database.

    Left by web development company at 8/14/2009 3:07 AM
    Gravatar
    Quite inspiring,

    I got that error and it seem that with easyily puting it in a transaction and rolling it back in the case of lock he problem would be solved

    thanks

    Your comment:



    (not displayed)

     
     
     
     
     

    Live Comment Preview:

     
    «August»
    SunMonTueWedThuFriSat
    272829303112
    3456789
    10111213141516
    17181920212223
    24252627282930
    31123456