Transaction within a Transaction in C#

I'm importing a flat file of invoices into a database using C#. I'm using the TransactionScope to roll back the entire operation if a problem is encountered.

It is a tricky input file, in that one row does not necessary equal one record. It also includes linked records. An invoice would have a header line, line items, and then a total line. Some of the invoices will need to be skipped, but I may not know it needs to be skipped until I reach the total line.

One strategy is to store the header, line items, and total line in memory, and save everything once the total line is reached. I'm pursuing that now.

However, I was wondering if it could be done a different way. Creating a "nes开发者_开发问答ted" transaction around the invoice, inserting the header row, and line items, then updating the invoice when the total line is reached. This "nested" transaction would roll back if it is determined the invoice needs to be skipped, but the overall transaction would continue.

Is this possible, practical, and how would you set this up?

---

Neither the TransactionScope nor SQL Server support nested transactions.

You can nest TransactionScope instances, but that only has the outward appearance of a nested transaction. In reality, there is something called an "ambient" transaction, and there can be only one at a time. Which transaction is the ambient transaction depends on what you use for TransactionScopeOption when you create the scope.

To explain in more detail, consider the following:

using (var outer = new TransactionScope())
{
    DoOuterWork();

    using (var inner1 = new TransactionScope(TransactionScopeOption.Suppress))
    {
        DoWork1();
        inner1.Complete();
    }

    using (var inner2 = new TransactionScope(TransactionScopeOption.RequiresNew))
    {
        DoWork2();
        inner2.Complete();
    }

    using (var inner3 = new TransactionScope(TransactionScopeOption.Required))
    {
        DoWork3();
        inner3.Complete();
    }

    outer.Complete();
}

Here is what happens for each of the inner scopes:

• inner1 is executed in an implicit transaction, independently of outer. Nothing that happens in DoWork1 is guaranteed to be atomic. If this fails midway through, you'll have inconsistent data. Any work that happens in here is always committed, regardless of what happens to outer.

• inner2 is executed in a new transaction, independently of outer. This is a different transaction from outer but it is not nested. If it fails, the work that happened in outer (DoOuterWork()) and any of the other scopes can still be committed, but here's the rub: If it completes, then rolling back the entire outer transaction will not roll back the work done inside inner2. This is why it is not truly nested. Also, inner2 won't have access to any rows locked by outer, so you could end up with deadlocks here if you're not careful.

• inner3 is executed in the same transaction as outer. This is the default behaviour. If DoWork3() fails and inner3 never completes, then the entire outer transaction is rolled back. Similarly, if inner3 completes successfully but outer is rolled back, then any work done in DoWork3() is also rolled back.

So you can hopefully see that none of these options are actually nested, and won't give you what you want. The Required option approximates a nested transaction, but doesn't give you the ability to independently commit or roll back specific units of work inside the transaction.

The closest thing you can get to true nested transactions in SQL Server is the SAVE TRAN statement combined with some TRY/CATCH blocks. If you can put your logic inside one or more Stored Procedures, this would be a good option.

Otherwise, you'll need to use separate transactions for each invoice as per Oded's suggestion.

---

This is accomplished with a transaction savepoint. It usually looks something like this:

BEGIN TRANSACTION
for each invoice
   SAVE TRANSACTION InvoiceStarted
   BEGIN TRY
     Save header
     Save line 1
     Save line 2
     Save Total
   END TRY
   BEGIN CATCH
     ROLLBACK TO Invoicestarted 
     Log Failed Invoice
   END CATCH
end for
COMMIT

I used a Transact-SQL based pseudo code and this is no accident. Savepoints are a database concept and the .Net Transactions don't support them. You can use SqlTransaction directly and leverage SqlTransaction.Save or you can use T-SQL stored procedures modeled after an exception safe template. I'd recommend you avoid the .Net transactions (ie. TransactionScope) in this case.

---

Instead of using nested transactions, you could create a transaction per invoice. This way only successful updates for whole invoices will occur.

If you would nest transactions the way you describe, you are in danger of having the whole set of data getting rolled back, which is not what you want.

---

Personally, I would first see if the invoice needs to be added - if it does, then do your inserts (in a transaction). Otherwise, just move onto the next invoice.

I don't think it's that great to insert and then do a rollback in the way you describe.

---

A failed inner transaction would roll back the outer transaction, so you can't go that route.

You can probably fake it, though, by using a temp (or a load) table. Insert each invoice transactionally into the load table, and then move from the load table to a permanent table atomically.