none
Open transaction after TransactionScope.Complete Options RRS feed

  • Question

  • Here's the story: I have an VB.NET Win application that use
    TransactionScope to tie together 3 distinct DB Connections to 3
    different servers.
    Each connection does it's job, then we call Complete and dispose of
    our connections and command objects. I check all SQL databases using a
    version of sp_Who to check how many open transactions are around.
    Turns out that only the connection associated with the first SQL
    command is properly cleaned up (SPID is still there, but no open
    transaction), the other 2 databases have the connection still around,
    but on top, each connection still has an active transaction. That's
    obviously a problem. The connections and transactions go away when I
    close the app.
    I can also issue commands to the databases from SQL Server Management
    Studio, the records affected by the SQL Commands and Connections are
    not locked up. I'm just concerned because we use the same methods in
    Windows Services that tend to have a lot of connections hanging
    around.
    I have found a post somewhere else that describes the same problem,
    but no resolution posted.

    Note: I've tried just about all combinations of defining the connections inside of the Using Scope statement, using "Using Conn" for each of the connections, all with the exact same results

    Thanks,
    Joe

    Here's the Code:

        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e
    As System.EventArgs) Handles Button1.Click
            Dim conn As SqlConnection = Nothing
            Dim conn2 As SqlConnection = Nothing
            Dim conn3 As SqlConnection = Nothing
            Dim cmd As SqlCommand = Nothing
            Dim cmd2 As SqlCommand = Nothing
            Dim cmd3 As SqlCommand = Nothing
            Try
                Using scope As TransactionScope = New TransactionScope
    (TransactionScopeOption.RequiresNew)
                    conn = New SqlConnection("Data Source=db1; ...")
                    conn2 = New SqlConnection("Data Source=db2; ...")
                    conn3 = New SqlConnection("Data Source=db3; ...")
                    ' option on the from to change order in which
    operations are done
                    If optHQFirst.Checked Then
                        conn.Open()
                        conn3.Open()
                        conn2.Open()
                    Else
                        conn2.Open()
                        conn3.Open()
                        conn.Open()
                    End If
                    cmd = New SqlCommand("UPDATE SalesOrder Set ContactID
    = 2757110 WHERE OrderNum = 9802531", conn)
                    cmd.CommandType = CommandType.Text

                    cmd2 = New SqlCommand("SELECT top 1 * from orders
    where orderID = '9802531'", conn2)
                    cmd2.CommandType = CommandType.Text

                    cmd3 = New SqlCommand("UPDATE SalesOrder Set ContactID
    = 2757110 WHERE OrderNum = 9802531", conn3)
                    cmd3.CommandType = CommandType.Text

                    ' option on the from to change order in which
    operations are done
                    If optHQFirst.Checked Then
                        Debug.Write(cmd.ExecuteNonQuery())
                        Debug.Write(cmd3.ExecuteNonQuery)
                        Debug.Write(cmd2.ExecuteNonQuery())
                    Else
                        Debug.Write(cmd2.ExecuteNonQuery())
                        Debug.Write(cmd3.ExecuteNonQuery)
                        Debug.Write(cmd.ExecuteNonQuery())
                    End If

                    scope.Complete()
                End Using
            Catch ex As Exception
                MessageBox.Show(ex.ToString, "error")
            Finally
                If cmd IsNot Nothing Then
                    cmd.Connection = Nothing
                    cmd.Dispose()
                    cmd = Nothing
                End If
                If cmd2 IsNot Nothing Then
                    cmd2.Connection = Nothing
                    cmd2.Dispose()
                    cmd2 = Nothing
                End If
                If cmd3 IsNot Nothing Then
                    cmd3.Connection = Nothing
                    cmd3.Dispose()
                    cmd3 = Nothing
                End If
                If conn IsNot Nothing Then
                    conn.Close()
                    conn.Dispose()
                    conn = Nothing
                End If
                If conn2 IsNot Nothing Then
                    conn2.Close()
                    conn2.Dispose()
                    conn2 = Nothing
                End If
                If conn3 IsNot Nothing Then
                    conn3.Close()
                    conn3.Dispose()
                    conn3 = Nothing
                End If
            End Try

        End Sub

    Thursday, October 22, 2009 9:50 PM