forum

Home / DeveloperSection / Forums / What is SQL Server "deadlock" and how to resolve it?

Ask Question
Ashutosh Kumar Verma

Ashutosh Kumar Verma

Follow
Hi! This is Ashutosh Kumar Verma. I am a software developer at MindStick Software Pvt Ltd since 2021. I have added some new and interesting features to the MindStick website like a story section, audio section, and merge profile feature on MindStick subdomains, etc. I love coding and I have good knowledge of SQL Database.

Can you answer this question?

Answer

1 Answers

Khushi Singh

Khushi Singh

01-Jan-2025

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:

  1. Process A locks Table 1 and needs access to Table 2.
  2. Process B locks Table 2 and needs access to Table 1.
  3. 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.

 

 

 

Liked By