Why do I see an "idle blocker" as a head blocker? How can I find the cause?

Question

I see the following in Ignite:

The top line shows SPID 91 as a head blocker with the notation "idle blocker", blocking SPID 179. How can I find what is causing the blocking?

In line 5, SPID 91 is again shown as a head blocker, this time with the notation "blocker" and the full details that I need to diagnose the situation. Is this relevant with regard to the idle blocker shown in line 1?

Thanks,

Mark

mandevil · Accepted Answer

Mark, Idle blocking occurs when you have a session that opened a transaction (establishing a lock on a resource) and then did not commit or rollback explicitly. The transaction stays open, even though no work is currently being done. Imagine you do a begin transaction and issue an update to a table. The results will come back, but you haven't closed (committed or rolled back) the transaction yet. In another session, query against the table/row used in the first session, it will just spin until the lock has been released. The second session will start to accrue wait time in the LCK wait type and the scenario will show the first session as idle blocker in the blocking tab. There can be a couple reasons for this - an abnormally terminated session that was doing the transaction initially that didn't close it out.  The other thing I've seen is application logic that opens a transaction in the database, then goes external to do a piece of work. Think like an OS command, parse a file, make a web service call, etc. Then based on the results of that external call, it will come back into the transaction and commit or rollback. If something occurs during that external call, the application may never come back into the transaction and complete it. (I'm not a fan of this kind of logic and external dependency personally). To find what that idle blocking session was doing, you will probably have to drill into Ignite to see when the blocking had started and see what that session had been running just prior to the blocking condition. There are reasons where we may not have gotten what the blocker SPID was doing at the time and sometimes it’s difficult to determine exactly when the blocking session actually ran their transaction. We poll once a second for active sessions. If the transaction was very quick and happened between polls we may have missed it. We don’t trace – that’s by design. The other consideration is that the locking could have begun at some indeterminate point in the past (from blocking session), but the blocking situation didn't occur until sometime later when the other session tries to get a lock on the same resource. Sorry for the novel, but wanted to help you understand what it means and potentially how to find more metadata about the blocker. Rob