.Net and subtransactions - Forum - OpenEdge RDBMS - Progress Community

.Net and subtransactions

 Forum

.Net and subtransactions

This question is not answered

Hello everyone,

We are looking at developing a .Net project that accesses a progress database. We have identified a possible problem area in the event of multiple transactions. The following highlights the situation with some pseudo .Net code:

        Start Transaction TA

            Start Subtransaction T1

                Create Customer

            Commit Subtransaction T1

            Perform some business logic

            Start Subtransaction T2

                Create Order

            Commit Subtransaction T2

            Start Subtransaction T3

                Create OrderLine

                If create OrderLine failed then roll back transaction TA including T1 and T2

            Commit Subtransaction T3

            If some condition is detected then roll back transaction TA including T1, T2 and T3

        CommitTransaction TA

Of course the tables involved in this example are for illustration purposes only. The question is, can we do this from .Net code? and how?

We are willing to have the .Net code call into a "progress based data access" layer.

All Replies
  • Oooh that's a nice can of worms opened. I don't know how to solve your particular problem in .net. What I do know is that you are doing something that (in my opinion) is bad practise. Your transaction scope here is rather large. It should be kept as tight as possible, locking as few records as possible. You run the risk of all sorts of problems like lock table overflows, and contention issues with a strategy as you describe.

  • Indeed, if this were an ABL client, what I would recommend is that you either commit the pieces separately or, if for some reason you want to commit them all together, then gather and verify all the information in local variables and then commit it together in a single tight block.

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • If these "create" activities include UI, then the only "safe" way to do what you want is to have some kind of context manager track what customer, order, an orderline "sub"transactions have been performed, and go back and do an explicit "delete" if one of the follow-on fail conditions are met.

  • Thanks Both James and Thomas for the quick answers, I am aware of the potential for this leading to a large transaction scope, however the main point I am trying to get to the bottom of is, can I group transactions together so I can roll back if one fails even when the call is from a .Net code into progress?

    If it makes it easier, you can forget about the commit part so the code looks more like:

       1.Start Transaction TA:

          2. do some work

           3.Create Customer

           4.do some more work some of it based on created customer

           5.Create Order

           6.Do some more work based on customer and/or order

           7.Create OrderLine

           8.Do some final work.

       9. Commit transaction TA

    If my code fails or decides to roll back at lines 4,6 or 8 would I be able to roll back the progress transaction? The above code would be in a Business Logic layer that is calling a Data Access layer. The data access layer can be a 4GL based layer.

  • .NET or not has nothing to do with how transactions work.

    Generally speaking I would expect that "some business logic" would be executed *before* you decided to commit a transaction to the database.  Doing so in the middle of a database transaction opens the door to problems with consistency, concurrency, record locks and so forth.  And if "business logic" includes user activity (even something like an "are you sure?" button) you're really asking for trouble.



    On 9/15/14, 9:59 AM, saiidcd@gmail.com wrote:
    Thread created by saiidcd@gmail.com

    Hello everyone,

    We are looking at developing a .Net project that accesses a progress database. We have identified a possible problem area in the event of multiple transactions. The following highlights the situation with some pseudo .Net code:

            Start Transaction TA

                Start Subtransaction T1

                    Create Customer

                Commit Subtransaction T1

                Perform some business logic

                Start Subtransaction T2

                    Create Order

                Commit Subtransaction T2

                Start Subtransaction T3

                    Create OrderLine

                    If create OrderLine failed then roll back transaction TA including T1 and T2

                Commit Subtransaction T3

                If some condition is detected then roll back transaction TA including T1, T2 and T3

            CommitTransaction TA

    Of course the tables involved in this example are for illustration purposes only. The question is, can we do this from .Net code? and how?

    We are willing to have the .Net code call into a "progress based data access" layer.

    Stop receiving emails on this subject.

    Flag this post as spam/abuse.



    -- 
    Tom Bascom
    603 396 4886
    tom@greenfieldtech.com

    --
    Tom Bascom
    tom@wss.com

  • Thanks Tim. No we would not allow any user interaction to take place during our transactions but some of our business logic can be intricate and we may decide to roll back due to any of many reasons. I am trying to avoid the explicit delete as we are building a large large system and we are looking for something similar to the .Net TransactionScope or the way 4GL can roll back the enclosing transaction.

  • What you're asking is doable, but it'd be kludgy as heck and potentially hard to follow.

    It'd look something like the following code, with "order" and "orderline" creation following a similar structure to the "createcustomer" procedure. 

    outer-block:
    DO TRANSACTION:
    
    run CreateCustomer(output isOk). 
    if not isok then 
       undo, leave outer-block.
    
    run CreateOrder(output isOk). 
    if not isok then 
       undo, leave outer-block.
    
    run CreateOrderLine(output isOk). 
    if not isok then 
       undo, leave outer-block.
    
    if isSomeOtherFailureCondition then 
       undo, leave outer-block.
    
    END. /* outer-block */
    
    PROCEDURE CreateCustomer:
    DEFINE OUTPUT PARAMETER isOk    AS LOGICAL     NO-UNDO.
    
    DEFINE BUFFER Customer  FOR Customer.
    
    work-block:
    DO TRANSACTION:     /* Tx Start     */
    
        /* Create customer stuff  */
    
    IF isFailCondition THEN
        DO:
        ASSIGN
            isOk = NO
            .
    
        UNDO, LEAVE work-block.
        END.
    
    ASSIGN isOk = YES.
    
    END. /* Tx End       */
    
    END PROCEDURE.
    


    Having written this, I'd much prefer a solution which creates results in a set of TTs, and when all is "Well and good" save the TTs off to their respective db tables or ditches them if things don't work out. 

  • If you imagine that you can write .NET code that makes multiple sequential calls to app servers to start database transactions and sub-transactions and then, later commit them or roll them back then you are not going to have success.

    If, on the other hand, the outline of transactions and sub-transactions that you showed is expected to occur within a single call to an app server then it would be within the normal 4gl capabilities.


    On 9/15/14, 10:34 AM, saiidcd@gmail.com wrote:
    Reply by saiidcd@gmail.com

    Thanks Tim. No we would not allow any user interaction to take place during our transactions but some of our business logic can be intricate and we may decide to roll back due to any of many reasons. I am trying to avoid the explicit delete as we are building a large large system and we are looking for something similar to the .Net TransactionScope or the way 4GL can roll back the enclosing transaction.

    Stop receiving emails on this subject.

    Flag this post as spam/abuse.



    -- 
    Tom Bascom
    603 396 4886
    tom@greenfieldtech.com

    --
    Tom Bascom
    tom@wss.com

  • In particular, you should distinguish what happens in your code from what happens in the database.  You can happily provide various kinds of roll backs, restarts, undos, etc. in your code dealing with local variables and then when you are completely done send a whole set of clean data to the database to commit as a single transaction.  You can also commit pieces if that makes business sense to you.  Just don't mush the two together.

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • ChUIMonster, can you tell me what kind of problems would I face if I were to implement the strategy you outlined. Call an AppServer (state aware I assume) from .Net code, get it to start a transaction, make the successive calls for each subtransaction, call the App server  to roll back or commit.

  • The phrase "performance / implementation nightmare" comes to mind. Appserver calls are expensive and you need to keep them as small and performant as possible. Doing multiple calls to perform - or rollback a TX is not the way to go.

    Go with the populate-some-TTs approach, and then commit or discard them as required at the end.