Demos - TechEd DAT305 - Best Practices for Exception Handling and Defensive Programming in Microsoft SQL Server

re: Demos - TechEd DAT305 - Best Practices for Exception Handling and Defensive Programming in Microsoft SQL Server

Vadivel — Thu, 14 May 2009 07:24:46 GMT

Adam,

Is there few ways by which we can avoid deadlocking on your SQL Server. Like, ensuring that our DB design is properly normalized, application access server objects in the same order each time etc., etc.,

That being the case do you really you need to promote a code block like /*Put deadlock-prone code here*/ ?

Regards

Vadivel

re: Demos - TechEd DAT305 - Best Practices for Exception Handling and Defensive Programming in Microsoft SQL Server

Vadivel — Thu, 14 May 2009 07:38:29 GMT

pls ignore my typos .... I guess its too late and i need to hit the bed right away :)

re: Demos - TechEd DAT305 - Best Practices for Exception Handling and Defensive Programming in Microsoft SQL Server

Adam Machanic — Mon, 18 May 2009 16:00:23 GMT

Hi Vadivel,

Avoiding deadlocks altogether is probably not possible, but it depends on the nature of your system. For example, data warehouses that do only nightly loads are unlikely to suffer from deadlocking issues, whereas active OLTP systems can be extremely deadlock prone. In SQL Server 2005/2008, the SNAPSHOT isolation levels can help a lot with many deadlock cases. You might also want to look at some of Alex Kuznetsov's posts on the topic.

I'm not sure what you mean about "promoting" the code block. Were you in the session at TechEd? That particular sample shows how to deal with deadlocks as a temporary stopgap while debugging. Some deadlocks can take quite a while to get to the bottom of and it's better to keep the system running if possible. That example is not intended to be a permanent fix or something that you would use without very good reason.