CODEWITHSUNDEEP
sql-serverazure-sql-databasetransactionscope
S. Deshmukh
S. Deshmukh

Reputation: 324

Getting TransactionInDoubtException when used single Transaction scope

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

Upvotes: 3

Views: 1027

Answers (1)

strickt01
strickt01

Reputation: 4048

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

Upvotes: 4

Related Questions