0

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sujit Prabhakaran
  • 295
  • 3
  • 12
  • 26

1 Answers1

2

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):

enter image description here

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

Bogdan Sahlean
  • 19,233
  • 3
  • 42
  • 57
  • 1
    Dear Bogdan, thanks for your advice. Another solution I received from my manager was to make use of 'LoggedInUser' field in the same table and do DML specific to currently 'LoggedInUser' to avoid concurrency issues. – Sujit Prabhakaran Oct 26 '13 at 16:10
  • @sujimon: Maybe if you give me more details about your problem I could help you more. At this moment I don't see any information regarding `LoggedInUser` column within your question. – Bogdan Sahlean Oct 26 '13 at 16:28
  • I think I didn't put across my question correctly. I was better off without any database lock on table. I just wanted to resolve concurrency issues in multi-user environment. Which I later found can be done by A> Identify logged user profile B> Identify the computer-name accessing the application C> Use a table variable inside stored-procedure. But I agree with you regarding Table locks so I will mark this as answer ! – Sujit Prabhakaran Nov 22 '13 at 07:55