Nested transaction scope .net

Question

I'm using SQL Server 2008 R2 and trying to use transactions.

First a question about transactions in .net and SQL Server. If I have something like this

try {
    var transactionOption = new TransactionOptions();
    transactionOption.IsolationLevel = IsolationLevel.ReadCommitted;
    transactionOption.Timeout = TransactionManager.MaximumTimeout;

    using (var scope = new TransactionScope(TransactionScopeOption.RequiresNew, transactionOption)) {
        //create question this creates a new question in the database
        Helpers.CreateQuestionBankItem(ref mappedOldNewQuestionItemGuid, missingQuestionBankItems);
        //question created

        //query database for the code of the newly inserted question, will the database give me the code since Complete has not been called as yet?    



        scope.Complete();
    }
}
catch (Exception ex) {
    throw;
}

//query database for the code of the newly inserted question,  will the database give me the code since Complete has been called as now?    

At which point should I call the database to ask for the code of the newly inserted question. Now my second question, before I ask I found this link Nested Transaction . In the light of the above link I want to still ask that if I have something like this

try {
    var transactionOption = new TransactionOptions();
    transactionOption.IsolationLevel = IsolationLevel.ReadCommitted;
    transactionOption.Timeout = TransactionManager.MaximumTimeout;

    using (var outerscope = new TransactionScope(TransactionScopeOption.RequiresNew, transactionOption)) {
        try {
            var transactionOption = new TransactionOptions();
            transactionOption.IsolationLevel = IsolationLevel.ReadCommitted;
            transactionOption.Timeout = TransactionManager.MaximumTimeout;

            using (var innerscope = new TransactionScope(TransactionScopeOption.RequiresNew, transactionOption)) {
                //create question this creates a new question in the database
                Helpers.CreateQuestionBankItem(ref mappedOldNewQuestionItemGuid, missingQuestionBankItems);
                //question created

                //query database for the code of the newly inserted question, will the database give me the code since Complete has not been called as yet?    

                innerscope.Complete();
            }
        }
        catch (Exception ex) {
        }

        //query database for the code of the newly inserted question,  will the database give me the code since Complete has been called as now?    

        outerscope.Complete();
    }
}
catch (Exception ex) {
    throw;
}

If my innerscope completes, will querying SQL Server give me the code of the newly created question.

What happens if the inner scope throws an exception and I gobble it up, will the outer scope also be disposed off?

Does calling innerscope.Complete() completes that inner scope?

Answer 1

If you want to recover from a failure in a transactional context you need to use transaction savepoints. Unfortunately the managed System.Transaction has no support for savepoints. Not only that, but you won't be able to use savepoints, even directly, if you use transaction scopes, because the transaction scope will escalate to distributed transactions and savepoints do not work in distributed contexts.

You can use instead the platform specific SqlTransaction which supports Save() for savepoints. See Exception Handling and Nested Transactions for an example of transaction-aware exception handling.