并行调用存储过程以增加计数器并确保原子增量

Question

我正在创建一个存储过程，它可以增加计数器的值，如果该调用负责达到 MaxValue，则返回。棘手的部分是这个过程将从不同的线程和不同的机器快速并行地调用。

示例场景：

并行执行的两个线程调用相同的存储过程来递增相同的计数器。假设 CounterId = 5 作为两者的参数传入。在任一执行之前，计数器记录当前的字段值为 CounterValue = 9 和 MaxValue = 10。

我想要发生的是其中一个过程成功地将 CurrentValue 增加到 10 并返回一个结果，表明它负责进行导致 CounterValue 达到 MaxValue 的更改。另一个过程不应增加该值（因为它会超过 10），并且应返回一个结果，指示已满足计数器的 MaxReach 要求。

我考虑过在之前或之后执行查询，但似乎可能会留下一个“漏洞”，其中可以通过单独的线程进行更改并导致返回误报/漏报。

这只是该程序的一个想法的开始。我觉得它需要锁定、交易什么的？

UPDATE SomeCounters
SET CounterValue = (CounterValue + @AddValue),
    MaxReached = CASE WHEN MaxValue = (CurrentValue + 1) THEN 1 ELSE 0
WHERE CounterId = @CounterId
  AND MaxReached = 0

Answer 1

使用 OUTPUT

DECLARE @temp TABLE (MaxReached BIT NOT NULL);

UPDATE SomeCounters
  SET CounterValue = (CounterValue + @AddValue),
      MaxReached = CASE WHEN MaxValue = (CurrentValue + 1) THEN 1 ELSE 0
  WHERE CounterId = @CounterId
    AND MaxReached = 0
  OUTPUT INSERTED.MaxReached INTO @temp

更新是原子的，然后您可以从 @temp 表中选择值并对其执行任何您想要的操作。这样您就能够捕获导致 MaxReached 设置为 true (1) 的确切更新。

Answer 2

您需要将其包装在一个事务中，并在同一事务中添加一个选择，如下所示：

BEGIN TRANSACTION; 

UPDATE SomeCounters
SET CounterValue = (CounterValue + @AddValue)
WHERE CounterId = @CounterId;

SELECT CASE WHEN MaxValue = CurrentValue THEN 1 ELSE 0 MaxReached
FROM SomeCounters
WHERE CounterId = @CounterId;

COMMIT TRANSACTION;

您可以将最后一部分放入输出参数中，以便从过程中返回它。

Answer 3

实现你所寻求的目标的一种方法是采取悲观的态度。这意味着每个存储过程仅更新未被另一个存储过程修改的记录，然后重试，直到达到最大值。为此，您需要在更新之前读取当前值，然后使用期望值相同的 WHERE 子句更新记录。如果您需要确保调用最终成功，您还需要一个循环。使用这种方法，一次只有 1 个存储过程会更新表，并重试工作，直到达到最大值。

像这样的事情：

DECLARE @savedValue int
DECLARE @maxedReached int
-- read current values for concurrency
SELECT @savedValue = CounterValue, @maxedReached = MaxReached 
  FROM SomeCounters WHERE CounterId = @counterId)

WHILE(@maxedReached = 0)
BEGIN

  UPDATE SomeCounters
  SET CounterValue = (CounterValue + @AddValue),    
    MaxReached = CASE WHEN MaxValue = (CurrentValue + 1) THEN 1 ELSE 0 END
  WHERE 
    CounterId = @CounterId  
    AND MaxReached = 0
    -- the next clause ensures that only one stored procedure will succeed
    AND CounterValue = @savedValue  

  if (@@rowcount = 0)
  BEGIN
    -- failed... another procedure made the change?
    -- If @maxReached becomes 1, the loop will exit and you will
    -- know the maximum was reached; if not the loop will try updating
    -- the value again
    -- read the values for concurrency again.
    SELECT @savedValue = CounterValue, @maxedReached = MaxReached 
        FROM SomeCounters WHERE CounterId = @counterId)

  END
END

Answer 4

我正在研究的另一个策略是在事务中使用 sp_getapplock。看来这将允许我为我试图更新的计数器创建一个唯一的字符串，并阻止其他并发执行，直到它完成。

这似乎特别有用，因为我的程序还将包含一些 IF EXISTS ... ELSE ... 逻辑，它将处理第一次创建计数器记录或更新和现有计数器记录。

http://msdn.microsoft.com/en-us/library/ms189823.aspx - sp_getapplock

Answer 5

假设 MaxValue 是众所周知的，并且每个计数器都相同，那么您不需要事务：

UPDATE CounterTable
SET Counter=Counter+1
WHERE CounterId = @CounterId

​​这是一个数据库，而不是多线程程序。这是对 SQL Server 的请求，要求增加表中一行的 Counter 列的值。 SQL Server 会这样做——我不认为它会允许表丢失其中一个请求。

因此，在最坏的情况下，您可能会得到 Counter >最大值。但如果您知道 MaxValue 是什么，那么您就会知道任何高于它的值实际上都意味着 MaxValue。无需立即在同一事务中安排工作。。

因此，根据“额外工作”的时间紧迫性，只需让一个作业或其他程序查询表，查找大于或等于 MaxValue 的任何计数器值，然后就在那里完成工作。最坏的情况是，创建一个在每次更新时触发的触发器，该触发器仅在计数器值较高时才起作用。

不需要事务，除非您需要在更新计数器的同一事务中执行“额外工作”。由于您没有说您现在正在使用事务，因此我怀疑您不需要在同一事务中进行“额外工作”。