Getting TransactionInDoubtException when used single Transaction scope

Question

I am using Transaction Scope in below manner,

var option1 = new TransactionOptions();
        option1.IsolationLevel = IsolationLevel.ReadCommitted;
        option1.Timeout = TimeSpan.FromSeconds(Convert.ToInt32(ConfigurationManager.AppSettings["Tide:TransactionTimeout"]))
using (var transactionScope = new TransactionScope(TransactionScopeOption.RequiresNew, option1))
                {
                        //delete data from database
                        //multiple BulkInsertData in different tables of database
                        //update data
                        transactionScope.Complete();
                }

There is no inner transaction started. I got TransactionInDoubtException, I can't understand why it has occurred. Previously code run smoothly, but suddenly from yesterday, this exception throws

System.Transactions.TransactionInDoubtException: The transaction is in doubt. ---> System.Data.SqlClient.SqlException: The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest) at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest) at System.Data.SqlClient.SqlDelegatedTransaction.SinglePhaseCommit(SinglePhaseEnlistment enlistment) --- End of inner exception stack trace

Although it's probably not the cause of your problem (at least I don't think so), your try .. catch block is wrong. The scope will already be disposed by the using, there is no need to do it again in the catch. The entire block is superfluous if you're not doing anything with exception. — Jeroen Mostert
There does not seem to be anything wrong with your transaction handling code. Can you post some more of your database call please? Do you get the error if you simply send a command like "select * from sys.objects" ? — EvalKeneval
Thank you for your reply, I have updated the database call in question. In transaction scope, we do not execute any select statements, I hope you will get what you want — S. Deshmukh

strickt01 strickt01 · Accepted Answer · 2019-12-16T14:24:03

From the exception message

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION

it seems likely that you are running some SQL that is not handling errors correctly. Do you have anything like this in the SQL that is run?

BEGIN TRAN

-- Some SQL here that generates an error

COMMIT TRAN

In this case any error within the BEGIN TRAN... clause will cause the transaction to fail. When COMMIT TRAN is then called the transaction state cannot be determined, hence the TransactionInDoubt exception. You need to make sure you call SET XACT_ABORT ON at the beginning of your procedures and handle errors correctly:

SET XACT_ABORT, NOCOUNT ON
BEGIN TRY
    BEGIN TRAN
   -- Some SQL here that generates an error
    COMMIT TRAN
END TRY
BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   DECLARE @msg nvarchar(2048) = error_message()
   RAISERROR (@msg, 16, 1)
   RETURN 55555
END CATCH

See this article on error handling in SQL Server

Getting TransactionInDoubtException when used single Transaction scope

1 Answers