REQUEST_FOR_DEADLOCK_SEARCH

I don’t want to make the readers feel bored of reading some repetitive things. So I am not explaining what is a deadlock how it occurs instead I felt it would be good if I mention what and how  SQL Server engine does internally about Deadlock.

Let me take you through a DMV called sys.dm_os_wait_stats. If you notice the columns that it uses, you will see something like …no Exactly like REQUEST_FOR_DEADLOCK_SEARCH. This is the guy we will be centering today.

 

What is this Doing????

This is basically a sleepless thread/process that wakes up every five seconds to see if we have any deadlocks. Wow…what a nice Guy!!!  If it finds any, it terminates one of the sessions. That’s true, it KILLS one of the two (multiple) sessions to let the other session have all the resources it needs.  So we don’t have to worry, right?

Well, SQL Server does have to worry a bit, even if it’s not going to prison.  So how does it decide which transaction to be killed?

It decides this by looking at the LOG USED value. Now what is that?

 Say if you have high Transaction Log usage (more Transactions), you will have a high log used value for that session (SPID). Consider another session with low value on LOG USED. It’s obvious that the second one will be easier to rollback. Because if SQL Server kills a transactions, any work it has done must be rolled back to bring the database to consistent state.  So it chooses a low value SPID and kills.

So what happens when the value are the same? 

It uses sort of LIFO (Last In First Out) method and kill whoever came in second.

How it Works????

It wakes up every 5 seconds to check for deadlocks. If it finds any, it follows above process to decide how to resolve it.  However, second time it wakes up right away, to make sure it is not a nested deadlock.  If there is one it kills that and goes back to sleep.  Next time it wakes up @4.90 seconds (I believe it’s actually 10ms).  It will keep going down to as small as 100ms; that is, it will wake up even 10 times per second to deal with Deadlock Issues.

 

So hope this was not a boring(For someone) but instead shed some light on the internal thread.

Have a Gr8 Day!! Njoy