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

Question

我想要做这件事：

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）。

- Greg

---

你考虑过使用 SET TRANSACTION / COMMIT 吗？我不太确定你在这里想要实现什么？ - MikeAinOz

Xin的答案更为简洁，资源消耗也更少。不过我必须使用TABLOCKX。 - RAD

Answer 1

我需要这个答案，从David Moye提供的链接中决定了这个答案，并认为对其他有同样问题的人也可能有用：

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

- Graham

---

我们不能使用 sp_getapplock 来完成这个任务吗？ - Nipuna

从文档（https://msdn.microsoft.com/en-us/library/ms189823.aspx）中可以看出，sp_getapplock似乎也可以使用sp_releaseapplock来释放锁。此外，它的优点是看起来不需要在事务内部执行。 - Graham

TABLOCK会防止其他会话的更新，而TABLOCKX不仅会防止更新还会防止读取。 - crokusek

此外，如果需要，您可以将lock-select插入到表变量中以隐藏它不被输出：DECLARE @HideSelectFromOutput TABLE (DoNotOutput INT); INSERT INTO @HideSelectFromOutput SELECT TOP 1 Id FROM a WITH (TABLOCK, HOLDLOCK); - Géza

在这个解决方案中，锁不是只在运行一个select语句时使用吗？ - Anders Lindén

@Anders - 锁定仍然有效，直到COMMIT。在此之前，您可以放置许多语句。 - Graham

Answer 2

BEGIN TRANSACTION

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

-- You do lots of things here

COMMIT

这将会持有“表锁”，直到您当前的“事务”结束。

- Xin

Answer 3

请在您的事务中使用TABLOCKX锁提示。 参见此文章 了解更多有关锁定的信息。

- David Moye

---

如果在您使用表格时允许其他人读取它，您可以选择使用UPDLOCK。 - David Moye

事务在哪里？我应该将整个存储过程包装在一个事务中吗？ - Greg

对于许多存储过程而言，在开始时启动事务并在结束时提交事务是有意义的。当然，也有例外情况，但总体上我认为这是一个好的实践。 - David Moye