CODEWITHSUNDEEP
sql-servert-sqlconcurrencysqltransaction
Sujit Prabhakaran
Sujit Prabhakaran

Reputation: 343

T-SQL lock resouce in an event of concurrent access

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

Inside a stored procedure:

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

Upvotes: 0

Views: 235

Answers (1)

Bogdan Sahlean
Bogdan Sahlean

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

enter image description here

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

Upvotes: 2

Related Questions