Database Reference
In-Depth Information
Checking before inserting isn't enough
Our first instinct might simply be to check the
users
table to make sure there isn't already
a row with the requested
username
in the table. To keep things concise, we might do
something like the following:
SELECT "username" FROM "users" WHERE "username" = 'eve'
LIMIT 1;
This will return a single row containing only the
username
value, if the
username
value is taken:
If the username is available, no rows will be returned. While checking the availability of a
username is a good way to give the user early feedback that they need to choose a different
name, it's not enough to guarantee that we won't accidentally overwrite an existing row.
The reason is that this sequence of operations is subject to a race condition in the case
where two users are trying to sign up with the same name at roughly the same time. Con-
cretely, we will see the following sequence of events, with both "
eve
1" and "
eve
2" try-
ing to create an account with username
eve
at the same time:
1. The character
eve
1 sends a request to our application to create an account.
2. The character
eve
2 sends a request to our application to create an account.
3. The process creating
eve
1's account checks for an existing row with username
eve
. It finds that there are none.
4. The process creating
eve
2's account checks for an existing row with username
eve
. It also finds that there are none.
5. The process creating
eve
1's account issues an
INSERT
statement to create a new
row with username,
eve
.
6. The process creating
eve
2's account issues an
INSERT
statement to create a row
with username,
eve
, overwriting the row created for
eve
1.
