Tips For Minimizing Deadlocks in SQL Server

Database servers are configured in such a way that they can service multiple requests from multiple users. Obviously this increases the likelihood of conflicts because multiple processes can request access to same resources at the same time. This type of conflict is also known as block or blocking. Blocking usually resolves itself after the locking process releases the resource for waiting process. Sometimes, blocking creates a more serious condition, called a deadlock or fatal embrace, which occurs when the first process is waiting for the resource that is locked by the second process and the second process, is waiting for the resource that is locked by the first process.

Deadlocks are caused by poor database design, inappropriate isolation level, inefficient code etc. Check out my article here in which I discussed different ways to minimise deadlocks on SQL Server.

This article is published on SQL-SERVER-PERFORMANCE.COM.

Tips to avoid deadlocks?

A database server should be able to service requests from a large number of concurrent users. When a database server is servicing requests from many clients, there is a strong possibility that conflicts will occur because different processes request access to the same resources at the same time. A conflict in which one process is waiting for another to release a resource is called a block. A blocked process usually resolves itself when the first process releases the resource.

A more serious condition, called a deadlock or fatal embrace, occurs when the first process is also waiting on a resource held by the second process (see below).  Each process is stuck waiting for the other to release a resource. Unless one process times out, the lock won’t clear itself.

Although we cannot completely avoid deadlocks but they can be minimised by following the tips below:

Continue reading