Tips for Minimizing Deadlocks

Deadlocks are caused by poor database design, inappropriate isolation level, inefficient code etc.

Checkout my article (Tips for Minimizing Deadlocks) on SSWUG.org, in which I shared some tips that you can follow to minimize deadlocks in SQL Server online transaction processing (OLTP) and online analytical processing (OLAP) applications.

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