"The operation is not valid for the state of the transaction" error and transaction scope

Question

I am getting the following error when I try to call a stored procedure that contains a SELECT Statement:

The operation is not valid for the state of the transaction

Here is the structure of my calls:

public void MyAddUpdateMethod()
{

    using (TransactionScope Scope = new TransactionScope(TransactionScopeOption.RequiresNew))
    {
        using(SQLServer Sql = new SQLServer(this.m_connstring))
        {
            //do my first add update statement

            //do my call to the select statement sp
            bool DoesRecordExist = this.SelectStatementCall(id)
        }
    }
}

public bool SelectStatementCall(System.Guid id)
{
    using(SQLServer Sql = new SQLServer(this.m_connstring)) //breaks on this line
    {
        //create parameters
        //
    }
}

Is the problem with me creating another connection to the same database within the transaction?

Coniine

Answer 1

After doing some research, it seems I cannot have two connections opened to the same database with the TransactionScope block. I needed to modify my code to look like this:

public void MyAddUpdateMethod()
{
    using (TransactionScope Scope = new TransactionScope(TransactionScopeOption.RequiresNew))
    {
        using(SQLServer Sql = new SQLServer(this.m_connstring))
        {
            //do my first add update statement            
        }

        //removed the method call from the first sql server using statement
        bool DoesRecordExist = this.SelectStatementCall(id)
    }
}

public bool SelectStatementCall(System.Guid id)
{
    using(SQLServer Sql = new SQLServer(this.m_connstring))
    {
        //create parameters
    }
}

Coniine

I stumbled onto same situation. I had to refer to two different databases within the same transaction scope. Thanks for the tip. – Thea

Good catch, a common place to see this happen is if you have a logging framework (nlog, log4net) that is writing to the DB, since the logging framework will create its own connection to the database as your application. – Volteface

Logging applications should probably suppress any outer TransactionScope context. – Ernaline

NLog supresses any outer TransactionScope: github.com/NLog/NLog/wiki/Database-target – Aeolus

I opened same connection twice with using statements under same TransactionScope but still worked. I used SqlConnection instead of SQLServer as used in your example above. – Raber

Answer 2

When I encountered this exception, there was an InnerException "Transaction Timeout". Since this was during a debug session, when I halted my code for some time inside the TransactionScope, I chose to ignore this issue.

When this specific exception with a timeout appears in deployed code, I think that the following section in you .config file will help you out:

<system.transactions> 
        <machineSettings maxTimeout="00:05:00" /> 
</system.transactions>

Grisgris

Is the same than TransactionOptions.Timeout ? – Partiality

Note that there's a machinewide .NET framework config that overrules this when it's over 10 minutes! – Rebarebah

Note that you'll probably want to do this in your dev config, not a prod one, if your goal is to allow your pauses when debugging and not to make your users wait longer for any bad operations that may never finish. – Priscillaprise

Answer 3

I also come across same problem, I changed transaction timeout to 15 minutes and it works. I hope this helps.

TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout = new TimeSpan(0, 15, 0);
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required,options))
{
    sp1();
    sp2();
    ...

}

Groome

I strongly suspect that it's not the timeout that changed the behaviour but the fact that you've changed the isolation level from Serializable to ReadCommitted. – Horten

This exception can also occur in the case of timeout and the information about timeout should be also included in the exception data – Informal

Answer 4

For any wanderer that comes across this in the future. If your application and database are on different machines and you are getting the above error especially when using TransactionScope, enable Network DTC access. Steps to do this are:

1. Add firewall rules to allow your machines to talk to each other.
2. Ensure the distributed transaction coordinator service is running
3. Enable network dtc access. Run dcomcnfg. Go to Component sevices > My Computer > Distributed Transaction Coordinator > Local DTC. Right click properties.
4. Enable network dtc access as shown.

Important: Do not edit/change the user account and password in the DTC Logon account field, leave it as is, you will end up re-installing windows if you do.

Kamikamikaze

@KJN thank you! Should we do this on the machine where SQL Server works? – Fusain

Yes, do it where sql server is running from. – Kamikamikaze

Well, I am on Linux servers and using MySQL :/? Any ideas? – Celia

Sorry @VedranMandić, my Linux knowledge is almost 0. Post a question tagging linux, mysql etc. Might find help from other people with linux/mysql knowledge. – Kamikamikaze

Answer 5

I've encountered this error when my Transaction is nested within another. Is it possible that the stored procedure declares its own transaction or that the calling function declares one?

Unlade

I do not have any t-sql transaction code in any of my stored procedures. In theory, the transaction should be controlled by the MyAddUpdateMethod() – Coniine

+1 because I have received this error when the transaction language in my stored proc was wrong, i.e. transaction counts being messed up. – Dramatize

Answer 6

In my case, the solution was neither to increase the time of the "transactionscope" nor to increase the time of the "machineSettings" property of "system.transactions" of the machine.config file.

In this case there was something strange because this error only happened when the volume of information was very high.

So the problem was based on the fact that in the code inside the transaction there were many "foreach" that made updates for different tables (I had to solve this problem in a code developed by other personnel). If tests were performed with few records in the tables, the error was not displayed, but if the number of records was increased then the error was displayed.

In the end the solution was to change from a single transaction to several separate ones in the different "foreach" that were within the transaction.

Archery

Answer 7

For me, this error came up when I was trying to rollback a transaction block after encountering an exception, inside another transaction block.

All I had to do to fix it was to remove my inner transaction block.

Things can get quite messy when using nested transactions, best to avoid this and just restructure your code.

Beaner

Answer 8

I encountered this by accidentally timing out the transaction with breakpoints. So fellow wanderers might want to check that isn't the case for them.

Ligature

Answer 9

You can't have two transactions open at the same time. What I do is that I specify transaction.Complete() before returning results that will be used by the 2nd transaction ;)

Adonai

Answer 10

I updated some proprietary 3rd party libraries that handled part of the database process, and got this error on all calls to save. I had to change a value in the web.config transaction key section because (it turned out) the referenced transaction scope method had moved from one library to another.

There was other errors previously connected with that key, but I got rid of them by commenting the key out (I know I should have been suspicious at that point, but I assumed it was now redundant as it wasn't in the shiny new library).

Towhead

Answer 11

To anyone who lands on this page here's another possible reason: watch out for async DB calls.

This won't work:

//pseudocode
using (new TransactionScope(whatever))
{
    await QueryDatabase();
    //"await" frees up the thread, it goes back into the pool
    //and if other code calls the database again
    //on the freed-up thread, it will fail
}

P.S. And the worst part, the exception will come from that other code, and you'll spend hours scratching your head "why the code that uses no transactions at all - throws this error??

Brumaire

Can you elaborate please? EF supports async calls like GetAsync, SubmitChangesAsync etc, so what issue with async/await are you referring to? – Sausage

@Sausage the issue is described in the comments. await causes a thread switch, and TransactionScope is incompatible with that. – Brumaire

Understood, I wasn't convinced that's true but indeed it was an issue if no extra options are passed. It is totally compatible but only by passing TransactionScopeAsyncFlowOption.Enabled into the constructor of TransactionScope. – Sausage