Use sys.dm_os_waiting_tasks to Find Blocking Transactions

If you want to do a quick and simple exercise to view blocking transactions on your SQL Server machine (code at bottom of blog).

I create a dummy table called “BlockingTable” and insert 10 rows of data. I then open a transaction and doing a simple update of the table. I leave the transaction open and open three new sessions.

[snippet id=”30″]

In session 1, I do a simple SELECT of the BlockingTable.

SELECT * FROM BlockingTable

In session 2, I do a simple update to the same BlockingTable.

UPDATE BlockingTable
SET col2 = 3
WHERE col1 = 3;
GO

Both of these sessions hang because they are waiting for the first transaction to finish and as a result you get the “Executing query…” at the bottom of SSMS (see image below).

Executing query

In session 3, I run a SELECT against the sys.dm_os_waiting_tasks DMV (Dynamic Management View) for all wait types that begin with “LCK_” and you will notice that the two sessions that are waiting for the very first UPDATE session are in the result set.

DMV output

In my scenario (your session IDs may vary), session 52 (the very first UPDATE) is blocking session 53 (my SELECT statement) and session 53 is blocking session 54 (the second UPDATE).

If you see the “wait_type” column, you will notice 2 lock wait types: LCK_M_S and LCK_M_U. The LCK_M_S occurs when a task is waiting to acquire a Shared lock and the LCK_M_U occurs when a task is waiting to acquire an Update lock. Visit the MSDN page for a complete list of wait types.

Pretty neat huh? This was a pretty straight-forward example but still wanted to share it because it’s exciting! I will definitely post more in the near future!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.