Why am I getting this error suddenly?

Question

So I have a WCF service, inside which there's a Process() method. This method reads a byte array (a file) from one table, and basically puts that data from that file into multiple tables. It just iterates through each row. It was working fine since a month in the Production environment. Now all of a sudden, it throws this error intermittently:

System.InvalidOperationException: The transaction associated with the current connection has completed but has not been disposed. The transaction must be disposed before the connection can be used to execute SQL statements.

Something which might help: About 2 weeks ago, we changed out Production web & DB servers. This error has been throwing up only after we moved. I have never encountered this problem when we were on the Old Servers. But the thing is, this error didn't occur in the first 9-10 days. Now it's happening suddenly and intermittently. I've uploaded large files (1k-2.5k rows) and they have worked fine, and this error throws up for much smaller files which have 200 rows! And the Service processes the same file perfectly sometimes.

Code snippet: (it's much larger, but similar operations are repeated)

using (var scope = new TransactionScope())
{
    // loop through each row/invoice
    foreach (var row in Rows)
    {
        Invoice invoice = (Invoice)CreateObjectWithConstantData(typeof(Invoice), doc, applicationName);
        invoice = (Invoice)FillObjectWithUserData(invoice, row, -1, -1, string.Empty);
        invoice.InvoiceNumber = InvoiceDBImpl.SaveInvoice(invoice, processFileRequest.RunId);

        if (invoice.InvoiceNumber == Guid.Empty)
        {
            throw new DataAccessException(string.Format(Messages.ErrorSavingInvoice, invoice.ReceiptId, invoice.ProductID));
        }
    }
}

One of the Stack Traces:

   at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult)
   at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries)
   at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
   at System.Data.Linq.DataContext.ExecuteMethodCall(Object instance, MethodInfo methodInfo, Object[] parameters)
   at Tavisca.TramsFileService.DataAccess.TramsDBDataContext.SaveTramsPayment(Nullable`1 paymentDate, String paymentType, Nullable`1 totalAmount, String bankAccount, String paymentMethod, String branch, String remarks, String creditCardLast4, String payeeName, String profileNumber, Nullable`1& paymentId)
   at Tavisca.TramsFileService.DataAccess.PaymentDBImpl.<>c__DisplayClass1.<SavePayment>b__0(TramsDBDataContext dc)
   at Tavisca.TramsFileService.DataAccess.SystemDataContext.PerformOperation(Action`1 action)
   at Tavisca.TramsFileService.DataAccess.PaymentDBImpl.SavePayment(Payment payment)
   at Tavisca.TramsFileService.Core.TramsFileController.ProcessFile(ProcessFileRQ processFileRequest)
   at Tavisca.TramsFileService.ServiceImplementation.TramsFileServiceImpl.ProcessFile(ProcessFileRQ processFileRequest)

I've been through some links:

1. Link 1
2. Link 2
3. Link 3

They all suggest increasing the TimeOut on the machine.config, but I'm not sure why it works sometimes and does not work the other times. Shouldn't this be consistent?

Solution 1

First of all I will suggest adding scope.Complete(); at end of TransactionScope like :

using (var scope = new TransactionScope())
{
     //Your stuff goes here

     scope.Complete();
}

Any transaction must be committed at last line using .Complete() function.

Secondly if increasing the TimeOut on the machine.config works, there is no harm in doing it, since long file obviously needs more time.

Thirdly make sure any other component called inside TransactionScope are working for all positive & negative scenarios. By stacktrace, it seems in particular use case some is breaking inside function Tavisca.TramsFileService.ServiceImplementation.TramsFileServiceImpl.ProcessFile(ProcessFileRQ processFileRequest)

Also make sure if some stored procedure is used by any underlying call inside TransactionScope then any failed transaction inside stored procedure can also result TransactionScope.

One more thing, exception thrown could be legitimate one also since you are throwing exception manually when invoice.InvoiceNumber == Guid.Empty but its not mentioned if it is handled/ catched or just passed to upper layers.

But first of all try adding scope.Complete();, this alone could be root cause.

Solution 2

Check out this connect article showing an issue in .NET ADO library.

http://connect.microsoft.com/VisualStudio/feedback/details/266095/transactionscope-timeout-issue-with-sqlconnection-and-ltm-some-operations-are-not-rolled-back

It is related to timeouts which has to be set at the client side (not SQL Server).

The first transaction times out but the second one throws the error message.

Are you using .NET 4.0 framework?

Here is an article on how to set the time out in the C# code.

http://paulklinker.blogspot.com/2011/08/transaction-timeouts-in-c.html

Solution 3

1. Use scope.Complete(); in a finally block so that even if a timeout exception occurs because of the db server taking time to respond you would have completed the transaction
2. Also, increase timeout, but that might help only if the db server is taking too long to respond for some reason but might not help if the db server becomes non responsive intermittently.

Since, the exception that is occurring is a timeout exception I would suggest and it occurs intermittently without any specific reproducible steps, we have reasons to believe that this is due to db server intermittently not responding properly or within the timeout specified (There might be memory issues or abrupt momentous downtime. we can't say that for sure since the server is newly setup. Although there could be a reason of a specific kind of character not handled in the data while loading and moving but that is less probable since the application was up and running for long before changing the servers)