Microsoft believes you, have lot of faith in you. So it offers tools to resolve and rescue from your coding disaster.
Say you have a deadlock, show it. Find how you can reproduce this, unless you know how to reproduce it, you will never be sure of your fix; well, like always.
First thing to do once you see a deadlock is, extract the deadlock trace data. To do so, you can set flags in SQL by running following command
dbcc traceon (1204, 3605, 1222, -1)
This flag is set only for current session of SQL server, if you restart the SQL you need to redo it. You can also set this flag as startup parameter by adding –T1222 in SQL server properties dialog.
When deadlock occurs, you can see the DB log to contain the most of the information. I have found graph drawn by the profiler as more useful than the log.
This graph you can view in profiler and you can store as XML. This XML has all the information that can give you a lead. XDL – ie XML representation of Deadlock graph has information like
1) Procedures involved
2) Line number involved in deadlock
3) Script line involved
4) Data involved
5) Victim thread, SQL terminates one process to break the deadlock
6) Mode of lock on the resource
Most of the cases this information would be sufficient to get you out of it.
Common solutions:
1) Consider rearranging the script so that you isolate all different operations like select, delete, insert and update. Make sure you have same pattern in both the scripts who are involved in deadlock. Say delete first, select next then you do a insert in both the stored procedures.
2) Un-clustered indexes adds overhead of updating indexes which require exclusive locks and if select is also running on table, then there is a possibility of deadlock. Making the clustered index can resolve the problem.
3) Run the Query analyzer and see the execution graph. You can see the pattern of two scripts running and see the overlap. This could give you fair idea on what could be going wrong, or gives the idea on possibilities.
Now you are on your own…
