Application code, that’s what causes deadlocks. Well, perhaps a combination of application code on top of database design, and access patterns, and transaction isolation levels, and…
OK, here’s the point I need to make clear: the database engine does not suddenly seize up and start deadlocking transactions arbitrarily because it is tired one day.
No, certain conditions must exist in order for a deadlock to happen, and all of those conditions require someone, somewhere, to be using the database. Here is a link to Bart Duncan’s blog that helps to explain deadlocking as well as the trace flag T1222.
Notice how Bart explains that deadlocking is not the same as blocking? I sure did, and it is a point that I think is often overlooked. I have had many a person explain dismay that their database is suffering blocking all the time. When I try to explain that a certain amount of blocking is to be expected I am usually met with “yeah, yeah, whatever…can you just update the stats and rebuild my indexes so it all goes away”. A better response would be “…yeah, I know I need to look at my design, but can you rebuild the indexes for me right now, and see if that will help for the time being?” My answer would be yes, followed by “can I help you with your design?”
You do not need large tables with indexes in order to facilitate a deadlock. Blocking and deadlocks can happen on small tables as well, it is a matter of design in most every case I have ever seen.
Let me offer you some advice. If you believe that constantly updating your stats is a way to prevent deadlocks in Microsoft SQL Server then you should find a new line of work. Actually, stay right where you are. Drop me an email. I charge reasonable rates. Thanks.
If you scrub the intertubz for deadlock information you will find a common theme. Most people will write that deadlocks cannot be avoided in a multi-user database. They will also write about the need to keep your transactions short, and to some that means having your stats and indexes up to date, rather than a good discussion over what a normalized database would look like. Not to mention the fact that many indexes are unnecessary. Look, no one likes to be told they built something horrible. And chances are when it was built it worked just fine, but as the data changes, so could the need for a reliable design. So if you are a database developer do not be offended if someone says “we need to examine the design, the data, and the code”. It is just a simple fact that things change over time.
As Bart states in his blog, there is a new trace flag in SQL 2005, T1222. If you are experiencing deadlocks and want to turn this on now, simply issue the following statement:
DBCC TRACEON (1222, -1)
The flag will be enabled and will start logging detailed deadlock information to the error log. The details from this trace flag are much easier to understand than the original Klingon returned by T1204. Unfortunately, by using the DBCC this trace flag will be lost after the next service restart. If you want the trace flag to be enabled and always running against your instance, you need to add -T1222 as a startup parameter to your instance.
If you want to confirm that a trace flag is running against your instance, simply run the following statement:
DBCC TRACESTATUS(-1)
As always, I hope this information helps you when you encounter deadlocks in your shop.
