SQL Server - 如何锁定表直到存储过程完成

2021-12-02 00:00:00 sql locking tsql sql-server stored-procedures

我想这样做:

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

这样的事情可能吗?

最终我希望我的 SQL Server Reporting Services 报告调用过程 A，然后仅在过程完成后显示表 a.(我无法更改过程 A 以返回表 a).

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

推荐答案

自己和来自 David Moye 提供的链接需要这个答案，决定这样做，并认为它可能对有同样问题的其他人有用:

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

相关文章