How to create blocking in SQL

• yujinagata72

  Ten Centuries

  Points: 1072

  More actions

  July 30, 2016 at 4:53 am

  #315753

  Hi,

  I would like to test my SP which can capture the blocking. But how I can create a blocking scenario via SQL script?

  Thanks

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  July 30, 2016 at 7:29 am

  #1892744

  1) Why write your own script? Just use sp_whoisactive!! AMAZING FREE script from Adam Machanic.

  2) You typically need to use 2 connections to create blocking.

  spid1:

  BEGIN TRAN

  SELECT *

  FROM myTable WITH (TABLOCKX)

  WHERE something

  spid2:

  BEGIN TRAN

  SELECT *

  FROM myTable WITH (TABLOCKX)

  WHERE samething

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service