How to avoid a database race condition when manually incrementing PK of new row

Question

I have a legacy data table in SQL Server 2005 that has a PK with no identity/autoincrement and no power to implement one.

As a result, I am forced to create new records in ASP.NET manually via the ole "SELECT MAX(id) + 1 FROM table"-before-insert technique.

Obviously this creates a race condition on the ID in the event of simultaneous inserts.

What's the best way to gracefully resolve the event of a race collision? I'm looking for VB.NET or C# code ideas along the lines of detecting a collision and then re-attempting the failed insert by getting yet another max(id) + 1. Can this be done?

Thoughts? Comments? Wisdom?

Thank you!

NOTE: What if I cannot change the database in any way?

Answer 1

Create an auxiliary table with an identity column. In a transaction insert into the aux table, retrieve the value and use it to insert in your legacy table. At this point you can even delete the row inserted in the aux table, the point is just to use it as a source of incremented values.

Answer 2

Not being able to change database schema is harsh.

If you insert existing PK into table you will get SqlException with a message indicating PK constraint violation. Catch this exception and retry insert a few times until you succeed. If you find that collision rate is too high, you may try max(id) + <small-random-int> instead of max(id) + 1. Note that with this approach your ids will have gaps and the id space will be exhausted sooner.

Another possible approach is to emulate autoincrementing id outside of database. For instance, create a static integer, Interlocked.Increment it every time you need next id and use returned value. The tricky part is to initialize this static counter to good value. I would do it with Interlocked.CompareExchange:

class Autoincrement {
  static int id = -1;
  public static int NextId() {
    if (id == -1) {
      // not initialized - initialize
      int lastId = <select max(id) from db>
      Interlocked.CompareExchange(id, -1, lastId);
    }
    // get next id atomically
    return Interlocked.Increment(id);
  }
}

Obviously the latter works only if all inserted ids are obtained via Autoincrement.NextId of single process.