Getting a serial number from shared table

Question

I have a table that keeps track of a global serial number shared between several tables. This is not the PK or unique id for each table. It is like a case identifier. Every time users create a new entity we need to give them a unique serial number from this row. My worries is what happens if 2 users hit the method for incrementing this counter at the exact same time. Would this solution be enough?

Also this code would be run from 2 servers using the same database.

public string GetIdentifier()
{
    int retries = 0;
    bool success = false;

    string newId;

    do
    {
        try
        {
            var tracker = Trackers.Find(1);
            newId = tracker.Increment();
            SaveChanges();
            success = true;

        }
        catch (DbUpdateConcurrencyException)
        {
            Thread.Sleep(500);
            if (++retries == 5)
                throw;
        }
        catch (Exception)
        {
            //do some logging
            throw;
        }

    } while (!success);

    return newId;
}

Answer 1

You propably won't ever get the effect that 2 people create the same unique identifier at the same time. Still, it could possibly happen, most databases will handle this. If you create your db with unique values only in your table, it will automatically give you an error when 2 values are identic.

Answer 2

You do not need a flag to exit the loop when you can simply return from function. Also the retry counter may be simplified with a simple for.

To put everything together:

for (int i=0; i <= MaximumNumberOfRetries; ++i)
{
    try
    {
        var tracker = Trackers.Find(1);
        var newId = tracker.Increment();

        SaveChanges();
        return newId;
    }
    catch (DbUpdateConcurrencyException)
    {
        Thread.Sleep(GetRandomDelay());
    }
    catch (Exception)
    {
        //do some logging
        throw;
    }
}

I Introduced a GetRandomDelay() function, in the (maybe rare) case that three threads/processes/servers are processing a batch of insertions then it's better to de-synchronize them (otherwise the one that will first wait will maybe wait until all the others finished).

---

What you're currently doing is something like this:

UPDATE Trackers
SET SerialNumber = @NewValue
WHERE SerialNumber = @OldValue

It's what EF does in your case and it works because you're updating the field to @NewValue only if the existing value is what you expected @OldValue (what you read). If someone else was updating the DB then value changed and you should retry (just check the number of affected rows as result of this command, EF throws DbUpdateConcurrencyException).

There is, however, another easier way which is atomic on the DB and it will take care of concurrency for you (because of the write lock acquired on the table when writing):

UPDATE Trackers
SET SerialNumber = SerialNumber + 1
OUTPUT INSERTED.SerialNumber    

In this case result of your query is the new serial number to use. Note that you have to write some SQL code (or a stored procedure) for this, it's not something you can (AFAIK) do directly from EF but using Database.SqlQuery().