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.

 
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

 
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

 
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.

Happy Learning
Lokesh Vij

Advertisement