How to implement nested SQL transactions with ADO.NET?

Question

I need to implement nested transactions in .NET using ADO.NET.

The situation is as follows:

--> Start Process (Begin Transaction)       

   --> Do DB things       

   --> Begin Transaction for step 1
   --> Step 1
   --> Commit transaction for step 1

   --> Begin transaction for step 2
   --> Step 2
   --> Rollback transaction for step 2

   --> etc ...

   --> Do DB things       


--> End Process(Commit or Rollback ALL commited steps --> a.k.a the process)

Can that be done with transaction scopes? Could anyone post an example?

In addition I'd need the process to work for SQL Server 2005 AND Oracle 10g databases... will transaction scopes work with both database engines?

Edit: Note this situation might happen:

Step1 is commited, Step2 is rolled back Step3 is commited.

The process is commited

(Step1 and Step3 do store data into the database, step2 does not)

On the other hand...

Step1 is commited, Step2 is rolled back Step3 is commited.

The process is rolled back.

the NO DATA IS COMMITED to the database

Note: No DB schema or domain values available

Why commit intermediate transactions when you are rolling back all if any one fails? So if nth transaction fails, n-1 to 1 all transactions will rollback, whether they are commited or not. — A G, Apr 09 '10 at 11:39
But he is rolling back some of the inner transactions so he can actually commit half of the steps and rollback the rest at the end, or rollback all of them — Jorge Córdoba, Apr 09 '10 at 11:41
@Jorge: On end process he has mentioned rollback all commited steps.. so its a complete rollback or commit not a partial one. — A G, Apr 09 '10 at 11:43
"I need to implement nested transactions" - Why? In a Transaction, either all steps are successful and the transaction completes, or a step fails and all steps are rolled back. — Paul Turner, Apr 09 '10 at 11:50

score 3 · Accepted Answer · answered Apr 09 '10 at 11:42

3

You could do that in TSQL via save points (SAVE TRAN on SQL Server), but frankly I don't recommend it. You can't do it via TransactionScope, as any abort is terminal (the entire transaction is rolled back as soon as any transaction in the tree indicates failure).

Personally: check the data first, and only perform valid actions. If it fails, that is terminal - roll it back. Possibly separate the work into atomic units that can be truly committed (or rolled back) in isolation.

answered Apr 09 '10 at 11:42

Marc Gravell

1,026,079
266
2,566
2,900

The possible errors are due to foreign key being violated not due to data values that can be previously checked, whether step2 fails or not, step3 can still be commited. Thanks about TSQL but I need a solution over ADO.NET (I have to work with Oracle aswell) – lestival Apr 09 '10 at 11:48
1

@manza_jurjur - sorry, but I don't accept that foreign keys can't be validated in advance. And while SqlTransaction has a `Save()` method, AFAIK that is not the case for Oracle. You can't necessarily have what doesn't exist. – Marc Gravell Apr 09 '10 at 11:53
Sure, but in this case I don't know the DB schema and domain values – lestival Apr 09 '10 at 12:03

score 2 · Answer 2 · answered Apr 09 '10 at 13:36

2

On Oracle:

BEGIN    
  SAVEPOINT STEP1;
  -- do some things
  IF your_criteria_for_commit_is_needed THEN
    NULL; -- do nothing
  ELSE
    ROLLBACK TO SAVEPOINT STEP1;
  END IF;

  SAVEPOINT STEP2;
  -- do some other things
  IF your_other_criteria_for_commit_is_needed THEN
    NULL; -- do nothing
  ELSE
    ROLLBACK TO SAVEPOINT STEP2;
  END IF;

  -- SOME NUMBER OF OTHER STEPS

  IF your_criteria_for_all_step_commit_is_needed THEN
    COMMIT; -- commit all changes to DB
  ELSE
    ROLLBACK; -- rollback all changes
  END IF;
END;
/

answered Apr 09 '10 at 13:36

jva

2,797
1
26
41

I think the whole idea of savepoints within a transaction dubious: when part of the transaction fails then the whole transaction ought to be rolled back. If it doesn't matter whether Step2 succeeds or fails why bother with Step2 at all? However, if your Unit Of Work is specified in such a wack fashion this is definitely the way to implement it. – APC Apr 10 '10 at 07:17
Great for PL-SQL but Could I do this over ADO.NET? – lestival Apr 12 '10 at 06:50

score 0 · Answer 3 · answered Apr 09 '10 at 11:42

If you are want ALL steps to be committed or NONE then wouldn't one transaction be more appropriate? You can pass an existing transaction object to the constructor of an ADO.Net Command object and thus perform multiple updates within the scope of a single transaction.

How to implement nested SQL transactions with ADO.NET?

3 Answers3