Search code examples
mysqltransactionsinnodb

Select FOR UPDATE gives duplicate key error

I am using SELECT...FOR UPDATE to enforce a unique key. My table looks like:

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;

So rows might look like:

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

So note that 'Ace-Hammer' and 'Menards-Hammer' can have the same productKey, only the product+key combination needs to be unique. The requirement that it is an integer defined in this way is organizational, I don't think this is something I can do with auto_increment using innoDb, but hence the question.

So if a vendor creates a new version of an existing product, we give it a distinct key for that vendor/product combination (I realize the pkKey column is redundant in these examples).

My stored procedure is like:

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

That's all! During periods of heavy use, (1000s of users), I see: Duplicate entry 'Ace-Hammer-Ace-Hammer-44613' for key 'pkKey'.

I can retry the transaction, but this is not an error I was expecting and I'd like to understand why it happens. I could understand the row locking causing deadlock but in this case it seems like the rows are not locked at all. I wonder if the issue is with max() in this context, or possibly the table index. This sproc is the only transaction that is performed on this table.

Any insight is appreciated. I have read several MySql/SO posts on the subject, most concerns and issues seem to be with over-locking or deadlocks. E.g. here: When using MySQL's FOR UPDATE locking, what is exactly locked?

Solution

  • I'm a little embarrassed but it is a pretty obvious problem. The issue is that 'FOR UPDATE' only locks the current row. So you can UPDATE it. But I am doing an INSERT! Not an update.

    If 2 queries collide, the row is locked but after the transaction is complete the row is unlocked and it can be read. So you are still reading a stale value. To get the behavior I was expected, you'd need to lock the whole table.

    So I think auto-increment would work for me, although I need a way to get the last_inserted_id so I need to be in the context of a procedure anyway (I am using c# driver).