Addressing deadlock related problems…

Deadlock occurs when two user processes have locks on separate objects and each process is trying to acquire a lock on the object that the other process has. This cause both process to wait for ever. SQL Server identifies this situation and ends the deadlock by automatically choosing one process to continue and rolling back the other process. Rolled back transaction is known as “Deadlock Victim”. (Typically the transaction which require lease amount of resource and time to rollback is deadlock victim.)

If your server experience deadlock, typically users experience below issues:

1. Wastage of precious server resource which in-turn cause performance problem on all services hosted on the server.
2. Frequent experience of deadlock is inconvenience for application users. (Even if they do not manually have to resubmit the transaction, still they experience delay)
3. Job failure may happen due to deadlock which can have cascading negative impact on the environment.

So if you experience deadlock in your server, follow below high-level steps to resolve this problem:

1. Figure out which applications (SP/ad-hoc queries etc) are involved in deadlock. If needed, enable appropriate trace flag to print details of deadlock in SQL Server error message temporarily.

2. After the offending processes (applications/sp ets) are identified, engage developers or/and application support to ensure that those process does not create deadlock again. (Most often simply rescheduling one of the offending process to run at another time will resolve this issue). If you are a database administrator or analyst, most likely you can not do anything at this stage because it is a problem from “Poorly written application” and can not be resolved from SQL Server. Rather it needs either code change or change in application’s configuration.

Also ideally none of the application should create any deadlock. To achieve this goal, you should incorporate/engage developers to incorporate below rules while designing an application:

• Code the applications to handle deadlocks automatically. (That is if a transaction becomes deadlock victim, application will resubmit the transaction automatically)
• Ensure the database design is properly normalized to minimize the possibility of deadlock.
• Have the application access server objects in the same order each time.
• During transactions, don't allow any user input. Collect it before the transaction begins.
• Avoid cursors.
• Keep transactions as short as possible.
• Reduce lock time. Try to develop your application so that it grabs locks at the latest possible time, and then releases them at the very earliest time.
• If appropriate, reduce lock escalation by using the ROWLOCK or PAGLOCK.
• If appropriate, consider using the NOLOCK hint to prevent locking if the data being locked is not modified often.
• If appropriate, use as low of isolation level as possible for the user connection running the transaction.