Database transaction timeout problem

Question

I have been experiencing an error that I believe is caused by the database timing out due to a large amount of data being processed and read to the database.

I keep getting this error message:

Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.

I timed how long it takes to timeout and it is constantly around 60 seconds. Hence, I thought that it might have something to do with the transaction timeout limit (default 60s) set in Component Services (Windows XP). I increased it to 300 seconds.

When that didn't work, I edited the machine.config file by adding:

<system.transactions>
  <machineSettings maxTimeout="02:00:00" />
</system.transactions>

This did not work either.

I don't believe it has anything to do with my data. It is read from an excel spreadsheet. It runs fine when I cut the spreadsheet into two separate files.

Hopefully, I'm just missing something simple like another max timeout setting somewhere.

Hopefully, somebody has run into this before!

EDIT: I am using SQL Server and Linq2SQL.

Answer 1

Andrew, I don't think this is caused by a timeout. Otherwise you would receive a specific timeout error. More than likely this is a programming error. I've encountered this myself, and almost always it was my poorly crafted code causing the issue.

I think you have another issue. It's not a good idea to have transactions running for this long. If your company has DBA's, they will likely, for good reason, throw fits over this. You're locking a lot of resources for a long period of time. Something is going to suffer for this.

BTW, if you're concerned about timeouts, check the timeout setting on your connection string.

Randy

Answer 2

To change the timeout, you can set the CommandTimeout property in your data context:

var db = new YourDataContext();
db.CommandTimeout = 300;

Having said that, any time you have a distributed transaction, it's worth taking a careful look at the reasons why, and trying to avoid them if at all possible -- your issue may well be related to that rather than a timeout....

Answer 3

I'm not sure why, but I fixed the problem by going into web.config and adding:

<system.transactions>
  <defaultSettings timeout="02:00:00"/>
</system.transactions>

I thought that this setting would be inherited from machine.config. Perhaps they are two different timeout settings? I don't know.

If anyone has additional clarification, please comment!

EDIT 1: Also, if anyone is using ASP.NET Ajax controls, be sure to increase the script manager's AsyncPostBackTimeout property to accommodate a longer period as well.

EDIT 2: I removed the lines I added to the machine.config and reset the distributed transaction timeout setting to it's default. This appeared to have no affect and my program ran fine with just the changes to the web.config file and the script manager.