Prevent unique constraint violations caused by concurrent inserts

Question

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.

Answer 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.)