Locking rows correctly during user signup procedure

Question

I'm pretty new to using InnoDB whilst utilising their locking mechanisms and I've encountered an issue I'm not so sure about.

Imagine you are creating a general user signup system, so user tries to register and you do something like this to check if their username they wanted is available:

SELECT COUNT(*) FROM users WHERE username='$username';

Then you find out the username is available so you go ahead and do this:

INSERT INTO users(username,email) VALUES('$username','$email');

Now what happens that in the very unlikely, but possible case that some other process checked for the same username before you inserted them into the database and they got the ok - obviously we don't want to end up with two users with the same username do we!?

So I thought of using a FOR UPDATE lock to prevent this, something like:

autocommit=0;
SELECT COUNT(*) FROM users WHERE username='$username" FOR UPDATE;
INSERT INTO users(username,email) VALUES('$username','$email');
commit();

However I then realized, at least from what I understand that this wouldn't work because it would only lock the rows that the SELECT found; and since it is a COUNT it not referencing any specific rows, and even if you changed it to not using a COUNT it still wouldn't reference any rows as the username wouldn't be found.

If the above is right and it wouldn't work, the only thing I can think of is table level locking to prevent other processes from reading or writing to the table, but I know table level locks are very bad and should be avoided.

Is there a better way to do what I want to do without using table level locks?

Answer 1

You could try using a UNIQUE index on the username column. This way, if you attempt to INSERT a duplicate value for username, MySQL will keep the statement from executing and throw a duplicate error. Your program can catch this error (for details, see your client library's documentation) and ask the user to use a different username. You can distinguish a duplicate username from other error messages by the error code that MySQL returns to your client library.

Also make sure you use parameterized queries instead of substituting the username string directly into the SQL statement. Otherwise, malicious users will be able to inject arbitrary SQL commands.