Insert the same time, it is possible to happen? Good evening.

I have a doubt about inclusion in the database. A common example is a user account where the user names must be unique.

In the following logic:


User enters data into a form.
Data are collected via $ _POST (or other method)
Before saving the data is checked if the user name is already registered
If not, save the record, if it is, it informs the user that the username that already exists.


Example:

&lt;?php
$username = $_POST['username'];
$query = "SELECT * FROM `user_tbl` WHERE `username` = '{$username}'";
$result = mysql_query($query);
if ( mysql_num_rows ( $result ) &gt; 1 ) {
    /* Username already exists */
    echo 'Username already exists';
} else {
    /* Username doesn't exist */
    /* a certain time is elapsed after checking */
    /* .. insert query */
}


My question is, in a system with high volume of requests, it is possible that between the time to check whether the user name already exists and the inclusion (if the user name does not exist), another user can do the same action in same time?
Theoretically you could end up with duplicate usernames. 
In a scenario of high incoming requests, a race condition could occur. 

If you want to avoid this, just put an UNIQUE index on your column. 
You could create a UNIQUE index on username by using this query:

CREATE UNIQUE INDEX IDX_USERNAME ON `user_tbl` (`username`);


Prior to creating this unique index, please verify that there are no duplicate entries in your table, as the index creation would fail. 

When you have an unique index on this field, it will be impossible to end up with duplicate usernames. 
When a username is inserted, the table is locked during the insertion, this guarantees that a race condition would not occur.