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?

Answer 1

Can multiple users edit the same post at the same time? If not then you do not have a race condition unless some how a single user can submit multiple edits simultaneously.

Answer 2

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.

Answer 3

Update directly in the database and return the new revision:

update Revisions
set EditNumber += 1
output INSERTED.EditNumber
where PostID = @postId;

Unfortunately, this is not possible in LINQ. In fact, is not possible in the client at all, no matter the technology used, short of doing pessimistic locking which has too many drawback to worth considering.

Updated:

Here is how I would insert a new revision (including first revision):

create procedure usp_insertPostRevision
  @postId int,
  @text nvarchar(max),
  @revisionId bigint output

as 
begin
  set nocount on;
  declare @nextEditNumber (EditNumber int not null);
  declare @rc int = 0;

  begin transaction;
  begin try
    update Posts
    set LastRevision += 1
    output INSERTED.LastRevision
       into @nextEditNumber (EditNumber)
    where PostId = @postId;

    set @rc = @@rowcount;

    if (@rc <> 1)
      raiserror (N'Expected exactly one post with Id:%i. Found:%i', 
        16, 1 , @postId, @rc);

    insert into Revisions
      (PostId, Text, EditNumber)
    select @postID, @text, EditNumber
    from @nextEditNumber;

    set @revisionId = scope_identity();

    commit;    
  end try
  begin catch
   ... // Error handling omitted
  end catch
end

I omitted the error handling, see Exception handling and nested transactions for a template procedure than handles errors and nested transactions properly.

You'll notice the Posts table has a LastRevision field that is used as the increment for the post revisions. This is much better than computing the MAX each time you add a revision, as it avoid a (range) scan of Revisions. It also acts as a concurrency protection: only one transaction at a time will be able to update it, and only that transaction will proceed with inserting a new revision. Concurrent transactions will block and wait until the first one commits, then the next transaction unblocked will correctly update the revision number to +1.

Answer 4

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.

Answer 5

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.