.net nesting transactioncope
We think we understand transaction and nesting (transactioncope.requires) - i.e.
-------------------------------------------------- ----- 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 am wondering if there is a way to create a transaction, nested transaction, dependent transaction, custom transaction, or whatever, where the rollback commit script also works? -
those. 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 reverts back, the outer transaction is still in a fully healthy state and is completely unaffected by the inner transaction, as if it had never been called?
source to share
No, and I think you are thinking about it the wrong way. This is all just one transaction, namely that nesting allows the blocks of code concerned to vote if the transaction is successful (without having to pass a transaction object) rather than creating nested transactions. In fact, why is the method on the transaction called Complete and not Commit.
Edit to address comments from OP
To get what you want, I think you need to create two TS objects, the second with RequiresNew, and then complete / rollback as needed. I don't know if the first transaction saw the changes from the second or not, you will have to experiment on your own and see if TS can help here.
I understand what you are trying to do, and I am not saying that you are wrong in trying to do it; if that's what your use case requires, then what it requires.
However, I do not believe TS is intended for this use case, and I think the documentation related to nested transactions is unfortunate as its not really nested transactions as is commonly discussed (e.g. in TSQL).
TS is for the more common use case where components A and B 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 used on its own or as part of A's work, and either starts a transaction or reuses A (since A is UoW) without having to pass a transaction object.
source to share
Not as a transaction area.
If your transactions are linked to the Database Resource Manager (which is the vast majority of all uses of a managed TransactionScope), you can use the database capabilities. Databases support transaction savepoints. The actual implementation depends on the database, lets talk about SQL Server.
You can use 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 routine template allows graceful recovery in the event of exceptions, allowing inner work to be rolled back while the outer work continues and occurs.
You can do the same in managed code using SqlTransaction.Save()
and SqlTransaction.Rollback Method (String)
.
However, none of these are supported by the System.Transactions API. this is not surprising given that one of the main features of System.Transactions is to manage distributed transactions (multiple RMs), but database transaction savepoints are incompatible with distributed transactions.
source to share