SQL Server: What happens after the Deadlock arise?

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 that 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 to proceed.

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:

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].

Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of