"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?

Michael Kniskern · Accepted Answer · 2016-02-11T00:03:39.430

70

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
    }
}

edited Feb 11 '16 at 00:03

answered Oct 15 '08 at 01:07

Michael Kniskern

24,792
68
164
231

I stumbled onto same situation. I had to refer to two different databases within the same transaction scope. Thanks for the tip. – rageit Apr 09 '12 at 12:52
5

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. – viggity Jul 11 '12 at 14:28
2

Logging applications *should* probably suppress any outer TransactionScope context. – user2864740 Jan 19 '17 at 07:35
NLog supresses any outer TransactionScope: https://github.com/NLog/NLog/wiki/Database-target – Alex Oct 17 '19 at 15:40
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. – Rajaram Shelar Dec 21 '20 at 12:25

R. Schreurs · Answer 2 · 2012-02-06T13:37:20.567

18

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>

edited Feb 06 '12 at 13:37

answered Feb 06 '12 at 13:26

R. Schreurs

8,587
5
43
62

1

Is the same than `TransactionOptions.Timeout` ? – Kiquenet Sep 27 '18 at 10:49
Note that there's a machinewide .NET framework config that overrules this when it's over 10 minutes! – DdW Aug 19 '20 at 17:35

score 14 · Answer 3 · answered Nov 30 '10 at 11:36

14

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();
    ...

}

answered Nov 30 '10 at 11:36

Sharique

4,199
6
36
54

18

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. – Russell Horwood May 27 '15 at 10:21
This exception can also occur in the case of timeout and the information about timeout should be also included in the exception data – Pawel Maga Apr 14 '21 at 21:05

J. Minjire · Answer 4 · 2020-11-02T12:10:50.500

8

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:

Add firewall rules to allow your machines to talk to each other.
Ensure the distributed transaction coordinator service is running
Enable network dtc access. Run dcomcnfg. Go to Component sevices > My Computer > Distributed Transaction Coordinator > Local DTC. Right click properties.
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.

edited Nov 02 '20 at 12:10

answered Aug 19 '20 at 11:33

J. Minjire

1,003
11
22

@KJN thank you! Should we do this on the machine where SQL Server works? – lilo.jacob Oct 11 '21 at 12:49
1

Yes, do it where sql server is running from. – J. Minjire Oct 12 '21 at 09:53
Well, I am on Linux servers and using MySQL :/? Any ideas? – Vedran Mandić Jul 10 '23 at 06:51
1

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. – J. Minjire Jul 10 '23 at 11:10

score 5 · Answer 5 · answered Oct 10 '08 at 21:55

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?

answered Oct 10 '08 at 21:55

Wyatt

1,366
9
14

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() – Michael Kniskern Oct 10 '08 at 22:04
+1 because I have received this error when the transaction language in my stored proc was wrong, i.e. transaction counts being messed up. – codeMonkey Mar 28 '17 at 16:48

score 3 · Answer 6 · answered May 04 '18 at 14:10

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.

score 3 · Answer 7 · answered Jun 03 '21 at 21:48

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.

score 0 · Answer 8 · answered Aug 10 '21 at 08:08

0

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 ;)

answered Aug 10 '21 at 08:08

Cătălin Rădoi

1,804
23
43

score 0 · Answer 9 · answered Nov 15 '21 at 14:27

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).

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

9 Answers9

Linked