mysql - 选择FOR UPDATE给出重复的键错误

我正在使用SELECT ... FOR UPDATE强制执行唯一键。我的桌子看起来像：

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使用auto_increment进行的操作，但这是问题所在。

因此，如果供应商创建了现有产品的新版本，我们将为该供应商/产品组合提供一个独特的密钥（我意识到在这些示例中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

就这样！在大量使用期间（数千名用户），我看到：
密钥“ pkKey”的条目“ Ace-Hammer-Ace-Hammer-44613”重复。

我可以重试该事务，但这不是我所期望的错误，我想了解为什么会发生。我可以理解导致死锁的行锁定，但是在这种情况下，似乎行根本没有被锁定。我想知道问题是否在这种情况下与max（）有关，或者可能与表索引有关。此存储过程是对此表执行的唯一事务。

任何见解均表示赞赏。我已经阅读了有关该主题的多个MySql / SO帖子，大多数担忧和问题似乎与过度锁定或死锁有关。例如。此处：When using MySQL's FOR UPDATE locking, what is exactly locked?

最佳答案

要实现“仅产品和组合键需要唯一”，例如

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)。

现在，我不清楚您是否需要将“ Menards”和“ Ace”锤都设为121？摘要：

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

情况1：两者都必须为“ 121”。您需要某种方式来显式地插入具有现有auto-inc值的新行。这不是问题;您只需指定“ 121”，而不是让它获取下一个自动增量值。

情况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)

然后在SP外部使用@new_id。

关于mysql - 选择FOR UPDATE给出重复的键错误，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/53936831/