Open transaction after TransactionScope.SetComplete

[Donald Swan]

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 SetComplete 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.

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

 

[Mary Chipman [MSFT]]

What you need to do is put all of your connections inside of Using
blocks. That way you ensure that all of the resources will be cleaned
up. Here's a link to ADO.NET documentation that demonstrates the
correct code pattern:
http://msdn.microsoft.com/en-us/library/ms172070(VS.100).aspx.

--Mary

 

[Donald Swan]

Unfortunately, that is not the problem. I've tried every combination,
following the pattern described to the fullest. I still have open
connections hanging around in every database except the one that the
first Command was executed against.
Is there something I've set incorrectly it the database or connection
string? The connection string has nothing specific noted, the DB
Server setup has every option unchecked in the Options panel of the
Property dialog.

Could it be that sp_who just shows old information since I can execute
a SELECT without any locking hit as well as UPDATE statements from
another connection while the open transaction hangs around?

Thanks for your time,
Joe

 

[Mary Chipman [MSFT]]

The reason the connections aren't being cleaned up is because they're
not inside of Using blocks.

Using connection As New SqlConnection(connectionString)
connection.Open()
' Do work here

' connection closed on following line.
End Using

--Mary