How can I separate my TransactionScopes so they don't interfere with each other?

Question

I have some business logic that executes within a simple TransactionScope:

        using (var scope = new TransactionScope())
        {

            // does some logic.

            // then some more...

            scope.Complete();
        }

During execution an ADO exception occurs and the transaction is on it's way to being disposed.

In the meantime the exception is captured further upstream, and I try to log it to my database, via another connection.

At this point I get another error saying The operation is not valid for the state of the transaction.

Eeek!

Now I can see I am able to use IsolationLevel, to change the way my transactions interact, but is this the right thing to do? And which should I use?

Sounds like all of your code is running inside on big DTC-transaction, correct? — Marius, Jun 02 '14 at 08:57
not sure what you mean? as above..during this particular execution the only transaction I am explicitly creating is as above. — shenku, Jun 02 '14 at 09:06
Yes but multiple connections can lead to distributed transactions, you mentioned a second connection against the database — Marius, Jun 02 '14 at 09:07
Did suppressing the transaction while logging resolve your issue? — andyp, Jun 04 '14 at 09:55

score 1 · Accepted Answer · edited May 23 '17 at 12:11

Since your are mentioning multiple connections and the error you are referring to is something I have seen with DTC transactions I am guessing you are running DTC and that the state of the transaction is faulted due to the AdoException. Try putting a "RequiresNew" transaction scope around your logging code or post more of your code, its hard to see your architecture from that small snippet.

Take a look at this answer showing how two connections can cause DTC to kick in based on which version of sql server you are running: TransactionScope automatically escalating to MSDTC on some machines?

andyp · Answer 2 · 2014-06-02T09:42:58.880

As your problem occurs because the transaction is being rolled back / disposed, I'd see two options: using a new transaction or no transaction at all to log. I'd probably go with the second option and log without a transaction.

You can pass a parameter of type TransactionScopeOption to the TransactionScope constructor to suppress transactions for your logging statements like this:

using (var scope = new TransactionScope(TransactionScopeOption.Suppress)
{
    // .. log here
}

To log within a new transaction pass TransactionScopeOption.RequiresNew.

score 0 · Answer 3 · edited May 23 '17 at 12:27

0

you can not have two connections open in transaction scope. close first connection, then open connection for logging error in db. have look at this "The operation is not valid for the state of the transaction" error and transaction scope

edited May 23 '17 at 12:27

Community

1
1

answered Jun 02 '14 at 09:21

Amit Gaikwad

923
1
9
15

How can I separate my TransactionScopes so they don't interfere with each other?

3 Answers3