SQL Server - How to lock a table until a stored procedure finishes

Question

I want to do this:

create procedure A as
  lock table a
  -- do some stuff unrelated to a to prepare to update a
  -- update a
  unlock table a
  return table b

Is something like that possible?

Ultimately I want my SQL server reporting services report to call procedure A, and then only show table a after the procedure has finished. (I'm not able to change procedure A to return table a).

Solution 1

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 ...
AS
BEGIN
  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 TRANSACTION
END

Solution 2

BEGIN TRANSACTION

select top 1 *
from table1
with (tablock, holdlock)

-- You do lots of things here

COMMIT

This will hold the 'table lock' until the end of your current "transaction".

Solution 3

Use the TABLOCKX lock hint for your transaction. See this article for more information on locking.

Comments

[David Moye]

You could, alternatively, use UPDLOCK if it's okay for others to read the table while you're using it.

[Greg]

Where does the transaction come in? Should I wrap my whole SP in a transaction?

[David Moye]

For many SPs, it makes sense to begin a transaction at the beginning and commit it at the end. There are, of course, exceptions to this rule, but in general I find it a good practice.

[Nipuna]

Can't we use sp_getapplock for this

[Graham]

From the documentation (msdn.microsoft.com/en-us/library/ms189823.aspx), it seems sp_getapplock will also do the job with sp_releaseapplock being used for releasing the lock. Also has the advantage of not needing to be inside a transaction by the looks of it.

[crokusek]

TABLOCK will prevent updates by other sessions, TABLOCKX would prevent both updates and reads.

[Géza]

Additionaly you can insert the lock-select into a table variable in order to hide it from the output, if needed: DECLARE @HideSelectFromOutput TABLE ( DoNotOutput INT); INSERT INTO @HideSelectFromOutput SELECT TOP 1 Id FROM a WITH (TABLOCK, HOLDLOCK);

[Anders Lindén]

Isnt the lock only in use when running one select statement in this solution?

[Graham]

@Anders - The lock remains in force until the COMMIT. You can put many statements before this.