How to create a blocking transaction in SQL Server – T-SQL (exclusive lock)

Excuse the odd topic.   I often like to experiment with SQL Server and create odd situations which we normally should be avoiding.  One of those things is creating SQL Statement which get blocked and creating a timeout.  One of the simplest ways to run a query against a table which is locked in another in another session.  To do this do the following:

1) Create a lock on a table.  

The example below demonstrates a lock on the Person table in the AdventureWorks2012 sample table.    The key points which make this work is to create a transaction, and then issuing a query using the TABLOCKX and HOLDLOCK table lock hints.  This will force an exclusive lock on the table.  To keep the lock for significant amount of time we specify that the transaction stays alive for a minute by introducing the WAITFOR DELAY ’00:01:00′ command, which will prevent any further execution of statements until a minute has passed.

2)  Now open up another session in SQL Server (new query from Microsoft SQL Server Management Studio), and execute the following query:

You will notice that after this query is excuted, the query seems to run forever.  It will actually never  execute until the query in step 1 is finished.

3) Check for blocked transaction.  You can find this by opening up an new query in Microsoft SQL Server Management Studio and executing the following query:

I will explain in more detail how this blocking query works in a later post.

If you have any other tips on how to create a blocked transaction please share them in the comments below.

%d bloggers like this: