Avoiding race-condition when manually implementing IDENTITY-like increment for a SQL Server DB column

Question!

I'm building an ASP.NET MVC 2 site that uses LINQ to SQL. In one of the places where my site accesses the DB, I think a race condition is possible.


DB Architecture

Here are some of the columns of the relevant DB table, named Revisions:

  • RevisionID - bigint, IDENTITY, PK
  • PostID - bigint, FK to PK of Posts table
  • EditNumber - int
  • RevisionText - nvarchar(max)

On my site, users can submit a Post and edit a Post later on. Users other than the original poster are able to edit a Post - so there is scope for multiple edits on a single Post simultaneously.

When submitting a Post, a record in the Posts table is created, as well as a record in the Revisions table with PostID set to the ID of the Posts record, RevisionText set to the Post text, and EditNumber set to 1.

When editing a Post, only a Revisions record is created, with EditNumber being set to 1 higher than the latest edit number.

Thus, the EditNumber column refers to how many times a Post has been edited.


Incrementing EditNumber

The challenge that I see in implementing those functions is incrementing the EditNumber column. As that column can't be an IDENTITY, I have to manipulate its value manually.

Here's my LINQ query for determining what EditNumber a new Revision should have:

using(var db = new DBDataContext())
{
    var rev = new Revision();
    rev.EditNumber = db.Revisions.Where(r => r.PostID == postID).Max(r => r.EditNumber) + 1;

    // ... (fill other properties)

    db.Revisions.InsertOnSubmit(rev);
    db.SubmitChanges();
}

Calculating a maximum and incrementing it can lead to a race condition.

Is there a better way to implement that function?



Answers

Since EditNumber is a property determined by membership in a collection, have the collection provide it.

Make EditNumber a computed column - COUNT of records for same post with lesser RevisionID.

By : David B


If revisions are only permitted by the user who submitted the comment then you're OK with the above - if multiple users can be revising a single comment then there's scope for problems.

By : Will A


Since there is only one record in the Posts table per Post, use a lock.

Read the record in the Posts table and use a table hint [WITH (ROWLOCK, XLOCKX)] to get an exclusive lock. Set the lock timeout to wait a few milliseconds.

If the process gets the lock, then it can add the revision record. If the process cannot get the lock, then have the process try again. After a few retries if the process cannot get a lock, return an error.



This video can help you solving your question :)
By: admin