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.
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.
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: A Microsoft Gold Partner
Subscribe to the Trusted Tech Team Blog
Get the latest posts delivered right to your inbox