SQL Server - How to lock a table until a stored procedure finishes
Needed this answer myself and from the link provided by David Moye, decided on this and thought it might be of use to others with the same question:
CREATE PROCEDURE ...ASBEGIN BEGIN TRANSACTION -- lock table "a" till end of transaction SELECT ... FROM a WITH (TABLOCK, HOLDLOCK) WHERE ... -- do some other stuff (including inserting/updating table "a") -- release lock COMMIT TRANSACTIONEND
BEGIN TRANSACTIONselect top 1 *from table1with (tablock, holdlock)-- You do lots of things hereCOMMITThis will hold the 'table lock' until the end of your current "transaction".