Locking SQL databases

Have you ever wanted or needed to lock a table in an SQL database? It turns out that it's actually useful to do so, especially when testing software which assumes that all database queries automatically work correctly.

I found the below SQL script over at stackoverflow:

SELECT * FROM [dbo].[tableName] WITH (TABLOCKX, HOLDLOCK)

It will select everything from a table with an exclusive lock (using TABLOCKX) and it will hold the lock until the end of the transaction (using HOLDLOCK).

We can extend this by using

BEGIN TRANSACTION

SELECT * FROM [dbo].[tableName] WITH (TABLOCKX, HOLDLOCK)
WHERE 0 = 1

WAITFOR DELAY '00:09'

END TRANSACTION

The above will select 0 rows from the table (WHERE 0 = 1, which always evaluates false) and will hold an exclusive lock on the selected table for 9 minutes (using WAITFOR DELAY).

Practically, this can be useful for testing anything which may throw an exception in SQL. For instance, when testing a bulk insert which inserts hundreds of thousands of rows may take some time, especially if other transactions are taking place simultaneously, a table lock can simulate what would happen if the query timed out in a production environment.