I have a situation where I have one procedure that executes a bunch of
database code in a transaction. In this procedure, in the middle of
the aforementioned transaction, I call another procedure that does a
series of things in yet another transaction. For some reason, after I
come back from the second procedure, where that transaction is
committed, and I try to run a simple SELECT SUM(*)... query in the
first one, it just times out. So, I am thinking it may be being caused
by the transaction stuff. My question relates to understanding the
proper way to code transactions within transactions. In case it's
relevant, these procedures are in different places (i.e. one in a
class, another in a form's code). Any insights would be greatly
appreciated.
A simplified version of what I have is something like follows:
Private Sub FirstProc()
Dim cnn As New
SqlClient.SqlConnection(My.Settings.MyConnectionSt ring)
cnn.Open()
Dim trx As SqlClient.SqlTransaction = cnn.BeginTransaction()
' Execute first command query
' Execute second command query
' Execute third command query
SecondProc()
' Try to execute SELECT SUM... FAILS with timeout
' Execute fourth command query
If (AllWentWell) Then
trx.Commit
Else
trx.Rollback
End If
'...
End Sub
Private Sub SecondProc()
m_cnn = New SqlClient.SqlConnection(My.Settings.GRPConnectionS tring)
m_cnn.Open()
m_cnnTransaction = m_cnn.BeginTransaction()
' Execute first command query
' Execute second command query
' Execute third command query
If (AllWentWell) Then
m_cnnTransaction.Commit
Else
m_cnnTransaction.Rollback
End If
'...
End Sub
