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!

8 Comments

  • what do you mean by static/shared databound objects. do you mean myBizObject.GetAll(); with get all loading objects (con open, sql, con close, return objects). like a table data gateway, or something completely different.

  • This specific instance refers to creating a static database connection object in a web-app that is shared among every request that relies on said connection. For instance, in your example; if con is a shared object and more than one request try to use it, it will behave the same as my story relates. Multiple requests would be competing for the same object instance. Think multi-threading.

  • thanks, so like a badly written singleton. The inner database connection object instead of using connection pooling and releasing it back to the pool, 800 connection objects are made if 800 people are using it. Or maybe trying to pass 1 connection object to all data objects to use from a registry of sorts. trying to save on what used to be a costly process of setting up the connection. Or writing shotty classes that try to get the insert id in another method or event rather than the insert statetment. Maybe a bad attempt at transactions.

  • Хоть кто-то здравомыслящий остался

  • не согласен с админом. стукни в асю плиз 830130

  • Кто что думает как долго будет продолжать эта невыносимая жара?

  • Halloween Horror_3A00_ The SQL Timeout.. Slap-up :)

  • Halloween Horror_3A00_ The SQL Timeout.. Reposted it :)

Comments have been disabled for this content.