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.

FROM <table_name> 

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

INSERT test_table

DECLARE @delay VARCHAR(8) = '00:00:10'


FROM   test_table WITH (TABLOCKX)
WHERE  col1 = '1'



Hope you enjoyed reading this post.

Happy Learning
Lokesh Vij

3 thoughts on “How to acquire a table lock for specific time?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s