Sql-server – How to determine which statement is blocking an update in SQL Server

blockingsql servertransaction

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?

Best Answer

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).

Related Question