.Net and subtransactions

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.

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 and subtransactions

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

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. 

RE: .Net and subtransactions

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

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.

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.