HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

Need to intentionally create blocking processes for testing

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
createneedblockingtestingforprocessesintentionally

Problem

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!

Solution

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

Code Snippets

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

Context

StackExchange Database Administrators Q#144591, answer score: 8

Revisions (0)

No revisions yet.