This article is half-done without your Comment! *** Please share your thoughts via Comment ***
When two sessions are blocked by each other and waiting for a resource which is locked, Deadlock arises in the Database System.
Internally, SQL Server detects those two sessions and makes one as a Deadlock victim.
After that, it rolls back the victim session and returns the deadlock error message to the client.
It releases all other locks held by that session and allows other sessions for the further process.
SQL Server is choosing a victim session mainly based on the two factors.
The one is a deadlock or session priority, and another is how that session rolls back fast.
You can also define a Deadlock priority using below T-SQL:
1 2 3 4 5 |
-- Syntax: SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | -- Example SET DEADLOCK_PRIORITY LOW; GO |
If the session has lowest deadlock priority, first it is chosen as a Deadlock victim.
You can also find sessionID or processID of the blocked or deadlocked transaction.
After that, you can also manually KILL that session by executing KILL [SPID].