C# nested transactions

Question

I have a question about the code shown below.

try
{
     using (TransactionScope outerTransScope = new TransactionScope(TransactionScopeOption.RequiresNew))
     {
          // 1.Change values in certain columns from the SQL database table

          using (TransactionScope innerTransScope = new TransactionScope(TransactionScopeOption.RequiresNew))
          {
               // 2.Update a column from the SQL database table by using the changed values from the outerTransScope as a JOIN condition.

               // 3.Commit the update query
               innerTransScope.Complete();
          }
     }
     // 4.Rollback the changed values that were executed in the outerTransScope
}
catch (Exception)
{
// Log the exception
}

Am I right to say that this isn't going to work as I expect? As the outerTransScope is actually rolling back the innerTransScope, because I never commit the outerTransScope.

This means you can only commit nested transactions if the root transaction is committed at the end. If the root transaction isn't committed then all the nested transactions are rolled back even if you commited the inner transactions.

Is it in any way possible to get the functionality shown in the code above? Thanks in advance.

EDIT: Let's say we got two tables, table A and table B.

Table A:                         Table B:
Column A | Column B              Column A | Column B
Siemens  | 100                   SIE      | null
Siemens  | 101                   SIE      | null
Siemens  | 102                   SIE      | null
Siemens  | 103                   SIE      | null

I want to update Column B from Table B with Column B from Table A, however I only want to do this when Column A from Table A and Column A from Table B are a 100% match. Right now nothing will happen because Column A from Table A does not equals Column A from Table B.

So to tackle this problem the user has the ability to edit columns with something I call an actionscheme. This actionscheme will replace 'Siemens' with 'SIE' in table A. When this is replaced the Column A from Table A equals Column A from Table B. With this match I can now update Column B from Table B with the values from Column B from Table A. When this update is finished I want to commit those changes and Rollback the edits I made on Column A from Table A ('Siemens' changed into 'SIE' has to be rolled back).

In my code it looks something like this:

try
{
    using (TransactionScope outerTransScope = new TransactionScope(TransactionScopeOption.RequiresNew))
    {
          // 1.Change Siemens into SIE Column A from Table A
              using (TransactionScope innerTransScope = new TransactionScope(TransactionScopeOption.RequiresNew))

          {
               // 2.Update Column B from Table B with the values of Column B from Table B, but only if Column A from Table A equals Column A from Table B. 

               // 3.Commit the update of Column B from Table B
               innerTransScope.Complete();
          }
     }
     // 4.Rollback the changes in Column A from Table A (So SIE becomes Siemens again).
}
catch (Exception)
{
// Log the exception
}

When you ran this for test purposes (which I assume you did); what did you find out? — Patrick, Jan 15 '15 at 12:30
There's no way to create nested transactions via ADO.NET and SQL Server 2005+: http://stackoverflow.com/questions/16214589/nested-transaction-scope-net — Mitch Wheat, Jan 15 '15 at 12:31
See [Nested/Child TransactionScope Rollback](http://stackoverflow.com/questions/2741988/nested-child-transactionscope-rollback) — Justin, Jan 15 '15 at 12:31
Transaction statements should be stored procedure in the database not on the client side. You will have much better flexibility if you create stored procedures to handle this. — , Jan 15 '15 at 12:42
@VladimirOselsky For the purpose of my application it isn't unfortunately. Thanks anyway. — LimpSquid, Jan 15 '15 at 12:44
TransactionScopeOption.RequiresNew causes the inner transaction to be independent of the outer one, so will be committed immediately instead of waiting to be committed or rolled back with the outer transaction. However your milage with SqlServer may vary depending on what that supports. You may need to open fresh connection within the inner transaction maybe. — Will, Jan 15 '15 at 12:47
So if I understand your question correctly, you want the inner transaction to always commit, independent of what the outer one does? RequiresNew is the option for that. If you want the two to be all or nothing, then use Required. I avoid RequiresNew, preferring Required, allowing nested transactions to work quite nicely. I will use RequiresNew to sometimes "break out" and do something independent. — Will, Jan 15 '15 at 12:53
@Patrick The update query in the innerTransScope changed the value of a column after it was committed. However when the outTransScope was disposed all the values (including the values that were updated in the innertransscope) were rolled back. — LimpSquid, Jan 15 '15 at 13:18
@Will The outer one is only used to change values temporarily that won't be a permanent change in the SQL database. The inner transaction is then used to make an update in the SQL database which should be a permanent change. However when I dispose the outer transaction, the inner transaction is also rolled back no matter what TransactionOptions I use. — LimpSquid, Jan 15 '15 at 13:23
@VladimirOselsky *"You will have much better flexibility"* ; That's a pretty subjective statement, without giving any reasons to why. — Patrick, Jan 15 '15 at 13:27
@Patrick Statement is subjective but that is why it is a comment. But to clarify, when transactions are handled in stored procedure you have much better control over roll-back of specific transaction. Plus you can use `XACT_STATE` with `TRY` `CATCH` to identify and solve other transaction issues. Additionally you save trips from client to the server better. — , Jan 15 '15 at 13:43

score 2 · Accepted Answer · answered Jan 15 '15 at 13:45

I could answer the question as asked but this is just a bad idea. What you are trying to do here will run into many problems such as isolation between transactions and distributed deadlocks between transactions. Distributed deadlocks cannot be resolved by SQL Server. They are resolved by the 30s timeout.

This is a brittle scheme.

Find a way to not need to rollback anything. For example create the temporary data in a temp table and use that. Use a single transaction and a single connection.

score 0 · Answer 2 · answered Jan 15 '15 at 12:51

In nested transactions there is nothing as transaction hierarchy. It will always have only one transasction. You can only create dependecy of transactions using TransactionScopeOption.Required By this it will use the existing available transaction and join it. You are creating dependency for a purpose. If you do not want dependency then first complete initial transaction dispose it and then create new scope and complete that.

C# nested transactions

2 Answers2