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

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from dbrnd.com

Leave a Reply

Be the First to Comment!

Notify of