0

I have a situation where I need to insert a unique record into the database:

  • SELECT user FROM users WHERE id = 'some_randomly_generated_id'
  • If user does not exist, INSERT INTO users WHERE id = 'some_randomly_generated_id'

The problem is, a matching record can be added after the SELECT query but before the INSERT query, causing a unique constraint error. How would I prevent/go about this? These are my current thoughts after some research:

  • Run these queries under a transaction and use SELECT user FROM users WHERE ... FOR SHARE to lock the row so no new record could be inserted (I might be wrong on this, please correct me).
  • Handle the unique constraint error in the application and pretend that the record is inserted successfully. (No transaction, could be better since it allows greater concurrency and is trivial to implement)
  • Run only the INSERT IGNORE query instead, which silently ignores the unique constraint error. (Similar to the 2nd approach in my opinion, just that the error is not handled in the application).

Thanks in advance. My dialect is MySQL and isolation level is REPEATABLE READS if that helps make answering this question easier.

1

Failure to wrap in a transaction is causing your problem.

do
    compute random number
    BEGIN;
    SELECT ... FOR UPDATE;
    if row exists, then start loop over
    INSERT ...;
    COMMIT;
until success

And don't worry about transaction isolation mode.

Or...

do
    compute random number
    INSERT ...;
until success

with autocommit = ON; no worry about isolation mode.

Or... Use an AUTO_INCREMENT column -- MySQL will deal with guaranteeing a unique id every time. (Caveat: A little more code is needed on multi-master topologies.)

4
  • Thanks for the answer. What do you think of just ignoring the violation and move on? – Brian Le Sep 22 '20 at 10:37
  • Seems like you would lose data. – Rick James Sep 22 '20 at 16:31
  • Maybe I didn't word it well. I mean to either send an error to the application in case of a violation or upsert depending on the situation. – Brian Le Sep 22 '20 at 22:38
  • @BrianLe - OK.. – Rick James Sep 22 '20 at 23:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.