This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

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

I see the following in Ignite:

Untitled.png

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

Parents
  • 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

  • Rob,

    Thanks for the response. I do appreciate all the detail. This blocking session is for a Grails/GORM/Hibernate app that doesn't do any external calls (that I know of). All the SQL Text for that SPID during the 10 minutes before the blocking were all just SELECT statements. But as you said, there is no guarantee that those are the only statements that it actually ran. If I see this a lot, I'll get a web developer to help me try to hunt this down. I've heard that JDBC has been known (here, anyway) to occasionally just drop connections and such, so it may not even be an issue in the application code.

    Anyway, thanks again for the info.

    - Mark

  • No problem - good luck hunting this down.  Are the selects being issues with the nolock clause?

  • No use of nolock or non-default transaction isolation levels.

    Mark Freeman

    Database Administrator | Rogue Fitness

    1080 Steelwood Road, Columbus, OH 43212

    MFreeman@RogueFitness.com

    On Fri, Jun 20, 2014 at 5:18 PM, mandevil <

  • Ok, so for read consistency, the selects could be causing the blocking.  Feel free to reach out to support for anything else also.

  • Further investigation showed that the Grails application is making a SOAP call to a third-party in the middle of a transaction (which could take a few seconds to a minute or two to return), so you were right on the money. We're looking into how to change the application code to avoid that.

  • Mark, I love it when I'm right!  8 )

    Glad it helped you in figuring out what's going on.

    Reach out to us with anything else, you know where to find us.

    Rob

Reply Children
No Data