SQL Deadlocks: Why They Happen & How To Avoid Them

In SQL Server, a deadlock happens when two processes are waiting on the other to issue a locked resource.

This performance issue occurs when one or more processes do not receive the required resource to complete their execution. Deadlocks, therefore, occur when neither session can complete its action because each is dependent on the other — yet they simultaneously interfere with one another.

4 conditions must be true for a SQL deadlock to occur:

  • Mutual exclusion - resources are unshareable and only usable through one process.
  • Hold and wait - processes that have garnered resources can reapply for new resources again.
  • No preemption - allocated resources cannot be forcibly deprived of the corresponding process.
  • Circular wait (loop wait condition) - several processes in the system form a loop in which each process is waiting for resources occupied by adjacent processes.

SQL SERVER Deadlock scenario
SQL SERVER Deadlock scenario

A SQL deadlock ultimately results in a stop execution. Stop executions do not only occur from a lack of (or locked) resources, they also can happen due to other sources like memory and multiple active result sets. This applies to all versions of SQL Server including SQL Server 2022 and SQL Server 2019.

The most common SQL deadlocks are cycle deadlocks and conversion deadlocks. Cycle deadlocks occur when processes acquire locks on different resources when they request resources that both of the processes have. In conversion deadlocks, both processes have a shared lock on the resource; and each process then wants to convert the shared lock into an exclusive lock within a transaction.

Avoid & Monitor Deadlocks

To avoid SQL deadlocks, it is important to keep your transactions short and quick.

In addition, data design must be normalized, and application access server objects must be in the exact same order for every instance. Furthermore, you must not allow user input during transactions (all input should be collected PRIOR to the transaction). Other ways to avoid deadlocks include avoiding cursors, reducing lock times, and using bound connections. Learn more about minimizing SQL deadlocks here.

SQL deadlock monitors are now standardized in the SQL Server database engine.

These monitors check for deadlock scenarios then select which process to terminate. Next, the eliminated process is stopped with an “error” (a 1205 error message to the user) and must be run again. In general, deadlock monitor threads run every 5 seconds. If a deadlock is detected, this interval can drop down to 100 milliseconds. When no deadlocks are found, the 5-second default interval is reinstated. Read more about monitoring SQL database deadlocks.

Trusted Tech Team is an accredited Microsoft CSP Direct Bill Partner, carrying multiple Solutions Partner designations and the now-legacy Microsoft Gold Partner competency. Based in Irvine, California, we report trends affecting IT pros everywhere.

If your organization uses Microsoft 365 or Azure, you may be eligible to receive a complimentary savings report from a Trusted Tech Team Licensing Engineer. Click here to schedule a consultation with our team now to learn how much you can save today.

Subscribe to the Trusted Tech Team Blog

Get the latest posts delivered right to your inbox

Trusted Tech Team

Trusted Tech Team

Your source for all things tech

Read More