Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Exception "The operation is not valid for the state of the transaction" using TransactionScope

We have a web service on server #1 and a database on server #2. Web service uses transaction scope to produce distributed transaction. Everything is correct.

And we have another database on server #3. We had some problems with this server and we reinstalled operation system and software. We configured MSDTC and tried to use web service from server #1 to communicate with database on this server. And now after first select statement within transaction scope we get: The operation is not valid for the state of the transaction. This exception falls in every web service request if it is using transaction scope. Server #2 and Server #3 is almost similar. The difference can be only in settings. .NET framework 3.5 SP1 installed and SQL Server SP3 on all servers.

Full stacktrace:

System.Transactions.TransactionState.EnlistPromotableSinglePhase(InternalTransaction tx, IPromotableSinglePhaseNotification promotableSinglePhaseNotification, Transaction atomicTransaction) в System.Transactions.Transaction.EnlistPromotableSinglePhase(IPromotableSinglePhaseNotification promotableSinglePhaseNotification) в System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction t в System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction t в System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction) в System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction) в System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) в System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) в System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) в System.Data.SqlClient.SqlConnection.Open() в NHibernate.Connection.DriverConnectionProvider.GetConnection() в NHibernate.Impl.SessionFactoryImpl.OpenConnection()

I searched this message but didn't found any appropriate solution. So what settings should I check and what exactly should I do to fix it?

like image 539
Mykhail Galushko Avatar asked Apr 12 '10 15:04

Mykhail Galushko


2 Answers

Lanfear, I have come across the same error message and I found a solution. Your situation may be different, but I hope that the following knowledge will be useful to you.

System.Transactions.Transaction.Current.TransactionInformation.Status will return the status of the current transaction.

In each case where an exception is thrown with the message The operation is not valid for the state of the transaction, when I step thru with the debugger, I see that the status is "Aborted" before the exception is thrown.

In my case, the problem was caused by nesting two transactions inside each other and mistakenly aborting both when I wanted to abort only one. Apparently, if you use the TransactionScope default constructor New TransactionScope() with two nested transactions, aborting the inner transaction aborts the outer transaction as well. The solution is to use the Constructor New TransactionScope(TransactionScopeOption.RequiresNew) Using this constructor, the inner transaction will be a new transaction and aborting it will not abort the outer transaction.

This solved my issue.

like image 113
Vivian River Avatar answered Oct 26 '22 06:10

Vivian River


Enabling the Distributed Transaction Coordinator solved the issue for me (this was done on both machines: the one that has the web service and the one that has the database. Not sure though if it is needed for the web service).

I followed the steps described here to enable the DTC and to add an exception to the Windows Firewall: Enable Network DTC Access for Windows Server 2008

like image 25
SzilardD Avatar answered Oct 26 '22 07:10

SzilardD