Need to intentionally create blocking processes for testing

Question

My team and I have an issue where we need to be able to identify processes that are blocking other processes and kill them. There are a ton of scripts that are freely available to perform these actions. We have tried various ones and scrutinized the code. (If you are someone that has posted one of these queries, thanks!)

Before I go further, let me tell you that this is against a vendor application that we cannot modify. The vendor is also not willing to spend the time to figure out why processes are getting blocked. The only choice we have at this point is to kill the long running processes (as suggested by the vendor support). Before killing these processes, we do review the query running, but 99.9% of the time, it shows up as FETCH API_CURSOR00000000000A7E1F, which tells us nothing.

This has been a manual process up to this point. Now, we want to automate the killing of these long running blocking processes instead of someone manually killing them.

We want to test this script before putting it into production. We would like some help creating a script that would intentionally create blocking processes. We have tried with the TEST environment with this application, but unfortunately, we have had no luck replicating blocking processes.

Thanks in advance for your assistance!

score 9 · Accepted Answer · edited Jul 22 '16 at 06:29

9

BEGIN TRANSACTION
  SELECT * FROM YourTable WITH (TABLOCKX, HOLDLOCK)
    WAITFOR DELAY '00:05:00' -- 5 minutes
ROLLBACK TRANSACTION

And then in another Query Window:

SELECT * FROM YourTable

edited Jul 22 '16 at 06:29

Mat

10,289
4
43
40

answered Jul 21 '16 at 19:31

Jason B.

642
1
4
12

score 4 · Answer 2 · answered Jul 21 '16 at 19:23

This is pretty easy to do. Open two query windows in Management Studio.

Run something like this in the first one (choose a test table that nobody is using, because this will block them, and make sure it has at least one row in it):

BEGIN TRANSACTION
SELECT * FROM sometesttable WITH (TABLOCK, XLOCK, HOLDLOCK)

Then in the second query window:

SELECT * FROM sometesttable

Your second query will be blocked until the first one is either committed or rolled back.

score 1 · Answer 3 · answered Jul 21 '16 at 21:15

You could also fire this off in a few concurrent sessions.

Only one session will be able to get the exclusive lock at any one time.

BEGIN TRANSACTION;   
EXEC sp_getapplock @Resource = 'FooBar',   
               @LockMode = 'Exclusive';  
WAITFOR DELAY '00:00:30';
EXEC sp_releaseapplock @Resource = 'FooBar';  
COMMIT TRANSACTION;

Need to intentionally create blocking processes for testing

3 Answers3