主页 / dba / 问题 / 58425
Accepted
deadLock
Asked: 2014-02-07 23:40:24 +0800 CST

如何避免跨数据库 proc 调用的竞争条件？和其他担忧

  • 772

我什至不确定这个问题是否有必要，但我很想知道每个人的想法。我在同一台服务器上有两个数据库，dbFoo，dbBar。dbFoo 有下表请注意，这是一种简化的示例，并且语法可能不正确，因为我急于求成，并且对潜在问题的答案更感兴趣，然后是执行此操作的代码...

CREATE TABLE dbo.CodeNumbers(
CodeNumbersID INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
CodeValue VARCHAR(30) NOT NULL
IsUsed BIT NOT NULL DEFAULT(0)
);

dbo.CodeNumbers使用提供的每月 CSV 进行填充，您选择的导入方法已经编写好以将它们放入其中。我们永远不会得到重复的代码。

让我们假设我们在表中有 10,000,000 行。导入时都遵循这种格式：

1, 'ajdirjfisofklrlfo039402', 0 all the way till
10000000, 'fkeiir9489', 0

现在在 dbBar 我有 2 个存储过程，第一个应该访问 dbFoo 中第一个未使用的代码，将其返回到 out 变量中并将其标记为已使用。所以我有类似的东西：

CREATE PROCEDURE GetNextUseableCode
   @CodeOut VARCHAR(30) OUTPUT,
   @CID INT OUTPUT
AS
   SELECT @CID = CodeNumbersID, @CodeOut = CodeValue
   FROM dbFoo.dbo.CodeNumbers
   WHERE IsUsed = 0

   UPDATE dbFoo.dbo.CodeNumbers
   SET IsUsed = 1
   WHERE CodeNumbersID = @CID

从 dbBar 调用该过程的代码每天在不同时间被 200k 会话访问。当dbFoo.Codes没有更多返回时，一切都很好，应用程序只是告诉抱歉明天不再检查。

我有3个主要问题..

  1. 为了避免竞争条件，我需要在代码中添加什么特别的东西，如果是这样，最好在不让系统崩溃的情况下处理这个问题。

  2. 它们是确保在调用过程时获取下一个代码的有效方法，是 ID 列中按时间顺序排列的下一个代码。

  3. 是否还有其他我现在没有考虑的可能引发大问题的问题，以及处理这种情况的雄辩方式是什么？

我知道这是一个很长的开放式问题，我有一些编码的解决方案，但我觉得有更好的方法来获得我想要的结果。

一如既往地提前感谢所有帮助。

sql-server sql-server-2012

1 个回答

  1. Best Answer

    你身上没有任何东西SELECT决定了秩序。它也不受两个会话读取同一行的保护。要查看它不安全：

    1. 创建一个带有键列的一次性表。

      CREATE TABLE dbo.GeneratedIDs(ID INT PRIMARY KEY);

    2. 从两个不同的会话循环运行此代码：

      SET NOCOUNT ON;

DECLARE @DECLARE @CID INT, @CodeOUt VARCHAR(30);

SELECT @CID = CodeNumbersID, @CodeOut = CodeValue
  FROM dbFoo.dbo.CodeNumbers
  WHERE IsUsed = 0

UPDATE dbFoo.dbo.CodeNumbers
  SET IsUsed = 1
  WHERE CodeNumbersID = @CID 

INSERT dbo.GeneratedIDs SELECT @CID;

GO 100000

    3. 检查这些输出 - 它们会发生：

      消息 2627，级别 14，状态 1
      违反主键约束“PK_hexcode”。无法在对象“dbo.GeneratedIDs”中插入重复键。重复键值为 (<some value>)。

      或者，如果您将SELECT/包装UPDATE在显式事务中，您可能会看到死锁：

      消息 1205，级别 13，状态 52
      事务（进程 ID 57）在锁定资源上与另一个进程死锁，并已被选为死锁牺牲品。重新运行事务。

    要解决此问题，并确保您获得的 ID 是可用的最低 ID，您可以这样做：

    BEGIN TRANSACTION;

SELECT TOP (1) @CID = ...
FROM dbFoo.dbo.CodeNumbers WITH (XLOCK, HOLDLOCK)
WHERE IsUsed = 0
ORDER BY CodeNumbersID;

UPDATE ...

COMMIT TRANSACTION;

    请注意，我添加了显式事务和XLOCK/HOLDLOCK提示以防止两个同时会话读取同一行。当然，这会对并发性产生影响（不幸的是，这正是您在这里想要和需要的）。

    执行此操作的其他方法包括仅更新行，然后使用表变量从OUTPUT子句中捕获值：

    DECLARE @x TABLE(CodeOut VARCHAR(30), CID INT);

;WITH x AS 
(
  SELECT TOP (1) CodeNumbersID, CodeValue, IsUsed
    FROM dbFoo.dbo.CodeNumbers WITH (XLOCK, HOLDLOCK)
    WHERE IsUsed = 0
    ORDER BY CodeNumbersID
)
UPDATE x SET IsUsed = 1
  OUTPUT inserted.CodeValue, inserted.CodeNumbersID INTO @x;

SELECT @CodeOut = CodeOut, @CID = CID FROM @x;

    根据 Paul 的更新，是的，您也可以在没有 table 变量的情况下执行此操作：

    ;WITH x AS 
(
  SELECT TOP (1) CodeNumbersID, CodeValue, IsUsed
    FROM dbFoo.dbo.CodeNumbers WITH (XLOCK, HOLDLOCK)
    WHERE IsUsed = 0
    ORDER BY CodeNumbersID
)
UPDATE x 
  SET @CodeOut = x.CodeValue, @CID = x.CodeNumbersID, IsUsed = 1;

    （虽然我不是很喜欢这种语法；不知道为什么。可能是我总是忘记它存在的原因。）

    您可以将调用者更改为期望一个结果集而不是两个输出参数，但这也是额外的工作。在这两种情况下，您仍然需要确保获得最低的可用 ID，这可能意味着具有SELECT相同提示的 CTE。这里有一些讨论。我还在这篇博文中讨论了类似的方法，但我没有涉及任何关于并发和两个会话试图同时删除同一行的内容。显然，在这种情况下，只有他们中的一个可以获胜，但是如果UPDATE他们都可以成功（至少在理论上）。

    为了使事情更容易，您可以放宽“下一个”分发的 ID 是可用的最低 ID 的限制。但是您仍然需要通过提示进行隔离，以确保两个同时会话不会碰巧读取相同的值（无论排序如何都可能发生）。希望通过合适的索引，这些不会破坏并发性。

    • 9

相关问题