cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 12

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

Jump to solution

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

0 Kudos
1 Solution
Level 14

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

View solution in original post

0 Kudos
7 Replies
Level 14

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

View solution in original post

0 Kudos

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

0 Kudos

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

0 Kudos

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 <

0 Kudos

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

0 Kudos

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.

0 Kudos

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

0 Kudos