What is Deadlock

A deadlock is a situation wherein two or more competing actions are waiting for the other to finish, and thus neither ever does. In Database systems, deadlock refers to a situation in which two or more processes are waiting for the other one to release the resource and neither of them finishes and concludes with a deadlock situation. Deadlocking represents a failure of processes to work and play well together. Deadlocks are the dark side of concurrency, that is, they occur when some combination of locking, coding, or transaction management goes wrong.

We have mainly the following conditions that can result in a Deadlock scenario, they are as follows

1.       Mutual exclusion condition: a resource that cannot be used by more than one process at a time

2.       Hold and wait condition: processes already holding resources may request for new resources

3.       No preemption condition: No resource can be forcibly removed from a process holding it, resources can be released only by the explicit action of the process

4.       Circular wait condition: two or more processes form a circular chain where each process waits for a resource that the next process in the chain holds

In SQL Server, Deadlocking 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. When this happens, SQL Server identifies the problem and ends the deadlock by automatically choosing one process and aborting it, allowing the other process to continue. The aborted transaction is rolled back and an error message is sent to the user who instantiated the aborted process. Generally, the transaction that requires the least amount of overhead to rollback is the transaction that is aborted.

How to Track Deadlock

We have different methods to track down Deadlocks. Let’s have a glance at the following methods

·         In SQL Server 2000 we can set Trace Flag 1204 to capture the processes involved in deadlocks.

·         In SQL Server 2005 we have SQL Server Profiler. Using this approach we will get detailed information on deadlocks.

Best practices to get rid of Deadlocks are as follows

• Implement a well normalized Database design
• 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.
• Try to avoid the usage of cursors whenever possible.
• Keep transactions as short as possible. Reduce the number of round trips between your application and SQL Server by using stored procedures or keeping transactions with a single batch.
•  Try to implement database level caching
•  Reduce lock time. Try to develop your application so that it grabs locks at the latest possible time, and then releases them at the earliest.
•  If appropriate, reduce lock escalation by using the ROWLOCK or PAGLOCK.
•  Consider using the NOLOCK hint to prevent locking if the data being locked is not modified often.
•  If appropriate, use the lowest level of isolation as possible for the user connection running the transaction. 
• Consider using bound connections.

 By DB Team,Software Associates