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

Bogdan Sahlean · Accepted Answer · 2013-10-26 08:55:28Z

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

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.
@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.
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 !

Collectives™ on Stack Overflow

T-SQL lock resouce in an event of concurrent access

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related