TSQL to generate blocking scenario for testing
Most of the times you have observed to identify the blocking and how to resolve them.
How about you need a script to generate a blocking scenario within your queries, this is to identify the blocker script is working or not. Also will help to test whether the alerting tool is working or not.
(I have used Northwin database to stage the scenario)
create procedure Gen_Blocker_Proc @OrderID int
as
select * from Orders with (holdlock)
where OrderID = @OrderID
waitfor delay '00:00:10'
--- this will block using shared lock
update Orders
set ShippedDate = getdate()
where OrderID = @OrderID
waitfor delay '00:01:30'
go
--- begin a transaction
begin tran
go
exec blocker_proc @OrderID=15000
go
--- rollback
Once you are able to get the desired results then take the comments from rollback statement above.
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.