T-SQL lock resouce in an event of concurrent access

Question

I want to lock a certain table in the event of concurrent access.

Inside a stored procedure:

• I truncate a table
• Do calculations and populate the above table
• After returning from PROCEDURE, do a select from the table

To avoid issues in event of concurrent access, I am planning to add a 'BEGIN TRANSACTION' followed by 'BEGIN TRY -END TRY' and 'BEGIN CATCH - END CATCH'. I 'COMMIT' just before 'END-TRY' and 'ROLLBACK' inside the 'CATCH'.

Will this alone resolve all concurrency issues or I need to do something more.

Many thanks, Sujit

Answer 1

You could lock entire table during a transaction using TABLOCK or TABLOCKX (references):

BEGIN TRANSACTION;

-- For shared locking:    
-- This will take a S(shared) lock at table level for duration of transaction
-- SELECT TOP(0) * FROM dbo.MyTable WITH(TABLOCK,HOLDLOCK)
-- HOLDLOCK hint is needed in this case because 
-- default behavior at default transaction isolation level (READ COMMITTED) 
-- is to maintain a S lock only during record reading.

-- For exclusive locking:    
-- This will take a (x)X(clusive) lock at table level for duration of transaction
SELECT TOP(0) * FROM dbo.MyTable WITH(TABLOCKX)

ROLLBACK;

Example (SQL Profiler output):

Note: SELECT OBJECT_ID(N'dbo.MyTable') = 1316199739