时间戳

首页 > 解决方案 > 选择 FOR UPDATE 给出重复键错误

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

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

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

任何见解都值得赞赏。我已经阅读了有关该主题的几篇 MySql/SO 帖子，大多数问题和问题似乎与过度锁定或死锁有关。例如这里： 当使用 MySQL 的 FOR UPDATE 锁定时，究竟锁定了什么？

标签： mysqltransactionsinnodb

解决方案

实现“只有产品+按键组合需要唯一”，说

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' 而不是让它获取下一个 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外使用。

推荐阅读