Website database duplicate records

Question

On every page of my website a token is passed in as a querystring parameter. The server-side code checks to see if the token already exists in the database. (The token is a uniqueidentifider field in the database). If the token exists then it will use the existing one, if not then it will create a new row with the new token.

The problem is once in a while I see a duplicate record in the database (two rows with the same uniqueidentifider). I have noticed the record insertion times were about half a second apart. My only guess is when the site is being visited for the first time the aspx pages weren't fully compiled. So it takes a few seconds and the user goes to another page of the site by typing in a different url and the two requests were executed almost at the same time.

Is there a way to prevent this duplicate record problem from happening? (on the server-side or in the database??...)

This is the code in questions that's part of every page of the website.

var record = (from x in db.Items
             where x.Token == token
             select x).FirstOrDefault();

if (record == null)
{
    var x = new Item();
    x.Id = Guid.NewGuid();
    x.Token = token;
    db.Items.InsertOnSubmit(x)
    db.SubmitChanges;

}

Answer 1

Yes, create a unique index on your token field.

create unique index tab_token on your_table(token);

This way, database will make sure you will never store two records with the same token value. Keep in mind that your code might fail when running this due to the index constraint, so make sure you are catching that exception in your code and treat it accordingly.

What is probably happening is that two request are being served at the exact same time and some racing conditions are causing two tokens getting the same value. It would help to identify your problem if you post some code.