What is SQL Server "deadlock" and how to resolve it?

Question

Khushi Singh · Answer

A “deadlock” in SQL Server occurs when two or more processes are locked and inability to proceed further as they are waiting for some other process for some resources. It’s rather the situation when two cars cannot proceed because each of them is expecting the other to start moving first. Example of a Deadlock: Process A locks Table 1 and needs access to Table 2.Process B locks Table 2 and needs access to Table 1.Both processes are stuck because they’re waiting for each other to release the locks. How to Resolve Deadlocks: Use proper indexing: Good indexes assist queries to do more work in less time hence minimizing the possibility of getting deadlocked.Access tables in the same order: All the processes should use resources in the same manner to avoid conflict of resource access.Avoid long transactions: Avoid using many transactions because this takes a long time and locks up resources.Use TRY...CATCH blocks: To tackle deadlocks one must try the transaction again if it has failed.Enable Deadlock Monitoring: SQL Server provides tools to solve such a problem, for example, the Deadlock Graph in Profiler or Extended Events. Queries and locks can cause deadlocks to happen but if they are properly optimized, they can be avoided.

forum

What is SQL Server "deadlock" and how to resolve it?

Can you answer this question?

1 Answers