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

Have you considered using SET TRANSACTION / COMMIT . I'm not too sure what you are trying to achieve here? — MikeAinOz, Sep 07 '10 at 22:22
Xin's answer was much more concise and less resource intensive. I had to use TABLOCKX though. — RAD, Jun 23 '17 at 21:29

score 64 · Answer 1 · edited May 23 '17 at 11:47

64

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

edited May 23 '17 at 11:47

Community

1
1

answered May 20 '14 at 12:02

Graham

7,807
20
69
114

1

Can't we use sp_getapplock for this – Nipuna Mar 08 '15 at 05:20
2

From the documentation (https://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. – Graham Mar 09 '15 at 12:14
18

TABLOCK will prevent updates by other sessions, TABLOCKX would prevent both updates and reads. – crokusek Sep 29 '15 at 20:39
2

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);` – Géza Apr 14 '17 at 08:03
Isnt the lock only in use when running one select statement in this solution? – Anders Lindén Feb 10 '20 at 13:20
@Anders - The lock remains in force until the COMMIT. You can put many statements before this. – Graham Feb 10 '20 at 14:12

Xin · Answer 2 · 2019-07-12T01:27:48.080

29

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".

edited Jul 12 '19 at 01:27

answered Feb 28 '17 at 05:12

Xin

33,823
14
84
85

score 19 · Answer 3 · edited Nov 09 '21 at 12:51

19

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

edited Nov 09 '21 at 12:51

Ergis

1,105
1
7
18

answered Sep 07 '10 at 21:13

David Moye

701
4
13

You could, alternatively, use UPDLOCK if it's okay for others to read the table while you're using it. – David Moye Sep 07 '10 at 21:13
Where does the transaction come in? Should I wrap my whole SP in a transaction? – Greg Sep 08 '10 at 12:14
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. – David Moye Sep 08 '10 at 14:04

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

3 Answers3

Linked

Related