CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedureslocking
Greg
Greg

Reputation: 47094

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

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

Upvotes: 69

Views: 171146

Answers (3)

David Moye
David Moye

Reputation: 790

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

Upvotes: 20

Xin
Xin

Reputation: 36490

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

Upvotes: 30

Graham
Graham

Reputation: 8141

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

Upvotes: 68

Related Questions