Exception when trying to create/use an inner transaction

My application (WCF service) uses LINQ data contexts and recently we've decided to wrap everything in a transaction. Seems to work pretty well; each call into the service has its own transaction so if an exception is thrown then everything is rolled back and no changes are committed to the database.

However we have a notion of a "supervisor code", which can be generated and used once-only. We wish that the supervisor code will be marked as being used even if an error should occur later in the operation. To me, this suggests that I should use an inner transaction?

So inside my SetToUsed method, I put in a new transaction like so:

    public void SetToUsed(string code)
    {
        // security codes explicitly need their own transaction so that they will still be marked as having been used even if the outer transaction were to be rolled back due to error
        using (var securityCodeTransaction = new TransactionScope(
                TransactionScopeOption.RequiresNew,
                new TransactionOptions
                {
                    IsolationLevel = IsolationLevel.ReadUncommitted
                },
            开发者_运维知识库    EnterpriseServicesInteropOption.Automatic))
        {
            var returnedItems = m_safetyCodeRepository.FindAll(sc => sc.safetyCode == code &&
                                                                     sc.safetyCodeTypeId == (long)GetCodeType() &&
                                                                     sc.isUsed == false);
            foreach (var item in returnedItems)
            {
                item.isUsed = true;
                m_safetyCodeRepository.SaveChanges(item);
            }

            securityCodeTransaction.Complete();
        }
    }

However, this causes an exception: System.InvalidOperationException: Connection currently has transaction enlisted. Finish current transaction and retry.

The exception is thrown on the FindAll line, which is a thin wrapper for

dataContext.GetTable<tbSftSafetyCodes>().Where(exp).ToList()

Am I missing something, or going about this in entirely the wrong way?

Edit: I realised that I did not actually need a Transaction for the Supervisor Code changes per se.

So I changed the TransactionScopeOption to be TransactionScopeOption.Suppress instead. I would still like to know why having the inner transaction using TransactionScopeOption.RequiresNew didn't work though!

---

TransactionScopeOption.RequiresNew    

attempts to start an entirely new transaction which is independent of the one it sits within, is this what you intend? The Requires option will enlist the new transaction into the currently active one.

However, if yopu always want the code to be marked as used - do you need to have it in a transaction at all? Could you not just mark it as used amd commit that change just before you start your transaction-wrapped operation?

---

In my case beside using the option TransactionScopeOption.RequiresNew it was also necessary to preceed the using statement with

    dc.Connection.Open();
    dc.Connection.EnlistTransaction(Transaction.Current);
    if (dc.Connection.State!=ConnectionState.Closed) dc.Connection.Close();

    using (var securityCodeTransaction = new TransactionScope(
            TransactionScopeOption.RequiresNew,
            new TransactionOptions
            {
                IsolationLevel = IsolationLevel.ReadUncommitted
            },
            EnterpriseServicesInteropOption.Automatic))
    {
            // same code as in question
    }

That made the issue go away - however, I have no idea why that was required.

Note: In order to enlist the transaction, the connection must be open - hence the open + close statements are required.