Geeks With Blogs

News My Blog has been MOVED to
Michael Freidgeim's OLD Blog My Blog has been MOVED to
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: 
using (SqlTransaction trans = conn.BeginTransaction())
            ds = SqlHelper.ExecuteDataset(trans, CommandType.StoredProcedure, SPName);
      catch (Exception ex)
//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 on Wednesday, May 16, 2007 2:25 AM ASP.NET , SQL Server | Back to top

    Comments on this post: Start Transaction from ASP.Net application can cause unexpected locks on the database.

    # re: Start Transaction from ASP.Net application can cause unexpected locks on the database.
    Requesting 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

    Left by web development company on Aug 14, 2009 3:07 AM

    Your comment:
     (will show your gravatar)

    Copyright © Michael Freidgeim | Powered by: