.net nesting transactionscope

4
votes

We think we understand transactionscope and nesting (transactionscope.requires) - ie


-------------------------------------------------------
inner    | outer     |  
-------------------------------------------------------
commit   | rollback  |  no changes are committed 
commit   | commit    |  all changes are committed
rollback | rollback  |  no changes are committed
rollback | commit    |      ---- doesn't work ----

However, I'm wondering, is there any way of creating a transactionscope, a nested transactionscope, a dependent transaction, a custom transaction or whatever, where rollback commit scenario also works? -

ie you have something in a library function that has its own transaction for whatever reason, that lives under the parent transaction. if the inner succeeds, then the outer transaction has access to any changes, but if the inner rolls back, the outer transaction is still in a completely usable state, and is completely unaffected by the inner transaction, as if it had never been called?

2
c#transactionscope
What you are asking for here ought to be directly supported, IMHO, but apparently isn't. - 500 - Internal Server Error
I agree. - and we've already worked around it, but at this point it's more an intellectual curiosity - ie is it actually possible to make it work like that? I was thinking of something like using an inherited scope, and override the dispose so it does nothing but sort of remember it, but then the next bit of code sees the database as if you committed the transaction - or having some way of magically catching it, and creating another transaction scope afterwards - but then you lose everything that happened before it. It's quite an interesting problem! - Darren Oakey
I believe you are confusing database transactions (which help enforce data consistency and atomic changes) with something else like units of work. From a data consistency perspective: if the outer depends on the inner (i.e. inner is nested in outer) you would never want to continue after an inner rollback. If not the case, outer is not dependent upon inner. In that case nested transactions are the wrong tool to for your task. With refactoring you can normally get the desired result a better way: use sequential transactions (not nested), preemptive checking (if inner will pass), etc. - Arkaine55

2 Answers

5
votes

No, and I believe you're thinking about this wrong. Its all still just a single transaction, its just that the nesting allows interested code blocks to vote if the transaction should succeed (without having to pass the transaction object around), not that you're creating nested transactions. That's actually why the method on transactionscope is called Complete and not Commit.

Edit to address the comments from the OP

To get what you want I think you'd have to create two TS objects, the second one with RequiresNew and then Complete / rollback each as needed. I don't know if the first transaction would see changes from the second or not, you'll have to experiment on your own and see if TS can be of help here.

I understand what you're trying to do and I'm not saying you're wrong to be attempting it; if that's what your use case requires then that's what it requires.

However I don't believe TS is designed for this use case and I think the documentation referring to nested transactions is unfortunate as its not really nested transactions as normally discussed (such as in TSQL).

TS is designed for the more common use case where components A & B both do transactional work, A uses B as part of its work but B can also be used independently. TS allows B to always be transactional whether being used independently or as part of A's work and either start a transaction or reuse A's (since A is a UoW) without having to pass around the transaction object.

1
votes

Not as transaction scopes.

If your transactions are all against a database Resource Manager (which is the vast majority of all uses of managed TransactionScope) then you can leverage the database capabilities. Databases support transaction savepoints. Actual implementation varies by DB, lets talk about SQL Server.

You can leverage transaction savepoints directly in T-SQL, for example see Exception handling and nested transactions:

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Do the actual work here

lbexit:
        if @trancount = 0   
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
    end catch   
end
go

This procedure template allows for graceful recovery in case of exceptions, allowing for the inner work to be rolled back while the outer work continues and commits.

You can do the same in managed code, as using SqlTransaction.Save() and SqlTransaction.Rollback Method (String).

However, none of these are supported by the System.Transactions API. that is hardly surprising, considering that one of the major roles of the System.Transactions is to manage distributed transactions (multiple RMs) but database transaction savepoints are incompatible with distributed transactions.