SQL 问题 - 大量反式和 PK 违规

Question

我正在编写一个大容量交易系统。我们每秒接收大约 300-500 条消息，然后需要尽快将这些消息保存到数据库中。这些消息存放在消息队列中，然后从那里读取。

我已经实现了一个竞争消费者模式，它从队列中读取并允许对消息进行多线程处理。但是，在应用程序运行时，我经常遇到主键冲突。

我们正在运行 SQL 2008。示例表结构将是:

TableA
{
    MessageSequence INT PRIMARY KEY,
    Data VARCHAR(50)
}

一个存储过程被调用来保存这条消息，看起来像这样:

BEGIN TRANSACTION

INSERT TableA(MessageSequence, Data )
SELECT @MessageSequence, @Data
WHERE NOT EXISTS
(
  SELECT TOP 1 MessageSequence FROM TableA WHERE MessageSequence = @MessageSequence
)

IF (@@ROWCOUNT = 0)
BEGIN

UPDATE TableA
SET Data = @Data
WHERE MessageSequence = @MessageSequence

END

COMMIT TRANSACTION

所有这些都在 TRY...CATCH 块中，因此如果出现错误，它会回滚事务。

我试过使用表提示，如 ROWLOCK，但没有任何区别。由于 Insert 被评估为单个语句，因此我仍然遇到“插入时的主键”问题似乎很可笑。

有谁知道为什么会这样？您是否有任何想法可以为我指明解决方案的方向？

Answer 1

为什么会这样？

SELECT TOP 1 MessageSequence FROM TableA WHERE MessageSequence = @MessageSequence

此 SELECT 将尝试定位该行，如果未找到 EXISTS 运算符将返回 FALSE，INSERT 将继续。然而，INSERT 的决定基于在 SELECT 时为真的状态，但在 INSERT 时不再保证为真。换句话说，您有竞争条件，其中两个线程都可以查找相同的@MessageSequence，都返回 NOT EXISTS 并且都尝试插入，当只有第一个成功时，第二个将导致 PK 冲突。

我该如何解决？

最快的解决方法是添加 WITH (UPDLOCK)向 SELECT 提示，这将强制保留放置在 @MessageSequence 键上的锁，从而使 INSERT/SELECT 以原子方式运行:

INSERT TableA(MessageSequence, Data )
   SELECT @MessageSequence, @Data
   WHERE NOT EXISTS (
      SELECT TOP 1 MessageSequence FROM TableA WITH(UPDLOCK) WHERE MessageSequence = @MessageSequence)

为了防止 SQL 执行页锁等花哨的操作，您还可以添加 ROWLOCK 提示。

然而，这不是我的建议。我的建议可能会让您感到惊讶，但这是: 做最有可能成功的操作，如果失败则处理错误 . IE。如果您的业务案例使 @MessageSequnce 更有可能是新的，请尝试 INSERT 并在失败时处理 PK。通过这种方式，您可以避免虚假查找，并且当第一次尝试成功时，捕获/重试的成本会在许多情况下分摊。

此外，也许值得研究使用 built-in queues that come with SQL Server .