none
Transactions are never closed RRS feed

  • Question

  • We are using a .NET 3.0 application to access a SQL Server 2005 database. We run all write operations in transactions by using TransactionScope. We create all TransactionScope variables in using blocks and sets the transaction timeout to one minute. This works fine most of the time, but sometimes a transaction gets stuck. From the activity monitor, we can see that the last batch was completed a long time ago but one transaction is still open. Since this transaction holds some locks, all other batches trying to access the same resource gets timed out. By debugging the code, we can see that the code never returns from TransactionScope.Dispose. The call stack is as follows:

     

    [In a sleep, wait or join]

    System.Threading.Wait(object obj, int millisecondsTimeout, bool exitContext)

    System.Transactions.CommittableTransaction.Commit()

    System.Transactions.TransactionScope.InternalDispose()

    System.Transactions.TransactionScope.Dispose()

    <user code>

     

    It appears that the .NET framework fails to commit the transaction. We are running about 10-20 batches/s and this error occurs after a few hours or days. If we kill the Sql server session that keeps the transaction open, other queries can complete succesfully but the application thread is still frozen.

     

    Any suggestions on what might cause this behaviour is appreciated.

    Erik

    Wednesday, September 19, 2007 12:08 PM

Answers

All replies

  • When you do a sp_who2 in SQL 2005, is this SQL Process the main blocker? (Meaning its BlkBy column is empty and its process ID is in other processes BlkBy column ).

    If so, can you send the sp_who2 line for this process?

    Wednesday, September 19, 2007 1:29 PM
  • The TransactionScope ends at the connection to SQL Server but all implements IDisposable so you could wrap all with the second Using statement which calls dispose for you automatically. The other option is to implement the dispose pattern, check the link below for details.

     

    http://blogs.msdn.com/andrewdownum/archive/2005/01/04/346448.aspx  

     

    Wednesday, September 19, 2007 1:37 PM
  • Yes, it is the main blocker. I had to restart the application to run other tests so I can't run sp_who2 right now. I can send the result the next time the error occurs. From the information I got from Activity monitor it should be something like this:

     

    SPID   status       BlkBy          Command                  ProgramName

     53       sleeping    -                 Awaiting command     .Net SqlClient Data Provider 

     

    Since I could see from the sys.dm_exec_sessions view that there was no request for SPID 53 I guess the REQUESTID column should have been 0.

    Wednesday, September 19, 2007 2:12 PM
  • We already use Using statements. Our code looks like:

     

    try

    {

    using (TransactionScope aTransaction = CreateTransactionScope)

    {

    using (SqlConnection aConn = new SqlConnection(myConnectionString))

    using (SqlCommand aSqlCommand = aConn.CreateCommand())

    {

       //Set SqlCommand parameters and execute query

    }

    aTransaction.Complete();

    }

    }

    catch (SqlException e)

    {

      //handle error

      //Make recursive call to the same method if transaction should be rerun (i.e if deadlock was detected)

    }

    catch (TransactionException e)

    {

      //handle error

    }

     

     

    We can see that aTransaction.Dispose() gets called at the end of the using statement. The problem is that that call never returns.

    Wednesday, September 19, 2007 2:25 PM
  • That could be related to how this class works with the ADO.NET and SQL Server objects and it is not good, depending on your operations the Scope gets promoted to distributed transaction which takes away the benefits of this class.  I would also check firewall in the box but please read the comments of other users about related issues. 

     

    http://msdn2.microsoft.com/en-us/library/system.transactions.transactionscope.aspx

     

    Wednesday, September 19, 2007 3:20 PM
  • The sp_who2 output for the hanging Sql session is:

     

    69 sleeping WcuWriter SECSERV1 . WCU2_0_FAT_Test AWAITING COMMAND 0 0 09/19 16:32:56 .Net SqlClient Data Provider 69 0

     

    For the blocked processes:

    51 SUSPENDED WcuWriter SECSERV1 72 WCU2_0_FAT_Test DELETE 0 0 09/19 18:29:43 .Net SqlClient Data Provider 51 0

    53 SUSPENDED WcuWriter SECSERV1 72 WCU2_0_FAT_Test DELETE 0 9 09/19 18:29:24 .Net SqlClient Data Provider 53 0

    72 SUSPENDED NT AUTHORITY\SYSTEM SECSERV1 69 WCU2_0_FAT_Test DELETE 359 496 09/19 17:00:00 SQLAgent - TSQL JobStep (Job 0x5CE753EE8A4D74498F50BFBE9EBAF4C8 : Step 1) 72 0

     

     

     

    Our operations are simple read and write operations on one database and we have no linked servers and if I query sys.dm_tran_session_transactions all transactions have is_local set to 1, so I don't think the transactions gets promoted to distributed transactions.

    Wednesday, September 19, 2007 4:56 PM
  • So what is the SQL Server Agent doing in your output because I know what you can do with it and that is a lot, simple read and write operation does not include the Agent.

     

    Wednesday, September 19, 2007 5:02 PM
  • We have an expiry date column in some of our tables. The SQL Agent is set to regulary execute a stored procedure that removes any expired rows. I have tried to disable the SQL Agent and still get the same problem.

     

    Thursday, September 20, 2007 8:20 AM
  • From the database I can see that one of the sessions are not running any request but have a opened transaction. No request is enlisted in that transaction. When querying the row modified by the last completed batch for that session, I get the data before the update if I am using read committed isolation level and the data after the update if using read uncommitted isolation level. Other sessions are blocked by this session since it holds some locks. Requests that don't need to acquire any of those locks completes normally. What I also can see is that all running requests have either two open transactions (write operations) or no open transactions (read operations), while the frozen session has one open transaction. If I kill the frozen session that transaction is rolled back and the blocked sessions can continue.

    So from information I can get from the database this is normal behaviour for a transaction that is started but never committed or rollbacked.

     

    From the debugger I can see that most threads are waiting for a database call, until it times out after 30 seconds. However, one of the threads are running the TransactionScope.Dispose method. By checking the kind of database operation run, and checking the values used, I can see that this is the thread that has started the transaction that are left open in the database. By looking at the external code part of the call stack (the one I sent in the first post), I see that it is waiting for a monitor in the CommittableTransaction.Commit mothod. From reflactor I get the code.

     
    public void Commit()
    {
        if (DiagnosticTrace.Verbose)
        {
            MethodEnteredTraceRecord.Trace(SR.GetString("TraceSourceLtm"), "CommittableTransaction.Commit");
            TransactionCommitCalledTraceRecord.Trace(SR.GetString("TraceSourceLtm"), base.TransactionTraceId);
        }
        if (base.Disposed)
        {
            throw new ObjectDisposedException("Transaction");
        }
        lock (base.internalTransaction)
        {
            if (base.complete)
            {
                throw TransactionException.CreateTransactionCompletedException(SR.GetString("TraceSourceLtm"));
            }
            base.internalTransaction.State.BeginCommit(base.internalTransaction, false, null, null);
            while (!base.internalTransaction.State.IsCompleted(base.internalTransaction) && Monitor.Wait(base.internalTransaction))
            {
            }
            base.internalTransaction.State.EndCommit(base.internalTransaction);
        }
        if (DiagnosticTrace.Verbose)
        {
            MethodExitedTraceRecord.Trace(SR.GetString("TraceSourceLtm"), "CommittableTransaction.Commit");
        }
    }
    

    Since the while loop after BeginCommit is the only call to Monitor.Wait I guess that is where the execution has frozen. I can see that the type of internalTransaction.State is TransactionStateSPC, which seems to be one of the intermediate active states between BeginCommit and commit is completed. By checking the absoluteTimeout value of internalTransaction I can see that the transaction timeout has expired a long time ago, but propably hadn't expired when Commit was called. After I killed the database session, this thread was still executing the exact same thing. The application is runnig several threads using transactions at the same time, but each transaction is only used by one thread and I am not messing with the internalTransaction object directly in any way.

     

    The only conclusion I can make is that this is related to a .Net framework or Sql Server bug. If there is any fix or workaround, or any idea of something I might be doing wrong, it would be very helpful.

    Thursday, September 20, 2007 11:51 AM
  • If the Agent is doing periodic deletes you need to setup a separate of operation for that and you are correct you can roll back none commited transaction, however you have the option of using lock hints instead of locks with the two other types of transactions in the Microsoft platform.  And there is no bug the problem is the monitor it is like mutex but the operating system cleans up idle mutex but not monitors and no mutex is not suited for your operation you could look into semaphore but the question is why are you adding these to transaction locks?  The link below is a white paper Microsoft paid for to help when you are using this class, if that did not help close this thread and post at the transaction programming forum they are the experts of this class.  Hope this helps.


    http://www.microsoft.com/downloads/details.aspx?familyid=AAC3D722-444C-4E27-8B2E-C6157ED16B15&displaylang=en

     


    http://msdn2.microsoft.com/en-us/library/system.threading.semaphore.aspx

    Thursday, September 20, 2007 1:51 PM
  • Since I am not using Windows Vista I didn't consider any of the forums under Software development for windows vista. I will try to post my problem there and see if that helps.

    Friday, September 21, 2007 8:49 AM
  • I see you have posted there but the transaction programming forum is not Vista specific just be patient you could get help on how to deal with monitor and transaction in a nested transaction application.

     

    Friday, September 21, 2007 12:43 PM