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.
SET col2 = 3
WHERE col1 = 3;
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).
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.
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!