none
Transaction.Connection is comming NULL inconsistently? RRS feed

  • Question

  • Hi All,

    I am using Microsoft Data Access Layer (MSDAL).

    I inconsistently receive the exception given below from the code snippet provide at the end.

    Exception Stack Trace

    <Exception ExceptionType="System.ArgumentNullException" Message="Value cannot be null.&#xD;&#xA;Parameter name: connection" ParamName="connection" Data="System.Collections.ListDictionaryInternal" TargetSite="Void PrepareCommand(System.Data.Common.DbCommand, System.Data.Common.DbConnection)" Source="Microsoft.Practices.EnterpriseLibrary.Data">
            <StackTrace>   at Microsoft.Practices.EnterpriseLibrary.Data.Database.PrepareCommand(DbCommand command, DbConnection connection)
       at Microsoft.Practices.EnterpriseLibrary.Data.Database.PrepareCommand(DbCommand command, DbTransaction transaction)
       at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(DbCommand command, DbTransaction transaction)
          </Exception>
        </Exception>


    Code Sinppet

          using (IDbConnection connection = database.CreateConnection())
                    {
    connection.Open();
                        using (IDbTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadUncommitted))
                        {
        //prepare command...
                            try
                            {
                                reader = database.ExecuteReader(commandWrapper, transaction);// This throws the exception
    try
    {
                                while(reader.Read())
    {
    }
    }
    finally
    {
    reader.Close()
    }
    transaction.Commit();
                            }
                            catch (SQLException ex)
                            {
                              
                            }
                        }
                    }

    After looking into the MSDAL code, PrepareCommand(DbCommand command, DbTransaction transaction) calls PrepareCommand(DbCommand command, DbConnection connection) method with command parameter
    which is passed as an argument and connection from transaction.Connection property.

    transaction.Connection is returning NULL inconsistently.

    I have read the documentation of transaction.Connection, iut says "Gets the DBConnection object associated with the transaction, or a null reference if the transaction is no longer valid".

    In my case transaction is valid.

    Is there any problem with the code snippet provided above? I am relying on the using block to rollback the transaction. Also, i havent come across any logs in which transaction is Rolled Back which means transaction is Commited explicitly everytime.

    -Sumit

    Tuesday, April 29, 2008 1:39 PM

Answers

  • This is a known bug in the 2.0 .Net Release. It was fixed in 2.0 SP1 (released with .Net 3.5 & Visual Studio 2008). There was a window in the BeginTransaction code where the SqlConnection had no strong references and would be collected if GC ran at that point, thus the intermittant nature.

    There are two possible ways to fix the problem:
    1. Update to 2.0 SP1 / 3.5 - probably the easiest way to go.
    2. Change your code to maintain an external reference on the connection past the BeginTransaction call. Simplest way to do this is to add a connection field next to your transaction field in the wrapper, and use that where today you are using Transaction.Connection (ie. don't rely on the transaction to carry around the connection, do it separately). Note that simply adding a local variable reference on the connection may not work if the compiler can optimize away that use.
    Hope that helps.
    Tuesday, April 29, 2008 6:03 PM

All replies

  • This is a known bug in the 2.0 .Net Release. It was fixed in 2.0 SP1 (released with .Net 3.5 & Visual Studio 2008). There was a window in the BeginTransaction code where the SqlConnection had no strong references and would be collected if GC ran at that point, thus the intermittant nature.

    There are two possible ways to fix the problem:
    1. Update to 2.0 SP1 / 3.5 - probably the easiest way to go.
    2. Change your code to maintain an external reference on the connection past the BeginTransaction call. Simplest way to do this is to add a connection field next to your transaction field in the wrapper, and use that where today you are using Transaction.Connection (ie. don't rely on the transaction to carry around the connection, do it separately). Note that simply adding a local variable reference on the connection may not work if the compiler can optimize away that use.
    Hope that helps.
    Tuesday, April 29, 2008 6:03 PM
  • Thanks Alzela.

    -Sumit

    Wednesday, April 30, 2008 6:21 AM
  • Please provide the KB that this references.

     

    Thanks!

     

     

    Thursday, May 8, 2008 3:18 PM
  • Hi Alazela,

    I have read the release notes of .NET 2.0 sp1. This bug is not present in the fixed list.

    Also, i have tried replicating this bug on our local servers. After 2-3 days of effort this problem replicated only twice. I have also created a sample application which has the same code as mentioned above on various thread and one thread calling GC.Collect() but i am not able to replicate the problem (all machines didnt have .NET 2.0 sp1).

    Also, on production servers problem is replicating quite consistently compared to our local servers.

    I would want to make sure the problem is fixed and no new problem is occuring before installing .NET 2.0 sp1 on production servers.

    Also, tell me one thing, Connection instance which created the SQLTransaction is Equal too SQLTransaction.Connection. If that is case then Connection will always have strong reference. Correct? And, what is the best way to replicate this problem?

    -Sumit


    Thursday, May 15, 2008 1:01 PM
  • I don't believe there was a KB article written on this one. SP1 was more than a roll-up release for System.Data (several features to work with Sql Server 2008 CTPs are included), so a number of bugs were fixed that were not externally documented.
    Wednesday, May 28, 2008 1:58 PM
  • The problem is with the SqlTransaction.Connection property handling inside SqlConnection.BeginTransaction. There is a short window of time within BeginTransaction where all it holds is a weak reference on the connection. The Connection property is set to the target of that weak reference at the end of the window, restoring the transaction's strong reference to the connection, but if you do not hold a reference on the connection outside the BeginTransaction call and GC collects the connection during that window, the Connection property will be set to null.

    You should be able to reproduce the issue with a test program that forces GC continuously on one thread while a second thread just loops creating a connection, calling BeginTransaction (must be last reference to connection variable), and testing for tran.Connection == null (the bug surfacing).
    Wednesday, May 28, 2008 2:11 PM
  • I got the same problem, and the exception is below

    2008-10-28 13:52:13,760 [29] ERROR FrameworkLogger - System.ArgumentNullException: Value cannot be null.
    Parameter name: connection
       at Microsoft.Practices.EnterpriseLibrary.Data.Database.PrepareCommand(DbCommand command, DbTransaction transaction)
       at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteNonQuery(DbCommand command, DbTransaction transaction)
       at Cyvee.Framework.Data.DataAccessHelper.ExecuteNonQuery(Database database, DbCommand command)

    I already installed the .NET 2.0 sp1 as your suggestion, but the bugs still happen. It's quite strange because it just happens in some PC. I means it runs ok in my PC, but not ok in my friend PC. Of course, they are the same code.
    Please give me a hand to solve this problem.

    Thanks


    Thursday, October 30, 2008 7:01 AM
  • I've got the same problem. I use VS2010 with .NET 4.0 and MS SQL 2008 R2.

     

    The test code is below:

    [code]

    using System;
    using System.Data.Common;
    using System.Data.SqlClient;

    namespace TransactionTest
    {
      class Program
      {
        private const String ConnectionString = @"Data Source=.\sqlexpress;Initial Catalog=DvDataImporter;Integrated Security=False;User ID=DvImporter;Password=TestImport";



        static void Main(string[] args)
        {
          using (DbConnection connection = new SqlConnection(ConnectionString))
          {
            connection.Open();
            try
            {
              DbTransaction transaction = connection.BeginTransaction();
              try
              {
                int iteration = 0;
                int repeatCount = 5;
                while (iteration < repeatCount)
                {
                  InsertDataRow(connection, transaction);
                  iteration++;
                }
                transaction.Commit();
              }
              catch (Exception e)
              {
                Console.WriteLine(String.Format("Critical error occured: {0}", e.Message));
                try
                {
                  transaction.Rollback();
                  transaction = null;
                }
                catch (Exception ex)
                {
                  Console.WriteLine(String.Format("Error on transaction rollback: {0}", ex.Message));
                  throw;
                }
              }
            }
            finally
            {
              connection.Close();
            }
          }
        }

        private static void InsertDataRow(DbConnection connection, DbTransaction transaction)
        {
          try
          {
            using (DbCommand command = connection.CreateCommand())
            {
              command.Transaction = transaction;
              command.CommandText = "insert into Target_Table2 (F_ID, F_NAME, F_DATE, F_FLOAT) values (1, 'aaaaa', 2011/03/01, 'jopa')";
              command.ExecuteNonQuery();
            }
          }
          catch (DbException e)
          {
            Console.WriteLine(String.Format("Query execution error occured: {0}", e.Message));
          }
        }
      }
    }
    [/code]

    Target_Table2 is created by the following script.

    [sql]

    CREATE TABLE [dbo].[Target_Table2](
        [F_ID] [int] NOT NULL,
        [F_Name] [varchar](50) NOT NULL,
        [F_Date] [datetime] NOT NULL,
        [F_Float] [real] NOT NULL
    ) ON [PRIMARY]

    [/sql]

    The test attempts to insert a string value into the F_Float field.

    After failure on "command.ExecuteNonQuery();" command.Transaction == nul and transaction.Connection==null too.

    Wednesday, March 2, 2011 8:49 AM