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.