Halloween Horror: The SQL Timeout
I don’t know when it started or why, but I love Halloween (and autumn for that matter). So keeping the tradition of Halloween alive, I’ve decided to share some “horror” stories from the software/IT field over the next few weeks.
The SQL Timeout…
A few years ago, myself and another developer were brought in to help get a project moving along that had been at a stand still basically because it was the first ASP.NET app the existing team had tried to tackle. They sure picked a whopper to take on as their “learning” project too, but that of course ended up being bitter-sweet for me. I should add that the team was very competent and well experienced; they just lacked the .NET mojo.
Anyway, by the time we got involved; they had laid most of the foundation and were starting to build the other layers on top. Due to the existing project state and the time-constraints, we were forced to do a lot of independent testing during development. When a major milestone in the project was reached and it came time to stress/load test the application, we were expecting similar results to those found in the independent tests (famous last words). Well… not quite. We kept getting a SQL Timeout Error whenever there were multiple, simultaneous requests for databound pages, and we couldn’t test anything beyond that point. The weird thing about this was that it didn’t just happen under heavy loads; it simply happened when there was more than 1 request.
I’ll spare you the story about all the things we checked and re-checked from the hardware to the end users. In the end, I found that someone had declared a SqlConnection object as Static/Shared in the heart of the O/R mapped code. So anytime a request was processed, it was using the same SqlConnection object as the previous request (remember, this is a web-app) and would kill the previous request. The result would be a SQL Timeout Error for any of the requests before the last. Ah, memories.
The moral of this story is that you should be very weary of creating Static/Shared databound objects in ASP.NET applications, especially connection objects.
Oh yeah, boo!