I am developing a web application where I need to create user accounts; the user-id is the e-mail address of the user. User-ids and passwords are stored in a MySQL database.
To create the account, the user goes to a registration page, and enters his/her e-mail address and a password. If the e-mail address is not already in the database, then a new account is created. These are TWO database operations - first to check if the e-mail address is already in the database, and if not, then second to create a new user record with this new e-mail address.
This TWO step process leaves open the possibility that two accounts with same e-mail address could be created. So my question is: How to make this an atomic operation to remove this possibility.
I'm using PHP and MySQL.
Edit: Don't understand why this question has been put on hold. Programmers building web applications encounter this problem without fail, the problem is asking for a fail-safe algorithm and deals with common tools PHP & MySQL. Furthermore, an attempted solution has been explained which clearly does not work.
First, create a unique constraint on that column, to ensure that it'll never have more than one record with the same email address. If the application tries to insert a duplicate record, the insert query will fail.
Second, you'll probably want to use MySQL's INSERT IGNORE to suppress the error when the application attempts to insert a duplicate record. Then you can do the two queries in the opposite order: first do the INSERT IGNORE to create a record if there wasn't one already, and then SELECT whatever info you need based on the email address. If there was already a record for the same email address, the INSERT IGNORE will do nothing and the SELECT will find that pre-existing record.