SQL Server——如何锁定一个表直到存储过程结束

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

最后，我希望我的SQL server reporting services报告调用过程A，然后在过程结束后只显示表A。(我无法更改程序A以返回表A)。

3 个解决方案

#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

#2

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

对事务使用TABLOCKX锁提示。有关锁定的更多信息，请参阅本文。

#3

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

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

这将保持“表锁”直到当前事务结束。

#1