Implementing my own NextID table

I have an application where I need the equivalent of a NextID table.  I need to make sure that any application that accesses the table will get a new incremented ID, and that no application will get the same ID.  I tried the following code and I get a deadlock on the SaveChanges line.  The commented out is where I call a stored procedure to do the same thing and it works just fine.

Any ideas?

public int GetNextIncidentID()
        {
            /*
            using (var uow = context.CreateUnitOfWork())
            {
                var query = new ProcedureQuery("NextID");
                return (int) uow.Calculate(query);
            }
            */
            
            using (var trans = new TransactionScope())
            {
                var newID = 0;
                using (var uow = context.CreateUnitOfWork())
                {
                    var currentID = uow.IncidentIDBases.First();
                    currentID.IncidentID++;
                    uow.SaveChanges();
                    newID = currentID.IncidentID;
                }
                trans.Complete();
                return newID;
            }
 
        
        }

The obvious difference between the two paths is the TransactionScope.  I would guess that there is already a transaction in progress and you have some sort of DTC issue.

However, we'd advise against using this idiom to create an incrementing sequence anyway.  First, using an entity has some overhead compared to a 'plain' database query.  Second, the code as written seems to have a concurrency bug despite the transaction scope, because the First() call will result in a SELECT without a 'for update' lock.  So other code could be querying the same table in the meantime, resulting in duplicate IDs.  You could get around this with locking on the client (as long as you have only one machine) or by passing the UPDLOCK table hint (if you're on SQL Server -- other databases have different conventions), but to be honest if all you want is a 'next id' table, without any additional business data, I would do it with raw ADO.NET rather than trying to force it into an entity paradigm (you can use IUnitOfWork.PrepareCommand to run a custom command on the UOW connection).  In fact this actually seems a really good fit for a stored procedure so I would probably go back to your original version IUnitOfWork.Calculate(ProcedureQuery) version!

Thanks for the detailed answer.  You brought up an interesting question.  What is the proper way to select an entity that will have an update lock?  I have been using first() -- uow.SomeEntity.First(x=>x.Foo == "bar")  like that, and then modifying and saving the entity.  Which way should I be doing it?

It depends on what you mean by an update lock.

What you're doing at the moment is perfectly correct.  It doesn't lock the database row, so other users can see the database row while the entity is being modified, but for business entities this isn't a big deal.  The entity data isn't use-once like your incrementing IDs.

If you want to make sure that two users working with the same entity at the same time don't save over each other's changes, you can use the Optimistic Concurrency Checking setting.  This doesn't actually take a lock, so User B can load the entity even while User A is editing it, but it does prevent conflicting updates.

If you need to lock a row at the database level to prevent other sessions from seeing the value until you have finished the overwrite -- which is what would be required for your NextID scenario -- then this is database-specific.  You could do this from LightSpeed on SQL Server by specifying WithTableHint("UPDLOCK").  Other databases use the syntax FOR UPDATE at the SQL level but we don't have a way of specifying this on an entity query because it's rare to lock business entities in this way, because it doesn't matter if two users both see the entity in the same state (that only matters for use-once data like your incrementing IDs).  For business entities it's almost always better to go with optimistic concurrency, whether checked or unchecked.

Caveat regarding database-level locking: this is my understanding but I'm not an expert on this -- I'll ask our SQL guru to take a look at this thread and correct me if I'm wrong!