none
System.Transactions does not close Open Transaction in Activity Monitor of SQL Server. Why? RRS feed

  • Question

  • I have a weird problem that I am not able to resolve.  I have a distributed transaction which inserts a record in a table in two databases.  I use System.Transactions to achieve this. After the distributed transaction has been committed, I notice from the Activity Monitor of SQL Server 2005 that the connection that executed the last SQL Statement always has an Open Transaction count of 1.  I tried querying the database using  SELECT * FROM sys.dm_tran_session_transactions which yields the same results. I am baffled and confused and would appreciate it if someone could tell me if this is a bug or not and provide me a solution.  I have recreated the problem from the production evnironment after writing similar code that simulates the production evnrionment as given below.  HELP!

    The SQL Script is:

    CREATE DATABASE DB1
    CREATE DATABASE DB2
    GO
    
    USE DB1
    CREATE TABLE TransactionTest
    (
         Id   int  NULL
    )
    GO
    
    USE DB2
    CREATE TABLE TransactionTest
    (
         Id   int  NULL
    ) 
    GO

    The C# code looks like this:

    using System;
    using System.Data;
    using System.Text;
    using System.Data.Common;
    using System.Transactions;
    using System.Diagnostics;
    using System.Data.SqlClient;
    
    namespace Test
    {
        public class Program
        {
            private const string _db1 
                = "Data Source=.;Initial Catalog=DB1;User ID=sa; Password=xxx; Type System Version=Latest;Connection Timeout=30;Application Name = TransactionTester;Connection Lifetime = 10;";
            private const string _db2
                = "Data Source=.;Initial Catalog=DB2;User ID=sa; Password=xxx; Type System Version=Latest;Connection Timeout=30;Application Name = TransactionTester;Connection Lifetime = 10;";
    
            private const string _query = @"INSERT INTO TransactionTest(Id) VALUES (1)";
            private const string _query2 = @"INSERT INTO TransactionTest(Id) VALUES (2)";
    
            static void Main(string[] args)
            {
                try
                {
                    //Distribution transaction starts when this event fires.
                    TransactionManager.DistributedTransactionStarted += delegate
                        (object sender, TransactionEventArgs e)
                    {
                        Console.WriteLine("Distributed Transaction Started");
                    };
                         
    
                    using (TransactionScope scope = new TransactionScope
                        (TransactionScopeOption.Required))
                    {
    
                        //Distribution transaction ends when this event fires.
                        Transaction.Current.TransactionCompleted += delegate
                            (object sender, TransactionEventArgs e)
                        {
                            Console.WriteLine(e.Transaction.TransactionInformation.Status);
                            Console.WriteLine("Distributed Transaction Completed");
                        };
                         
                        //Insert into the first database
                        using(SqlConnection conn1 = new SqlConnection(_db1)) 
                        {
                            Insert(conn1);
                        }
    
                        //Insert into the second database
                        using(SqlConnection conn1 = new SqlConnection(_db2)) 
                        {
                            Insert(conn1);
                        }
    
                        //Commit Distributed Transaction
                        scope.Complete();
                    }
    
                    Console.WriteLine("Execution completed! Press Enter..");        
                    
    Console.ReadLine(); } catch (Exception e) { Console.WriteLine(e); Console.ReadLine(); } } //Do the insertion private static void Insert(SqlConnection connection) { try { using (DbCommand command = new SqlCommand(_query)) { if (connection.State != ConnectionState.Open) connection.Open(); command.Connection = connection; command.ExecuteNonQuery(); } } catch (Exception e) { throw e; } } } }

    The observation here is this.

    1.  When this application is run exactly as in the code above, the activity monitor has 1 open transaction against a SPID
    2.  The Open Transaction goes down after 20 minutes or so
    3.  When the application is running, after the transaction is committed, even after restarting the MSDTC, the ActivityMonitor (after refresh) shows 1 open transaction against the same SPID
    4.  The Connection is being Disposed using the Using statement of C# which means that the connection is returned back to the connection pool.
    5.   This code runs with .NET framework 2.0 SP1 and SQL Server 2005 SP1
    6.  Also tried changing the connection string with transaction binding set to Implicit Unbind and Explicit Unbind.. just for the heck of it.

    I think this is very similar to http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/86591556-0319-496d-be4c-2c26634b9400/

    I cannot use SqlConnection.ClearAllPools() as the code similar to this is running as a windows service with multiple threads and each thread proc initiates a distributed transaction.

    Any thoughts/comments would be greatly appreciated.

    Thanks in advance!

    Ksrisriv

    Friday, May 22, 2009 10:46 AM

All replies

  • I have a weird problem that I am not able to resolve.  I have a distributed transaction which inserts a record in a table in two databases.  I use System.Transactions to achieve this. After the distributed transaction has been committed, I notice from the Activity Monitor of SQL Server 2005 that the connection that executed the last SQL Statement always has an Open Transaction count of 1.  I tried querying the database using  SELECT * FROM sys.dm_tran_session_transactions which yields the same results. I am baffled and confused and would appreciate it if someone could tell me if this is a bug or not and provide me a solution.  I have recreated the problem from the production evnironment after writing similar code that simulates the production evnrionment as given below.  HELP!

    The SQL Script is:

    CREATE DATABASE DB1
    
    CREATE DATABASE DB2
    
    GO
    
    
    
    USE DB1
    
    CREATE TABLE TransactionTest
    
    (
    
         Id   int  NULL
    
    )
    
    GO
    
    
    
    USE DB2
    
    CREATE TABLE TransactionTest
    
    (
    
         Id   int  NULL
    
    ) 
    
    GO
    
    

    The C# code looks like this:

    using System;
    
    using System.Data;
    
    using System.Text;
    
    using System.Data.Common;
    
    using System.Transactions;
    
    using System.Diagnostics;
    
    using System.Data.SqlClient;
    
    
    
    namespace Test
    
    {
    
        public class Program
    
        {
    
            private const string _db1 
    
                = "Data Source=.;Initial Catalog=DB1;User ID=sa; Password=xxx; Type System Version=Latest;Connection Timeout=30;Application Name = TransactionTester;Connection Lifetime = 10;";
    
            private const string _db2
    
                = "Data Source=.;Initial Catalog=DB2;User ID=sa; Password=xxx; Type System Version=Latest;Connection Timeout=30;Application Name = TransactionTester;Connection Lifetime = 10;";
    
    
    
            private const string _query = @"INSERT INTO TransactionTest(Id) VALUES (1)";
    
            private const string _query2 = @"INSERT INTO TransactionTest(Id) VALUES (2)";
    
    
    
            static void Main(string[] args)
    
            {
    
                try
    
                {
    
                    //Distribution transaction starts when this event fires.
    
                    TransactionManager.DistributedTransactionStarted += delegate
    
                        (object sender, TransactionEventArgs e)
    
                    {
    
                        Console.WriteLine("Distributed Transaction Started");
    
                    };
    
                         
    
    
    
                    using (TransactionScope scope = new TransactionScope
    
                        (TransactionScopeOption.Required))
    
                    {
    
    
    
                        //Distribution transaction ends when this event fires.
    
                        Transaction.Current.TransactionCompleted += delegate
    
                            (object sender, TransactionEventArgs e)
    
                        {
    
                            Console.WriteLine(e.Transaction.TransactionInformation.Status);
    
                            Console.WriteLine("Distributed Transaction Completed");
    
                        };
    
                         
    
                        //Insert into the first database
    
                        using(SqlConnection conn1 = new SqlConnection(_db1)) 
    
                        {
    
                            Insert(conn1);
    
                        }
    
    
    
                        //Insert into the second database
    
                        using(SqlConnection conn1 = new SqlConnection(_db2)) 
    
                        {
    
                            Insert(conn1);
    
                        }
    
    
    
                        //Commit Distributed Transaction
    
                        scope.Complete();
    
                    }
    
    
    
                    Console.WriteLine("Execution completed! Press Enter..");        
    
                    <br/>                Console.ReadLine();
    
                }
    
                catch (Exception e)
    
                {
    
                    Console.WriteLine(e);
    
                    Console.ReadLine();
    
                }
    
            }
    
    
    
            //Do the insertion
    
            private static void Insert(SqlConnection connection)
    
            {
    
                try
    
                {
    
                    using (DbCommand command = new SqlCommand(_query))
    
                    {
    
                        if (connection.State != ConnectionState.Open)
    
                            connection.Open();
    
    
    
                        command.Connection = connection;
    
    
    
                        command.ExecuteNonQuery();
    
                    }
    
                }
    
                catch (Exception e)
    
                {
    
                    throw e;
    
                }
    
            }
    
             
    
        }
    
    }
    
    
    
    

    The observation here is this.

    1.  When this application is run exactly as in the code above, the activity monitor has 1 open transaction against a SPID
    2.  The Open Transaction goes down after 20 minutes or so
    3.  When the application is running, after the transaction is committed, even after restarting the MSDTC, the ActivityMonitor (after refresh) shows 1 open transaction against the same SPID
    4.  The Connection is being Disposed using the Using statement of C# which means that the connection is returned back to the connection pool.
    5.   This code runs with .NET framework 2.0 SP1 and SQL Server 2005 SP1
    6.  Also tried changing the connection string with transaction binding set to Implicit Unbind and Explicit Unbind.. just for the heck of it.

    I think this is very similar to http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/86591556-0319-496d-be4c-2c26634b9400/

    I cannot use SqlConnection.ClearAllPools() as the code similar to this is running as a windows service with multiple threads and each thread proc initiates a distributed transaction.

    Any thoughts/comments would be greatly appreciated.

    Thanks in advance!

    Ksrisriv


    One more observation has been made in this regard. 
    1. If the Connection Pooling is turned off by setting "Pooling = false" in the connection string, the Open Transaction is 0 (in the Activity Monitor of SQL Server 2005) at the end of the distributed transaction.  I turned on connection pooling and tried SQLConnection.ClearAllPools() and this makes the open transaction set to 0 as expected!!!
    2. I also tried Disposing the Transaction object (when Pooling = true) when the Transaction.Current.TransactionCompleted event fires and expected the Open Transaction to be 0 in the Activity Monitor of SQL Server 2005.  Not true!  It still shows up as Open Transaction = 1.

    What is happening!? 

    Saturday, May 23, 2009 6:46 AM
  • Try installing the .NET framework 2.0 Service Pack 2 (or .Net Framework 3.5 Service Pack 1 which also includes 2.0 SP2) and see if it resolves the problem.

    Also, try increasing the connection lifetime to values greater than 10 (in seconds), for example an hour or so.

    And, if pooling is enabled, check that all SqlConnection instances get closed before transaction completes (either with commit or rollback) in your production code, this is is already true in your sample above.
    Postings are provided "As Is" with no warranties and confer no rights.
    Friday, June 12, 2009 1:40 AM
    Moderator
  • Hello

    My production environment and my simulation environment is the same.  .NET framework SP2 and SQL Server also has the latest service pack installed.  I cannot migrate to 3.5 SP1 but I will try and see if the same problem persists.

    Will give a feedback after I increase the connection lifetime., though I am not sure how this will help.  

    And yes, all connections are explicitly closed as in the sample above.

    Thanks 
    Friday, June 12, 2009 9:27 AM
  • Ksrisriv,

    Did you find an answer to your problem?  I'm running into a similar issue with our web application.  This came to light on our production servers when the users kept running out of connections in the connection pool.

    Hope to hear you have a solution to the problem that we could try...

    Thanks in advance,
    Michael R. Blevins

    Thursday, August 20, 2009 2:17 PM