Help with this "timeout expired" problem

[HongKongCV]

Okay, I'm having a very strange problem, and am looking for thoughts. The basic problem is that, when running a mass-import program, I am geting the following error message:

"The timeout period elapsed prior to completion of the operation or the server is not responding."

However, I do not believe this is a timeout problem.

The program is written in .NET 2010, runs under Windows 7, and accesses a Sql Server Express 2010 database on a remote server.

The import application is multi-threaded (10 threads), each processing 200 "policies" worth of data at a time. Each thread loads the data into a dataset, then saves the dataset to the database one table at a time. I also keep rather detailed log files for each thread, so I know what's going on.

There are about 70k "policies" worth of data. Processed in chunks of 200, that means the application saves to the database 350 different times... and only gets the timeout errors 6 times. The first timeout error occurs after 90 minutes of processing, and by that time the application has already saved almost 54,000 policies worth of data. Two threads show one timeout error, and two threads show two timeout errors... never in back-to-back batches. The rest work just fine.

I looked at the failures, and each and every one times out in less than a minute. On the other hand, I looked at two examples in the log file that SUCCESSFULLY saved data... but took an hour and 24 minutes to save.

If this were really a timeout issue, then why does the timeout fire in under a minute for those 6 occurrences, but saves taking over an hour work fine?

I'm looking for any and all opinions and thoughts, here.

[HongKongCV]

The timeouts are still occurring, but at least I'm getting the process to finish by trying again. When the timeout expires, I essentially "try again" up to a total of 10 attempts to save. So far, none have needed more then two or three re-tries to save the data to the database.

Yes, I know this is a kludge, but so far I'm getting the data saved correctly, and this at least buys me time to try and determine the underlying cause of the error. Note that this also reinforces my thoughts that something besides an actual timeout is triggering the error... the data saves eventually.

And, for the record, I am still interested in finding out what is causing these errors. Any suggestions?

[jmcilhinney]

Specific information about the code and the exception might help.

[FunkyDexter]

As JM said, extra information would probably help but I'll hazard a guess.

I believe they ARE timeouts. The connection isn't going to report a timeout to you unless it hasn't heard back from the server and not hearing back is the definition of a timeout.

Given the amount of threads you've got trying to update the same table in parallel, what's probably happening is that one thread ends up blocking another. Worse, your locks are likely being escalated to page and/or table locks, making blocking more likely. The reason the behaviour appears random is because it basically is; any thread has the potential to block another at any time.

I suspect Lock escalation is your most likely culprit so try putting Rowlock hints on the updates. N.B. This WILL slow the updates down but will reduce the chance of blocking hugely.

Failing that the only thing I could think of would be if you've written your own code to handle the network comms rather than using ADO.Net. If so the problem could be a bug in there soewhere. If you're using ADO.Net I think you can say with pretty good confidence that these are genuine timeouts in the database. The other possible culprit would be some sort of temporary network failure but then I'd expect to see all your threads bomb at the same time.


By the way, is there a good reason you've implemented such a complex solution? It sounds like overkill to me and there might be easier and better ways of achieving your goal.