locked
Transaction Exception: The operation is not valid for the state of the transaction. RRS feed

  • Question

  • Hello All!

    I'm using a code sample I found on http://softwaredevscott.spaces.live.com/blog/cns!1A9E939F7373F3B7!155.entry in order to try and keep a clean database environment by utilizing TransactionScope with MBUnit. Basically, the idea being that every unit test with a DB call that has potential to modify the DB is wrapped in a TransactionScope() and rolled back after each test.

    I'm relatively new to the idea of transactions at the application level, although I have used them in the past with TSQL. I've done some research and haven't found a lot of information about the particular error I'm having and because I don't understand all of it 100%, I won't reiterate it.

    Basically, I have an abstract class that is similar to the one in the article I linked above. I'm extending that abstract class in my own test classes. Inside my unit test I query the DB to get a PK value, then I call my webservice business object, then I make one more query to the DB to ensure that the expected outcome has occurred (in this case, a row has been deleted).

    Abstract Class:
    [TestFixture]
    public abstract class TransactionalFixture
    {
        /// <summary>
        /// Transaction that we hold open for the duration of the test.
        /// </summary>
        private System.Transactions.TransactionScope testTransaction;

        [SetUp]
        public void SetUp()
        {
            testTransaction = new TransactionScope();
        }

        [TearDown]
        public void TearDown()
        {
            testTransaction.Dispose();
        }
    }

    Pseudo code for my the class:
    public class Sets : TransactionalFixture
    {
        [Test]
        public void DeleteReport()
        {
            Database db = DatabaseFactory.CreateDatabase("ReportingDB");
            DbCommand dbc = null;
            DataSet ds = null;

            try
            {
                string query = "select top 1 reportid from ReportRequest"; //pseudo query
                dbc = db.GetSqlStringCommand(query);
                ds = db.ExecuteDataSet(dbc);

                // check for expected data
                long reportID = Convert.ToInt64(ds.Tables[0].Rows[0][0]);

                // call business object
                Webservice.DeleteReport(reportID); // this web service call deletes the record out of the ReportRequest table

                string query = "select count(*) from ReportRequest where reportid = " + reportID
                dbc = db.GetSqlStringCommand(query);
                int reportCount = Convert.ToInt32(db.ExecuteScalar(dbc)); // this is the line it fails on
            }
            catch(Exception ex){ .... }
        }
    }

    I get an exception every single time this is run:
    System.Transactions.TransactionException: The operation is not valid for the state of the transaction.
    at System.Transactions.TransactionState.EnlistPromotableSinglePhase(InternalTransaction tx, IPromotableSinglePhaseNotification promotableSinglePhaseNotification, Transaction atomicTransaction)
    at System.Transactions.Transaction.EnlistPromotableSinglePhase(IPromotableSinglePhaseNotification promotableSinglePhaseNotification)
    at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
    at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
    at System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction)
    at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)
    at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
    at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
    at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
    at System.Data.SqlClient.SqlConnection.Open()
    at Microsoft.Practices.EnterpriseLibrary.Data.Database.OpenConnection()
    at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteScalar(DbCommand command)
    at SAAS.BusinessObjects.Reporting.Sets.DeleteReport() in C:\p4\depot\SAAS\Development\UnitTest\SAAS.BusinessObjects.Reporting.UnitTest\Sets.cs:line 120

    If I comment out the bottom DB call, there is no error.

    I'm running this on a Windows XP machine and talking to a MS SQL Server 2005 machine on Windows 2003 Server. I've checked the queries and they're all valid in my environment.

    Does anyone have any ideas on what I am doing wrong? I would appreciate as much description as possible, I'm new to this space. ; >
    Tuesday, April 17, 2007 8:17 PM

Answers

  • Hi JonahSimpson,

    To help diagnose the problem, Could you tell me if the error occur without the webservice call?

    Also pls get MSDTC trace info(you can find how to enable trace for MSDTC on http://support.microsoft.com/kb/899115) on both machines.

    -Hong

     

     

    Sunday, April 22, 2007 5:26 AM
  • I got this same exception.

     

    It turns out that, because I was dealing with a TON  of data, the transaction was timing out in the Microsoft Distributed Transaction Coordinator (MSDTC).

     

    Despite the fact that I declared my System.Transaction context with a MaxTimeout value, the MSDTC still threw the exception:

     

    using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required, System.TimeSpan.MaxValue))

                    {

                                      //Database stuff happens here

                                      ts.Complete();

      }

     

    This problem was caused by the fact that microsoft strongly typed datasets open and close connections to the same database for every single operation.  Therefore, the MSDTC interprets this incorrectly as a distributed transaction and it's promoted from the simple transaction coordinator to the MSDTC which takes much longer to "work its magic".

     

    The solution turned out to be editing the transaction timeout value in the machine.config file.

     

    This is achieved by going to your .Net 2.0 folder and editing the machine.config file and adding the following to the end of the configuration section.  That is, right before the </configuration> closing tag.

     

    <system.transactions>

            <machineSettings maxTimeout="02:00:00" />

        </system.transactions>

     

    This gives a max timeout of 2 hours.  It may be necessary to increase this depending on how long it takes to process your data.
    Tuesday, August 28, 2007 6:38 PM

All replies

  • Nobody? : <
    Friday, April 20, 2007 3:06 PM
  • Hi JonahSimpson,

    To help diagnose the problem, Could you tell me if the error occur without the webservice call?

    Also pls get MSDTC trace info(you can find how to enable trace for MSDTC on http://support.microsoft.com/kb/899115) on both machines.

    -Hong

     

     

    Sunday, April 22, 2007 5:26 AM
  • I got this same exception.

     

    It turns out that, because I was dealing with a TON  of data, the transaction was timing out in the Microsoft Distributed Transaction Coordinator (MSDTC).

     

    Despite the fact that I declared my System.Transaction context with a MaxTimeout value, the MSDTC still threw the exception:

     

    using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required, System.TimeSpan.MaxValue))

                    {

                                      //Database stuff happens here

                                      ts.Complete();

      }

     

    This problem was caused by the fact that microsoft strongly typed datasets open and close connections to the same database for every single operation.  Therefore, the MSDTC interprets this incorrectly as a distributed transaction and it's promoted from the simple transaction coordinator to the MSDTC which takes much longer to "work its magic".

     

    The solution turned out to be editing the transaction timeout value in the machine.config file.

     

    This is achieved by going to your .Net 2.0 folder and editing the machine.config file and adding the following to the end of the configuration section.  That is, right before the </configuration> closing tag.

     

    <system.transactions>

            <machineSettings maxTimeout="02:00:00" />

        </system.transactions>

     

    This gives a max timeout of 2 hours.  It may be necessary to increase this depending on how long it takes to process your data.
    Tuesday, August 28, 2007 6:38 PM
  • Hi,

    I have the same error when the inner transaction scope was not "Complete()ed" (or rollback) and then some other operation on db was made. During transaction promotion "System.Transactions.TransactionException: The operation is not valid for the state of the transaction" was thrown bacause state of outer transaction is propably marked as something like "Destined to die" (because of not Complete()ed inner transaction), and it is not OK to call other operation bacause any change you will made will have no efect on DB after end of the outer transaction.

     

    Code Block

     

    // begin outer transaction scope

    System.Transactions.TransactionScope testTransaction;

    testTransaction = new TransactionScope();

     

    // first inner transaction scope

    using (TransactionScope ts = new TransactionScope())

    {

    // ...

    // Do something on DB (body of your DeleteReport(reportID);)

    // ...

     

    //ts.Complete(); //Complete() not called because of error, timeout,

                     //or just forget to call Complete()

    }

     

    // second inner transaction scope

    using (TransactionScope ts = new TransactionScope())

    {

    // ...

    // Doing something on DB (check if report was deleted in your case)

    // will cause System.Transactions.TransactionException: The

    // operation is not valid for the state of the transaction

    // because outer transaction must be rolled back.

    // ...

     

    ts.Complete();

    }

     

     

    //ts.Complete(); // no complete to rollback all changes (NUnit changes)

    testTransaction.Dispose(); // rollback all changes (NUnit changes)

     

    Avoid DB operation in outer TransactionScope after first inner transaction fails - throw exception (or just not catch) from inner transaction.

     

    Let me know if this help or not. Hope this help,

    Daniel Wojciechowski

     

    • Proposed as answer by g4j Friday, September 10, 2010 4:42 PM
    Friday, October 19, 2007 10:06 AM
  • Hi Smoothex,

    Your solution to change machineSettings sounds reasonable, but because of shared hosting I cannot modify anything in machine.config. Is there anything else I can do to fix it?

    Thanks!
    Wednesday, September 17, 2008 3:04 PM
  • Since modifying the machine.config was unacceptable in my situation also, I fixed the problem by not using system.transactions or strongly typed datasets.

    Instead I converted the project to use NHibernate, the C# .NET version of the object-relational mapper for Java.  (www.hibernate.org).

    It's free, open source, and both mature and very reliable.  It uses its own transaction coordinator and bypasses the MSDTC.

    I don't know how feasible this solution is for you, but it worked perfectly for me.


    Wednesday, September 17, 2008 5:33 PM
  •  

    I'am having the same problem as you, and tried to increate the time out but still couldn't solved the problem.  By the way the program is running on Windows Server 2003 and SQL Server 2005.

     

    Any suggest?

    Saturday, September 20, 2008 11:27 AM
  • Iam using windows server 2003 SP2 and SQL Server 2005 and having this error "The operation is not valid for the state of the transaction.". Cant figure out what is causing this error. There is no time out issue as error comes after 2/3 seconds and timeout in machine.config is modified to 2 hrs. The code uses data readers (c#) to connect to multiple database within a single transaction. All readers are used in using block. The error pops up nearly randomly and the issue seems to be other than of the code. I want to know if there is some kind of hotfix/service pack provided by microsoft to resolve this problem. Also pasted is the stack trace of the exception so that anybody can figure out the problem.

     

    Solutions?

     

    Stack Trace:

     

    System.Transactions.TransactionException: The operation is not valid for the state of the transaction.
       at System.Transactions.TransactionState.EnlistPromotableSinglePhase(InternalTransaction tx, IPromotableSinglePhaseNotification promotableSinglePhaseNotification, Transaction atomicTransaction)
       at System.Transactions.Transaction.EnlistPromotableSinglePhase(IPromotableSinglePhaseNotification promotableSinglePhaseNotification)
       at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
       at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
       at System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction)
       at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)
       at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.SqlClient.SqlConnection.Open()
       at Microsoft.Practices.EnterpriseLibrary.Data.Database.GetNewOpenConnection()
       at Microsoft.Practices.EnterpriseLibrary.Data.TransactionScopeConnections.GetConnection(Database db)
       at Microsoft.Practices.EnterpriseLibrary.Data.Database.GetOpenConnection(Boolean disposeInnerConnection)
       at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(DbCommand command)
       at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(CommandType commandType, String commandText)
       at Itim.P2P.PatchInstaller.CustomPatches.CustomPatch126.GetApproverIds(String customerConnectionString, String& ocList, String& poList, String& userList) in E:\Testing\SomeApplication - CatalogImportTest\DBUpdater\DBUpdater\CustomPatches\CustomPatch126.cs:line 486
       at Itim.P2P.PatchInstaller.CustomPatches.CustomPatch126.UpdateApproverXml() in E:\Testing\SomeApplication - CatalogImportTest\DBUpdater\DBUpdater\CustomPatches\CustomPatch126.cs:line 225
       at Itim.P2P.PatchInstaller.CustomPatches.CustomPatch126.Install() in E:\Testing\SomeApplication - CatalogImportTest\DBUpdater\DBUpdater\CustomPatches\CustomPatch126.cs:line 108
       at Itim.P2P.PatchInstaller.PatchInstaller.InstallCustomPatches(String id) in E:\Testing\SomeApplication - CatalogImportTest\DBUpdater\DBUpdater\PatchInstaller.cs:line 316
       at Itim.P2P.PatchInstaller.PatchInstaller.StartInstallation() in E:\Testing\SomeApplication - CatalogImportTest\DBUpdater\DBUpdater\PatchInstaller.cs:line 198
       at DBUpdater.Program.Main(String[] args) in E:\Testing\SomeApplication - CatalogImportTest\DBUpdater\DBUpdater\Program.cs:line 107
    Wednesday, October 8, 2008 7:27 AM
  • I noticed u used Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader. The code will close connection when it encounters exception. Refer to Who closed the connection? (WF?Enterprise Lib 3.1)
    Thursday, August 20, 2009 11:47 PM
  • This is caused due to the Distributed transaction services. Start the service and get going.
    Monday, August 9, 2010 10:39 AM
  • Yeah this was definatly the problem that I had in my code. Thanks alot.

     

    God Bless!

    Friday, September 10, 2010 4:42 PM
  • Why would you specify System.TimeSpan.MaxValue and force yourself to accordingly change the machine.config file machineSettings maxTimeout attribute?

    Would the following not make more sense?

     

    using (TransactionScope ts = new TransactionScope (TransactionScopeOption . Required, new TimeSpan(2,0,0) ))

    Tuesday, January 11, 2011 10:04 PM
  • Thx Daniel !!

    It helped in my case, I didn`t put Complete in correct place. So sometimes it wasn`t executed.

    The message is very confusing.

    Konstantin.

    Wednesday, January 12, 2011 4:45 PM
  • Thx Daniel !!

    It helped in my case, I didn`t put Complete in correct place. So sometimes it wasn`t executed.

    The message is very confusing.

    Konstantin.

    I'm glad to hear that my message posted in 2007 helped somebody after 3 years.

    Daniel

    Friday, January 28, 2011 11:17 AM
  • Why would you specify System.TimeSpan.MaxValue and force yourself to accordingly change the machine.config file machineSettings maxTimeout attribute?

    Would the following not make more sense?

     

    using (TransactionScope ts = new TransactionScope (TransactionScopeOption . Required, new TimeSpan(2,0,0) ))


    This is exactly what I did to fix the problem.


    .Net Developer
    Tuesday, February 15, 2011 4:39 PM
  • try open and close connection explicit

    using (TransactionScope ts = new TransactionScope())

    {

    using (SqlConnection conn = new SqlConnection(connString))

    {

    conn.Open();

    ...

    conn.Close();

    }

    }

    Tuesday, June 7, 2011 8:21 AM
  • Hi, i am getting the same problem but the same is working in windows application but the same is not working with web service. could you please help me here to solve this issue
    Monday, October 3, 2011 6:57 PM
  •  c:\windows\system32\drivers\ect\host

     client Ip  client name

    Wednesday, July 11, 2012 8:45 AM
  • EXELENT !
    Thursday, July 12, 2012 10:17 PM