How to acquire a table lock for specific time?

Recently one of my colleagues asked me how to acquire an exclusive lock on a database table for a specific amount of time. And this lock should be released once the specified time expires.

You need two components to accomplish this task:
1) Table hints – that puts exclusive lock on the table
2) Delay element – that will hold the lock for a specified amount of time

1) Table hints
Table hint TABLOCKX can be used to acquire exclusive lock on the table.

1 2 3

SELECT * FROM <table_name> WITH (TABLOCKX)

2) Delay element
WAITFOR statement inside transaction block can be used to introduce delay. Following sample code snippet introduces a delay for 10 seconds

1	WAITFOR DELAY '00:00:10'

Here is the code snippet that creates a test table, inserts some data and places an exclusive lock for 10 seconds on this table

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

CREATE TABLE test_table   (      col1 INT   ) GO   INSERT test_table VALUES (1) GO   DECLARE @delay VARCHAR(8) = '00:00:10'   BEGIN TRAN   SELECT * FROM   test_table WITH (TABLOCKX) WHERE  col1 = '1'   WAITFOR DELAY @delay   ROLLBACK TRAN

Hope you enjoyed reading this post.

Ref : https://sqlpathy.com/2012/08/22/how-to-acquire-a-table-lock-for-specific-time/