Troubleshooting SQL Deadlocks

  • Updated

A SQL Deadlock occurs when two or more tasks permanently block each other. Each task locks a resource which the other tasks are trying to lock. The following resource types can be deadlocked:

  • Locks - Waiting to acquire locks on resources such as objects, pages, metadata, and applications.
  • Worker threads - Queued tasks waiting for an available worker thread.
  • Memory - Requests are waiting for memory grants, and no memory is available.

How a Deadlock is normally detected

By default, SQL Server Database Engine automatically detects deadlock cycles within SQL Server. It chooses a deadlock victim and terminates the resource causing the blockage.

 

 

Troubleshooting deadlocks

You can take these additional steps to retrieve data about what is occurring at the time of dead locks or performance degradation.

  1. Run a SQL trace on the database to capture transactions - Analyze Deadlocks with SQL Server Profiler - MSDN
  2. Attach and execute a plan to retrieve data on call to stored procedures, and verify the percentage of time it takes to run - Display an Actual Execution Plan - MSDN - Microsoft

Additional resources

 

Avoiding or minimizing deadlocks

Here are suggestions for avoiding a deadlock, depending on its cause.

  • Access objects in the same order at the same time.
  • Avoid user interaction in transactions.
  • Keep transactions short and in one batch.
  • Wrap the code in a try catch. If a deadlock error is caught, apply a thread.sleep for a short random time and increment a retry on the counter.
  • If using multi-threading, avoid deadlocks by using a timeout value. You can also use the Monitor class (system.Threading.Monitor) to set a timeout while a lock is being acquired.