sql-server - SQL Server 2008 R2 - 在同一个表上使用 SELECT 作为查询的一部分进行更新

Question

数周以来，我一直在尝试找出每 100,000 多笔交易中发生一次的问题。我已经尝试了几十种变体，但已经用尽了所有想法，所以我希望有人以前见过这个。

总而言之，我有一个像队列一样工作的表。记录可以单独插入，也可以成批插入，有时一条记录“依赖于”另一条记录(因此在它所依赖的记录第一次被删除之前，它没有资格从队列中删除)。表的基本结构包括以下列:

• item_id - 唯一标识记录的 GUID
• depend_id - 一个 GUID，用于标识此记录所依赖的记录(如果不依赖于任何内容，则为 NULL)
• item_lock - 一个以 NULL 开始的 GUID，但当记录被“锁定”以进行处理时设置为“所有者进程”(当工作完成时，记录被从表中删除)

调用以“锁定”下一个“就绪”记录的轮询查询的简化版本是:

UPDATE TOP(1) Q1 SET
  lock_id = @lock,
  FROM item_queue Q1
WHERE (lock_id IS NULL)
AND (depend_id IS NULL OR depend_id NOT IN (SELECT item_id FROM item_queue))
AND execute_at < GETUTCDATE()

我的目标(这几乎一直有效)是 NOT IN SELECT 将简单地检查是否有资格设置其 lock_id 的项目，如果它是 depend_id 则不会被选择匹配仍在表中的另一个项目。但是，在对存储过程的 100,000 多次调用中，有 1 次该约束失败，并且选择了具有与仍在表中的 item_id 匹配的 depend_id 的记录。

我尝试了 NOT IN SELECT 的各种替代方案；所有方法都“有效”，但都以同样的方式失败。在已提交的事务中插入“依赖”记录及其依赖项的情况总是如此。

欢迎任何想法...我很难过。

PS - 我应该提到在不同的客户端机器上有许多不同的线程添加和轮询/锁定/删除该表中的记录。我的一个工作理论是，有某种锁定/争用偶尔会导致被依赖的记录不显示在 NOT IN SELECT 子查询中，从而导致依赖记录成为合格的(但我一直无法想出发生这种情况的具体场景)。

编辑: 有关事务隔离级别的更多信息:我正在使用默认的 READ COMMITTED 隔离级别运行。这是否可能导致在另一个线程刚刚更新它的“竞争条件”情况下，从 NOT IN SELECT 子查询中省略“依赖”记录？如果是这样，我并不完全清楚我需要什么隔离级别来确保表中的任何记录(无论是否正在更新)在该查询中返回。

Answer 1

你试过做相关更新吗？通过 depend_id 到 item_id 的第二次左连接到项目队列表，你不应该在第二个实例中锁定任何东西，并且让标准只看左连接结果是否为 NULL。如果 depend_id 为空，则它不会通过左连接找到匹配项。如果它确实在 depend_id 中有一个值，而不是作为 item_id 值，那么它也会导致 null。只有当 depend_id MATCHED 和 ITEM_ID 才不会为 NULL 并因此被排除在考虑之外。

UPDATE TOP(1) item_queue 
   SET lock_id = @lock
  FROM item_queue
     LEFT JOIN item_queue IQ2
        ON item_queue.depend_id = IQ2.item_id
  WHERE
         lock_id is null
     AND IQ2.item_id is null
     AND execute_at < GETUTCDATE()