I have this table:
User
id INT PK
login VARCHAR UNIQUE
I want to know the best way to handle UNIQUE index insertion (ex: new user creation)
1) I can simply INSERT and when a "duplicate entry is raised" and I catch the exception and handle it in the application
=> I'm not sure this is the best solution to do that
2) I can SELECT ... WHERE login = ... then INSERT when no record found or display an error if the select found something
=> This is not atomic, an insert can happen between SELECT and INSERT
3) I can start a transaction, SELECT FOR UPDATE, INSERT then COMMIT when no records found or ROLLBACK and display an error if the user already exists
=> This simply won't work since MySQL doesn't lock no existing lines... So it will certainly result in a deadlock
So what is the best way to handle this very simple study case?