Transaction processing strange behavior

Author Message
 Posted 6/14/2012 8:56:04 AM
StrataFrame Novice

StrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame Novice

Group: StrataFrame Users
Last Login: 12/15/2016 10:18:04 AM
Posts: 84, Visits: 835
Hi,

For some reason when I try to save data to two tables during a transaction save, sometimes the transaction only commits one of the tables. This is on a website. It happens when a person might put a payment through and the entire transaction fails, and rolls back for some reason.   Then the person will try it again within a couple of minutes, and the transaction only commits the data to the 2nd save.  See below:


        '-- first generate a unique transaction key
        Dim lcTransactionKey As String = AES.BOL.Base.Utils.NewSeqGuid().ToString

        Try
            TransactionBegin(Me.OPPaymentDetail.DataSourceKey, lcTransactionKey, IsolationLevel.ReadCommitted)
           
            '-- First save data to the contact information table - This save Fails
            Me.OPPaymentDetail.Save(True, lcTransactionKey)
           
            '-- next get the primary key value and save to ACH detail table - This save Succeeds
            Me.OPACHInfoBO.ah_op_Key = Me.OPPaymentDetail.GetPostSavePrimaryKeyValue(-1).ToString
            Me.OPACHInfoBO.Save(True, lcTransactionKey)


            TransactionCommit(Me.OPPaymentDetail.DataSourceKey, lcTransactionKey)
 
            Return True
        Catch ex As Exception

            TransactionRollback(Me.OPPaymentDetail.DataSourceKey, lcTransactionKey)

            Me.SendTransactionReportError()
            Return False
        End Try


It seems the 2nd transaction always commits, but the first doesn't. 
It's running on SQL Server Express 2005, Windows Server 2003.  Could it be some memory cache issue?

Thanks,

Marcel


Tags:
Post #31244
Add to Twitter Add to Facebook
 Posted 6/14/2012 10:08:47 AM
StrataFrame VIP

StrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIP

Group: StrataFrame Users
Last Login: 1/19/2021 11:57:36 AM
Posts: 2,436, Visits: 24,158
Hi Marcel,

Your transaction looks just fine, except that I would put a condition to the Me.OPPaymentDetail.Save() as follow:

  '-- first generate a unique transaction key
         Dim lcTransactionKey As String = AES.BOL.Base.Utils.NewSeqGuid().ToString
         Try
             TransactionBegin(Me.OPPaymentDetail.DataSourceKey, lcTransactionKey, IsolationLevel.ReadCommitted)
             '-- First save data to the contact information table - This save Fails
             If Me.OPPaymentDetail.Save(True, lcTransactionKey) = MicroFour.StrataFrame.Data.SaveUndoResult.Success Then
                 '-- next get the primary key value and save to ACH detail table - This save Succeeds
                 Me.OPACHInfoBO.ah_op_Key = Me.OPPaymentDetail.GetPostSavePrimaryKeyValue(-1).ToString
                 Me.OPACHInfoBO.Save(True, lcTransactionKey)
             End If

             TransactionCommit(Me.OPPaymentDetail.DataSourceKey, lcTransactionKey)
             Return True
         Catch ex As Exception
             TransactionRollback(Me.OPPaymentDetail.DataSourceKey, lcTransactionKey)
             Me.SendTransactionReportError()
             Return False
         End Try

Also if your transaction is failing, you are not passing the ex Exception to your Me.SendTransactionReportError() so you don't really know why it may be failing.


Edhy Rijo
(Using VB.Net 2019/SQL Server 2017)
Post #31245
Add to Twitter Add to Facebook
 Posted 6/18/2012 8:56:39 PM
StrataFrame Novice

StrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame Novice

Group: StrataFrame Users
Last Login: 12/15/2016 10:18:04 AM
Posts: 84, Visits: 835
Thanks for you reply Edhy!

The thought didn't occur to me that I need to check for success.  I always thought that the try/catch would trap for transaction failure automatically without the conditional and skip the rest.

Marcel


Edited: 6/18/2012 8:58:05 PM by Marcel Heitlager
Post #31249
Add to Twitter Add to Facebook
 Posted 6/19/2012 2:09:57 AM
StrataFrame VIP

StrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIP

Group: StrataFrame Users
Last Login: 1/19/2021 11:57:36 AM
Posts: 2,436, Visits: 24,158
Hi Marcel,

You are welcome!!!

Marcel Heitlager (6/19/2012)
The thought didn't occur to me that I need to check for success.  I always thought that the try/catch would trap for transaction failure automatically without the conditional and skip the rest.Marcel

Well, you are right, but since you don't know why the transaction is failing, then I make myself a habit to check the saving results in most of the time either way.  Bottom line is that you need to try to figure out why the transaction is failing in order to present the user with a nice message and possible instructions on how to continue after the failing transaction.


Edhy Rijo
(Using VB.Net 2019/SQL Server 2017)
Post #31250
Add to Twitter Add to Facebook


Similar Topics

Expand / Collapse

Reading This Topic

Expand / Collapse

Site Map - Home - My Account - Forum - About Us - Contact Us - Try It - Buy It

Microsoft, Visual Studio, and the Visual Studio logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.