Problem Description:

Many a times it happens that we want to test something for blocking and want to create some blocking scenario. Below are the steps to create blocking scenario which can be created on test servers.

Solution:

Step1: create test database

create database Dbalyfe;

Step2: Create table

use Dbalyfe
go
CREATE TABLE dbo.BlkScenario
( ID INT IDENTITY(1,1) NOT NULL,
Name varchar(60) NOT NULL,
Salary INT NOT NULL
);

Step3: Populate data into above created table

use Dbalyfe
go
insert dbo.BlkScenario(Name, Salary)
select 'Raj', 5000
union
select 'Simran', 4000
union
select 'Rahul', 8000
union
select 'Sameer', 11000
union
select 'Ranjit', 20000
go

Step4: Take new database session and run below query

use Dbalyfe
go
begin tran
update dbo.BlkScenario
set Salary = Salary + (Salary*2)

Step5: Take another session and run below query

use Dbalyfe
go
select * from dbo.BlkScenario;

Step6: Take one more session to check for blocking scenario

select spid,blocked,db_name(dbid)'db_name',loginame from sysprocesses where blocked>0