如何在SQL Server 2005中锁定表格，我是否应该这样做？

Question

这个需要一些解释。我创建了一个特定的自定义消息队列在SQL Server 2005中。我有一个表格，其中包含包括确认和完成的时间戳的消息。调用者执行的存储过程还会确认消息以获取他们队列中的下一条消息。到目前为止都很好。嗯，如果系统正在经历大量的交易（每分钟数千次），是不是可能一条消息在另一个存储过程的执行确认之后，另一个存储过程还在准备确认它？让我通过展示存储过程中我的SQL代码来帮助你理解：

--Grab the next message id
declare @MessageId uniqueidentifier
set @MessageId = (select top(1) ActionMessageId from UnacknowledgedDemands);

--Acknowledge the message
update ActionMessages
set AcknowledgedTime = getdate()
where ActionMessageId = @MessageId

--Select the entire message
...
...

在上述代码中，是否可能同时运行另一个存储过程并获取相同的id，然后尝试同时确认它？我是否应该实现某种锁定以防止另一个存储过程确认另一个存储过程正在查询的消息？

哇，这是否有任何意义？用言语表达有点困难...

- Kilhoffer

---

您可能希望重新标记此内容，以便“bestpractice”更改为更常用的“best-practices”。 - martinatime

Answer 1

类似这样的

--Grab the next message id
begin tran
declare @MessageId uniqueidentifier
select top 1 @MessageId =   ActionMessageId from UnacknowledgedDemands with(holdlock, updlock);

--Acknowledge the message
update ActionMessages
set AcknowledgedTime = getdate()
where ActionMessageId = @MessageId

-- some error checking
commit tran

--Select the entire message
...
...

- SQLMenace

Answer 2

这似乎是使用OUTPUT的有用场景，特别是在涉及IT技术方面的情况下。

-- Acknowledge and grab the next message
declare @message table (
    -- ...your `ActionMessages` columns here...
)
update ActionMessages
set    AcknowledgedTime = getdate()
output INSERTED.* into @message
where  ActionMessageId in (select top(1) ActionMessageId from UnacknowledgedDemands)
  and  AcknowledgedTime is null

-- Use the data in @message, which will have zero or one rows assuming
-- `ActionMessageId` uniquely identifies a row (strongly implied in your question)
...
...

在这里，我们在同一操作中更新并获取行，这告诉查询优化器确切地我们正在做什么，使其能够选择最细粒度的锁，并将其保持在最短的时间内。（尽管列前缀是INSERTED，但OUTPUT就像触发器一样，在UPDATE被视为删除行并插入新行的情况下表达。）

我需要更多关于您的ActionMessages和UnacknowledgedDemands表（视图/ TVF /任何内容）的信息，更不用说对SQL Server的自动锁定有更深入的了解，才能确定是否需要and AcknowledgedTime is null子句。它存在于子查询和更新之间的竞争条件中。如果我们从ActionMessages本身进行选择（例如，where AcknowledgedTime is null带有update上的top，而不是在UnacknowledgedDemands上进行子查询），那么我确定它不会是必需的。即使它是不必要的，我相信它也是无害的。

请注意，OUTPUT适用于SQL Server 2005及以上版本。这是您所说的您正在使用的版本，但如果需要与SQL Server 2000安装兼容，则需要采用其他方法。

- T.J. Crowder

---

（太晚了，但我看没有人提到，所以...） - T.J. Crowder

Answer 3

与其使用显式锁定，这种方法通常会被 SQL Server 升级到比所需的更高的粒度，为什么不尝试这种方法呢：

declare @MessageId uniqueidentifier
select top 1 @MessageId = ActionMessageId from UnacknowledgedDemands

update ActionMessages
  set AcknowledgedTime = getdate()
  where ActionMessageId = @MessageId and AcknowledgedTime is null

if @@rowcount > 0
  /* acknoweldge succeeded */
else
  /* concurrent query acknowledged message before us,
     go back and try another one */

锁得越少，你的并发性就越高。

- Constantin

Answer 4

@Kilhoffer：

整个 SQL 批处理在执行之前都会被解析，因此 SQL 知道您将对表进行更新并从中选择。

编辑：此外，SQL 不一定会锁定整个表 - 它只能锁定必要的行。请参阅 here 以获取 SQL Server 中锁定的概述。

- Blorgbeard

---

我不同意这个观点。解析与此无关。如果有关系的话，通过EXECUTE运行的SQL将不会是事务安全的。 - Euro Micelli

但这里没有执行。不过说得好 - 当通过执行运行任意SQL时，SQL如何知道要锁定什么？ - Blorgbeard

SQL Server决定锁定什么，以及是否保留锁定或释放它们，这是在运行每个事务语句时发生的，而不是在解析时间。因此，在EXEC调用和正常批处理之间没有区别。唯一的区别在于查询计划的选择（是否使用缓存的查询计划）。 - Tao

Answer 5

你真的需要一个一个地处理吗？难道不应该让SQL Server确认所有未确认的消息，并返回今天的日期吗？（当然也要在一个事务中）

- rpetrich

---

这不就是我正在做的吗？SQL Server代表调用者确认消息并返回它们。 - Kilhoffer

不，我指的是这样的（未经测试）：开始事务 从未确认需求中选择*； 更新 ActionMessages 设置 AcknowledgetTime = getdate() 如果存在 (从未确认需求中选择ActionMessages.ActionMessageId = UnacknowledgedDemands.ActionMessageId) 提交事务 - rpetrich

Answer 6

了解更多关于 SQL Server Select Locking 的信息在这里和在这里。SQL Server 有能力在选择时调用表锁定。在事务期间，表不会发生任何变化。当事务完成后，任何插入或更新将自行解决。

- Jarrett Meyer

---

那么这是否意味着，仅仅将上述代码放入一个事务中，SQL Server确实会按照上述链接中描述的方式实现选择锁定？ - Kilhoffer

Answer 7

您想将您的代码放入一个事务中，那么SQL服务器将处理适当的行或表的锁定。

begin transaction

--Grab the next message id
declare @MessageId uniqueidentifier
set @MessageId = (select top(1) ActionMessageId from UnacknowledgedDemands);

--Acknowledge the message
update ActionMessages
set AcknowledgedTime = getdate()
where ActionMessageId = @MessageId

commit transaction

--Select the entire message
...

- Blorgbeard

---

要是这么简单就好了...是否由第一条语句持有(共享)锁将取决于事务隔离级别，最好的情况是(如果隔离级别使共享锁被持有)，那么该代码将导致死锁，因为两个不同的连接获取相同的消息并尝试更新它(每个连接都被对方的锁所阻止)。 - Tao