c# - 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.