Why Am I Blocked? (A Month of Activity Monitoring, Part 19 of 30)

The Node Knows (A Month of Activity Monitoring, Part 20 of 30)

Adam Machanic — Wed, 20 Apr 2011 14:32:09 GMT

This post is part 20 of a 30-part series about the Who is Active stored procedure. A new post will run

The Key to Your Locks (A Month of Activity Monitoring, Part 22 of 30)

Adam Machanic — Fri, 22 Apr 2011 20:31:51 GMT

This post is part 22 of a 30-part series about the Who is Active stored procedure. A new post will run

Twenty Nine Days of Activity Monitoring (A Month of Activity Monitoring, Part 30 of 30)

Adam Machanic — Sat, 30 Apr 2011 15:45:16 GMT

This post is part 30 of a 30-part series about the Who is Active stored procedure. A new post will run

re: Why Am I Blocked? (A Month of Activity Monitoring, Part 19 of 30)

IJeb Reitsma — Tue, 31 May 2011 17:24:15 GMT

Thanks for these series of high quality posts.

This very usefull feature is somewhat hidden, although it is very well documented. I tried to find out why the first query of post 17 was blocked by the following query of post 14 and the first one was not.

UPDATE TOP(10) Sales.SalesOrderDetail SET OrderQty += 7

I expected it to see together with the locking information, but this only shows the GRANTED locks, not the locks with status WAIT.

In the case of a keylock I also miss information about the actual record(s) that cause the blocking.

re: Why Am I Blocked? (A Month of Activity Monitoring, Part 19 of 30)

IJeb Reitsma — Tue, 31 May 2011 21:15:45 GMT

Sorry for my previous post. After going to post 22 I noticed that the locks xml also shows locks with request status "WAIT".

re: Why Am I Blocked? (A Month of Activity Monitoring, Part 19 of 30)

Adam Machanic — Tue, 31 May 2011 21:32:17 GMT

Hi Ijeb,

Glad to hear that it worked out! Enjoy using Who is Active :-)

--Adam

re: Why Am I Blocked? (A Month of Activity Monitoring, Part 19 of 30)

Gilles — Thu, 19 Jul 2012 23:52:10 GMT

Hi,

Have used the dbo.sp_WhoIsActive

@get_locks=1,

@get_additional_info = 1

to find out why one of my session was blocked by another one.

i actually run the following:

WHILE (1=1)

BEGIN

EXEC dbo.sp_WhoIsActive

@get_plans=1,

@get_locks=1,

@get_additional_info = 1

WAITFOR DELAY '00:00:01'

END

and as expected i obtained the following results:

session 52 has a blocking session 53 (for multiple 10 of seconds) BUT when i look at the additional_info XML column for the blocked session 52 i do not see any 'block_info' node. It just tells me that the transaction_isolation_level is 'Serializable' and the command_type is 'UPDATE'.

If i look at the locks XML column for the blocked session 52, i see only 'GRANT' locks, not a single 'WAIT' one...

I was expecting to have at least one WAIT lock to explain why I am blocking for multiple seconds.

I should add that if i change the application code to not require the session 52 to be using the Serializable isolation mode and downgrade it to 'ReadCommitted', no long blocking is experienced.

Am sure am missing or not understanding something here. Do you have a suggestion?

Thank you.

Gilles.

re: Why Am I Blocked? (A Month of Activity Monitoring, Part 19 of 30)

Gilles — Fri, 20 Jul 2012 00:06:06 GMT

actually i had forgotten to use the @get_task_info=2, it now shows a <block_info> XML node with a keylock lock_type. Should i conclude that because the blocked session 52 is using a Serializable isolation mode, it is blocking as a single row lock has been put by session 53...then still why can't we see some WAIT locks in the lock column?

Thanks again.

Gilles