SQL Server - 如何在存储过程完成之前锁定表

2022-01-31 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 报告服务报告调用过程 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

相关文章