Reducing Deadlocking Problems
Many different things can contribute to the cause of a deadlock in SQL Server. Below are some suggestions on how to eliminate, or at least mitigate, deadlocking problems in your applications. This list is only a starting point and should not be considered a complete list of the options you have to prevent or reduce deadlocking. You may want to share this list with your developers, along with the information you identified during your trace.
• Ensure the database design is properly normalized.
• Have the application access database objects in the same order every time.
• Keep transactions as short as possible.
• During transactions, don't allow any user input.
• Avoid cursors.
• Consider reducing lock escalation by using the ROWLOCK or PAGLOCK hint.
• Consider using the NOLOCK hint to prevent locking.
• Use as low a level of isolation as possible for user connections.