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.

USE master;
GO

IF DATABASEPROPERTYEX('BlockingDB', 'Version') > 0
BEGIN
    ALTER DATABASE BlockingDB SET SINGLE_USER
		WITH ROLLBACK IMMEDIATE;
	DROP DATABASE BlockingDB;
END

CREATE DATABASE BlockingDB;
GO

USE BlockingDB;
GO

CREATE TABLE BlockingTable(
col1 INT IDENTITY,
col2 INT
);
GO
--INSERT 10 rows
INSERT INTO BlockingTable
VALUES (2);
GO 10

--Open TRAN and update rows
BEGIN TRAN
UPDATE BlockingTable
SET col2 = 3
WHERE col1 = 3;
GO

--OPEN 3 NEW SESSIONS
--1. do a SELECT * from BlockingTable
--2. Do an UPDATE to BlockingTbale
--3. Query sys.dm_os_waiting_tasks

ROLLBACK

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!