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'
userdoes 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 SHAREto 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 IGNOREquery 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.