1

I am troubleshooting a problem that seems to be random

The problem is sometimes an application (dynamics SL) is freezing

When the application is freezing , I notice there is a blocked by in SQL activity monitor and a head blocker

in my limited understanding , head blocker means a session is currently running and is locking a resource and that resource is also needed by another session. so all sessions that need access to that resource cannot continue thus the "blocked by" that I see in activity monitor

my question is in five cases that I have seen is that the head blocker task status is actually blank. usually a task status is "running" or "suspended" now how can a session that is not running becomes the head blocker?

I am thinking my understanding of head blocker maybe wrong. but please enlightened me ELI5 style :)

each of the 5 cases. I just kill the process of the head blocker. then everyone is not freezing anymore.

I am hoping to identify the source of this head blocker and come up with a solution

When I click the details of the head blocker. all I see is

select @@identity

please advise?

thank you

2
  • Could you go through your code and find anywhere you're using @@IDENTITY (and change it to use SCOPE_IDENTITY() instead)? Also see if this is executed inside of a transaction, since you should be able to pull this data outside of that context (and prevent any subsequent queries from continuing to hold locks on the resource that generated the identity value in the first place). Apr 4, 2014 at 18:57

1 Answer 1

4

Actually in order to be blocking a session only has to hold a lock to a resource something else wants. It does not actually have to be doing anything. For example if you run the following

BEGIN TRANSACTION
    UPDATE MyTable SET Col1 = 'ABC'

And don't run a COMMIT then you are going to leave a transaction open. The session is not doing anything and in fact it will not show up in sys.dm_exec_requests. However the locks are going to continue to be held and continue to block other sessions. You can find the information on open transactions in sys.dm_tran_session_transactions.

Next point is the command you are seeing. That is just the last batch of the session. If you open a transaction and run several batches through it you may very well be holding locks from earlier commands that are not going to show up.

I wrote about both of these things in quite a bit more detail in the following links:

Transactions: Who What and Where

Using sys.dm_exec_sql_text to figure out blocking is sometimes flawed.

Also the first link will have information on how to find who is running/ran the offending transaction. I frequently find that if the login is a shared one, or a SQL ID I can track the individual down by using the information in the host_name column of sys.dm_exec_sessions.

1
  • Thanks, Definitely will read in more details on that link. This clears up my understanding on head blocker Thank you
    – BobNoobGuy
    Apr 11, 2014 at 17:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.