3

So I'm building a program that uses nested TransactionScopes. Initially, I used the default constructor but I found when one of my inner scopes fail, everything fails and I was unable to continue. What I want is to have one inner scope that can fail without stopping the outer scope, but if the outer scope fails I want all inner scopes to be rolled back. Is that possible?

puretppc
  • 3,232
  • 8
  • 38
  • 65
ForeverNoobie
  • 531
  • 5
  • 17
  • "What I want is to have one inner scope that can fail without stopping the outer scope" Then the outer scope isn't a transaction. – dursk Jan 16 '14 at 20:17
  • @mattm Ok so using transaction scopes there is no possible way to detect an error, roll back the last few operations (ie. one innerscope) and then continue processing? What if I get rid of the outer scope so essentially I have a bunch of isolated TransactionScopes that execute one at a time. Is there any way to roll back all the successful ones if I determine thats needed? – ForeverNoobie Jan 16 '14 at 20:51
  • 1
    Check out: http://msdn.microsoft.com/en-us/library/ms172152%28v=vs.90%29.aspx#Y1642, and http://stackoverflow.com/questions/2741988/nested-child-transactionscope-rollback – dursk Jan 16 '14 at 21:32
  • @dursk If I consider common definitions of a closed nested transaction, I can not understand your statement "Then the outer scope isn't a transaction. ". See this definition "aborting the nested transaction has no effect on the state of the parent transaction" (https://docs.oracle.com/cd/E17276_01/html/gsg_xml_txn/java/nestedtxn.html) – Niklas Peter Jan 31 '17 at 10:10

1 Answers1

0

Although in SQL there's something that transaction level, and it seems that nested transactions exists, but it's not so - at least not in the way you think it is (i know, had similar issue to solve).

EXPERIMENT: You can try calling multiple begin tran inside a SQL query window, ask the transaction level and transaction id. The level will increase, but in each transaction you get the ID of the outermost transaction, so the outermost is used (so the TransactionScope.Requires new option isn't a big help there).

POSSIBLE SOLUTION: What you want to achieve is possible, but not exactly that way: you can bypass transaction in an inner scope (but have to stick to an atomic process). If this does not apply, you must reconsider what you do and whether you can solve it without this complexity...

Read more about transaction nesting, it behaves differrently on Oracle and SQL.

baHI
  • 1,510
  • 15
  • 20