How to remove locking deadlock in MSSQL Server

Let's first understand the concept of deadlock. When two or more transactions are trying to access resources that have already been locked by one of them, a deadlock situation occurs. Due to the ongoing waiting between the two transactions, database resources are significantly impacted in this situation. The SQL Server's assistance ends this congestion problem. It selects a victim from among the transactions participating in the deadlock and forces it to undo all of its previous activities.

In this real-world example, users of an internal application receive an error response and inform the development team of it.

Although the development team is aware of the deadlock issue, they were unable to identify the root cause. Given these facts, the group decides to hire an experienced database administrator as consulting assistance. We shall discover how database administrators approach and handle this deadlock issue in the following sections.

There are few thing that you need to keep in mind.

·      Use with no lock with tableName

SELECT * FROM School.Student WITH (NOLOCK) WHERE StudentID < 20

·      Check where clause whether there is any condition which scans the entire table as it increase the execution time. 

Isnull, Convert, other function, Please try to find alternatives 

·      Check Index on table, Create Index if it is required.

·      Check fragmentation or Rebuild Indexes.

·      Update the statics.

·      Re-write the optimize query

  

I hope you will Implement things and get the best results.