MySQL: How to ensure thread safety and avoid duplicate key errors, when Primary Key is generated in-CodePudding

I have a table in which the primary key is a 20 character VARCHAR field that gets generated in PHP before getting inserted into the table. The key generation logic uses grouping and sequencing mechanism as given below.

SELECT
    SUBSTR(prod_code, 15) AS prod_num
FROM
    items
  , products
WHERE
    items.cat_type    = $category
    AND items.sub_grp = $sub_grp
    AND items.prod_id = products.prod_id
ORDER BY
    prod_num DESC LIMIT 1

The prod_num thus got is incremented in PHP and prefixed with a product code to create a unique primary key. However multiple users can do the transaction concurrently for same category and sub_group leading to same keys generated for those users. This may lead to duplicate key error, as its a unique primary key. What is the best way to handle such a situation?

CodePudding user response：

Don't use "Smart IDs".

Smart IDs were all the rage in the 1980s, and went out of fashion for several reasons:

The only requirement of a PK is that is has to be unique. A PK doesn't need to have a format, or to be sexy or good looking. Their specific sequence, case, or composition is not relevant and actually counter-productive.
They are not relational. Parts of the ID could establish a relationship with other tables and that can cause a lot of issues. This goes against Normal Forms defined in database design.

Now, if you still need a Smart ID, then create a secondary column (that can also be unique) and then populate it after the row is created. If you are facing thread safety issues, you can run a single deferred process that will assign nice looking values after a few minutes. Alternatively, you can implement a queue, that can resolve this is seconds.

CodePudding user response：

Agree with "The Impaler".

But if you decide to proceed that way: to handle your concurrency issue could be through a retry-mechanism. This is similar to how deadlocks are typically handled.

If the insertion fails because of violation of the unique primary key, just try again in PHP with a new key. Your framework might have retry functions already. Otherwise it's easy to implement yourself.