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

Advertisements

3 comments

  1. Bharath · December 27, 2013

    Awesome , Thanks !!

    Like

    • sqlpathy · December 27, 2013

      Glad to know Bharath that you like the post.

      Like

  2. www.valez.ru · September 5, 2014

    Great treatise totally what I wanted to find.

    Like

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s