2

We have an UPDATE statement running in a large J2EE application that is getting blocked by some other statement. The update is using the primary key, so it should affect only one row. The isolation level being used in the application should be READ UNCOMMITTED, so it should only be another modification that is causing the block, but we can't see where.

We have obtained a report of All Blocking Transactions and this indeed shows a session blocking an update. Using the DBCC INPUTBUFFER command shows the last statement executed by the blocking session, but this is an SELECT on another table than the UPDATE, so I think it can't be the cause. I presume it must be a statement earlier in the transaction.

At this point, my SQL Server knowledge runs dry, and I also have problems reproducing the issue; it occurs intermittently.

What steps should I know take to find the blocking statement?

  • If you are interested in tracking blocking issues see Tony Rogerson's post at: sqlblogcasts.com/blogs/tonyrogerson/archive/2007/04/06/… – RLF Jan 5 '15 at 16:10
  • @RLF: I think the event notification is not useful as I already have detected the event (the application server complains about stuck threads), but this comment in the linked article is interesting: "Because you have the xactid you can track that down through SQL Profiler if you’d have been tracing SQLTransaction as well (another blog post on that I guess – the list get’s longer...)." I think that is what I want ... – rghome Jan 6 '15 at 8:29
  • No problem. I use this because the XML also (usually) shows where the block was in the blocked code and what the 'other' transaction was running. – RLF Jan 6 '15 at 13:22
7

Before you run your update:

SELECT @@SPID;

Now, run the update, if it is not completing quick enough, then in another window run:

SELECT status, command, wait_type, last_wait_type, blocking_session_id
  FROM sys.dm_exec_requests
  WHERE session_id = <@@SPID from above>;

If you get a value in blocking_session_id, then you can run the same query for that SPID. You can also say:

DBCC INPUTBUFFER(<blocking spid>);

(Well, as long as you're not on SQL Azure.)

If this doesn't yield a clue, then it's possible the blocking statement was actually at a different point in a larger transaction. You'll have to track that down by figuring out where that spid is executing from and whether it's a batch of ad hoc T-SQL, a stored procedure, etc. There's not really any magic "look here!" we can provide you.

If it's not getting blocked, you may find relevant info in the other columns that can help determine why it's slow (e.g. perhaps it is waiting on a ginormous log autogrow event).

  • Aaron - thanks for the feedback. I should add that I can't modify the application easily as it is on a production system. As I mentioned, I already ran DBCC INPUTPUTBUFFER, but it did not return the relevant operation, so my guess is that the blocking statement is (as you say) "at a different point in a larger transaction". I admit, I was looking for a magic command to tell me where that was. – rghome Jan 6 '15 at 8:11
  • In my case, the slow statement was being blocked by another one that was also suspended, so I had to repeat the process for that one too and so on. At the end I found the culprit. In summary, your statement may be blocked by another one that is also waiting. Keep going until you find the one is running. – Marques Aug 8 '18 at 16:00
0

Given that I cannot add this explanation as a comment, I'm adding an answer. But it's just a complement to the answer from @Aaron Bertrand:

Your statement may be waiting for another one that is also waiting. I just added the SELECT from Aaron a few more times. Repeat until you see the one is running. That one may be the problem:

Declare @blc int

SELECT @blc = blocking_session_id
FROM sys.dm_exec_requests
WHERE session_id = 109   --Use the SPID here 

SELECT status, command, wait_type, last_wait_type, blocking_session_id
FROM sys.dm_exec_requests
WHERE session_id = @blc

DBCC INPUTBUFFER(@blc);



DECLARE @blc2 int

SELECT @blc2 = blocking_session_id
FROM sys.dm_exec_requests
WHERE session_id = @blc

SELECT status, command, wait_type, last_wait_type, blocking_session_id
FROM sys.dm_exec_requests
WHERE session_id = @blc2

DBCC INPUTBUFFER(@blc2);



DECLARE @blc3 int

SELECT @blc3 = blocking_session_id
FROM sys.dm_exec_requests
WHERE session_id = @blc2

SELECT status, command, wait_type, last_wait_type, blocking_session_id
FROM sys.dm_exec_requests
WHERE session_id = @blc3

DBCC INPUTBUFFER(@blc3);



DECLARE @blc4 int

SELECT @blc4 = blocking_session_id
FROM sys.dm_exec_requests
WHERE session_id = @blc3

SELECT status, command, wait_type, last_wait_type, blocking_session_id
FROM sys.dm_exec_requests
WHERE session_id = @blc4

DBCC INPUTBUFFER(@blc4);
0

I found a great sp_who2 query on stack exchange, I modified it, and it shows every command currently running and which is executing, and also blocking. It has helped me tremendously in finding blocking on all databases. It eliminates sleeping processes and anything from the master. Run your processes, then run this in a separate window on Management Studio. Refresh it to see which statements are blocking. SUSPENDED means its just waiting for resources.

Declare @loginame sysname = null

 DECLARE @whotbl TABLE 
    ( 
      SPID        INT    NULL 
     ,Status    VARCHAR(50)    NULL 
     ,Login        SYSNAME    NULL 
     ,HostName    SYSNAME    NULL 
     ,BlkBy        VARCHAR(5)    NULL 
     ,DBName    SYSNAME    NULL 
     ,Command    VARCHAR(1000)    NULL 
     ,CPUTime    INT    NULL 
     ,DiskIO    INT    NULL 
     ,LastBatch VARCHAR(50)    NULL 
     ,ProgramName VARCHAR(200)    NULL 
     ,SPID2        INT    NULL 
     ,RequestID INT    NULL 
     ) 


     INSERT INTO @whotbl 
     EXEC sp_who2  @loginame = @loginame 

    SELECT CommandText = sql.text ,
           W.*  
          ,ExecutionPlan   = pln.query_plan 
          ,ObjectName  = so.name  
          ,der.percent_complete 
          ,der.estimated_completion_time 
          --,CommandType =der.command 
      FROM @whotbl  W 
 LEFT JOIN sys.dm_exec_requests der 
        ON der.session_id = w.SPID 
       OUTER APPLY SYS.dm_exec_sql_text (der.sql_handle) Sql 
       OUTER APPLY sys.dm_exec_query_plan (der.plan_handle) pln 
 LEFT JOIN sys.objects so 
        ON so.object_id = sql.objectid 
WHERE       DBName <> 'master'
and w.[status]<>'sleeping'
0

The most reliable blocking tool that I have found is beta_lockinfo written by Erland Sommarskog.

Excellent documentation and very detailed SP that gets a ton of info to help troubleshoot blocking situation.

It has archive mode to write to a table for the info to be persisted and ability to extract that info out as well.

0

Well, you already have great answers posted here by experts, I would like to add this to your kind consideration that you may use sp_whoisactive developed by Adam Machanic and you may download the same from Brent Ozar Website

They have also recently added one new procedure called sp_blitzlock, you may download this since it is incorporated into First Responder Kit; I am sure this would be of help.

If you are using any Database Monitoring tool like Solarwinds DPA or Idera, even that will be very handy to provide you much more details about the query and blocking.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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