none
Very ocassionally Connection property of SqlTransaction instance is set to nothing after being used with sqlCommand.ExecuteScalar RRS feed

  • Question

  • Hi

    We got Vb Net code that uses sqlTransaction instance to execute 5 Stored Procedures

    It uses try catch block to manage commit or rollback

    It pass the sqlTransaction instance as a parameter to other methods which uses de Connection property of sqlTransaction instance

    Occasionally, just after executing the first Stored Procedurte with a ExecuteScalar() the sqlTransction instance has its Connection property to nothing

    So in the next attempt to use the sqlTransaction and its Connection property a System.InvalidOperation "Connection property has not been initialized" is thrown, the catch block do execute sqlTransaction.rollback() without further exceptions (no "ZombieCheck" neither "transaction is not usable", etc)

    The only one SP executed has an internal BEGIN TRAN.... COMMIT and it does saves data in two tables,also an Idendity value is created as the Identifier of the data, this Id is used in .net code for next steps and is the evidence that SP executes without exceptions

    Even the sqlTransaction.Rollback is executed the data persists in the repository

    We activated sql server 2008 R2 to write deadlocks events in the sqlserver log but no deadlocks were involved

    The funny thing is that we are able to "try again" the same whole process in the same environment: same servers, same Vb Net Code, same SQL objects and it works as expected

    In another Post in this forum mentions a Bug in Net 2.0 for a weak reference in a time intervale in sqlConnection.BeginTransaction() so sqlTransaction.Connection gets null if Gargage Collector runs  and the fix its supposed to be in Service Pack 2, but checking the registry we have Service Pack 3, some people has reply the post argued that they have service Pack 2 but Bug still happens.

    Any help is appreciated



    • Edited by Nemachtiani Thursday, February 21, 2013 7:12 PM more descriptive title
    Thursday, February 21, 2013 5:08 PM

All replies

  • Please try to find a way which can produce this scenario every time, not ocassionally.

    Thank you.


    Ghost,
    Call me ghost for short, Thanks
    To get the better answer, it should be a better question.

    Tuesday, February 26, 2013 4:50 PM
  • Hi CrazyGhost_Von

    thanks for the response

    This happens in one of production environments, cannot produce this scenario "just because", most of the time (99.7%) is working just fine. (0.03% is this case)

    has been really hard "trying to find a way" to produce it outside that environment.

    there are no trends, can ocurr any day, any time, can take days without this to happen.

    Used sqlTransaction instance lost its Connection property, just suddenly sqlTransaction.Connection=Nothing

    We realized this because of an attempt to reuse sqlTransaction.Connection throws InvalidOperationException wich is handled and sqlTransaction.Rollback is executed (and does not throws any further exception), anyway data IS persisted.

    Without further changes, same environment, same Input data, same code, just do repeat the entire "process" and works.

    Bug on Net 2.0 seems to be a "reasonable" cause to find Connection = Nothing but is very rare to find persisted data after the sqlTransaction.Rollback.

    This time I'm trying to do execute SELECT @@TRANCOUNT and log somewhere all version of the involved assemblies, will take some time.

    Any other tip will be appreciated.

    Tuesday, February 26, 2013 5:21 PM
  • This is the other post with similar behavior

    http://social.msdn.microsoft.com/Forums/en/adodotnetdataproviders/thread/f078f26e-5b3b-462c-baa3-e6c792cf374b

    Should I post my question in another forum?

    How can I check exact Major-minor-builds of Net Framework to bypass this "bug"?

    Monday, March 4, 2013 11:43 PM
  • I've got this same problem here with ADO.NET 4.0.

    This is an excerpt of my code:

    private SqlTransaction _transaction;
    private SqlConnection _con;

    public SqlHelperNG()
    {
    _con = new SqlConnection(_conStr);
    _con.Open();
    _transaction = _con.BeginTransaction();
    }

    ~SqlHelperNG() { (if _con != null && _transaction != null && _transaction.Connection != null) _transaction.Rollback(); // here the error occurs }

    During execution of the finalizer, while _transaction.Rollback() is executed, the internal SqlTransaction's Connection property is set to null and the internal SqlTransaction's Rollback call fails with a NullReferenceException.

    What's going on here?

    Any help is appreciated.

    Regards,
    Axel


    Microsoft Marketing should care for customer feedback on their consumer products, e.g. Windows & Office.


    Have had Windows 8 …  went back to Windows 7.


    Monday, March 18, 2013 2:28 PM
  • Hi BetterToday

    In our case we are not using any "helper" class, and dont have any finalizer... 

    about your helper class: does the exception happens always or eventually?

    what happens if you invoke GC.Collect() ?

    Friday, March 22, 2013 2:30 PM
  • I've isolated the code when my problem happens

    i'm trying to keep this code segment as similar as possible and i assume it is done this way because of refactorings and TDD
    I've ommited the code of executing SELECT @@TRANCOUNT because is not part of original code, inside de code segment put some comments about it

     

    'note:assume it exist for TDD or as a way to use another SP
    Private _uspInitialInsert as String = ""
    Public Property USPInitialInsert() As String
    	Get
    		If _uspInitialInsert = "" Then
    			_uspInitialInsert = System.Configuration.ConfigurationSettings.AppSettings("uspInitialInsert")
    			If _uspInitialInsert Is Nothing OrElse _uspInitialInsert = "" Then
    				_uspInitialInsert = "uspInitialInsert"
    			End If
    		End If
    		Return _uspInitialInsert
    	End Get
    	Set(ByVal value As String)
    		_uspInitialInsert = value
    	End Set
    End Property
    
    Public Function DoInitialInsert(byval CustomerID as string, byval Input as String, byval tran as SqlTransaction) as Int64
    	Dim InsertId as Int64
    	
    	'note: perhaps an incomplete refactoring, ignores Stored procedure stated at CreateInitInsertCmd and executes a CommandText see further
    	Dim cmd as SqlCommand = CreateInitInsertCmd(tran, tran.Connection)
    	
    	'note: at this point needs to run this first, don't know exactly why, if don't another exception ocurrs, maybe something has been done before whit tran 
    
    parameter, applying this makes no sense of setting the stored procedure at CreateInitInsertCmd
    	cmd.CommandText = "SET ARITHABORT,QUOTED_IDENTIFIER, ANSI_NULLS ON"
    	cmd.ExecuteNonQuery()
    
    	'note:at this point i've tryied SELECT @@TRANCOUNT using tran and tran.Connection, @@TRANCOUNT results 1
    
    	'note: reusing cmd, perhaps an incomplete refactoring for TDD, it uses same storedProcedure as CreateInitInsertCmd
    	cmd.CommandText = USPInitialInsert
    	cmd.CommandType = CommandType.StoredProcedure
    	cmd.CommandTimeout = System.Configuration.ConfigurationSettings.AppSettings("uspInitialInsertTimeOut")
    	cmd.Parameters.Add("@CustomerID", CustomerID)
    	cmd.Parameters.Add("@Input", Input)
    
    	InsertId = Convert.ToInt64(CLng(cmd.ExecuteScalar()))
    
    	'note:at this point i've tryied SELECT @@TRANCOUNT using tran and tran.Connection but now tran.Connection is Nothing
    	
    	If InsertId = 0 Then
    		Throw New ApplicationException("Cannot do insert")
    	End If
    
    	Return InsertId
    
    End Function
    
    'assume it exist for TDD
    Public Function CreateInitInsertCmd(byval tran as SqlTransaction, byval cn as SqlConnection) as SqlCommand
    	Dim cmd As New SqlCommand(USPInitialInsert, cn, tran)
    	return cmd
    End Function

    For an unknown reason inside function DoInitialInsert the Connection property of "tran" parameter is turned to Nothing just after the cmd.ExecuteScalar(), but this happens very ocassionally

    DoInitialInsert is invoked and returns a Non-Zero value that in fact corresponds to an Identity column value, no exception occurs during execution of DoInitialInsert.

    The next attempt to use Connection property of "tran" parameter rise the "InvalidOperationException Connection property has not been initialized"

    Any idea?

    Wednesday, March 27, 2013 4:37 PM
  • I've been struggling with the same issue in the last few months as well and I wish I had a solution, but perhaps I can share what I've found so far and it may lead you to a solution.  Please reply if this helps or if you make any progress, I could use some help too. :)

    After a lot of research, I believe SQL Server is severing the connection for 1 of several reasons, but ADO.NET is still returning the result and continuing instead of throwing an exception.  Right after the Execute call, the Transaction.Connection is null, but a separate reference to the same Connection is still valid.  In our specific case, we've narrowed it down to a deadlock that causes the issue.  I've also read that trigger errors or other server side nonsense can cause the same behavior, but I'm not sure about those.  

    We have deadlock retry logic written but it expects ExecuteScalar, ExecuteReader, ExecuteNonQuery, etc... to throw a SqlException which has the deadlock error code in it, but in some cases, it does not.

    Do yourself a favor and look at what SQL Server is doing, run some profiles or traces.  To check for deadlocks, you can try turning on tracing using [DBCC TRACEON (1222,-1)] and see if the query executing in .NET correlates with a deadlock on the server.  

    For me, ExecuteNonQuery is not an issue, but ExecuteScalar and ExecuteReader are (I suspect because they both use ExecuteReader under the hood).  ExecuteNonQuery raises a deadlock exception like I expect in this case.

    Other things worth mentioning:
    If I use TransactionScope to manage the transaction instead of calling connection.BeginTransaction(), the culprit ExecuteScalar call will throw an appropriate SqlException indicating the deadlock.  If I run the same code, causing the same deadlock, but use connection.BeginTransaction() instead of TransactionScope, the ExecuteScalar call succeeds, returns the result, but the transaction.Connection property is now null and indicates being "zombied".

    Unfortunately for us, we cannot simply rewrite our entire enterprise level application using TransactionScope to fix the bug in older versions and because of cross database platform support concerns with 3rd party drivers, but that's for the future.

    I really want ADO.NET to be consistent here and throw the proper SqlException.  In my mind, this is an ADO.NET bug, but I'm not sure it's worth Microsoft's time to fix.  I tested this with .NET 3.5 and 4.5, same thing in both cases.  Unfortunately, I can't get source code step-in to work (due to security fixes for ADO.NET and symbol disparities???), or I would dig a lot deeper and try to understand what's happening. 

    My only suggestion at this point is to change our database transaction manager that wraps transaction calls to check if Transaction.connection was nulled out unexpectedly and treat it as a recoverable deadlock exception and retry the entire transaction.

    Hopefully that helps, if anyone else has suggestions (or questions), I'm all ears.

    Monday, April 1, 2013 11:46 PM
  • We did activate deadlock tracing but no deadlock events ocurrs or where registered at the time this issue happened

    Transaction was commited, not rolled back.

    Last thing I try was before calling "DoInitialInsert" (of the example code i post before) keep another instance copy of tran.Connection:

    ...
    Dim loTranConnCopy as SqlConnection = tran.Connection
    
    RecordId = DoInitialInsert(CustomerId, Input, tran)
    
    ...

    Even, I did execute a "SELECT @@TRAN" before and after invoking "DoInitialInsert" with tran.Connection and loTranConnCopy: both returned "1" before, but issue happens tran.Connection = Nothing so cannot use it for executing "SELECT @@TRAN" and when using loTranConnCopy it returns "0", besides that  loTranConnCopy <> Nothing and loTranConnCopy.State = Open !!!!

    My next attempt will be to run "SELECT @@SPID" before and after and maybe some detail about @@SPID

    However this will not solve my problem:  it commits itself !!!(?) and besides that it closes the sqlConnection... who would want to use a transaction with that behaviour?

    Maybe i just should not use ExecuteScalar()... that seems faster than trying with TransactionScope... lot of legacy...

    happy hunting.

    Tuesday, April 2, 2013 2:56 AM
  • I'm missing responses from Microsoft affiliates here... I'm paying MSDN to receive a three day response action here in the forums.

    Microsoft Marketing should care for customer feedback on their consumer products, e.g. Windows & Office.


    Have had Windows 8 …  went back to Windows 7.

    Tuesday, April 2, 2013 1:48 PM
  • taking up again the issue: we have a "tran" variable that is an initialized sqlTransaction instance

    we use Connection property of "tran" to initizalize a sqlCommand instance and execute a SP with ExecuteScalar()

    after ExecuteScalar() Connection property of tran turns to Nothing but no exceptions are thrown,

    we try to use tran.Connection again and an "InvalidOperationException Connection property has not been initilized"

    this last exception is catched and then  we do run a tran.Rollback() and this Rollback don´t throw an exception, never reach tran.Commit thatis placed in try clausule

    we check database and data used in SP was persisted...

    So...

    we use loTranConnCopy variable to keep a reference to the same memory location of "tran.Connection"...

    loTranConnCopy AND tran.Connection are the same object identity, (in developer machine check this with GetHashCode() and Object.ReferenceEquals(tran.Connection, loTranConnCopy)

    before ExecuteScalar() we do:

    1. use tran.Connection and run "SELECT @@TRANCOUNT" got value of "1"

    2. use loTranConnCopy and run "SELECT @@TRANCOUNT" got value of "1"

    3. use tran.Connection but this time we run "SELECT @@SPID" got value "97"

    4. use loTranConnCopy and run "SELECT @@SPID" got value "97"

    then invoke ExecuteScalar(), no exceptions ocurr

    but now tran.Connection = Nothing so

    1. cannot use tran.Connection for trying to run "SELECT @@TRANCOUNT" so we didnt try

    2. loTranConnCopy is not nothing and even its State = Open so we use it and run "SELECT @@TRANCOUNT" got value "0" ZERO!

    3. even we know tran.Connection is nothing we try to use it for running "SELECT @@SPID" and as expected got an exception "Connection property has not been initialized" but we already know this will happen

    4. so we use loTranConnCopy and run "SELECT @@SPID" and got value "97"!!!!

    so, for an unkown reason:

    1. tran COMMITS itself
    2. lost its Connection property...
    3. no exception was generated

    I asked our DBA to search for events un SQL Server log, he previously sad every error occurred in SQL SERVER will be logged, hope something is there to give us a clue

    Any thoughts?

    Friday, April 5, 2013 10:29 PM
  • Can you please post the code for the Stored Procedure that is causing the transaction to commit?

    As a side note, SqlTransaction.Connection is expected to become null if the transaction has been committed.
    Please be aware that the state of the transaction is purely controlled by the server: if it says that the transaction is committed, then ADO.NET will mark the transaction is committed (even if you did not call SqlTransaction.Commit). This also explains why there is data left in the database after you call Rollback - as far as ADO.NET is concerned, the transaction is complete and there is nothing to rollback.


    Daniel Paoliello [MSFT]
    ADO.Net Managed Providers and DataSet

    Wednesday, May 8, 2013 6:05 PM
    Moderator
  • Hi Daniel

    Unfortunately, i'm not authorized to post the SP

    but the question then goes to a SQL Server 2008 R2 Standar forum... why is SQL Server "forcing" a commit we´re not "invoking to apply"?

    we're sure SP is not "invoking random commits", also we have tried running again whole process (input, code, etc) and works as expected

    at this time SQL Server is not giving any clue about this, there are no events in SQL Log, there's no deadlocks or anything that could help to trace such behavior

    At least I could discard ADO .NET...

    thank you so much!

    Wednesday, May 8, 2013 10:48 PM
  • Hi Daniel

    Even though this is not the real SP could be useful as it depict the "general structure", real SP has many BR implemented (we're aware of consequences) but not all BR applies and are executed all time, has 1700 lines but it is mainly for being indented (as you can see below), has many blank lines, etc.

    CREATE PROC [dbo].[spInitialInsert]
     @CustomerId varchar(10),
     @Input   ntext
    AS                              
    BEGIN 
    
     DECLARE @IdGenerated bigint, @idoc int
     DECLARE @pathMain nvarchar(200)
     DECLARE @MainData1 varchar(10), @MainData2 varchar(50) 
     
     SELECT @pathMain = '/MainData'
     
     EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
    
     SELECT @MainData1 = MainData1,
      @MainData2 = MainData2
     FROM OPENXML (@idoc, @pathMain,1)                              
     WITH PreProcess
     IF @@ERROR<>0                              
     BEGIN                              
      EXEC sp_xml_removedocument @idoc                                                          
      RAISERROR 2000100 'custom message error regarding xml load'                              
      RETURN -2                              
     END   
      
     SET NOCOUNT ON              
     
     BEGIN TRAN   
     
    	 INSERT MainTable(CustomerId, MainData1, MainData2)
    	 VALUES(@CustomerId, @MainData1, @MainData2)
    	 
    	 IF @@ERROR<>0                              
    	 BEGIN                     
    	  EXEC sp_xml_removedocument @idoc          
    	  ROLLBACK TRAN                              
    	  RAISERROR 2000100 'custom message error regarding main table'                              
    	  RETURN -2                              
    	 END                              
    	                           
    	SELECT @IdGenerated = SCOPE_IDENTITY()  
    	 
    	INSERT LogTable (IdGenerated, LogDate)
    	VALUES(@IdGenerated, GETDATE())
    
    	IF @@ERROR<>0                              
    	BEGIN
    	  EXEC sp_xml_removedocument @idoc                              
    	  ROLLBACK TRAN                              
    	  RAISERROR 2000100 'custom message error regarding log table'                              
    	  RETURN -2                              
    	END 
    
    	EXEC sp_xml_removedocument @idoc
    	
    	SELECT @IdGenerated
    	                              
     COMMIT TRAN                              
                                    
    END
    go
    thanks again
    Thursday, May 9, 2013 1:44 PM
  • A couple of things to note from your code:

    • If the transaction is rolled back by one of the "ROLLBACK TRAN" statements, then both the T-SQL transaction and your SqlTransaction will be rolled back
    • If an error\rollback occurs, the value of @IdGenerated will not be reset - so you should not rely on this to indicate if the stored procedure worked or not

    Also, I attempted to reproduce the issue you were seeing, but I was getting syntax errors for the "RAISERROR" statements - are you sure that these are correct?


    Daniel Paoliello [MSFT]
    ADO.Net Managed Providers and DataSet

    Thursday, May 9, 2013 5:36 PM
    Moderator
  • Daniel

    thank you for pointing out, we were already aware about both issues

    try this:

    go
    CREATE TABLE LoadXMLHelper(
    	MainData1 varchar(100), 
    	MainData2 varchar(100)
    )
    go
    CREATE TABLE MainTable(
    	IdGenerated bigint not null IDENTITY(1,1),
    	CustomerId varchar(10) not null, 
    	MainData1 varchar(100) not null,
    	MainData2 varchar(100) not null
    	)
    go
    CREATE TABLE LogTable(
    	IdGenerated bigint not null,
    	LogDate datetime not null
    	)
    go
    CREATE PROC [dbo].[spInitialInsert]
     @CustomerId varchar(10),
     @InputAsXml   ntext
    AS                              
    BEGIN 
    
     DECLARE @IdGenerated bigint, @idoc int
     DECLARE @pathMain nvarchar(200)
     DECLARE @MainData1 varchar(10), @MainData2 varchar(50) 
     
     SELECT @pathMain = '/MainData'
     
     EXEC sp_xml_preparedocument @idoc OUTPUT, @InputAsXml
    
     SELECT @MainData1 = MainData1,
      @MainData2 = MainData2
     FROM OPENXML (@idoc, @pathMain,1)                              
     WITH LoadXMLHelper
     IF @@ERROR<>0                              
     BEGIN                              
      EXEC sp_xml_removedocument @idoc                                                          
      RAISERROR 2000100 'custom message error regarding xml load'                              
      RETURN -2                              
     END   
      
     SET NOCOUNT ON              
     
     BEGIN TRAN   
     
    	 INSERT MainTable(CustomerId, MainData1, MainData2)
    	 VALUES(@CustomerId, @MainData1, @MainData2)
    	 
    	 IF @@ERROR<>0                              
    	 BEGIN                     
    	  EXEC sp_xml_removedocument @idoc          
    	  ROLLBACK TRAN                              
    	  RAISERROR 2000100 'custom message error regarding main table'                              
    	  RETURN -2                              
    	 END                              
    	                           
    	SELECT @IdGenerated = SCOPE_IDENTITY()  
    	 
    	INSERT LogTable (IdGenerated, LogDate)
    	VALUES(@IdGenerated, GETDATE())
    
    	IF @@ERROR<>0                              
    	BEGIN
    	  EXEC sp_xml_removedocument @idoc                              
    	  ROLLBACK TRAN                              
    	  RAISERROR 2000100 'custom message error regarding log table'                              
    	  RETURN -2                              
    	END 
    
    	EXEC sp_xml_removedocument @idoc
    	
    	SELECT @IdGenerated
    	                              
     COMMIT TRAN                              
                                    
    END
    go
    
    DBCC CHECKIDENT('MainTable', noreseed)
    go
    select * from MainTable
    go
    select * from LogTable
    go
    EXEC [dbo].[spInitialInsert] 'CustId01', '<MainData MainData1="Value1" MainData2="Value2"/>'
    go
    select * from MainTable
    go
    select * from LogTable
    go
    DBCC CHECKIDENT('MainTable', noreseed)
    go
    
    DROP PROC [dbo].[spInitialInsert]
    go
    DROP TABLE LoadXMLHelper
    go
    DROP TABLE MainTable
    go
    DROP TABLE LogTable
    go
    thanks again
    Thursday, May 9, 2013 6:37 PM
  • I had a similar problem and I could reproduce it very easily. "MyTable" contains 2 columns, both are strings. It's used as a lookup table, with StrField1 as the key and StrField2 as the corresponding value. Here's the code:

    SqlCommand cmd = new SqlCommand("SELECT StrField2 FROM MyTable WHERE StrField1=14", connection);

    cmd.Transaction = trans;

    object oResult = cmd.ExecuteScalar();

    The column "StrField1" is a string column in the database. When I didn't enclose "14" in single quotes, the command worked fine (returning the correct StrField2 value), but cmd.Transaction was set to null! There was no Exception thrown. The running Transaction was aborted. 

    This didn't happen any longer once I enclosed the 14 in single quotes, like this:

    SqlCommand cmd = new SqlCommand("SELECT StrField2 FROM MyTable WHERE StrField1='14'", connection);

    I wasn't able to find any documentation stating that this is supposed to happen (silently)...


    • Edited by Kumps Luc Saturday, November 22, 2014 3:13 PM
    Saturday, November 22, 2014 10:17 AM