Checking to see if entry exists before creating it still throws duplicate entry error

I have a table with the columns alias and item_id. Together this 2 fields create a unique index.

Then I have some code that has an alias and an item id and want to update a record for it (or create a record if it does not exist)

//get the current stats for this item
$stats = $model->query(
    'SELECT *, DATEDIFF(NOW(),decayed) as days_since_decay, DATEDIFF(NOW(),created) as days_active
    FROM popularity_views 
    WHERE item_id = '.$query[0][$model->alias]['id'].'
    AND alias = "'.$model->alias.'"'
);
if(empty($stats))
{
    //create new entry
    $insert = $model->query(
        'INSERT INTO popularity_views (item_id, views, alias, created, decayed) 
        VALUES ('.$query[0][$model->alias]['id'].', 1, "'.$model->alias.'", NOW(), NOW())'
    );
}

Somehow this code is getting called twice, I am not sure how. Even if it is called twice though it should not matter because as you can see, I check to make sure an entry does not exist before adding one.

However when loaded this page, (if an entry does not exist before loading the page) it tries to create an entry twice and throws a SQL Error 1062: Duplicate entry. This does not make sense to me. Also if the entry is there before the page loads it's开发者_StackOverflow中文版 smart enough not to try to add it at all.

Any ideas?

If this code is executing in two separate threads (like from two web requests), it's possible you have a race condition. That is, things happen in the following order:

1. Select in thread #1 executes
2. Select in thread #2 executes
3. Insert in thread #1 executes
4. Insert in thread #2 executes (returning error)

You can solve this problem by wrapping the sequence of queries in a transaction (only available with the InnoDB table type), or using REPLACE INTO instead of the separate SELECT and INSERT.

Personally, I'd use that last option (REPLACE), since it's likely to perform best, and reduces the code complexity, but there's many ways to do it.