sanokistoka (10/22/2009)
The claim "that a deadlock in and of itself is not necessarily a problem" and suggestion that "A properly designed and coded application ... can intercept the 1205 error and resubmit the deadlock victim request back to SQL Server" is forgetting a few basic things. Imagine, for example, if all client applications to any server they talk to had to retry their requests all the time - like, say, your web browser to any web server? What was rather meant, I hope, is that a properly designed database would never get you into unexpected deadlocks.
Nope, I said what I meant, and I meant what I said there.
A properly designed application would have handling in place to deal with a deadlock occurence. Even in the worst instance of deadlocking I have dealt with while consulting, and deadlocks were occuring roughly every 3-5 seconds, this was a very small portion of the overall workload that was actually being submitted. It's not that every request has to be retried, or that they have to retry all the time. The point being made is that the database transaction problem of a deadlock shouldn't break the application on the front end. That is bad application side design coupled with potentially problemattic database design as well.
These days not all deadlocks are preventable. Communication Buffer deadlocks can occur infrequently that you won't fix because the lock is not on a resource you can control or change. Intraquery parallelism deadlocks are also becoming more commonplace due to the reduction of cost for multi-core multi-processor hardware. Those aren't database design problems, they are configuration problems. A properly designed database on a improperly configured SQL Server can most definately deadlock unexpectedly. Which is why the application should be coded to handle that.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]