TransactionScope including SQL Command and BO leaves transaction around

TransactionScope including SQL Command and BO leaves transaction around

Old forum URL: forums.lhotka.net/forums/t/7826.aspx


landesjoe posted on Tuesday, October 20, 2009

Here's the basic configuration:

WindowsService uses a section of Code that's wrapped in a TransactionScope Using statement. Inside the Using section, I create a SQLConnection object, which subsequently calls 3 stored procedures in the same Database. Last step before Scope.SetComplete and End Using is calling BusinessObject.DeleteBusinessObject passing along the a different connection string then used for the 3 stored procedure calls to delete something on a remote database.

Everything works, except that we have an open transaction hanging around after the End Using statement (code below).

Tracing the SQL database (SQL 2005) shows that we initially have on a SQL transaction for the SPID generated for the hqConn connection. Once the BusinessLogic.QueuepOReceiptToHQ.Delete... begins, the transactions are promoted to a distributed Transaction via MSDTC. I see the Commit happening for the transactions using the hqConn object as well as the business object but then there's a open transaction remaining from the business object that does not get cleaned up intil garbage collection happens at some unspecified and unreliable time.

Any suggests on what to do here?


Thanks in advance,
Joe Landes

Dim hqConn As SqlConnection = Nothing
   Using scope As New TransactionScope(TransactionScopeOption.RequiresNew)
   hqConn = DBHelper.GetConnection(DBConnectionHelper.GetConnectionString(0))
   
' insert TeleSAM EPSReceipt
   
EPSReceiptID = POReceipt.InsertEPSReceipt(hqConn, tranLogRow, tranRefNum, dCID)
   
' insert TeleSAM EPSReceiptLine, using EPSReceiptID from InsertEPSReceipt call
   
POReceipt.InsertEPSReceiptLine(hqConn, tranLogRow, EPSReceiptID, tranRefNum, dCID)
   
' update TeleSAM EPSReceipt to set ReadyToExport=1
   
POReceipt.UpdateEPSReceiptReadyToExport(hqConn, EPSReceiptID)
   
' delete queue record
   
BusinessLogic.QueuePOReceiptToHQ.DeleteQueuePOReceiptToHQ(DBConnectionHelper.GetConnectionString(dCID), queuePOReceiptToHQID)
   
' Done
   
scope.Complete()
End Using

RockfordLhotka replied on Tuesday, October 20, 2009

Are you sure you are disposing all your data objects? (connection, command, datareader, etc all must be disposed)

And are they disposed in your code here, as well as in the other business class?

I'm assuming the answer is no - since this code appears to get a connection and not dispose (or even close) that connection.

landesjoe replied on Tuesday, October 20, 2009

A side note: we use CSLA 2.0 and .NET 2.0.

I left out the Try/Catch around the scope. Here's what the Finally block looks like right after the End Using statement for the WindowsService class.

Finally
   DBHelper.CloseConnection(
hqConn)
   
If hqConn IsNot Nothing Then hqConn.Dispose()
   hqConn =
Nothing
End Try

The Business Object DataPortal_Delete looks like this:

Protected Overrides Sub DataPortal_Delete(ByVal passedInCriteria As Object)

' This method is responsible for calling a single stored procedure that will
' delete the object's record and any foreign key records that point to it.

Dim conn As SqlConnection = Nothing
Dim tr As SqlTransaction = Nothing

Try
   If Not (passedInCriteria Is Nothing) Then
   p_dbConnectionString = CType(passedInCriteria, CriteriaBase).DBConnectionString
   End If

   ' Set up the database connectivity.
   conn = DBHelper.GetConnection(p_dbConnectionString)
   conn.Open()

   ' Begin a transaction, in case this is a multi-table delete
   ' because of child objects.

   tr = DBHelper.GetTransaction(conn)
   Dim cmd As SqlCommand = Nothing

   Try
      cmd = DBHelper.GetStoredProcCommand(p_SqlDelete, tr, 300)
      AddDeleteParameters(passedInCriteria, cmd)
      ' Both the parent record and the child records 
      ' are deleted in the stored procedure.
      DBHelper.ExecuteNonQuery(cmd)
      tr.Commit()
   Catch ex As Exception
      tr.Rollback()
      Throw 
   Finally
      If cmd IsNot Nothing Then cmd.Dispose()
      cmd =
Nothing
End Try
Finally
   
' Close the connection.
   DBHelper.CloseConnection(tr)
   If tr IsNot Nothing Then tr.Dispose()
   tr =
Nothing
   DBHelper.CloseConnection(conn)
   If conn IsNot Nothing Then conn.Dispose()
   conn =
Nothing
End Try

End Sub

landesjoe replied on Wednesday, October 21, 2009

Anybody have any suggestions on the ammended post showing the actual cleanup work?

Thanks

Copyright (c) Marimer LLC