2

I have a sql transaction inside a transactionscope and that transaction is doing a commit or a rollback. But from what I've read, that commit/rollback will have no meaning as it's controlled by the transactionscope. However if I leave the transaction object inside the dosomething method without the commit or rollback, my webservice method, which is doing a save into a database, does not work as the individual transaction object has obtained the lock. Could someone please explain how is that possible? Or is this the usual behaviour and if so why?

public sub test()
   Using ts As New TransactionScope()
    Save_CustomerDetails()         
    Save_AddressDetails()
    'cannot show the code for security reasons
    CallToWebServiceFunctionWhichSavesTheDataInDatabase()
    ts.Complete()
   End Using
end sub

Private Sub Save_CustomerDetails()
    Dim ExistingData As DataSet
    Dim DB_CustomerDetails As New CustomerDetails(objDBconn.Connection, objDBconn.Transaction)

    Try
        ExistingData = DB_CustomerDetails.CustomerDetails_Set()

        If DB_CustomerDetails.ReturnCode<> 0 Then
            Throw New ReturnException(DB_CustomerDetails.ReturnCode)
        End If


    Catch lex As ReturnException
        JavaOnload = lex.AlertMessage
        Throw
    Finally
        DB_CustomerDetails = Nothing
        If Not(objDBCommon Is Nothing) Then
                       objDBCommon.CleanUp()
                    End If

    End Try
End Sub

Public Sub Save_AddressDetails()

    Dim DB_AddressDetails As New AddressDetails(objDBCommon.Connection, objDBCommon.Transaction)

    Try

        ExistingData = DB_AddressDetails.AddressDetails_Set()

        If DB_AddressDetails.ReturnCode<> 0 Then
            Throw New ReturnException(DB_AddressDetails.ReturnCode)
        End If


    Catch lex As ReturnException
        JavaOnload = lex.AlertMessage
        Throw
    Catch lex As System.Exception
        Throw
    Finally
        DB_AddressDetails = Nothing
    End Try
End Sub

 Function CustomerDetails_Set(Optional ByVal inFunction As String = "")

    Dim Params() As SqlClient.SqlParameter

    Try

        Parameters.Clear()
        Params = MyBase.GetParameters(Me.Connection, "sprCustomerDetails_Modify", True)

        AddParam(Parameters, Params(0), ParameterDirection.ReturnValue)
        AddParam(Parameters, Params(1), ParameterDirection.Input, inFunction)
        AddParam(Parameters, Params(2), ParameterDirection.Input, m_Customer_ID)

        MyBase.CommandType = CommandType.StoredProcedure
        MyBase.CommandText = "sprCustomerDetails_Modify"
        MyBase.Transaction = Me.Transaction
        MyBase.Connection = Me.Connection
        MyBase.Load(m_oDS)

        ReturnCode = Parameters.Item(0).Value
        Return m_oDS

    Catch ex As SqlClient.SqlException
        If ex.Number = eReturnCodes.SaveSinceLastUpdate Then
            ReturnCode = ex.Number
        Else
            Throw
        End If
    Finally
    End Try
End Function

Function AddressDetails_Set(Optional ByVal inFunction As String = "") As DataSet
            Dim Params() As SqlClient.SqlParameter

            Try

                    Parameters.Clear()
                    Params = MyBase.GetParameters(Me.Connection, "sprAddressDetails_Modify", True)

                    AddParam(Parameters, Params(0), ParameterDirection.ReturnValue)
                    AddParam(Parameters, Params(1), ParameterDirection.Input, inFunction)
                    AddParam(Parameters, Params(2), ParameterDirection.Input, m_Customer_ID)

                    MyBase.CommandType = CommandType.StoredProcedure
                    MyBase.CommandText = "sprAddressDetails_Modify"
                    MyBase.Transaction = Me.Transaction
                    MyBase.Connection = Me.Connection
                    MyBase.Load(m_oDS)

                    ReturnCode = Parameters.Item(0).Value
                    Return m_oDS
            Catch ex As SqlClient.SqlException
                    If ex.Number = eReturnCodes.SaveSinceLastUpdate Then
                            ReturnCode = ex.Number
                    Else
                            Throw
                    End If
            Catch ex As System.Exception
                    Throw
            Finally
            End Try
    End Function

 Protected Overridable Function GetParameters(ByRef inConnection As SqlClient.SqlConnection _
                                    , ByVal inStoredProcedureName As String _
                                    , ByVal inIncludeReturnValue As Boolean) As SqlClient.SqlParameter()

    Return SqlHelperParameterCache.GetSpParameterSet(inConnection, inStoredProcedureName, inIncludeReturnValue)
End Function

 Public Sub CleanUp()

    Try
        If Not Me.Transaction Is Nothing Then
            If m_ReturnCode< 0 Then
                Try
                    Me.Transaction.Rollback()
                Catch ex As Exception
                End Try
            Else
                Try
                    Me.Transaction.Commit()
                Catch ex As Exception
                End Try
            End If
        End If

        If Me.Connection.State = ConnectionState.Open Then
            Me.Connection.Close()
        End If
    Catch ex As Exception
    Finally
        If Not Me.Transaction Is Nothing Then
            Me.Transaction.Dispose()
            Me.Transaction = Nothing
        End If
        If Not Me.Connection Is Nothing Then
            Me.Connection.Dispose()
            Me.Connection = Nothing
        End If
    End Try

End Sub
Baahubali
  • 4,604
  • 6
  • 33
  • 72
  • Can you show us your actual code? There is no `ts` defined, and it's outside the using scope in any case. It's hard to give you an answer when we don't see what you're actually doing. – Rob May 05 '16 at 10:16
  • If inside a `TransactionScope`, `Transaction.Commit` won't actually commit until the transaction scope completes. That doesn't mean you don't still have to call it! – Jeroen Mostert May 05 '16 at 10:22
  • @JeroenMostert: If transaction won't commit, how come does my web service can't save it's changes and only work after write transaction.commit – Baahubali May 05 '16 at 10:44
  • Why is there a new transaction in dosomething()? What is the purpose? – RichardCL May 05 '16 at 10:48
  • From [`Transaction`](https://msdn.microsoft.com/en-us/library/system.transactions.transaction(v=vs.110).aspx): "The System.Transactions namespace provides both an explicit programming model based on the Transaction class, as well as an implicit programming model using the TransactionScope class, in which transactions are automatically managed by the infrastructure. It is highly recommended that you use the easier implicit model for development.". To which I'd add my own recommendation - don't **mix** the two models. Pick one model and stick to that. – Damien_The_Unbeliever May 05 '16 at 10:53
  • @Damien_The_Unbeliever: I agree. However i have inherited this legacy code and if i remove the transaction object, it will break the code at 50 other places and therefore i wrapped the whole thing in a transactionscope – Baahubali May 05 '16 at 11:36
  • @RichardCL: Please see the above answer that i provided for Damien – Baahubali May 05 '16 at 11:37
  • I rather agree with Damien. There's a lot of complexity here. (1) There's the .NET framework transaction mechanism being used in two ways. (2) Lightweight Transaction Manager. (3) MSDTC is probably getting involved as the outer transaction straddles your web service and SQL Server. (4) SQL Server's transaction mechanism. (5) Web service's transaction mechanism. – RichardCL May 05 '16 at 11:52
  • In dosomething(), the Transaction is being instantiated inside a using block, in the same way as the TransactionScope object. However I'm not sure it behaves in the same way. The documentation of the Transaction.Dispose() method does not mention rolling back the transaction. See Transaction.Dispose Method (): https://msdn.microsoft.com/en-us/library/system.transactions.transaction.dispose(v=vs.110).aspx – RichardCL May 05 '16 at 11:59
  • i missed writing a catch block in stackoverflow as i didn't want to overcomplicate the code. in the real code, transaction is inside a try catch and whenever there is an excption, it rolls back the code inside the catch block otherwise it commits it. i am sorry but it's not using the using statement. i just wrote it to ease things. in reality i just wanted to understand the behaviour and thought it will be straightforward but that does not look like to be the case now. however would appreciate any help to understand this. – Baahubali May 05 '16 at 12:01
  • Ha ha! The truth is gradually emerging. It would help if you could update your code please. – RichardCL May 05 '16 at 12:03
  • It helps if you can create a [mcve] - we don't need to see your actual code, but it helps if you can create an actual, runnable, example that demonstrates the problem you're having. When you show us unrunnable code, all we can do is vaguely speculate. – Damien_The_Unbeliever May 05 '16 at 17:49
  • @Damien_The_Unbeliever: I have updated the code sample. – Baahubali May 06 '16 at 01:09
  • @RichardCL: I have updated the code. – Baahubali May 06 '16 at 01:09

0 Answers0