Quick quiz: what causes deadlocks? No, no, no. Keep your answers simple. Here, choose from these two options:
1. MS SQL Server, or
2. Applications designed to run on MS SQL Server
There, that should make things easy. Can we all agree that MS SQL Server does not simply produce deadlocks by itself? In other words, when a deadlock occurs, the first reaction should not be “what’s wrong with the database server?” The database engine is processing data in the way that it has been told to do so. It uses the objects that you have created. It does not, I repeat, DOES NOT just switch into “deadlock mode” and start killing spids.
And don’t start down this path of “well, the indexes just needed to be rebuilt, or the stats were out of date”. Look, if your application is designed in such a way that a deadlock can occur, then a deadlock can occur. It is that simple. The next logical question is: can I design a system that will never have a deadlock?
I say no, however I can offer no proof. It is simply my belief that the nature of any database engine is such that a deadlock can happen no matter what the design. When you factor in tables, indexes, transactions, and data you can get a recipe for deadlocks. Jonathan Kehayias recently blogged about how deadlocks can occur, and he even took the time to draw some pictures. By the way, pictures are quite valuable when trying to present this information to others, just an FYI should you be called into an important meeting to explain what is wrong with the server.
So, if deadlocks cannot be avoided altogether, what should you do?
Learn to work with them, not against them. Keeping in mind that you might not be able to avoid them altogether, learn how to properly capture the event. If you can capture the deadlock when it happens, you should be able to simply resubmit the transaction.
If that is not possible, or desired, then you will have to learn to live with them. Here is a good KB article that I recommend for anyone looking to learn more about deadlocks.
Another tip: test your designs to see if they scale. If your system works now with one hundred transactions a second, see what happens at ten thousand transactions a second. If you start to see concurrency issues such as blocks or deadlocks, dive into your design to see what can be done now to remedy the situation. Trust me, it is better to learn these things now rather than at 3AM.
