select-for-update出现重复密钥错误

hrysbysz 于 2021-06-17 发布在 Mysql

关注(0)|答案(2)|浏览(268)

我正在使用select…for update强制执行唯一密钥。我的table看起来像：

CREATE TABLE tblProductKeys (
  pkKey varchar(100) DEFAULT NULL,
  fkVendor varchar(50) DEFAULT NULL,
  productType varchar(100) DEFAULT NULL,
  productKey bigint(20) DEFAULT NULL,
  UNIQUE KEY pkKey (pkKey,fkVendor,productType,productKey)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

所以行可能看起来像：

{'Ace-Hammer','Ace','Hammer',121}, 
{'Ace-Hammer','Ace','Hammer',122},
... 
{'Menards-Hammer','Menards','Hammer',121},
...

因此请注意，“ace-hammer”和“menards-hammer”可以具有相同的productkey，只有product+key组合需要唯一。以这种方式定义的整数的要求是有组织的，我不认为我可以用innodb实现自动增量，但这就是问题所在。
因此，如果一个供应商创建了一个现有产品的新版本，我们就为该供应商/产品组合给它一个不同的键（我意识到在这些示例中pkkey列是多余的）。
我的存储过程如下：

CREATE PROCEDURE getNewKey(IN vkey varchar(50),vvendor varchar(50),vkeyType varchar(50)) BEGIN
start transaction;

set @newKey=(select max(productKey) from tblProductKeys where pkKey=vkey and fkVendor=vvendor and productType=vkeyType FOR UPDATE);
set @newKey=coalesce(@newKey,0);
set @newKey=@newKey+1;
insert into tblProductKeys values (vkey,vclient,vkeyType,@newKey);

commit;
select @newKey as keyMax;
END

这就是全部！在大量使用期间（1000个用户），我看到：重复输入“ace-hammer-ace-hammer-44613”作为键“pkkey”。
我可以重试事务，但这不是我所期望的错误，我想了解为什么会发生这种情况。我可以理解行锁定导致死锁，但在这种情况下，行似乎根本没有被锁定。我想知道问题是在这个上下文中的max（）还是表索引。此存储过程是在此表上执行的唯一事务。
任何见解都是值得赞赏的。我读过几篇关于这个主题的mysql/so文章，大多数关注点和问题似乎都是过度锁定或死锁。e、这里：当使用mysql进行更新锁定时，到底锁定了什么？

mysql innodb transactions

来源：https://stackoverflow.com/questions/53936831/select-for-update-gives-duplicate-key-error

2条答案

按热度按时间

ljsrvy3e1#

要实现“只有产品+键的组合才需要独特”，比如

UNIQUE(pkKey, productKey)

以任何顺序。然后，你的4列 UNIQUE 是多余的。它可以变成平原 INDEX 如果需要某些特定的查询。
此外，你真的应该有一个 PRIMARY KEY . 也可能是

PRIMARY KEY(pkKey, productKey)  -- in either order

然后把我的建议扔掉 UNIQUE 钥匙。
没有充分的理由 productKey 取决于pkkey，如果这是你想的。相反，只要做

productKey INT UNSIGNED AUTO_INCREMENT

至少应该有 INDEX(productKey) .
现在，我不清楚你是否需要把“梅纳德”和“王牌”的锤子都定为121号？总结：

PRIMARY KEY(pkKey, productKey),
INDEX(productKey)

案例1：两者都必须是“121”。您需要某种方法来显式插入一个具有现有auto inc值的新行。这不是问题；只需指定“121”，而不是让它获取下一个auto inc值。
情况2：没有必要两者都是“121”。然后简单地使用 AUTO_INCREMENT :

PRIMARY KEY(productKey)

但是，如果您真的喜欢您的sp，让我们将其缩短为一条语句，甚至可以忽略事务：

BEGIN;
    INSERT
         INTO  tblProductKeys 
    SELECT  vkey, vclient, vkeyType,
            @new_id := COALESCE(MAX(productKey) + 1, 0)
        FROM  tblProductKeys
        WHERE  pkKey = vkey
          AND  fkVendor = vvendor
          AND  productType = vkeyType;
END //

现在，你需要

INDEX(pkKey, fkVendor, productType,  -- in any order
      productKey)                    -- last
PRIMARY KEY(pkKey, productKey)  -- in either order (as previously discussed)

然后使用 @new_id 在sp之外。

赞(0）回复(0）举报 2021-06-18

6rqinv9w2#

我有点不好意思，但这是个很明显的问题。问题是“for update”只锁定当前行。所以你可以更新它。但我正在插播！不是最新消息。
如果两个查询发生冲突，则该行将被锁定，但在事务完成后，该行将被解锁并可以读取。所以您仍然在读取一个过时的值。要达到我预期的效果，你得把整个table都锁上。
所以我认为自动递增对我来说是可行的，尽管我需要一种方法来获取最后插入的#id，所以无论如何我都需要在一个过程的上下文中（我使用的是c#driver）。

赞(0）回复(0）举报 2021-06-17

我来回答

select-for-update出现重复密钥错误

2条答案

相关问题

热门标签

最新问答